1 / 27

SQL Exercícios de Revisão

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

tashya-hudson
Download Presentation

SQL Exercícios de Revisão

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. SQLExercícios de Revisão Profa. Sandra de Amo Programa de Pós-graduação em Ciência da Computação – UFU 2012 - 2

  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’

  3. 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’)

  4. 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’))

  5. 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’)))

  6. ΠEnome ΠEnome σPeriodo =1 σPeriodo =1 ΠEnum σPnome=M.Amelia σPnome=M.Amelia Estudante Estudante Matriculado Matriculado Professor Disciplina Professor Disciplina Exercicio 4.b Plano da Consulta 2 Plano da Consulta 1 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.

  7. ΠEnome σPeriodo =1 ΠEnum Estudante ΠDID Matriculado σPnome=M.Amelia Professor Disciplina Exercicio 4.b Plano da Consulta 4 ΠEnome Plano da Consulta 3 σPeriodo =1 ΠEnum Estudante ΠDID Matriculado ΠPID Disciplina σPnome=M.Amelia 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.

  8. 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)

  9. 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’)

  10. 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.

  11. 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)

  12. 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.

  13. 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)

  14. 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)

  15. 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’)

  16. 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’)

  17. 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)

  18. 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)

  19. 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)

  20. 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) 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. 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.

  21. 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.

  22. 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.

  23. 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.

  24. 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) ) Identificadores de barcos que não foram reservados pelo marinheiro com identificador Sid b) Esta consulta é correlacionada.

  25. Exercicio 16a a) Consulta retorna os nomes dos marinheiros que reservaram todos os barcos. SELECT S.SNOME FROM SAILORS S WHERENOT EXISTS (( SELECT B.BID FROM BARCOS B WHERENOT EXISTS ( SELECT R.BID FROM RESERVAS R WHERE R.BID = B.BID AND R.SID = S.SID) ) Identificadores de barcos que não foram reservados pelo marinheiro com Identificador SID Identificadores de barcos que foram reservados pelo marinheiro com Identificador SID

  26. 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.

  27. 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) ) Identificadores de marinheiros com 20 anos que não reservaram o barco com identificador B.BID b) Esta consulta SQL é correlacionada.

More Related