Prof. Luiz Vivacqua

Projeto de Banco de Dados

Laboratório de Banco de Dados

Prof. Luiz Antônio Vivacqua C. Meyer

(luiz.vcm@gmail.com)

Prof. Luiz VivacquaProf. Luiz A. Vivacqua C. Meyer

SQL - CONSULTAS

Sintaxe GeralSELECT [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 VivacquaProf. Luiz A. Vivacqua C. Meyer

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 id, 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 id, 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.id=id_forn;

Junção com condição adicional: Obter o nome do fornecedor

que fornece a peça P2.

Select distinct nome

From Fornecedor, Fornecimento

Where Fornecedor.id=id_forn

and id_peca=´p2´;

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 Peca.nome

From fornecedor S, peca P, fornecimento SP

Where S.id = id_forn

and P.id = id_peca

and S.cidade=´rj´;

Inner Join

Obter o nome dos fornecedores que fornecem peças.

Select distinct nome

From Fornecedor INNER JOIN Fornecimento

ON fornecedor.id = 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.id = 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 id, 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 id, 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 id

From fornecedor

Where status IS NULL;

Prof. Luiz Vivacqua

SQL - CONSULTAS

Recuperação com SubconsultaSubconsulta é 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 id 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 id IN (Select id_forn

From fornecimento

Where id_peca IN (Select id

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 id, nome

From fornecedor

Where cidade = (Select cidade

From fornecedor

Where id = ‘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 id, nome from fornecedor T1

Where EXISTS (select 1

from fornecimento T2

where T2.id_forn = T1.id);

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.id = 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 T1

where NOT EXISTS (select 1

FROM fornecimento T2

where T1.id = T2.id_forn)

Select nome from fornecedor

where id 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 id

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 Id,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 Id, status,

Case

WHEN status=10 THEN ‘ÓTIMO’

WHEN status=20 THEN ‘BOM’

WHEN status=30 THEN ‘REGULAR’

ELSE ‘RUIM’

End

From Fornecedor;