SQL
Exercícios de Revisão
Profa. Sandra de Amo
Programa de Pós-graduação em Ciência da
Computação – UFU
2012 - 2
Exercicio 1.
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’
Exercicio 2.
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’)
Exercicio 3.
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’))
Exercicio 4.a
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’)))
Exercicio 4.b
Plano da Consulta 1
ΠEnome
σPeriodo =1
Plano da Consulta 2
ΠEnome
σPeriodo =1
σPnome=M.Amelia
ΠEnum
σPnome=M.Amelia
Estudante
Estudante
Matriculado
Matriculado
Disciplina
Disciplina
Professor
Professor
Análise: Plano 2 é mais eficiente que o plano 1, pois no plano 2, o último (as operações são
executadas na ordem “de baixo para cima” no plano) operador de junção opera sobre relações
muito menores do que o último operador de junção na consulta 1. Os outros dois operadores de
junção sãoexecutados de forma idêntica nos dois planos.
Exercicio 4.b
Plano da Consulta 4
ΠEnome
Plano da Consulta 3
ΠEnome
σPeriodo =1
σPeriodo =1
ΠEnum
ΠEnum
Estudante
Estudante
Matriculado
Matriculado
ΠDID
ΠDID
σPnome=M.Amelia
Disciplina
ΠPID
σPnome=M.Amelia
Professor
Disciplina
Professor
Análise: Plano 3 é mais eficiente que o plano 2 e plano 4 é mais
eficiente do que o 3. A justificativa é análoga à justificativa dada
para concluir que o plano 2 é mais eficiente do que o plano 1.
Exercicio 5a.
a) Dê um comando SQL aninhado usando dois blocos básicos ligados
pelo comando EXISTS para a seguinte consulta:
SELECT S.Snome
FROM Sailors S
WHERE EXISTS
(SELECT *
FROM Reservas R
WHERE R.Bid = ‘103’ AND
R.Sid = S.Sid)
b) Dê um comando SQL aninhado usando dois blocos básicos ligados
pelo comando IN para a mesma consulta do item a)
Exercicio 5b.
b) Dê um comando SQL aninhado usando dois blocos básicos ligados pelo
comando IN para a mesma consulta do item a)
SELECT S.Snome
FROM Sailors S
WHERE S.SID IN
(SELECT R.SID
FROM Reservas R
WHERE R.Bid = ‘103’)
Exercicio 5c.

A segunda consulta (com IN) é muito mais eficiente do
que a primeira consulta (com EXISTS), pois na segunda
consulta, o bloco aninhado (interno) é
CORRELACIONADO com o bloco externo.

Isto é, a variável S.SID corresponde à variável S.SID do
bloco externo. Isto significa que o bloco interno é
executado PARA CADA TUPLA do bloco externo !!

