SQL – Consultas
Aninhadas
Profa. Sandra de Amo
Capitulo 5 – Livro Texto
Database Management Systems
Ramakrishnan - Gehrke
Exercícios


Variantes da consulta “Dê o nome dos
marinheiros que reservaram barcos
vermelhos”
Variantes equivalentes da consulta “Dê o
nome dos estudantes do primeiro período
que estão matriculados em alguma disciplina
ministrada pela Profa. Maria Amélia”
Dê os nomes dos marinheiros que reservaram
Sailors
barcos vermelhos
Resposta
Sid Snome Status Idade
N1
SELECT S.Snome
FROM Sailors S
WHERE S.Sid IN
(SELECT R.Sid
FROM Reservas R
WHERE R.Bid IN
(SELECT B.Bid
FROM Barcos B
WHERE
B.Cor = ‘Vermelho’))
N3
N6
22
22
31
31
64
102
104
22
29
31
32
58
64
71
74
85
95
N1
N2
N3
N4
N5
N6
N7
N6
N8
N9
7
1
8
8
10
7
10
9
3
3
45
33
55,5
25,5
35
35
16
35
25,5
63,5
Reservas
Sid
22
22
22
22
31
31
31
64
Bid Dia
101 D1
102 D1
103 D3
104 D4
102 D5
103 D6
104 D7
101 D8
64
74
102
103
D9
D9
Barcos
Bid BN Cor
101 A1 Azul
102 A1 Verm
103
104
A2 Verde
A3 Verm
Dê os nomes dos marinheiros que NÃO reservaram
barcos vermelhos
Sailors
N2
SELECT S.Snome
FROM Sailors S
WHERE S.Sid NOT IN
(SELECT R.Sid
FROM Reservas R
WHERE R.Bid IN
(SELECT B.Bid
FROM Barcos B
WHERE
B.Cor = ‘Vermelho’))
N4
N5
N7
N6
N8
N9
22
22
31
31
64
102
104
Resposta
Sid Snome Status Idade
22 N1
7
45
29 N2
1
33
8
55,5
31 N3
8
32 N4
25,5
58 N5
35
10
35
64 N6
7
71 N7
10
16
N6
74
9
35
85 N8
3
25,5
95 N9
3
63,5
Reservas
Sid Bid
Dia
22 101 D1
22 102 D1
22 103 D3
22 104 D4
31 102 D5
31 103 D6
31 104 D7
64 101 D8
64 102 D9
74 103 D9
Barcos
Bid BN Cor
101 A1 Azul
102 A1 Verm
103 A2 Verde
104 A3 Verm
Dê os nomes dos marinheiros que reservaram
barcos não vermelhos Resposta Sid SnomeSailors
Status Idade
SELECT S.Snome
FROM Sailors S
WHERE S.Sid IN
(SELECT R.Sid
FROM Reservas R
WHERE R.Bid NOT IN
(SELECT B.Bid
FROM Barcos B
WHERE
B.Cor = ‘Vermelho’))
N1
N3
N6
N6
22
22
31
64
74
102
104
22
29
31
32
58
64
71
74
85
95
N1
N2
N3
N4
N5
N6
N7
N6
N8
N9
7
1
8
8
10
7
10
9
3
3
45
33
55,5
25,5
35
35
16
35
25,5
63,5
Reservas
Sid Bid Dia
22 101 D1
22 102 D1
22 103 D3
22 104 D4
31 102 D5
31 103 D6
31 104 D7
64 101 D8
64
74
102
103
D9
D9
Barcos
Bid
101
102
103
104
BN
A1
A1
A2
A3
Cor
Azul
Verm
Verde
Verm
Dê os nomes dos marinheiros que não
reservaram barcos não vermelhos Sailors
Resposta
SELECT S.Snome
FROM Sailors S
WHERE S.Sid NOT IN
(SELECT R.Sid
FROM Reservas R
WHERE R.Bid NOT IN
(SELECT B.Bid
FROM Barcos B
WHERE
B.Cor = ‘Vermelho’))
N2
N4
N5
N7
N8
N9
22
22
31
64
74
102
104
Sid Snome Status Idade
45
22 N1
7
1
29 N2
33
31 N3
55,5
8
25,5
8
32 N4
10
35
58 N5
64 N6
7
35
16
71 N7
10
74 N6
9
35
85 N8
25,5
3
95 N9
3
63,5
Reservas
Sid Bid Dia
22 101 D1
22 102 D1
22 103 D3
22 104 D4
31 102 D5
31 103 D6
31 104 D7
64 101 D8
64
74
102
103
D9
D9
Barcos
Bid
101
102
103
104
BN
A1
A1
A2
A3
Cor
Azul
Verm
Verde
Verm
Dê o nome dos estudantes do primeiro período que
estão matriculados em alguma disciplina ministrada
pela Profa. Maria Amélia”
VARIANTE 1 : Sem subconsultas aninhadas
SELECT E.ENOME
FROM ESTUDANTE E, MATRICULADO M, DISCIPLINA D, PROF P
WHERE E.PERIODO = ‘1’ AND E.ENUM = M.ENUM AND
M.DID = D.DID AND D.PID = P.PID AND
P.PNOME = ‘Maria Amélia’
Dê o nome dos estudantes do primeiro período que
estão matriculados em alguma disciplina ministrada
pela Profa. Maria Amélia”
VARIANTE 2 : com um nível de aninhamento
SELECT E.ENOME
FROM ESTUDANTE
WHERE E.PERIODO = ‘1’ AND
E.NUM IN
(SELECT M.ENUM
FROM MATRICULADO M, DISCIPLINA D, PROF P
WHERE M.DID = D.DID AND D.PID = P.PID AND
P.PNOME = ‘Maria Amélia’)
Dê o nome dos estudantes do primeiro período que
estão matriculados em alguma disciplina ministrada
pela Profa. Maria Amélia”
VARIANTE 3 : com dois níveis de aninhamento
SELECT E.ENOME
FROM ESTUDANTE
WHERE E.PERIODO = ‘1’ AND
E.NUM IN
(SELECT M.ENUM
FROM MATRICULADO M
WHERE M.DID IN (SELECT D.DID
FROM DISCIPLINA D, PROF P
WHERE D.PID = P.PID AND
P.PNOME = ‘Maria Amélia’))
Dê o nome dos estudantes do primeiro período que
estão matriculados em alguma disciplina ministrada
pela Profa. Maria Amélia”
VARIANTE 4 : com três níveis de aninhamento
SELECT E.ENOME
FROM ESTUDANTE
WHERE E.PERIODO = ‘1’ AND
E.NUM IN
(SELECT M.ENUM
FROM MATRICULADO M
WHERE M.DID IN
(SELECT D.DID
FROM DISCIPLINA D
WHERE D.PID IN
(SELECT P.PID
FROM PROF P
WHERE P.PNOME = ‘Maria Amélia’)))
Condições do WHERE – em consultas
aninhadas

