# Using Joins - Exercise

Using Joins - Exercise

# Using Joins

Duration: 25 to 40 minutes.

In this exercise, you will practice using joins.

1. Create a report that shows the order ids and the associated employee names for orders that shipped after the required date. It should return the following. There should be 37 rows returned.
2. Create a report that shows the total quantity of products (from the `Order_Details` table) ordered. Only show records for products for which the quantity ordered is fewer than 200. The report should return the following 5 rows.
3. Create a report that shows the total number of orders by Customer since December 31, 1996. The report should only return rows for which the NumOrders is greater than 15. The report should return the following 5 rows.
4. Create a report that shows the company name, order id, and total price of all products of which Northwind has sold more than \$10,000 worth. There is no need for a `GROUP BY` clause in this report.

## Solution:

SubqueriesJoinsUnions/Solutions/Joins.sql
```SELECT e.FirstName, e.LastName, o.OrderID
FROM Employees e JOIN Orders o ON
(e.EmployeeID = o.EmployeeID)
WHERE o.RequiredDate < o.ShippedDate
ORDER BY e.LastName, e.FirstName;

SELECT p.ProductName, SUM(od.Quantity) AS TotalUnits
FROM Order_Details od JOIN Products p ON
(p.ProductID = od.ProductID)
GROUP BY p.ProductName
HAVING SUM(Quantity) < 200;

/******************************
For the third problem, both of the solutions below will work in SQL Server

Oracle Solution
******************************/
SELECT c.CompanyName, COUNT(o.OrderID) AS NumOrders
FROM Customers c JOIN Orders o ON
(c.CustomerID = o.CustomerID)
WHERE OrderDate > '31-Dec-1996'
GROUP BY c.CompanyName
HAVING COUNT(o.OrderID) > 15
ORDER BY NumOrders DESC;

/******************************
MySQL
******************************/
SELECT c.CompanyName, COUNT(o.OrderID) AS NumOrders
FROM Customers c JOIN Orders o ON
(c.CustomerID = o.CustomerID)
WHERE OrderDate > '1996-12-31'
GROUP BY c.CompanyName
HAVING COUNT(o.OrderID) > 15
ORDER BY NumOrders DESC;

SELECT c.CompanyName, o.OrderID,
od.UnitPrice * od.Quantity * (1-od.Discount) AS TotalPrice
FROM Order_Details od
JOIN Orders o ON (o.OrderID = od.OrderID)
JOIN Customers c ON (c.CustomerID = o.CustomerID)
WHERE od.UnitPrice * od.Quantity * (1-od.Discount) > 10000
ORDER BY TotalPrice DESC;

/*
SQL Server users will replace Order_Details with "Order Details"
*/```