SQL-Consultas

Prévia do material em texto

Prof. Luiz Vivacqua
Projeto de Banco de Dados
Banco de Dados II
Prof. Luiz Antônio Vivacqua C. Meyer
(luiz.vcm@gmail.com)
Prof. Luiz Vivacqua
SQL - CONSULTAS
❑Sintaxe Geral
SELECT [ALL/DISTINCT] coluna1, coluna2, ..., colunaN
FROM nome_tabela1, nome_tabela2, ..., nome_tabelaN
[WHERE predicado]
[GROUP BY coluna1, coluna2, ..., colunaX]
[HAVING predicado]
[ORDER BY coluna1[asc/desc], coluna2[asc/desc], ..., colunaY[asc/desc]];
Prof. Luiz Vivacqua
SQL - CONSULTAS
Peça
Fornecedor
Fornecimento
Prof. Luiz Vivacqua
SQL - CONSULTAS
Recuperação Simples: Obter a identificação de todas as 
peças fornecidas.
Select id_peca
from fornecimento;
Prof. Luiz VivacquaProf. Luiz A. Vivacqua C. Meyer
SQL - CONSULTAS
Recuperação Simples sem ocorrência de duplicatas.
Obter o código dos fornecedores que fornecem peça
Select Distinct id_peca From fornecimento;
Recuperação simples de toda a tabela. Obter todas as 
informações dos fornecedores
Select * From Fornecedor;
Prof. Luiz Vivacqua
SQL - CONSULTAS
Recuperação qualificada: Obter a identificacao dos fornecedores de 
SP com status > 20.
Select codigo, nome 
From fornecedor
Where Cidade =’sp’ and status > 20;
Obs: A condição ou predicado que segue WHERE pode incluir os 
operadores comparativos =, >, <, >=, <=, <> e os operadores lógicos 
AND, OR, NOT.
Recuperação com Ordenação: Obter a identificação e o 
status de fornecedores de SP, na ordem decrescente de status.
Select codigo, Status
From fornecedor Where cidade = ‘sp’
Order by status desc;
Prof. Luiz Vivacqua
SQL - CONSULTAS
CONSULTA COM JUNÇÃO : RECUPERA DADOS DE 
MAIS DE UMA TABELA.
Obter o nome dos fornecedores que fornecem peças.
Select distinct nome
From Fornecedor, Fornecimento
Where Fornecedor.codigo=id_forn;
Junção com condição adicional: Obter o nome do fornecedor 
que fornece a peça P3.
Select distinct nome
From Fornecedor, Fornecimento
Where Fornecedor.codigo=id_forn 
and id_peca=´p3´;
Prof. Luiz Vivacqua
SQL - CONSULTAS
Junção de três tabelas: Obter o nome das peças que são fornecidas 
por fornecedores do RJ
Select Distinct P.nome
From fornecedor S, peca P, fornecimento SP
Where S.codigo = id_forn 
and P.codigo = id_peca 
and S.cidade=´rio´;
Inner Join
Obter o nome dos fornecedores que fornecem peças.
Select distinct nome
From Fornecedor INNER JOIN Fornecimento 
ON fornecedor.codigo = id_forn;
Prof. Luiz Vivacqua
SQL - CONSULTAS
LEFT JOIN
Obter o nome dos fornecedores e 
a qtde fornecida de peças 
inclusive dos que não fazem fornecimento
Select nome, qtde
From Fornecedor LEFT JOIN Fornecimento 
ON fornecedor.codigo = id_forn;
RIGHT JOIN – O inverso do Left join
Obter o nome das peças e o nome dos 
fornecedores na mesma cidade inclusive 
os fornecedores em outras cidades
select P.nome, F.nome 
from peca P RIGHT JOIN fornecedor F 
ON P.cidade=F.cidade
Prof. Luiz Vivacqua
SQL - CONSULTAS
Full Join – Left Join + Right Join
Select P.nome, F.nome, P.cidade, F.cidade 
from peca P FULL JOIN fornecedor F 
ON P.cidade=F.cidade
Prof. Luiz Vivacqua
SQL - CONSULTAS
Funções embutidas
COUNT – retorna o número de linhas na tabela.
SUM – retorna a soma dos valores na coluna que tem que ser 
numérica.
AVG – retorna a média de valores na coluna que tem que ser 
numérica.
MAX – retorna o maior valor na coluna.
MIN – retorna o menor valor na coluna. 
Obter o número total de fornecedores.
Select count(*) From fornecedor;
Prof. Luiz Vivacqua
SQL - CONSULTAS
Obter o número de fornecedores que fornecem peças
Select count(DISTINCT id_forn)
From fornecimento;
Obter o número de fornecimentos para a peça P2.
Select count(*) 
From Fornecimento
Where id_peca = ‘p2’
Prof. Luiz Vivacqua
SQL - CONSULTAS
Obter a quantidade total de peças P2 fornecidas.
Select SUM(Qtde)
From fornecimento
Where id_peca = ‘p2’;
Uso do Group By
A cláusula group by pode ser usada em conjunto com 
funções de forma de agrupar o resultado segundo uma 
ou mais colunas.
Prof. Luiz Vivacqua
SQL - CONSULTAS
Obter a quantidade total de cada peça fornecida.
Select id_peca, SUM(Qtde)
From fornecimento
Group by id_peca;
Uso do having
A cláusula having funciona para os Grupos assim como o
Where para linhas.
Prof. Luiz Vivacqua
SQL - CONSULTAS
Obter a identificação da peça e a soma das quantidades fornecidas 
de todas as peças fornecidas com qtde > 500.
Select id_peca, SUM(Qtde)
From fornecimento
Group by id_peca
Having SUM(Qtde) > 500;
Prof. Luiz Vivacqua
SQL - CONSULTAS
Recuperação com Like
_ vale para qq caracter
% vale para qq sequência de n caracteres onde n pode ser 0.
Negação – Not Like
Obter a identificação e a cor das peças cujos nomes começam 
com a letra “R”.
Select codigo, cor
From Peca
Where nome like ‘r%';
Prof. Luiz Vivacqua
SQL - CONSULTAS
Obter a identificação, peso e cor das peças cujos nomes começam 
com a letra “r”, tem “b” na terceira letra e terminam com “e”.
Select codigo, peso, cor
From Peca
Where nome like ‘r_b%e';
Recuperação com NULL
Usado para testar a presença ou ausência de valores nulos.
Obter a identificação dos fornecedores com status igual a nulo.
Select codigo
From fornecedor
Where status IS NULL;
Prof. Luiz Vivacqua
SQL - CONSULTAS
Recuperação com Subconsulta
❑Subconsulta é uma expressão SELECT FROM WHERE aninhada dentro de 
uma cláusula WHERE ou HAVING. 
❑É executada uma vez antes da execução da consulta externa
Obter os nomes dos fornecedores que fornecem a peça P2.
Select distinct nome
From fornecedor
Where codigo IN (Select id_forn
From fornecimento 
Where id_peca = ‘p2’);
Prof. Luiz Vivacqua
SQL - CONSULTAS
Subconsulta com vários níveis de aninhamentos.
Obter os nomes dos fornecedores que forneçam pelo menos 
uma peça verde.
Select distinct nome
From fornecedor
Where codigo IN (Select id_forn
From fornecimento 
Where id_peca IN (Select codigo
From Peca
where cor = 'verde')); 
Prof. Luiz Vivacqua
SQL - CONSULTAS
Subconsulta c/ operador diferente de IN
Obter a identificação e o nome dos fornecedores localizados na 
mesma cidade que o fornecedor S1
Select codigo, nome
From fornecedor
Where cidade = (Select cidade
From fornecedor
Where codigo = ‘s1’);
Prof. Luiz Vivacqua
SQL - CONSULTAS
Função numa subconsulta
Obter o nome e a cidade dos fornecedores cujo status seja inferior 
ao maior status existente.
Select nome, cidade
From fornecedor
Where status < (Select MAX(status)
From fornecedor);
Prof. Luiz Vivacqua
SQL - CONSULTAS
Subconsulta correlacionada:
❑Uma subconsulta correlacionada referencia uma ou mais colunas
na instrução SQL externa.
❑Usado quando se precisa de uma resposta para uma pergunta que
depende de um valor em cada linha contida em uma consulta 
externa
❑É executada uma vez para cada linha na consulta externa
❑O operador EXISTS verifica a existência de linhas retornadas por uma 
Subconsulta. Embora possa ser usado em subconsultas não correlacionadas,
geralmente é usado neste tipo de subconsulta.
Prof. Luiz Vivacqua
SQL - CONSULTAS
Obter o nome dos fornecedores que fizeram fornecimento
Select codigo, nome from fornecedor T1
Where EXISTS (select 1
from fornecimento T2
where T2.id_forn = T1.codigo);
Obs: Como EXISTS apenas verifica
A existência de linhas retornadas pela 
subconsulta, uma subconsulta não 
precisa retornar uma coluna, ela pode
retornar um valor literal, melhorando
o desempenho
Prof. Luiz Vivacqua
SQL - CONSULTAS
Obter o nome dos fornecedores que não fizeram fornecimento
select nome from fornecedor T1
where NOT EXISTS (select 1 
FROM fornecimento T2
where T1.codigo = T2.id_forn)
Forma negativa:
Prof. Luiz Vivacqua
SQL - CONSULTAS
Dicas:
❑ Normalmente, EXISTS oferece um desempenho melhor 
do que IN em subconsultas.
❑Quando uma lista de valores contem um valor nulo, NOT EXISTS
retorna verdadeiro, mas NOT IN retorna falso
Exemplo:
Insert into fornecimento values(13, null, ‘p1’,300)
Select nome from fornecedor T1where NOT EXISTS (select 1 
FROM fornecimento T2
where T1.codigo = T2.id_forn)
Select nome from fornecedor
where codigo NOT IN (select id_forn 
FROM fornecimento)
Prof. Luiz Vivacqua
SQL - CONSULTAS
select nome from fornecedor where cidade NOT IN('sp');
select nome from fornecedor where cidade NOT IN('sp‘, null);
Prof. Luiz Vivacqua
SQL - CONSULTAS
Operadores de conjunto
Combinam os resultados das consultas que devem ter o mesmo 
número de colunas com o mesmo tipo de dados.
Union
É o operador de união da teoria de conjuntos tradicionais. O resultado 
é a união dos resultados da cada consulta. As duplicidades são 
sempre eliminadas.
Obter a identificação das peças que pesam mais de 15 ou 
que são fornecidas por S2.
Select codigo
From Peca
Where peso > 15
UNION
Select id_peca
From fornecimento
Where id_forn = ‘S2’;
Prof. Luiz Vivacqua
SQL - CONSULTAS
Consulta com Intersect
É o operador de interseção de conjuntos. O resultado é a interseção 
dos resultados da cada consulta.
Obter as cidades onde existam fornecedores e peças.
Select cidade
From fornecedor
INTERSECT
Select cidade
From Peca;
Prof. Luiz Vivacqua
SQL - CONSULTAS
Consulta com EXCEPT
É o operador de subtração de conjuntos. O resultado apresenta as 
linhas da primeira consulta que não estão na segunda consulta.
Obter as cidades onde existam fornecedores e que não existam peças.
Select cidade
From fornecedor
EXCEPT
Select cidade
From Peca;
Prof. Luiz Vivacqua
SQL - CONSULTAS
Usando a expressão CASE
Similar ao IF...THEN...ELSE de outras linguagens
CASE WHEN CONDIÇÃO THEN RESULTADO
[WHEN ...]
[ELSE RESULT]
END
CONDIÇÃO é uma expressão que retorna um boleano
Se a cláusula ELSE for omitida e nenhuma condição for verdade
o resultado é NULO.
Prof. Luiz Vivacqua
SQL - CONSULTAS
EXEMPLOS:
Select codigo,status,
Case Status
WHEN 10 Then 'OTIMO'
WHEN 20 Then 'BOM'
WHEN 30 Then 'REGULAR'
ELSE 'RUIM'
End
From Fornecedor;
Expressão CASE pesquisada
Select codigo, status,
Case
WHEN status=10 THEN ‘ÓTIMO’
WHEN status=20 THEN ‘BOM’
WHEN status=30 THEN ‘REGULAR’
ELSE ‘RUIM’
End
From Fornecedor;