Comandos SQL avançados

Prévia do material em texto

21
Aula 02
COMANDOS
SQL AVANÇADO
1. O OPERADOR DE CONCATENAÇÃO 
O operador de concatenação ( || ), pode ser utilizado para unir dados de colunas, no 
Firebird você pode criar novas colunas em uma instrução SELECT. 
Apenas as colunas CHAR E VARCHAR suportam concatenação. 
Estaremos utilizando o seguinte diagrama de relacionamento Físico de Dados: 
425
22
 Sendo a tabela (entidade) Vendedor contém os seguintes registros:
Exemplo 01: Iremos fazer uma seleção na tabela (entidade) VENDEDOR aonde 
iremos concatenara as colunas (campos) Nome do vendedor e seu telefone, onde utilizaremos 
também o comando LIKE buscando todos os vendedores que começam com o nome “LOU”, 
o resultado será em uma única coluna (campo).
SELECT NOMEVEND|| ‘ ‘ || TELEFONE 
FROM VENDEDOR
WHERE NOMEVEND LIKE ‘%LOU%’
426
23
Exemplo 02: Neste segundo exemplo estamos incrementando os separadores entre as 
colunas (campos), acrescentando mais uma coluna na concatenação o salário. Vamos colocar 
um nome na nova coluna derivada através do operador AS no qual irá especifi car um apelido 
para a coluna resultante.
SELECT NOMEVEND|| ‘ - ‘ || TELEFONE || ‘ - R$ ‘ || SALARIO AS 
VENDEDOR
FROM VENDEDOR
WHERE NOMEVEND LIKE ‘%LOU%’
 
Temos os seguintes dados na tabela (entidade) SETOR:
 
Exemplo 03: Iremos fazer uma nova concatenação, mas agora adicionaremos uma 
nova entidade o SETOR. Faremos uma junção (União) entre as entidades VENDEDOR E 
SETOR. Temos em comum entre as entidades a chave estrangeira na entidade VENDEDOR 
(CODSETOR), e chave primária na entidade SETOR (CODSETOR). Somente iremos 
selecionar os vendedores do setor 1 (informática).
SELECT C.NOMEVEND|| ‘ - ‘ || C.TELEFONE || ‘ - R$ ‘ || C.SALARIO || ‘ - ‘ || 
D.NOMESETOR AS VENDEDOR_SETOR
FROM VENDEDOR C JOIN SETOR D
ON C.CODSETOR = D.CODSETOR
427
24
WHERE D.CODSETOR =1
 
1.1 Passagem de Parâmetro
No Banco de Dados Firebird podemos passar parâmetros para as colunas (campos) 
durante a comparação, ou seja, em vez de utilizarmos um campo fi xo, podemos entrar com 
um valor diferente a cada execução da consulta, atualização ou deleção. Isto torna esta 
comparação mais dinâmica, podendo assim testar várias possibilidades, sem fi car alterando o 
formato de comparação do comando SQL. 
Exemplo 04: Selecionar o Código e o nome do vendedor cujo nome comece por um 
nome parcial e informado por um parâmetro.
SELECT CODVEND, NOMEVEND FROM VENDEDOR
WHERE NOMEVEND LIKE ‘%’||:NOMEVEND||’%’
 
O Cursor fi ca parado esperando o usuário entrar com qualquer caractere, neste 
exemplo entrou com “AN”. Como se trata de uma busca parcial de caracteres, ele irá pegar 
todos os nomes ou partes dos nomes cadastrados que tenham “AN”, veja o resultado:
428
25
Exemplo 05: Selecionar o Código e o nome do vendedor que trabalhem em um setor informado:
SELECT C.CODVEND, C.NOMEVEND, D.NOMESETOR AS SETOR
FROM VENDEDOR C JOIN SETOR D
ON C.CODSETOR = D.CODSETOR
WHERE D.CODSETOR =:SETOR
Se o usuário selecionasse o setor = 4, logo teríamos o seguinte resultado:
Exemplo 06: Selecionar o Código, nome, telefone e estado de um cliente que moram 
em uma cidade informada:
429
26
SELECT C.CODCLI, C.NOME, C.TELEFONE, D.UF, D.NOMECID AS 
CIDADE
FROM CLIENTE C JOIN CIDADE D
ON C.CODCID = D.CODCID
WHERE D.CODCID =:CIDADE
Se o usuário selecionasse a cidade = 1, logo teríamos o seguinte resultado:
 
