Lista2_GABARITO_sql

Prévia do material em texto

BD 1 – Lista 2 – gabarito SQL 
 
 
A) Escreva as sentenças SQL que gerem as seguintes consultas: 
 
1. Nomes dos hóspedes da cidade de São Paulo que possuíam peso superior a 80 
Kg na entrada. 
SELECT HNome FROM Hóspede WHERE HCidade = ‘São Paulo’ AND HPesoEntr > 
80; 
 
2. Hóspedes que possuem a palavra Jenifer no nome. 
SELECT HNome FROM Hóspede WHERE HNome LIKE ‘%Jenifer%’; 
 
3. Nomes dos hóspedes que estão hospedados atualmente no spa. 
SELECT HNome FROM Hóspede WHERE HDtaSaida IS NULL; 
 
4. Programas que utilizam cardápios com valor de calorias inferior a 1000. 
SELECT PCod FROM Programa, Cardápio WHERE Programa.CCod = 
Cardápio.CCod AND CCalorias < 1000; 
 
5. Nomes das atividades coordenadas por um dos seguintes treinadores: Paulo, 
Juca ou Anderson. 
SELECT ANome FROM Atividade WHERE ATreinador IN (‘Paulo’, ‘Juca’, ‘Anderson’); 
 
6. Considerando a perda de peso apresentada pelos hóspedes que já passaram 
pelo spa, qual foi a perda de peso média obtida com cada programa utilizado 
pelos hóspedes. 
SELECT PCod, AVG(HPesoEntr - HPesoSaida) FROM Hóspede WHERE HPesoSaida 
IS NOT NULL GROUP BY PCod; 
 
7. Idem à questão anterior, só que listando apenas os programas que tiveram 
perda de peso média superior a 10 Kg. 
SELECT PCod, AVG(HPesoEntr - HPesoSaida) FROM Hóspede WHERE HPesoSaida 
IS NOT NULL GROUP BY PCod HAVING AVG(HPesoEntr - HPesoSaida) > 10; 
 
8. Para cada programa, quantos hóspedes obtiveram perda de peso superior ao 
valor de redução prometido pelo respectivo programa (PReduçãoPeso). 
SELECT PCod, COUNT(*) FROM Hóspede, Programa WHERE Hóspede.PCod = 
Programa.PCod AND HPesoSaida IS NOT NULL AND (HPesoEntr – HpesoSaida) > 
PReduçãoPeso GROUP BY PCod; 
 
9. Para cada programa, os nomes das atividades que fazem parte do mesmo, 
ordenado pelo código do programa e pelo nome da atividade. 
SELECT PCod, ANome FROM Atividade, Ativ_Progr WHERE Atividade.ACod = 
Ativ_Progr.ACod ORDER BY PCod, ANome; 
 
10. Para cada um dos treinadores, uma lista com o nome dos hóspedes que estão 
sendo atendidos por ele atualmente. 
SELECT ATreinador, HNome FROM Hóspede, Ativ_Progr, Atividade WHERE 
Hóspede.PCod = Ativ_Progr.PCod AND Ativ_Progr.ACod = Atividade.ACod AND 
HDtaSaida IS NULL ORDER BY ATreinador, HNome; 
 
11. Nomes dos treinadores que não participam do programa 5. 
SELECT ATreinador FROM Atividade WHERE NOT EXISTS (SELECT * FROM 
Ativ_Progr WHERE Atividade.ACod = Ativ_Progr.ACod AND PCod = 5); 
 
12. Peso de entrada dos hóspedes inscritos no programa 2, cujo peso de entrada 
foi superior ao de todos os hóspedes inscritos no programa 1. 
SELECT HPesoEntr FROM Hóspede WHERE PCod = 2 AND HPesoEntr > ALL 
(SELECT HPesoEntr FROM Hóspede WHERE PCod = 1); 
 
13. Cardápíos cujo valor de calorias é inferior à média de todos os cardápios. 
SELECT CCod FROM Cardápio WHERE CCalorias < (SELECT AVG(CCalorias) 
FROM Cardápio); 
 
14. Programas que possuem a mesma redução de peso do programa 5. 
SELECT PCod FROM Programa WHERE PReduçãoPeso = (SELECT PReduçãoPeso 
FROM Programa WHERE PCod = 5); 
 
 
B) Crie uma visão (em SQL) que permita a um hóspede consultar as seguintes 
informações: 
- nome do hóspede; 
- qtde de dias em que está hospedado; 
- seu peso na entrada; 
- a redução de peso esperada; 
- a quantidade de calorias do seu cardápio. 
CREATE VIEW Vis_Consulta_Hóspede (Nome_hóspede, Qtde_dias_hospedado, 
Peso_entrada, Redução_peso_esperada, Qtde_calorias_cardápio) AS SELECT 
HNome, now() - HDtaEntr, HPesoEntr, PReduçãoPeso, CCalorias FROM Hóspede, 
Programa, Cardápio WHERE Hóspede.PCod = Programa.PCod AND Programa.CCod 
= Cardápio.CCod;