Na primeira consulta, o bloco interno é executado UMA
ÚNICA VEZ.
Exercicio 6a.
a)
SELECT S.Snome
FROM Sailors S
WHERE NOT EXISTS
(SELECT *
FROM Reservas R
WHERE R.Bid = ‘103’ AND
R.Sid = S.Sid)
Exercicio 6b. e 6c
b) SELECT S.Snome
FROM Sailors S
WHERE S.SID NOT IN
(SELECT *
FROM Reservas R
WHERE R.Bid = ‘103’)
c) Consulta (b) é muito mais eficiente do que a consulta (a).
Justificativa análoga à do Exercicio 5.
Exercicio 7a.
a) 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)
Exercicio 7b
b) Você poderia dar um comando SQL para a mesma consulta do item
(a) sem utilizar o comando UNIQUE ?
SELECT S.Snome
FROM Sailors S
WHERE S.SID IN
(SELECT R.SID
FROM Reservas R
WHERE R.Bid = ‘103’
GROUP BY R.SID
HAVING COUNT (*) = 1)
Exercicio 8a.
a) 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’)
Exercicio 8b
b) Você poderia dar um comando SQL para a mesma consulta do item
(a) sem utilizar o comando ANY ?
SELECT S.Sid
FROM Sailors S
WHERE S.Status >= (SELECT MIN(S2.Status)
FROM Sailors S2
WHERE S2.Snome = ‘Horacio’)
Exercicio 9a.
a) 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)
Exercicio 9b
b) Você poderia dar um comando SQL para a mesma consulta do item
(a) sem utilizar o comando ALL ?
SELECT S.Sid
FROM Sailors S
WHERE S.Status = (SELECT MAX(S2.Status)
FROM Sailors S2)
Exercício 10.
Dê comando SQL para a seguinte consulta, usando dois blocos SQL
aninhados, sem comandos de agregação:
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)
Exercicio 11.
Considere as duas consultas abaixo. Diga quais delas são correlacionadas
e por que. Diga também o que cada retorna.
SELECT S.Snome
FROM Sailors S
WHERE EXISTS
(SELECT *
FROM Reservas R
WHERE R.Bid = ‘103`
AND
R.Sid = S.Sid)
A consulta à esquerda é correlacionada,
Pouco eficiente. A execução do bloco interno
é feita diversas vezes a medida que o bloco
externo é executado. Retorna: Nomes de
marinheiros 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)
A consulta à direita não é correlacionada. O
bloco interno é executado uma única vez, é
independente do bloco externo.
Retorna: Nomes de marinheiros que são
mais ou igualmente jovens do que algum
marinheiro que reservou o barco 103.
Exercicio 12.
O objetivo deste exercicio é dar um comando SQL para retornar os nomes dos
marinheiros que reservaram algum barco vermelhor e algum barco verde.
As alternativas 1 e 2 são corretas. A alternativa 3 NÃO é correta.
Caso a relação SAILORS contenha dois marinheiros com o mesmo nome, onde um
deles reservou um barco vermelho (mas não reservou barco verde) e o outro
reservou um barco verde (mas não reservou um barco vermelho), este nome comum
dos dois marinheiros será retornado na resposta da consulta, o que é incorreto.
Exercicio 13.
O objetivo deste exercicio é dar um comando SQL para retornar os nomes dos
marinheiros que reservaram barcos vermelhos e não reservaram barcos verdes.
As alternativas 1 e 2 são corretas. A alternativa 3 NÃO é correta.
Caso a relação SAILORS contenha dois marinheiros com o mesmo nome, onde um
deles reservou um barco vermelho (mas não reservou barco verde) e o outro reservou
um barco vermelho e um barco verde este nome comum dos dois marinheiros não vai
ser retornado, o que é incorreto, pois um dos marinheiros com este nome, o primeiro,
satisfaz a condição da consulta: reservou barco vermelho mas não reservou barco
verde.
Exercicio 14.
O objetivo deste exercicio é dar um comando SQL para retornar os nomes dos
marinheiros que reservaram barcos vermelhos ou verdes.
As 3 alternativas propostas estão corretas. Veja que a alternativa 3, contrariamente ao
que ocorreu nos dois exercicios anteriores (12 e 13), é correta.
Suponha que um nome de marinheiro está na resposta da alternativa 3. Então ou um
marinheiro com este nome reservou o barco vermelho (e portanto este nome com certeza
vai aparecer na resposta da alternativa 1 e 2) ou um marinheiro com este nome reservou
o barco verde (portanto este nome com certeza vai aparecer na resposta da alternativa 1
e 2).
Suponha que um nome de marinheiro NÃO ESTÁ na resposta da alternativa 3. Então não
existe marinheiro com este nome que tenha reservado o barco vermelho ou verde. Logo
este nome de marinheiro não pode aparecer na resposta da alternativa 1 ou 2.
Exercicio 15.
a) Consulta retorna os nomes 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) )
b) Esta consulta é correlacionada.
Identificadores de
barcos que não
foram
reservados pelo
marinheiro com
identificador Sid
Exercicio 16a
a) Consulta retorna os nomes 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
Exercicio 16b
b) Esta consulta é correlicionada. Bem menos
eficiente do que a consulta do Exercicio 15, embora
ambas sejam correlacionadas.
A razão é por que a consulta do exercicio 16 tem duas
variáveis correlacionadas. Isto é, dois blocos que
são executados diversas vezes, dentro da execução
do bloco externo. Na consulta 15, só um bloco é
executado diversas vezes dentro da execução do
bloco externo.
Exercicio 17.
Encontrar os nomes dos barcos que foram reservados por
todos os marinheiros com idade igual a 20 anos.
SELECT B.BNOME
FROM BARCOS B
WHERE NOT EXISTS
( ( SELECT S.SID
FROM SAILORS S
WHERE S.IDADE = 20
EXCEPT
(SELECT R.SID
FROM RESERVAS R
WHERE R.BID = B.BID) )
b) Esta consulta SQL é correlacionada.
Identificadores de
marinheiros com 20
anos que não
reservaram o barco
com identificador
B.BID