Prova BD2 2013 - Prova 1º GQ - UNICAP

prova_bd2_2013_1gq.sql

--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. (3,0 pontos) Faça uma stored procedure que retorne os nomes de todos os clientes que já
--compraram os três produtos mais vendidos. OBS: considere o campo quantidade para verificar quais os
--produtos mais vendidos. OBS2: considere apenas os 3 mais vendidos, sem ocorrência de empate

CREATE PROCEDURE nomes_empregados
SELECT c.CompanyName
FROM Customers c
JOIN Orders o ON o.CustomerID = c.CustomerID
JOIN Order Details od ON od.OrderID = o.OrderId
WHERE od.ProductID IN (
		SELECT od.ProductID
		FROM Products p
		JOIN Order Details od ON p.ProductID = od.ProductID
		GROUP BY od.ProductID
		ORDER BY SUM(od.ProductID) DESC
		LIMIT 3);
		
--Questão 2. (3,0 pontos) Faça uma stored procedure que retorne a região dos clientes e sua respectiva
--quantidade de compras realizadas que contenham produtos de fornecedores das suas regiões.

CREATE PROCEDURE regiao_cliente_compras
SELECT c.Region, COUNT(o.OrderID) as qtd
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.Region = c.Region
GROUP BY c.Region, o.orderID