A IN (R: Resultado de subconsulta)


A NOT IN (R: Resultado de subconsulta)


Verdadeiro se o valor do atributo A não está em R
EXISTS (R: Resultado de subconsulta)


Verdadeiro se o valor do atributo A está em R
Verdadeiro se R é não vazio
NOT EXISTS (R: Resultado de subconsulta)

Verdadeiro se R é vazio
Exemplo
Encontre nome de marinheiros que reservaram
o barco ‘103’
SELECT S.Snome
FROM Sailors S
WHERE EXISTS
(SELECT *
FROM Reservas R
WHERE R.Bid = ‘103’ AND
R.Sid = S.Sid)
Exemplo
Encontre nome de marinheiros que não
reservaram o barco ‘103’
SELECT S.Snome
FROM Sailors S
WHERE NOT EXISTS
(SELECT *
FROM Reservas R
WHERE R.Bid = ‘103’ AND
R.Sid = S.Sid)
Exemplo
Encontre nome de marinheiros que reservaram
uma única vez o barco ‘103’
SELECT S.Snome
FROM Sailors S
WHERE UNIQUE
(SELECT *
FROM Reservas R
WHERE R.Bid = ‘103’ AND
R.Sid = S.Sid)
Condições do WHERE – em consultas
aninhadas

