/*      ~JETS
        LISTA 1 SQL GDI
        2015.1
*/

--1
SELECT DISTINCT cpf
FROM piloto p, pilota q, aviao a
WHERE q.id_aviao=a.id
AND a.tipo LIKE 'Boeing%'
AND cpf_comandante='34174141720';
 

--2
SELECT DISTINCT COUNT (p.id_aviao)
FROM plano_voo p
        INNER JOIN rota r
                ON p.id_rota=r.id
                AND r.destino='Aeroporto Internacional John F. Kennedy'
        INNER JOIN voo v
                ON p.id_voo=v.id
                AND v.max_velocidade BETWEEN 200 AND 300;
 

--3    
SELECT p.condicoes, COUNT(p.comprimento) AS qtdPistas
FROM pista p
WHERE p.comprimento >
        (SELECT avg(comprimento)
        FROM pista)
GROUP BY p.condicoes;
 

--4
SELECT p.nome, e.rua
FROM escalado esc
        INNER JOIN piloto p
                ON p.cpf=esc.cpf_piloto
        INNER JOIN voo v
                ON v.id=esc.id_voo
                AND v.peso_vazio>200000
        INNER JOIN endereco e
                ON e.cpf_piloto=p.cpf
ORDER BY e.rua;
 

--5
select sum(quantidade_combustivel)
from voo v
inner join (select extract(hour from horario) as hora
  from passagem) p
 on o.hora between 8 and 17;
 

--6

--7

--8

--9
select nome p
from piloto p
 inner join escalado e
  on p.cpf=e.cpf_piloto
 inner join voo v
  on v.id=e.id_voo
 inner join passagem pass
  on v.id=pass.id_voo
  and to_char(pass.horario, 'DD/MM/YYYY') like '02/05/2015';


--10
select r.partida, r.destino, v.quantidade_combustivel
from plano_voo pv
 inner join rota r
  on r.id=pv.id_rota
 inner join voo v
  on v.id=pv.id_voo
  and v.quantidade_combustivel > 
  all(select v2.quantidade_combustivel
   from voo v2
   inner join plano_voo pv2
    on v2.id=pv2.id_voo
   inner join aviao a
    on a.id=pv2.id_aviao
    and a.tipo='Boeing 747');

--