20220531

use exercicio13
--1)Apresentar marca e modelo de carro e a soma total da distância percorrida pelos carros,
--em viagens, de uma dada empresa, ordenado pela distância percorrida
SELECT
    car.marca
    , car.modelo
    , SUM(v.distanciaPercorrida) AS soma_distancia
FROM carro AS car
    INNER JOIN viagem AS v
     ON v.idCarro = car.id
GROUP BY car.id,car.modelo,car.marca
ORDER BY soma_distancia DESC
--2)Consultar Nomes das empresas que não tem carros cadastrados
SELECT
    emp.nome
FROM empresa AS emp
    LEFT OUTER JOIN carro AS car
      ON emp.id = car.idEmpresa
WHERE car.id IS NULL
--3)Consultar Marca e modelos dos carros que não fizeram viagens
SELECT
    car.marca
    , car.modelo
FROM carro AS car
    LEFT OUTER JOIN viagem AS v
        ON v.idCarro = car.id
WHERE v.idCarro IS NULL
--4)Consultar quantas viagens foram feitas por cada carro (marca e modelo) de cada empresa
--em ordem ascendente de nome de empresa e descendente de quantidade
SELECT
    COUNT(v.idCarro) AS qtd_viagens
    , car.marca
    , car.modelo
    , emp.nome
FROM carro AS car
    INNER JOIN viagem AS v
        ON v.idCarro = car.id
    INNER JOIN empresa AS emp
        ON emp.id = car.idEmpresa
GROUP by car.id,car.marca,car.modelo,emp.nome
ORDER BY emp.nome ASC, qtd_viagens DESC

--5)Consultar o nome da empresa, a marca e o modelo do carro, a distância percorrida
--e o valor total ganho por viagem, sabendo que para distâncias inferiores a 1000 km, o valor é R$10,00
--por km e para viagens superiores a 1000 km, o valor é R$15,00 por km.
SELECT
    emp.nome
    , car.marca
    , car.modelo
    , v.distanciaPercorrida
    , CASE WHEN (v.distanciaPercorrida < 1000)
        THEN
            (v.distanciaPercorrida * 10)
        ELSE
            (v.distanciaPercorrida * 15)
     END AS 'Valor Total Viagem'
FROM empresa AS emp
    INNER JOIN carro AS car
        ON car.idEmpresa = emp.id
    INNER JOIN viagem AS v
        ON v.idCarro = car.id
GROUP BY car.id,distanciaPercorrida,car.marca,car.modelo,emp.nome
ORDER BY distanciaPercorrida DESC