A > ANY (R: Resultado de subconsulta)


A > ALL (R: Resultado de subconsulta)


Verdadeiro se o valor do atributo A é maior do que
qualquer valor em R
A <> ALL (R: Resultado de subconsulta)


Verdadeiro se o valor do atributo A é maior do que
algum valor de R
Verdadeiro se o valor de A não está em R
A = ANY (R: Resultado de subconsulta)

Verdadeiro se o valor de A está em R
Exemplo

Encontre os identificadores de marinheiros cujo
status é melhor do que algum marinheiro chamado
“Horácio”
SELECT S.Sid
FROM Sailors S
WHERE S.Status >= ANY
(SELECT S2.Status
FROM Sailors S2
WHERE S2.Snome =
‘Horacio’)
Exemplo

Encontre os identificadores de marinheiros
que têm os maiores status.
SELECT S.Sid
FROM Sailors S
WHERE S.Status >= ALL
(SELECT S2.Status
FROM Sailors S2)
Exercício 1

Quais são os nomes dos marinheiros mais jovens
que reservaram o barco 103 ?
SELECT S.Snome
FROM Sailors
WHERE S.Idade <= ALL
(SELECT S2.Idade
FROM S2 Sailors, R Reservas
WHERE R.Sid = S2.Sid
AND R.Bid = 103)
Consultas Aninhadas Correlacionadas
(a evitar - ineficientes)
Encontre nome de marinheiros que reservaram
o barco ‘103’
SELECT S.Snome
FROM Sailors S
WHERE EXISTS
(SELECT *
Consulta executada
repetidamente: uma
FROM
Reservas
R
vez para cada tupla
da relação Sailors
WHERE R.Bid = ‘103’ AND
R.Sid = S.Sid)
Exercício 2

A consulta a seguir é correlacionada ?
SELECT S.Snome
FROM Sailors
WHERE S.Idade <= ALL
(SELECT S2.Idade
FROM S2 Sailors, R Reservas
WHERE R.Sid = S2.Sid
AND R.Bid = 103)
Resposta: Não
Como simular operador de Intersecção
Encontre os nomes de marinheiros que reservaram barcos
verdes e vermelhos.
SELECT S.Snome
FROM Sailors S, Reservas R, Barcos B
WHERE S.Sid = R.Sid AND R.Bid = B.Bid AND
B.Cor = ‘Vermelho’ AND S.Sid IN
(SELECT S2.Sid
FROM Sailors S2, Barcos B2, Reservas R2
WHERE S2.Sid = R2.Sid AND R2.Bid =
B2.Bid AND B2.Cor = ‘Verde’)
Variante: calcula a mesma resposta ?
(SIM)
SELECT S.Snome
FROM Sailors S
WHERE S.Sid IN
( (SELECT R.Sid
FROM Barcos B, Reservas R
WHERE R.Bid = B.Bid AND
B.Cor = ‘Vermelho’)
INTERSECT
(SELECT R.Sid
FROM Barcos B, Reservas R
WHERE R.Bid = B.Bid AND
B.Cor = ‘Verde’) )
Variante: calcula a mesma resposta ?
(NÃO !!)
(SELECT S.Snome
FROM Sailors S, Barcos B, Reservas R
WHERE S.Sid = R.Sid AND R.Bid = B.Bid AND
B.Cor = ‘Vermelho’)
INTERSECT
(SELECT S.Snome
FROM Sailors S, Barcos B, Reservas R
WHERE S.Sid = R.Sid AND R.Bid = B.Bid AND
B.Cor = ‘Verde’)
Exercício 3 : Operador de Diferença