2. CONSULTAS UTILIZANDO FUNÇÕES
Funções retornam o valor resultante de uma determinada operação realizada sobre um 
argumento ou uma série de argumentos. O Firebird oferece um grupo de funções denominadas 
funções agregadas. As funções agregadas são funções que tomam uma coleção (conjunto e 
subconjunto) e valores como entrada, retornando um valor simples. 
2.1 Funções Agregadas
Funções agregadas são assim denominadas pela habilidade que têm em agregar 
valores. Sendo a sua principal característica é a de produzirem um único valor a partir 
dos valores obtidos das colunas. A função de agregar permite que você colete valores de 
várias tuplas (linhas) em uma tupla (linha) única. Realizar essa operação, permite que 
você veja as tendências que podem não ser visíveis de outra forma entre milhares ou 
milhões de tuplas (linhas). 
2.1.1 Consultas com AVG
A função AVG permite retornar a média aritmética de um grupo de tuplas (linhas). 
Somente os valores numéricos podem ser utilizados como agrupamento desta função, se 
nenhuma linha é retornada, a função retorna o valor NULL. Sendo que os valores nulos ou 
desconhecidos são ignorados. A função AVG aceita a cláusula DISTINCT para eliminar 
valores duplicados.
Exemplo 06: Verifi car a médias dos salários pagos a todos os vendedores da 
empresa.
SELECT AVG (SALARIO) AS MEDIA_SALARIO FROM VENDEDOR
 
Neste mesmo exemplo se não desejarmos que os valores de salários duplicados 
entrem no cálculo da média:
430
27
SELECT AVG (DISTINCT SALARIO) AS MEDIA_SALARIO FROM 
VENDEDOR
 
Exemplo 07: Verifi car a média dos salários pagos aos vendedores do setor 1.
SELECT AVG (SALARIO) AS MEDIA_SALARIO FROM vendedor
WHERE CODSETOR = 1
 
2.1.2 Consultas com MAX e MIN
A função Max retorna o máximo valor entre as tuplas e a função Min retorna o 
mínimo valor entre as tuplas. Podemos também utilizar a cláusula DISTINCT para eliminar 
valores duplicados antes do valor máximo e mínimo. Estas funções aceitam argumentos 
numéricos ou do tipo texto.
Exemplo 08: Verifi car o máximo e o mínimo salário pago aos vendedores do setor 1.
SELECT MAX (SALARIO) AS SAL_MAX, MIN (SALARIO) AS SAL_MIN 
FROM VENDEDOR
WHERE CODSETOR = 1
 
2.2 Funções não Agregadas
As funções não agregadas operam sobre cada tupla (linha) individualmente.
2.2.1 Consultas com UPPER e LOWER
A função UPPER converte caracteres minúsculos para maiúsculos, já a função 
LOWER passa todos os caracteres para minúsculo.
Exemplo 09: Passar para maiúsculo todas os e-mail não nulos ou vazios da entidade 
cliente:
 
SELECT UPPER (EMAIL) AS EMAIL FROM CLIENTE
WHERE EMAIL IS NOT NULL
431
28
 Exemplo 10: Converter para minúsculo todos os nomes e endereços dos clientes da 
entidade Cliente:
SELECT LOWER (NOME) AS NOME, LOWER (ENDERECO) AS ENDERECO 
FROM CLIENTE
 
2.2.2 Consultas com SUBSTRING
Podemos fazer consultas com SUBSTRING, ou seja pegar somente algumas partes 
desejadas das colunas (campo) selecionadas.
Sintaxe: SELECT SUBSTRING(‘nome da coluna’ FROM ‘coluna_inicial’ FOR 
‘caracteres’) FROM ‘Table_name’
Exemplo 11: Mostre somente os 10 primeiros caracteres da coluna (campo) nome da 
tabela Clientes:
SELECT SUBSTRING (NOME FROM 1 FOR 10) AS UTILIZACAO_SUBSTRING 
FROM CLIENTE
432
29
 Note que o espaço em branco também foi incluído na conta dos caracteres de 1 até 10.
2.2.3 Consultas com EXTRACT
A função EXTRACT no Firebird trata de manipulação dos dados do tipo DATE.
YEAR – Extrai o ano de uma data
MONTH – Extrai o mês de uma data
DAY – Extrai o dia de uma data
Temos em nossa tabela o campo DT_NASC, onde são apresentadas todas as datas de 
Aniversário dos funcionários.
 
Exemplo 12: Extrair todos os valores distintos do aniversário dos funcionários 
retornando somente os anos e meses de todos os funcionários:
SELECT DISTINCT (EXTRACT(YEAR FROM DT_NASC)) AS ANO,
(EXTRACT(MONTH FROM DT_NASC)) AS MES
FROM VENDEDOR
ORDER BY ANO,MES
 
