IFPB – CAMPUS CAMPINA GRANDE.
DISCIPLINA: BANCO DE DADOS
Aluno:Aryel de Souza Silva
Atividade 10 - SQL
O DER abaixo apresenta o diagrama referente ao sistema de banco de dados de Fórmula 1.
Considerando o modelo conceitual representado pelo DER acima, implemente as consultas SQL abaixo:
1. Exiba a data da prova mais antiga e data da prova mais recente.
SELECT MIN(dt_prova) AS prova_mais_antiga, MAX(dt_prova) AS prova_mais_recente
FROM prova;
2. Exiba a quantidade de pilotos que nasceram na Alemanha.
SELECT COUNT(*) AS quantidade_pilotos_alemaes
FROM piloto
WHERE pais = (SELECT cd_pais FROM pais WHERE nm_pais = 'Alemanha');
3. Exiba a quantidade de provas realizadas no mês de novembro.
SELECT COUNT(*) AS quantidade_provas_novembro
FROM prova
WHERE MONTH(dt_prova) = 11;
4. Listar o nome de cada equipe seguida do nome do país que ela representa.
SELECT e.nm_eq AS equipe, p.nm_pais AS pais
FROM equipe e
INNER JOIN pais p ON e.pais = p.cd_pais;
5. Listar o nome de cada circuito seguido do nome do país onde é localizado o circuito
SELECT c.nm_cir AS circuito, p.nm_pais AS pais
FROM circuito c
INNER JOIN pais p ON c.pais = p.cd_pais;
6. Exiba a data da prova do GP da Holanda.
SELECT dt_prova
FROM prova
WHERE cd_pro = (SELECT cd_pro FROM circuito WHERE nm_cir =
'GP da Holanda');
7. Exiba o nome dos circuitos que tiveram provas realizadas no mês de dezembro.
SELECT c.nm_cir AS circuito
FROM prova p
INNER JOIN circuito c ON p.circuito = c.cd_cir
WHERE MONTH(p.dt_prova) = 12;
8. Listar o calendário da temporada 2021 com o nome de cada circuito seguido do nome país e da data da prova.
SELECT c.nm_cir AS circuito, p.nm_pais AS pais, p.dt_prova
FROM prova p
INNER JOIN circuito c ON p.circuito = c.cd_cir
WHERE YEAR(p.dt_prova) = 2021;
9. Listar a quantidade de vitórias de Lewis Hamilton.
SELECT COUNT(*) AS quantidade_vitorias
FROM piloto_pos pp
INNER JOIN piloto p ON pp.piloto = p.cd_pil
WHERE p.nm_pil = 'Lewis Hamilton' AND pp.posicao = 1;
10. Listar o nome do piloto quantidade de vitórias de cada piloto que venceu pelo menos uma prova.
SELECT p.nm_pil AS piloto, COUNT(*) AS quantidade_vitorias
FROM piloto_pos pp
INNER JOIN piloto p ON pp.piloto = p.cd_pil
WHERE pp.posicao = 1
GROUP BY p.cd_pil;
11. Listar o pódio do GP Brasil, com a posição e o nome do piloto.
SELECT pp.posicao, p.nm_pil AS piloto
FROM piloto_pos pp
INNER JOIN piloto p ON pp.piloto = p.cd_pil
WHERE pp.prova = (SELECT cd_pro FROM prova WHERE nm_prova = 'GP do Brasil');
12. Listar o nome de cada equipe seguida do nome do país que ela representa, mas listar também os demais
países que não tem representação.
SELECT e.nm_eq AS equipe, COALESCE(p.nm_pais, 'Sem Representação') AS pais
FROM equipe e
LEFT JOIN pais p ON e.pais = p.cd_pais;
13. Listar o nome dos pilotos que obtiveram 1º lugar em alguma prova (posição=1). Não é necessário repetir o
nome do piloto caso ele tenha obtido o 1º lugar em mais de uma prova.
SELECT DISTINCT p.nm_pil AS piloto
FROM piloto_pos pp
INNER JOIN piloto p ON pp.piloto = p.cd_pil
WHERE pp.posicao = 1;
14. Listar os nomes dos pilotos vencedores (obtiveram 1º lugar em alguma prova) de prova e suas respectivas
equipes.
SELECT p.nm_pil AS piloto, e.nm_eq AS equipe
FROM piloto_pos pp
INNER JOIN piloto p ON pp.piloto = p.cd_pil
INNER JOIN equipe e ON p.equipe = e.cd_eq
WHERE pp.posicao = 1;
15. Listar o pódio de cada prova, com a posição, o nome do piloto e o nome do circuito (evento). O pódio é
composta pelos pilotos que obtiveram o primeiro, segundo e terceiro lugar.
SELECT pp.prova, pp.posicao, p.nm_pil AS piloto, c.nm_cir AS circuito
FROM piloto_pos pp
INNER JOIN piloto p ON pp.piloto = p.cd_pil
INNER JOIN prova pr ON pp.prova = pr.cd_pro
INNER JOIN circuito c ON pr.circuito = c.cd_cir
ORDER BY pp.prova, pp.posicao;
16. Listar os nomes dos pilotos vencedores de prova, nome de sua equipe e a quantidade de vitórias de cada
piloto.
SELECT p.nm_pil AS piloto, e.nm_eq AS equipe, COUNT(*) AS quantidade_vitorias
FROM piloto_pos pp
INNER JOIN piloto p ON pp.piloto = p.cd_pil
INNER JOIN equipe e ON p.equipe = e.cd_eq
WHERE pp.posicao = 1
GROUP BY p.cd_pil;
17. Listar os nomes dos pilotos que conseguiram mais de duas vitórias.
SELECT p.nm_pil AS piloto, COUNT(*) AS quantidade_vitorias
FROM piloto_pos pp
INNER JOIN piloto p ON pp.piloto = p.cd_pil
WHERE pp.posicao = 1
GROUP BY p.cd_pil
HAVING COUNT(*) > 2;
18. Exibir o nome dos pilotos que nunca venceram prova.
SELECT p.nm_pil AS piloto
FROM piloto p
WHERE p.cd_pil NOT IN (SELECT piloto FROM piloto_pos WHERE posicao = 1);