Dê os nomes dos marinheiros que reservaram
barcos vermelhos e não reservaram barcos verdes.
SELECT S.Snome
FROM Sailors S, Reservas R, Barcos B
WHERE S.Sid = R.Sid AND R.Bid = B.Bid AND
B.Cor = ‘Vermelho’ AND S.Sid NOT IN
(SELECT S2.Sid
FROM Sailors S2, Barcos B2, Reservas R2
WHERE S2.Sid = R2.Sid AND R2.Bid =
B2.Bid AND B2.Cor = ‘Verde’)
Variante: Calcula a mesma resposta ?
(SIM)
SELECT S.Snome
FROM Sailors S
WHERE S.Sid IN
( (SELECT R.Sid
FROM Barcos B, Reservas R
WHERE R.Bid = B.Bid AND
B.Cor = ‘Vermelho’)
EXCEPT
(SELECT R.Sid
FROM Barcos B, Reservas R
WHERE R.Bid = B.Bid AND
B.Cor = ‘Verde’) )
Variante: calcula a mesma resposta ?
(NÃO !!)
(SELECT S.Snome
FROM Sailors S, Barcos B, Reservas R
WHERE S.Sid = R.Sid AND R.Bid = B.Bid AND
B.Cor = ‘Vermelho’)
EXCEPT
(SELECT S.Snome
FROM Sailors S, Barcos B, Reservas R
WHERE S.Sid = R.Sid AND R.Bid = B.Bid AND
B.Cor = ‘Verde’)
Exercicio 4 : Operador de União

Dê o nome dos marinheiros que reservaram
barcos vermelhos ou verdes.
SELECT S.Snome
FROM Sailors S, Reservas R, Barcos B
WHERE S.Sid = R.Sid AND R.Bid = B.Bid AND
B.Cor = ‘Vermelho’ OR S.Sid IN
(SELECT S2.Sid
FROM Sailors S2, Barcos B2, Reservas R2
WHERE S2.Sid = R2.Sid AND R2.Bid =
B2.Bid AND B2.Cor = ‘Verde’)
Variante: Calcula a mesma resposta ?
(SIM)
SELECT S.Snome
FROM Sailors S
WHERE S.Sid IN
( (SELECT R.Sid
FROM Barcos B, Reservas R
WHERE R.Bid = B.Bid AND
B.Cor = ‘Vermelho’)
UNION
(SELECT R.Sid
FROM Barcos B, Reservas R
WHERE R.Bid = B.Bid AND
B.Cor = ‘Verde’) )
Variante: calcula a mesma resposta ?
(SIM !!)
(SELECT S.Snome
FROM Sailors S, Barcos B, Reservas R
WHERE S.Sid = R.Sid AND R.Bid = B.Bid AND
B.Cor = ‘Vermelho’)
UNION
(SELECT S.Snome
FROM Sailors S, Barcos B, Reservas R
WHERE S.Sid = R.Sid AND R.Bid = B.Bid AND
B.Cor = ‘Verde’)
Operador de Divisão

Dê o nome dos marinheiros que reservaram todos os barcos.
SELECT S.SNOME
FROM SAILORS S
WHERE NOT EXISTS
( ( SELECT B.BID
FROM BARCOS B
EXCEPT
(SELECT R.BID
FROM RESERVAS R
WHERE R.SID = S.SID) )
Identificadores de
barcos que não
foram
reservados pelo
marinheiro com
identificador Sid
Variante (sem EXCEPT)

Dê o nome dos marinheiros que reservaram todos os barcos.
SELECT S.SNOME
FROM SAILORS S
WHERE NOT EXISTS
Identificadores
de barcos
que não foram
reservados pelo
marinheiro com
Identificador SID
(( SELECT B.BID
FROM BARCOS B
WHERE NOT EXISTS
( SELECT R.BID
FROM RESERVAS R
WHERE R.BID = B.BID
AND
R.SID = S.SID) )
Identificadores de
barcos que foram
reservados pelo
marinheiro com
Identificador SID
Exercício 5

Dê o nome dos marinheiros que reservaram todos os
barcos vermelhos.
SELECT S.SNOME
FROM SAILORS S
WHERE NOT EXISTS
( (SELECT B.BID
FROM BARCOS B, RESERVAS R
WHERE B.Cor = ‘Vermelho’)
EXCEPT
(SELECT R.BID
FROM RESERVAS R
WHERE R.SID = S.SID) )