3. ENTENDENDO OS JOINS
Quando utilizamos o JOINS estamos unindo as tabelas em uma instrução SQL, 
fazendo assim um relacionamento entre elas. Nós vimos os comandos INNER JOIN onde 
somente foram incluídas as linhas (tuplas) que satisfazem à condição do JOIN.
Vamos acrescentar mais duas tabelas (entidade):
433
30
Para os exemplos seguintes, temos as entidades PEDIDO, CLIENTE e ITEMPEDIDO, 
que reproduzem o clássico relacionamento pai-fi lho:
A entidade PEDIDO possui uma relação de um-para-muitos com a tabela 
ITEMPEDIDO, através do campo NUMPED. A entidade PEDIDO possui uma relação de 
um-para-um com a tabela CLIENTE, através do campo CODCLI.
3.1 INNER JOIN
O INNER JOIN que geralmente é a maneira mais utilizada para consultar entreentidades, funciona seguindo o princípio de que para as tuplas retornadas de uma entidade, 
deve haver algum tipo de relação com as tuplas da outra tabela. No caso da igualdade, 
somente os registros das duas tabelas, que possuírem o mesmo valor para uma determinada 
tupla, são retornados. 
Exemplo 13: Selecionar todos os pedidos do cliente 1, retornando o produto e 
quantidade pedida por ele.
SELECT C.NUMPED,D.CODPROD, D.QTIDADE
FROM PEDIDO C JOIN ITEMPEDIDO D
ON C.NUMPED = D.NUMPED
WHERE C.CODCLI=1
 
434
31
Percebam no exemplo acima que somente os registros que contiverem o mesmo 
valor para o campo NUMPED nas duas tabelas são retornados. 
3.2 OUTER JOIN
O OUTER JOIN é um tipo de JOIN que possui o funcionamento um pouco diferente. 
Neste tipo de seleção em que são restritas as tuplas que interessam em uma entidade, mas 
são consideradas todas as tuplas de outra entidade. Ou seja, queremos ver quais tuplas de 
uma entidade estão relacionadas com a outra entidade e quais tuplas não estão. Um exemplo 
prático seria qual o cliente tem pedidos e quais não têm nenhum pedido. Note que este tipo de 
JOIN é de muita utilidade quando queremos verifi car se as PK e SK não estão em sincronia. 
Geralmente, este tipo de JOIN pode ser utilizado em duas entidades. 
A opção LEFT OUTER JOIN, são incluídas todas as tuplas da entidade do primeiro 
nome da entidade (a entidade mais à esquerda da expressão).
A opção RIGHT OUTER JOIN, são incluídas todas as tuplas da entidade do segundo 
nome da entidade das expressão (entidade mais à direita da expressão).
Como podemos alterar a ordem na qual colocamos a entidade na instrução, há uma 
equivalência em termos de funcionalidade para estas opções de Outer Join. Importante: esta 
ordem é em relação às tabelas, e não à comparação feita após a palavra chave ON.
Exemplo 14: Listar todos os PEDIDOS que tenham relação com a entidade de 
ITEMPEDIDO e também os PEDIDOS que não tenham relação com nenhum ITEMPEDIDO 
e que sejam do cliente 01:
SELECT C.NUMPED,D.CODPROD, D.QTIDADE
FROM PEDIDO C LEFT OUTER JOIN ITEMPEDIDO D
ON C.NUMPED = D.NUMPED
WHERE C.CODCLI=1
435
32
Vejam que para as tuplas em que a relação de igualdade foi encontrada, os campos 
de ambas as tabelas são retornados corretamente. Nas tuplas em que nenhum item de pedido 
foi encontrado, é colocado o valor NULL para todos os campos da tabela ITEMPEDIDO.
Exemplo 15: Quais são os clientes que tem pedido e os que não tem pedido.
SELECT C.CODCLI, C.NOME, D.NUMPED, D.CODCLI
FROM CLIENTE C LEFT OUTER JOIN PEDIDO D
ON C.CODCLI = D.CODCLI
3.3 Cláusula UNION
Às vezes é necessário combinar os resultados de duas ou mais consultas feitas sobre 
entidades. Para realizar esta operação, temos o operador UNION que é uma listagem contendo 
os resultados das consultas combinadas.
Exemplo 16: Listar o código e nome dos clientes que possuem email nulo e dos 
vendedores que possui salário maior que 1000.
SELECT CODCLI AS CODIGO, NOME AS NOME FROM CLIENTE
WHERE EMAIL IS NULL
UNION
SELECT CODVEND, NOMEVEND FROM VENDEDOR
WHERE SALARIO > 1000
 
436
33
Neste caso temos que observar que as duas listas de colunas (campos) que utilizamos 
neste SELECT contêm o mesmo número de itens (duas colunas) e os tipos de dados são 
compatíveis.
ATIVIDADES 
As atividades referentes a esta aula estão disponibilizadas na ferramenta 
“Atividades”. Após respondê-las, envie-nas por meio do Portfólio- ferramenta do 
ambiente de aprendizagem UNIGRAN Virtual. Em caso de dúvidas, utilize as ferramentas 
apropriadas para se comunicar com o professor.
437