prova_bd2_2012_1gq.sql

--Os seguintes esquemas serão utilizados como base para a resolução desta prova. Chaves primárias estão
--sublinhadas e chaves estrangeiras estão em itálico.
--Customers (CustomerID, CompanyName, ContactName, Address, City, Region, Country, Phone, Fax)
--Employees (EmployeeID, LastName, FirstName, BirthDate, HireDate, Address, City, Region, Country)
--Orders (OrderID, CustomerID, EmployeeID, OrderDate, ShippedDate)
--Order Details (OrderID, ProductID, UnitPrice, Quantity, Discount)
--Products (ProductID, ProductName, SupplierID, CategoryID, UnitPrice, UnitsInStock, ReorderLevel,
--Discontinued)
--Suppliers (SupplierID, CompanyName, ContactName, Address, City, Region, Country, Phone, Fax,
--HomePage)
--Categories (CategoryID, CategoryName, Description, Picture)

--Questão 1. (2,5 pontos) Faça uma stored procedure que receba como parâmetro um nome de país, e
--encontre o cliente que mais comprou produtos de fornecedores deste país. OBS: se em um pedido,
--existirem vários produtos de fornecedores deste país, só considere este pedido uma única vez.

CREATE PROCEDURE cliente_que_mais_comprou (nome_pais VARCHAR)
SELECT c.CustomerID, c.CompanyName
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
JOIN Order Details od ON od.OrderID = o.OrderID
JOIN Products p ON p.ProductID = od.ProductID
JOIN Suppliers s ON s.SupplierID = p.SupplierID AND s.Country = nome_pais
GROUP BY od.ProductID, c.CustomerID, c.CompanyName
ORDER BY COUNT(DISTINCT od.ProductID) as DESC
LIMIT 1;

--Questão 2. (2,5 pontos) Encontre o nome do cliente que mais economizou com os descontos de todos os
--seus pedidos feitos.

SELECT c.CompanyName, c.CompanyName
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
JOIN Order Details od ON od.OrderID = o.OrderID
GROUP BY o.OrderID, od.Discount, c.CustomerID, c.CompanyName
ORDER BY SUM(od.Discount) DESC
LIMIT 1;