Ejercicios Resueltos de SQL: Consultas con JOIN, UNION y GROUP BY sobre Ventas

Ejercicio 1

(SELECT clt_id AS idcliente, 'Oro' AS Nivel, CONCAT(clt_nome, ',', clt_apelidos) AS Nome FROM clientes
JOIN vendas ON clt_id = ven_cliente
JOIN detalle_vendas ON ven_id = dev_venda
WHERE (YEAR(ven_data) = 2012)
GROUP BY clt_id
HAVING (SUM(dev_cantidade * (dev_prezo_unitario * (1 - IFNULL(dev_desconto / 100, 0)))) > 5000 AND SUM(dev_cantidade * (dev_prezo_unitario * (1 - IFNULL(dev_desconto, 0)))) < 1500))

UNION

(SELECT clt_id AS idcliente, 'Plata' AS Nivel, CONCAT(clt_nome, ',', clt_apelidos) AS Nome FROM clientes
JOIN vendas ON clt_id = ven_cliente
JOIN detalle_vendas ON ven_id = dev_venda
WHERE YEAR(ven_data) = 2012
GROUP BY clt_id
HAVING (SUM(dev_cantidade * (dev_prezo_unitario * (1 - IFNULL(dev_desconto / 100, 0)))) > 0 AND SUM(dev_cantidade * (dev_prezo_unitario * (1 - IFNULL(dev_desconto, 0)))) < 5000))

UNION

(SELECT clt_id AS idcliente, 'Diamante' AS Nivel, CONCAT(clt_nome, ',', clt_apelidos) AS Nome FROM clientes
JOIN vendas ON clt_id = ven_cliente
JOIN detalle_vendas ON ven_id = dev_venda
WHERE (YEAR(ven_data) = 2012)
GROUP BY clt_id
HAVING (SUM(dev_cantidade * (dev_prezo_unitario * (1 - IFNULL(dev_desconto / 100, 0)))) > 15000))

ORDER BY Nivel;

#Me falta agrupar por dinero gastado

Ejercicio 2

(SELECT clt_id AS ID, CONCAT(clt_nome, ',', clt_apelidos) AS NombreCompleto, COUNT(*) AS Boletos FROM clientes
JOIN vendas ON clt_id = ven_cliente
WHERE ven_tenda = (SELECT tda_id FROM tendas WHERE tda_poboacion = 'PADRON')
GROUP BY clt_id)

UNION

(SELECT emp_id AS ID, CONCAT(emp_nome, ',', emp_apelidos) AS NombreCompleto, (YEAR(CURDATE()) - YEAR(emp_alta)) AS Boletos FROM empregados
JOIN vendas ON emp_id = ven_empregado
WHERE ven_tenda = (SELECT tda_id FROM tendas WHERE tda_poboacion = 'PADRON')
GROUP BY emp_id);

Ejercicio 3

SELECT art_codigo, SUM(dev_cantidade) AS Cantidade, IFNULL(dev_desconto, 0) AS Desconto FROM artigos
JOIN detalle_vendas ON art_codigo = dev_artigo
JOIN vendas ON dev_venda = ven_id
WHERE YEAR(ven_data) = 2012
GROUP BY art_codigo, dev_desconto
ORDER BY art_codigo, dev_desconto DESC;

Ejercicio 4

SELECT art_provedor AS Provedor, art_stock AS Stock, art_codigo, IFNULL(SUM(dev_cantidade), 0) AS TotalVendas FROM artigos
LEFT JOIN detalle_vendas ON art_codigo = dev_artigo
GROUP BY art_provedor, art_codigo
ORDER BY TotalVendas DESC;

Ejercicio 5

SELECT pai_nome AS Pais, COUNT(clt_pais) AS TotalClientes, SUM(dev_cantidade * (dev_prezo_unitario * (1 - IFNULL(dev_desconto / 100, 0)))) AS TotalVentas, MIN(ven_data) AS PrimeiraData, MAX(ven_data) AS UltData FROM clientes
JOIN vendas ON clt_id = ven_cliente
JOIN detalle_vendas ON ven_id = dev_venda
JOIN paises ON clt_pais = pai_id
GROUP BY pai_nome;