-- 1. Quantas viagens saíram de cada aeroporto em determinada data
SELECT DISTINCT a.nm_aeroporto, COUNT(v.id_viagem)
    FROM aeroporto a inner join terminal t on a.id_aeroporto = t.id_aeroporto
                     inner join viagem v on t.id_terminal = v.id_terminal_saida
    WHERE v.dt_saida between '2016-01-01' and '2016-12-31'
GROUP BY a.nm_aeroporto ORDER BY 2 DESC;


-- 2. Quanto cada passageiro gastou com bilhetes
SELECT DISTINCT ps.id_passageiro AS 'id', ps.nm_completo AS 'Nome', SUM(pa.vl_parcela) AS 'Gastos'
    FROM passageiro ps inner join bilhete b on ps.id_passageiro = b.id_passageiro
                       inner join parcela pa on pa.id_bilhete = b.id_bilhete
    WHERE pa.dt_pagamento IS NOT null
GROUP BY ps.id_passageiro ORDER BY 3 DESC;


-- 3. Número de parcelas que não foram pagas
SELECT COUNT(pa.id_parcela)
    FROM parcela pa
    WHERE pa.dt_pagamento IS NULL;


-- 4. Quantas viagens as aeronaves de cada país já fizeram
SELECT DISTINCT n.nm_nacionalidade as 'Pais', COUNT(av.id_aeronave) as 'N Aeronaves', COUNT(v.id_viagem) as 'N Viagens'
    FROM nacionalidade n inner join aeronave av on av.id_nacionalidade = n.id_nacionalidade
                         inner join viagem v on v.id_aeronave = av.id_aeronave
GROUP BY n.nm_nacionalidade ORDER BY 3 DESC;


-- 5. Aeroporto com maior número de bilhetes vendidos à vista
SELECT DISTINCT a.nm_aeroporto, COUNT(b.id_bilhete)
    FROM aeroporto a inner join terminal t on a.id_aeroporto = t.id_aeroporto
                     inner join viagem v on t.id_terminal = v.id_terminal_saida
                     inner join assento ass on v.id_viagem = ass.id_viagem
                     inner join bilhete b on ass.id_assento = b.id_assento
    WHERE b.qt_parcelas = 1
GROUP BY a.id_aeroporto ORDER BY 2 DESC;


-- 6. Nacionalidade com maior número de parcelas
SELECT DISTINCT n.nm_nacionalidade, COUNT(pr.id_parcela)
    FROM nacionalidade n inner join passageiro p on n.id_nacionalidade = p.id_nacionalidade
                         inner join bilhete b on p.id_passageiro = b.id_passageiro
                         inner join parcela pr on b.id_bilhete = pr.id_bilhete
GROUP BY n.nm_nacionalidade ORDER BY 2 DESC;


-- 7. País que mais recebe viagens
SELECT DISTINCT n.nm_nacionalidade, COUNT(v.id_viagem)
    FROM nacionalidade n inner join aeroporto ar on n.id_nacionalidade = ar.id_nacionalidade
                         inner join terminal t on ar.id_aeroporto = t.id_aeroporto
                         inner join viagem v on t.id_terminal = v.id_terminal_destino
GROUP BY n.nm_nacionalidade ORDER BY 2 DESC;

-- 8. Porcentagem de preenchimento de cada viagem
SELECT DISTINCT v.id_viagem, (COUNT(ass.id_assento) * 100) / av.qt_assentos AS '% preenchimento'
    FROM assento ass inner join viagem v on ass.id_assento = v.id_viagem
                     inner join aeronave av on v.id_aeronave = av.id_aeronave
    WHERE ass.fg_disponivel = false
GROUP BY v.id_viagem ORDER BY 2 DESC;