Exercicio Implementação BDs com respostas

Esta é uma pré-visualização de arquivo. Entre para ver o arquivo original

--1) Informe os modelos de aviões dos vôos que tiveram mais
-- de 2 tripulantes e mais de 5 passageiros ao mesmo tempo
select distinct m.descricao
from modelos m
inner join avioes a on a.cod_modelo = m.codigo
inner join voos v on v.cod_aviao = a.codigo
inner join (select num_voo, count(*) as qtd_trip
 from voos_tripulantes
			group by num_voo) vt on vt.num_voo = v.numero
inner join (select num_voo, count(*) as qtd_pass
 from voos_passageiros
			group by num_voo) vp on vp.num_voo = v.numero
where vt.qtd_trip > 2 and vp.qtd_pass > 5
--Outra forma
select distinct m.descricao
from modelos m
inner join avioes a on a.cod_modelo = m.codigo
inner join voos v on v.cod_aviao = a.codigo
where (select count(*) as qtd_trip
 from voos_tripulantes
	 where num_voo = v.numero) > 2
 and (select count(*) as qtd_pass
 from voos_passageiros
	 where num_voo = v.numero) > 5
--2) Informe os nomes dos aeroportos que foram aeroporto de
-- chegada ou de partida de pelo menos 5 vôos
select a.nome
from aeroportos a
left join (select cod_aer_che, count(*) as qtd_voos
 from voos
 group by cod_aer_che) vc on vc.cod_aer_che = a.codigo
left join (select cod_aer_par, count(*) as qtd_voos
 from voos
 group by cod_aer_par) vp on vp.cod_aer_par = a.codigo
where vc.qtd_voos >= 5
 or vp.qtd_voos >= 5
 or vc.qtd_voos + vp.qtd_voos >= 5
--Outra forma
select a.nome
from aeroportos a
where (select count(*) as qtd_voos
 from voos
 where cod_aer_che = a.codigo) +
 (select count(*) as qtd_voos
 from voos
 where cod_aer_par = a.codigo) >= 5
--3) Retorne os nomes dos passageiros que voaram mais de uma vez
-- no mesmo avião
select distinct p.nome
from pessoas p
inner join voos_passageiros vp on vp.cpf_passageiro = p.cpf
inner join voos v on vp.num_voo = v.numero
where v.cod_aviao in (select cod_aviao
 from voos v2
 inner join voos_passageiros vp2 on vp2.num_voo = v2.numero
 where vp2.cpf_passageiro = vp.cpf_passageiro
 and v2.numero <> v.numero)
--Outra forma
select distinct p.nome
from pessoas p
inner join voos_passageiros vp on vp.cpf_passageiro = p.cpf
inner join voos v on vp.num_voo = v.numero
where exists (select v2.numero
 from voos v2
 inner join voos_passageiros vp2 on vp2.num_voo = v2.numero
 where vp2.cpf_passageiro = vp.cpf_passageiro
 and v2.numero <> v.numero
 and v2.cod_aviao = v.cod_aviao)
--Outra forma sem sub-consulta
select distinct p.nome
from pessoas p
inner join voos_passageiros vp on vp.cpf_passageiro = p.cpf
inner join voos v on vp.num_voo = v.numero
inner join voos v2 on v2.numero <> v.numero and v2.cod_aviao = v.cod_aviao
inner join voos_passageiros vp2 on vp2.num_voo = v2.numero and vp2.cpf_passageiro = p.cpf
--4) Diga os nomes dos funcionários que foram passageiros de vôos em
-- aviões de que já foram tripulantes (em outros vôos)
select distinct p.nome
from pessoas p
inner join funcionarios f on f.cpf = p.cpf
inner join voos_passageiros vp on vp.cpf_passageiro = f.cpf
inner join voos v on vp.num_voo = v.numero
where exists (select vt.num_voo
 from voos_tripulantes vt on vt.num_voo = v2.numero
 inner join funcionarios f2 on f2.matricula = vt.mat_tripulante
 inner join voos v2 on vt.num_voo = v2.numero
 where f2.matricula = f.matricula
 and vt.num_voo <> vp.num_voo
 and v2.cod_aviao = v.cod_aviao)
--Outra forma
select distinct p.nome
from pessoas p
inner join funcionarios f on f.cpf = p.cpf
inner join voos_passageiros vp on vp.cpf_passageiro = f.cpf
inner join voos v on v.numero = vp.num_voo
inner join voos_tripulantes vt
	on vt.mat_tripulante = f.matricula and vt.num_voo <> v.numero
inner join voos v2 on v2.numero = vt.num_voo and v2.cod_aviao = v.cod_aviao