FBD - Slide 04 - Consultas SQL.pdf

Consultas em SQL

• Consultas Básicas

• Implementando Junção

• Definindo Visão

• Subconsultas

• Ordenando resultados

• Operações e Comparação de Conjuntos

• Gerando resumos

select

< lista de atributos>

from

< lista de nomes de relação>

where

< condição de seleção> ;

select

A1,...,An

from

R1,...,Rm

where

<condição>;

(R1 x ... x Rm) [ <condição> ] [A1,...,An]

produto cartesiano seleção projeção

Q1: Liste todas informações em E sobre empregados

em Fortaleza.

select

*

from

E

where

E.localização

=

‘Fortaleza’;

E (enome, dno, sal, localização)

D (dno, dnome, ger)

Q2: Liste todas as informações em D

select

*

from

D

;

Q3: Liste todos os empregados e o nome dos seus

departamentos

Implementando Junção

select

enome

,

dnome

from

E

,

D

where

E.dno

=

D.dno

;

select

enome

,

dnome

from

E

inner join

D

on

E.dno

=

D.dno

;

Implícito

Q4: Nome dos empregados gerenciados por Gustavo e

que tem um salário < R$1000?

Implementando Junção

select

enome

from

E, D

where

E.dno

=

D.dno

AND

D.ger

=

‘gustavo’

AND

sal

<

1000;

 SQL não tem nenhum suporte especial para junção natural

Q4: Nome dos empregados gerenciados por Gustavo e

que tem um salário < R$1000?

Implementando Junção

select

enome

from

E

inner join D on

E.dno

=

D.dno

where

D.ger

=

‘gustavo’

AND

Q5: Nome dos empregados que ganham mais que

seu gerente?

Implementando Junção

select

E1.enome

from

E E1,

D,

E E2

where

E1.dno

=

D.dno

AND

D.ger

=

E2.enome

AND

E1.sal

>

E2.sal;

 Nós precisamos de duas cópias de E. Cria-se uma alias para evitar ambiguidades.

Q5: Nome dos empregados que ganham mais que

seu gerente?

Implementando Junção

select

E1.enome

from

E E1

inner join D on E1.dno

=

D.dno

inner join E E2 on

D.ger

=

E2.enome

where E1.sal

>

E2.sal;

Q6: Nome dos gerentes que ganham menos que os seus

empregados?

Implementando Junção

 SQL não elimina tuplas duplicadas! você deve requisitar explicitamente dizendo ‘select distinct.’. Caso contrário, a resposta repetirá o nome do

gerente para cada empregado que ganha mais.

select

distinct

E2.enome

from

E E1, D, E E2

where

E1.dno

= D.dno

AND

D.ger

= E2.enome

AND

E1.sal

> E2.sal;

Q6: Nome dos gerentes que ganham menos que os seus

empregados?

Implementando Junção

 SQL não elimina tuplas duplicadas! você deve requisitar explicitamente dizendo ‘select distinct.’. Caso contrário, a resposta repetirá o nome do

gerente para cada empregado que ganha mais.

select

distinct

E2.enome

from

E E1

inner join D

on

E1.dno

= D.dno

inner join

E E2

on D.ger

= E2.enome

where

E1.sal

> E2.sal;

Q7: Encontre a quantidade fornecida de cada peça.

Devem constar inclusive as peças que não são

fornecidas por nenhum fornecedor.

Implementando Junção

