USE Northwind

--Advanced queries on joined tables

--INNER JOIN join condition in ON clause
select c.categoryid, categoryname, productname 
from categories c join products p -- the categories table is aliased with a c
ON c.categoryid = p.categoryid

--INNER JOIN but same join condition in WHERE clause
select c.categoryid, categoryname, productname 
from categories c, products p 
WHERE c.categoryid = p.categoryid

--INNER JOIN 
--Which customers are using a shipping company in the USA?
SELECT o.CustomerID, o.OrderID, s.CompanyName as ShipCompany, o.ShipCountry
FROM Orders o JOIN Shippers s
      ON o.ShipVia = s.ShipperID
WHERE ShipCountry = 'USA'
ORDER BY o.CustomerID, s.CompanyName

--LEFT OUTER JOIN all rows in left table but
--only rows that meet join conditions in right table
--Check row 108, since join condition o.ShipCity = c.City not met null values are inserted
--That is, there is a row for an Order without a Customer
SELECT o.OrderID, o.CustomerID, c.ContactName, c.City
FROM Orders o  LEFT JOIN Customers c
      ON o.CustomerID = c.CustomerID
      AND o.ShipCity = c.City
ORDER BY o.OrderID


--RIGHT OUTER JOIN all rows in right table but
--only rows that meet join conditions in left table
--Check rows 1,2,3, since join condition not met null values are inserted on left
--That is, there are rows for Customers  without an Order
SELECT o.OrderID, o.CustomerID, c.ContactName, c.City
FROM Orders o RIGHT JOIN Customers c
      ON o.CustomerID = c.CustomerID
      AND o.ShipCity = c.City
ORDER BY o.OrderID

--FULL OUTER JOIN all rows in right and left tables 
SELECT o.OrderID, o.CustomerID, c.ContactName, c.City
FROM Orders o FULL OUTER JOIN Customers c
      ON o.CustomerID = c.CustomerID
      AND o.ShipCity = c.City
ORDER BY o.OrderID

--CROSS JOIN the product of all rows in right for each row in left tables 
SELECT o.OrderID, o.CustomerID, c.ContactName, c.City
FROM Orders o CROSS JOIN Customers c
ORDER BY o.OrderID

--************

--SUB QUERIES return value for WHERE the condition
--Orders taken by Seattle employees
SELECT OrderID, EmployeeID AS EmpID
FROM Orders
WHERE EmployeeID IN --Look in a different table
	 (
	  SELECT EmployeeID
	  FROM Employees as e
	  WHERE City = 'Seattle'
	  )
ORDER BY OrderID

--ALL Order IDs where UnitPrice is greater than the   
--maximum UnitPrice taken by EmployeeID=5
SELECT OrderID, UnitPrice
FROM [Order Details]
WHERE UnitPrice > ALL
	 (
	 SELECT UnitPrice --Look in 2 joined table
	 FROM [Order Details] JOIN Orders
	 ON [Order Details].OrderID = Orders.OrderID
	 AND Orders.EmployeeID = 5
	  )
ORDER BY UnitPrice, OrderID

--All Order IDs for London customers 
SELECT Orders.OrderID, Orders.CustomerID
FROM Orders
WHERE EXISTS --Look in Customers table
	 (
	 SELECT * FROM Customers
	 WHERE Customers.CustomerID = Orders.CustomerID  --join condition
	      AND Customers.City = 'London'
	 )
ORDER BY OrderID

--List products ordered with sum of numbers ordered for each unit price
--Add group totals and grand total rows (at top)

--List all products ordered by ID with the total number ordered or each price & product
--Include a grand total of all ordered products at the top
SELECT ProductID, UnitPrice, SUM(Quantity) AS 'Toltal'
FROM [Order Details]
GROUP BY ProductID, UnitPrice
WITH ROLLUP
ORDER BY ProductID

-- Try replacing SUM() with AVG, COUNT, MAX, or MIN.

-- Compute the total unit price and show at bottom of result set
SELECT ProductID, UnitPrice
FROM [Order Details]
COMPUTE SUM(UnitPrice)

-- Compute the total unit price for each product id
-- then group products by id and show at bottom of group
SELECT ProductID, UnitPrice
FROM [Order Details]
ORDER BY ProductID
COMPUTE SUM(UnitPrice) BY ProductID