PEÇAS (#p, pnome)

FORNECEDORES_PEÇAS (#f,#p,qtd)

select

pnome

,

qtd

from

PEÇAS P

,

FORNECEDORES_PEÇAS FP

where

P.#p

*=

FP.#P

Q7: Encontre a quantidade fornecida de cada peça.

Devem constar inclusive as peças que não são

fornecidas por nenhum fornecedor.

Implementando Junção

PEÇAS (#p, pnome)

FORNECEDORES_PEÇAS (#f,#p,qtd)

select

pnome

,

qtd

from

PEÇAS P

left join

FORNECEDORES_PEÇAS FP

on

P.p = FP.p

Implementando Junção

Q8: Como criar uma visão para salvar o resultado de uma

consulta temporariamente?

create view

<nome_da_ visão>

as

<uma_consulta>

create view

EDM

(

emp

,

dept

,

ger

)

as select

enome

,

dnome

,

ger

from

E

,

D

where

e.dno

=

d.dno

;



Q9 : Imprima o nome de todos os empregados que

ganham mais de 90% do salário do seu gerente.

create view ESGS(e_nome, e_sal, g_nome, g_sal)

as select E1.enome, E1.sal, E2.enome, E2.sal

from E E1, D, E E2

where E1.dno = D.dno AND

D.ger = E2.enome;

select e_nome

from ESGS

where e_sal > 0.9 * g_sal;

Tipos de Subconsultas

• Testa membros de um conjunto:

– IN

– NOT IN

• Verificação de Relações Vazias:

– EXISTS

– NOT EXISTS

• Comparação de Conjuntos: Subconsultas

introduzidas com um operador de comparação

(=,< >, >, >=, <, <=, ou !> ) seguida por

Q10: Quem está no mesmo departamento de Vera?

Subconsultas: Membros de um conjunto

select E1.enome

from E E1, E E2

where E2.enome = “Vera” and

E1.dno = E2.dno;

select enome

from E

where E.dno IN (‘NOT IN)

(select dno

from E subconsulta

Q11: Nomes dos empregados que têm um dependente com

o mesmo nome do empregado?

select E.enome

from EMP E

where E.#CI IN ( select e_#CI

from DEP

where e_#CI = E.#CI AND

DEP.nome = E.enome);

EMP ( enome, #CI, idade) DEP ( nome, e_#CI, ... )

Q11: Nomes dos empregados que têm um dependente com

o mesmo nome do empregado?

select E.enome

from EMP E

where E.enome IN ( select nome

from DEP

where e_#CI = E.#CI);

EMP ( enome, #CI, idade) DEP ( nome, e_#CI, ... )

Q12: Nomes dos empregados que têm um dependente com

o mesmo nome do empregado?

select E.enome

from EMP E

where EXISTS ( select *

from DEP

where e_#CI = E.#CI AND

nome = E.enome);

Q13: Nomes dos empregados que não têm dependentes?

Subconsultas

select

E.enome

from

EMP

where NOT EXISTS ( select

*

from

DEP

Q14: Quem tem o maior salário?

Subconsultas

select

enome

from

E

where sal

>= all (select

sal

from

E

);

Q15: Quem ganha mais do que alguém no departamento

de brinquedos?

Subconsultas

select

enome

from

E

where

sal

> any

(select

sal

from

E, D

where

E.dno

= D.dno

AND

D.dnome

=

“brinquedo”);

“> any”

(some) --- Maior do que ao menos um.

Q

salário. Se existir empate de salario, use ordem alfabética no nome.

Ordenando resultados

Especifica ordem de apresentação na tela

enome dno sal

localização

Susana 1 3000 Rio Maria 1 2000 Rio Jane 1 1900 Rio Jim 2 1500 Fortaleza João 2 1500 Fortaleza Select * from E

Gerando Dados de Resumo

• Funções de Agregação

• GROUP BY e HAVING

Q17: Qual é a média de salário dos empregados no

departamento de brinquedos?

* Nao podemos responder em alg. rel. . mas é facil em SQL.

Funções de Agregação

select avg(sal)

from

E, D

where

E.dno

= D.dno

AND

D.dname

= “brinquedo”;

 Na cláusula select , pode-se usar:

Q18: Quantos empregados trabalham em mais de um

departamento?

Funções de Agregação

select count (distinct

enome)

from

E E1

where

enome

in

(select

E.enome

from

E E2

where

E2.enome

= E1.enome

AND

E2.dno

!= E1.dno);

Q19: Nome dos empregados que ganham mais que a

média de salário do seu departamento ?

Funções de Agregação

where sal > all

(select avg(sal)

from E E2

 Resposta errada !!! Imprime cada nome de

departamento seguido pelo salário da companhia e total de empregados.

brinquedo 1,000, 500 2002 Manuf 1,000, 500 2002 Pessoal 1,000, 500 2002

GROUP BY

select

dnome, sum(sal), count (enome)

from

E, D

where

E.dno

= D.dno;

Q20: Para cada departamento, liste o número total de

Quando queremos que um agregado seja

computado separadamente para cada valor de um atributo então deveremos usar:

‘group by’

Q21: Para cada departamento, liste o número total de

empregados do departamento e a soma total dos

salários.

GROUP BY

select

dnome, sum (sal), count (enome)

from

E, D

where

E.dno

= D.dno

group by

dnome;



devem ser exatamente os itens

não-agregado da linha de seleção (na maioria das SQLs).”

Q22: Para cada departamento dê o número de empregados

e a média de salário.

GROUP BY

select

DNO, COUNT(*), AVG(sal)

from

EMP

Create View MediaDep(dnome, avgsal, noEmps)

as select dnome, avg(sal), count(enome)

from E, D

where E.dno = D.dno

group by dnome;

GROUP BY

select dnome

from MediaDep

where avgsal > all (select avg(avgsal)

from MediaDep)

AND

noEmps > all (select avg(noEmps)

from MediaDep);

Q23: Quais departamentos têm mais empregados do que a média dos dept, e uma média de salario por empregado mais alta que a média dos

Q

Q

resposta em ordem alfabética.

GROUP BY e HAVING

 Somente para testes em um grupo inteiro. Não para testes em tuplas. todas as condições devem

envolver agregados.

Create view GrandesDeptos (dnome, avgSal, noEmps)

as select dnome, avg(sal), count(enome)

from E, D

where E.dno = d.dno

group by dnome

having sum(sal) >=10000

GROUP BY

select dnome, avg(sal)

from E, D

where E.dno = D.dno

group by dnome

having avg(sal) > 2.000

GROUP BY

select dnome

from ( select dnome, avg(sal)

from E, D

where E.dno = D.dno

group by dnome)

as resultado (dnome, avgsal)

where avgsal > 2.000

Subconsultas na Cláusula FROM

Q

Q

Create view dois_depts

as select E1.enome, sum(E2.sal) #4

from E E1, D, E E2

where E1.dno = D.dno AND #1

E2.enome = D.ger

group by E1.enome #2

having count(*) > 1 #3

order by enome; #5

Sequencia de avaliação das consultas:

#1: primeiro, tuplas são escolhidas #2: então, grupos são formados #3: então, grupos são eliminados

#4: então, os agregados são computados para a linha de seleção

#5: então, as tuplas da resposta são ordenadas corretamente e impressas. Esta sequência de avaliação é seguida por todas as consultas.

Q26: Liste todos os tipos de livros que terminam com

‘info’ e a soma total dos seus preços.

select

tipo

,

preço

from

TÍTULOS

where

tipo

like

‘%info’

order by

tipo

,

preço

compute SUM(

preço

)

DB_info 30.95 DB_info 10.40 IA_info 42.05 RC_info 67.20 sum 150.60 tipo preço

TÍTULO (título, tipo, preço)

Q26: Liste todos os tipos de livros que terminam

com ‘info’e a soma total dos seus preços.

select

tipo

,

preço

from

TÍTULOS

where

tipo

like

‘%info’

order by

tipo

,

preço

compute SUM(

preço

) by

tipo

compute SUM(

preço

)

DB_info 30.95 DB_info 10.40 sum 41.35 IA_info 42.05 42.05 sum 150.60 tipo preço

TÍTULO (título, tipo, preço)

Operações de Conjunto

• SQL tem incorporado algumas das operações de

conjunto da álgebra relacional

– União (UNION)

– Diferença (MINUS)

– Interseção (INTERSECT)

• Tuplas duplicadas são eliminadas do resultado

• As relações devem ser compatíveis ( têm os

EMPREGADOS (E)

enome CPF salário CPF_Supervisor dnumero

DEPARTAMENTOS (D)

dnome dnúmero CPF_gerente

TRABALHA (T)

CPF_Emp pnumero pnome pnúmero dnúmero

PROJETOS (P)

Operações de Conjunto

Q27: Liste os nomes dos projetos que têm um empregado

chamado “João Silva”que trabalha no projeto ou gerencia o

departamento que controla o projeto

select pnome

from P, D, E

where P.dnum=D. dnum and D.CPF_gerente = E.CPF and E.enome = ‘João Silva’

union

select pnome

from T, P, E

where T.pnum=P. dnum and T.CPF_emp = E.CPF and E.enome = ‘João Silva’

Operações de Conjunto

Q28: Liste os nomes dos empregados que não trabalham

em nenhum projeto

Operações de Conjunto

Q29: Liste os nomes dos empregados que não trabalham

em nenhum projeto

select enome

from E

where NOT EXIST (select *

from T

Comparação de Conjuntos

• SQL tem especificado o operador de comparação

de conjuntos CONTAINS

• S1 CONTAINS S2

– retorna Verdadeiro se S1 contém todos os valores de

S2

• A maioria dos sistemas não implementam este

operador

Comparação de Conjuntos

Q30: Liste os nomes dos empregados que trabalham em

todos os projetos

select enome

from E

where ((select pnumero

from T

where T.CPF_emp = E.CPF ) Contains

(select pnumero

Comparação de Conjuntos

Q31: Liste os nomes dos empregados que trabalham em

todos os projetos

select enome

from E

where NOT EXISTS ( (select pnumero from P)

EXCEPT

(select pnumero

from T

Comparação de Conjuntos

Q32: Liste os nomes dos empregados que trabalham em

todos os projetos

select enome

from E

where NOT EXISTS ( select *

from P

where NOT EXITS (select *

from T

where T.CPF_emp = E.CPF and )) T.pnum = P.CPF ))

1: seleção select *

R[A = ‘a’] from R

where A = ‘a’;

2: projeção select A1,...,Ak

R[ A1,...,Ak] from R;

3: Produto cartesiano select *

R1 x R2 from R1, R2 ;

4:União select *

R1 U R2 from R1

-- sem duplicatas- union

select * from R2; 5: Diferença select * R1 - R2 from R1 where * not in (select * from R2);

De AR para SQL

5: Diferença select A1, ..., An R1 - R2 from R1 except select B1, ..., Bn from R2);

De AR para SQL

6: Interseção select A1, ..., An

R1 R2 from R1

intersect

select B1, ..., Bn