/*
CIS 324

JOINS

pp. 74 (relational operation)
Natural Join

pp. 264-268, 293-300

*/

USE Northwind



/*
List Products.ProductID, Products.CategoryID, Products.ProductName, 
Products.UnitPrice and Categories.CategoryName 
for products with a unitprice greater than 45
*/

SELECT *
FROM Products, Categories
order by ProductID

--The above query will produce 80*8=640 rows of output

SELECT 80*8 AS NumberOfRows

--How many rows for the query below?

SELECT * 
FROM Customers, Employees, Orders, [Order Details], Products, Categories

SELECT (SELECT CAST(COUNT(*) AS REAL) FROM Customers) *
       (SELECT CAST(COUNT(*) AS REAL) FROM Employees) *
       (SELECT CAST(COUNT(*) AS REAL) FROM Orders) *
       (SELECT CAST(COUNT(*) AS REAL) FROM [Order Details]) *
       (SELECT CAST(COUNT(*) AS REAL) FROM Products) *
       (SELECT CAST(COUNT(*) AS REAL) FROM Categories) AS NumberOfRows

/*
That is: 937,538,810,000 rows! 937 billion....


We could also ask the dbms to estimate for us before running the query

SET SHOWPLAN_ALL { ON | OFF }

*/
SET SHOWPLAN_ALL  OFF

/*
Creating links through foreign keys pp. 264
What is a primary key?
*/

SELECT * FROM Categories
SELECT * FROM Products ORDER BY CategoryID

SELECT *
FROM Products, Categories


--Add pk=fk comparison
SELECT *
FROM Categories, Products
WHERE Categories.CategoryID=Products.CategoryID --Pk=Fk
--Add UnitPrice > 45 condition
--You will need n-1 pk=fk comparison where n = number of tables
--in the FROM clause

SELECT *
FROM Categories, Products
WHERE Categories.CategoryID=Products.CategoryID --Pk=Fk
      AND UnitPrice > 45
--Illustrate with INNER JOIN syntax

SELECT *
FROM Categories INNER JOIN Products
     ON Categories.CategoryID=Products.CategoryID --Pk=Fk
WHERE UnitPrice > 45


--Note the absence of categories 2 and 5 in the > 45 query

/*
Generic syntax of a JOIN: 2-table One-to-Many relationship
Table1 one-to-many Table2

SELECT Table1.Attribute, Table2.Attribute...
FROM Table1, Table2
WHERE Table1.PrimaryKey=Table2.ForeignKey AND
      condition...

SELECT Table1.Attribute, Table2.Attribute...
FROM Table1 INNER JOIN Table2
  ON Table1.PrimaryKey=Table2.ForeignKey 
  AND condition...

You sometimes put the condition in a WHERE clause sometimes
as an AND with the JOIN ON. It depends on the order in which
the operation is executed by the optimizer. You have to check...

Generic syntax of a JOIN: 3-table: One-to-Many-to-One relationship
Table1 one-to-many Table2 many-to-one Table3

SELECT Table1.Attribute, Table2.Attribute, Table3.Attribute...
FROM Table1 
  INNER JOIN Table2
    ON Table1.PrimaryKey=Table2.ForeignKey 
  INNER JOIN Tabl3
    ON Table3.PrimaryKey=Table2.ForeignKey 
  AND Condition...

SELECT Table1.Attribute, Table2.Attribute, Table3.Attribute...
FROM Table1, Table2, Table3
WHERE Table1.PrimaryKey=Table2.ForeignKey AND
      Table3.PrimaryKey=Table2.ForeignKey AND
      condition...

*/

/*
Build the query to generate the data for Order 10250 (see handout)
*/


--Start with the itemized list table (Orders) WHERE OrderID = 10250

SELECT *
FROM Orders
WHERE OrderID = 10250

--Enumerate attributes from Orders. Note the aliases.
SELECT OrderID, Orderdate,*
FROM Orders
WHERE OrderID = 10250

--Join with [Order Details] to retrieve line items 
SELECT Orders.OrderID, Orderdate,OrderDetail.*
FROM Orders, [Order Details] AS OrderDetail
WHERE Orders.OrderID=OrderDetail.OrderID AND --Pk=Fk
      Orders.OrderID = 10250

--Enumerate attributes from [Order Details]
SELECT Orders.OrderID, Orderdate, 
       ProductID, UnitPrice*(1-Discount) AS 'Ext Price', 
       Quantity AS Qty, OrderDetail.*
FROM Orders, [Order Details] AS OrderDetail
WHERE Orders.OrderID=OrderDetail.OrderID AND --Pk=Fk
      Orders.OrderID = 10250

--Join with Products
SELECT Orders.OrderID, Orderdate, 
       Products.ProductID,
       OrderDetail.UnitPrice*(1-Discount) AS 'Ext Price', 
       Quantity AS Qty, Products.*
FROM Orders, [Order Details] AS OrderDetail, Products
WHERE Orders.OrderID=OrderDetail.OrderID AND --Pk=Fk
      Products.ProductID=OrderDetail.ProductID AND --Pk=Fk
      Orders.OrderID = 10250

-- Enumerate attributes from Products
SELECT Orders.OrderID, Orderdate, 
       Products.ProductID,Products.ProductName AS Description, 
       OrderDetail.UnitPrice*(1-Discount) AS 'Ext Price', 
       Quantity AS Qty, Products.*
FROM Orders, [Order Details] AS OrderDetail, Products
WHERE Orders.OrderID=OrderDetail.OrderID AND --Pk=Fk
      Products.ProductID=OrderDetail.ProductID AND --Pk=Fk
      Orders.OrderID = 10250

--Join with Customers
SELECT Orders.OrderID, Orderdate, 
       Products.ProductID,Products.ProductName AS Description, 
       OrderDetail.UnitPrice*(1-Discount) AS 'Ext Price', 
       Quantity AS Qty, Customers.*
FROM Orders, [Order Details] AS OrderDetail, Products, Customers
WHERE Orders.OrderID=OrderDetail.OrderID AND --Pk=Fk
      Products.ProductID=OrderDetail.ProductID AND --Pk=Fk
      Customers.CustomerID=Orders.CustomerID AND --Pk=Fk
      Orders.OrderID = 10250

--Enumberate attributes from Customers
SELECT Orders.OrderID, Orderdate,
       Customers.CompanyName AS Customer, 
       Products.ProductID,Products.ProductName AS Description, 
       OrderDetail.UnitPrice*(1-Discount) AS 'Ext Price', 
       Quantity AS Qty, Customers.*
FROM Orders, [Order Details] AS OrderDetail, Products, Customers
WHERE Orders.OrderID=OrderDetail.OrderID AND --Pk=Fk
      Products.ProductID=OrderDetail.ProductID AND --Pk=Fk
      Customers.CustomerID=Orders.CustomerID AND --Pk=Fk
      Orders.OrderID = 10250

--Join with Employees
SELECT Orders.OrderID, Orderdate,
       Customers.CompanyName AS Customer, 
       Products.ProductID,Products.ProductName AS Description, 
       OrderDetail.UnitPrice*(1-Discount) AS 'Ext Price', 
       Quantity AS Qty, Employees.*
FROM Orders, [Order Details] AS OrderDetail, Products, Customers, Employees
WHERE Orders.OrderID=OrderDetail.OrderID AND --Pk=Fk
      Products.ProductID=OrderDetail.ProductID AND --Pk=Fk
      Customers.CustomerID=Orders.CustomerID AND --Pk=Fk
      Employees.EmployeeID=Orders.EmployeeID AND --Pk=Fk
      Orders.OrderID = 10250

--Enumerate attributes from Employees
SELECT Orders.OrderID, Orderdate,
       Customers.CompanyName AS Customer, 
       Employees.LastName AS Employee,
       Products.ProductID,Products.ProductName AS Description, 
       OrderDetail.UnitPrice*(1-Discount) AS 'Ext Price', 
       Quantity AS Qty, Employees.*
FROM Orders, [Order Details] AS OrderDetail, Products, Customers, Employees
WHERE Orders.OrderID=OrderDetail.OrderID AND --Pk=Fk
      Products.ProductID=OrderDetail.ProductID AND --Pk=Fk
      Customers.CustomerID=Orders.CustomerID AND --Pk=Fk
      Employees.EmployeeID=Orders.EmployeeID AND --Pk=Fk
      Orders.OrderID = 10250

--And finally join with Shippers
SELECT Orders.OrderID, Orderdate,
       Customers.CompanyName AS Customer, 
       Employees.LastName AS Employee,
       Products.ProductID,Products.ProductName AS Description, 
       OrderDetail.UnitPrice*(1-Discount) AS 'Ext Price', 
       Quantity AS Qty, Shippers.*
FROM Orders, [Order Details] AS OrderDetail, 
     Products, Customers, Employees, Shippers
WHERE Orders.OrderID=OrderDetail.OrderID AND --Pk=Fk
      Products.ProductID=OrderDetail.ProductID AND --Pk=Fk
      Customers.CustomerID=Orders.CustomerID AND --Pk=Fk
      Employees.EmployeeID=Orders.EmployeeID AND --Pk=Fk
      Shippers.ShipperID=Orders.ShipVia AND --Pk=Fk
      Orders.OrderID = 10250

--Enumerate attributes from Shippers
SELECT Orders.OrderID, Orderdate,
       Customers.CompanyName AS Customer, 
       Employees.LastName AS Employee,
       Shippers.CompanyName AS Shipper,
       Products.ProductID,Products.ProductName AS Description, 
       OrderDetail.UnitPrice*(1-Discount) AS 'Ext Price', 
       Quantity AS Qty, Shippers.*
FROM Orders, [Order Details] AS OrderDetail, 
     Products, Customers, Employees, Shippers
WHERE Orders.OrderID=OrderDetail.OrderID AND --Pk=Fk
      Products.ProductID=OrderDetail.ProductID AND --Pk=Fk
      Customers.CustomerID=Orders.CustomerID AND --Pk=Fk
      Employees.EmployeeID=Orders.EmployeeID AND --Pk=Fk
      Shippers.ShipperID=Orders.ShipVia AND --Pk=Fk
      Orders.OrderID = 10250


--Derived attributes: Total and GrandTotal
SELECT Orders.OrderID, Orderdate,
       Customers.CompanyName AS Customer, 
       Employees.LastName AS Employee,
       Shippers.CompanyName AS Shipper,
       Products.ProductID,Products.ProductName AS Description, 
       OrderDetail.UnitPrice*(1-Discount) AS 'Ext Price', 
       Quantity AS Qty,
       OrderDetail.UnitPrice*(1-Discount)*Quantity AS Total--, Shippers.*
FROM Orders, [Order Details] AS OrderDetail, 
     Products, Customers, Employees, Shippers
WHERE Orders.OrderID=OrderDetail.OrderID AND --Pk=Fk
      Products.ProductID=OrderDetail.ProductID AND --Pk=Fk
      Customers.CustomerID=Orders.CustomerID AND --Pk=Fk
      Employees.EmployeeID=Orders.EmployeeID AND --Pk=Fk
      Shippers.ShipperID=Orders.ShipVia AND --Pk=Fk
      Orders.OrderID = 10250


--UnitPrice*(1-Discount)*Quantity AS Total


--How to produce the grand total?
/*
At least two ways
1. Execute a GROUP BY and then JOIN the result
2. Insert a correlated subquery in the SELECT statement
*/
--1. is based on the idea that the result of a query is a relation

SELECT Orders.OrderID, SUM(OrderDetail.UnitPrice*(1-Discount)*Quantity) AS GrandTotal
FROM Orders, [Order Details] AS OrderDetail
WHERE Orders.OrderID=OrderDetail.OrderID
GROUP BY Orders.OrderID

CREATE VIEW vwGrandTotalAll
AS
SELECT Orders.OrderID, SUM(OrderDetail.UnitPrice*(1-Discount)*Quantity) AS GrandTotal
FROM Orders, [Order Details] AS OrderDetail
WHERE Orders.OrderID=OrderDetail.OrderID
GROUP BY Orders.OrderID

SELECT *
FROM vwGrandTotalAll

SELECT Orders.OrderID, Orderdate,
       Customers.CompanyName AS Customer, 
       Employees.LastName AS Employee,
       Shippers.CompanyName AS Shipper,
       Products.ProductID,Products.ProductName AS Description, 
       OrderDetail.UnitPrice*(1-Discount) AS 'Ext Price', 
       Quantity AS Qty,
       OrderDetail.UnitPrice*(1-Discount)*Quantity AS Total,
       vwGrandTotalAll.*
FROM Orders, [Order Details] AS OrderDetail, 
     Products, Customers, Employees, Shippers, vwGrandTotalAll
WHERE Orders.OrderID=OrderDetail.OrderID AND --Pk=Fk
      Products.ProductID=OrderDetail.ProductID AND --Pk=Fk
      Customers.CustomerID=Orders.CustomerID AND --Pk=Fk
      Employees.EmployeeID=Orders.EmployeeID AND --Pk=Fk
      Shippers.ShipperID=Orders.ShipVia AND --Pk=Fk
      Orders.OrderID=vwGrandTotalAll.OrderID AND --Pk=Fk
      Orders.OrderID = 10250


--Correlated subquery in SELECT statement
SELECT Orders.OrderID, Orderdate,
       Customers.CompanyName AS Customer, 
       Employees.LastName AS Employee,
       Shippers.CompanyName AS Shipper,
       Products.ProductID,Products.ProductName AS Description, 
       OrderDetail.UnitPrice*(1-Discount) AS 'Ext Price', 
       Quantity AS Qty,
       OrderDetail.UnitPrice*(1-Discount)*Quantity AS Total,
       ('subquery here...') AS GrandTotal --, *
FROM Orders, [Order Details] AS OrderDetail, 
     Products, Customers, Employees, Shippers
WHERE Orders.OrderID=OrderDetail.OrderID AND --Pk=Fk
      Products.ProductID=OrderDetail.ProductID AND --Pk=Fk
      Customers.CustomerID=Orders.CustomerID AND --Pk=Fk
      Employees.EmployeeID=Orders.EmployeeID AND --Pk=Fk
      Shippers.ShipperID=Orders.ShipVia AND --Pk=Fk
      Orders.OrderID = 10250

SELECT Orders.OrderID, Orderdate,
       Customers.CompanyName AS Customer, 
       Employees.LastName AS Employee,
       Shippers.CompanyName AS Shipper,
       Products.ProductID,Products.ProductName AS Description, 
       OrderDetail.UnitPrice*(1-Discount) AS 'Ext Price', 
       Quantity AS Qty,
       OrderDetail.UnitPrice*(1-Discount)*Quantity AS Total,
       (SELECT GrandTotal 
        FROM vwGrandTotalAll 
        WHERE vwGRandTotalAll.OrderID=Orders.OrderID) AS GrandTotal --, *
FROM Orders, [Order Details] AS OrderDetail, 
     Products, Customers, Employees, Shippers
WHERE Orders.OrderID=OrderDetail.OrderID AND --Pk=Fk
      Products.ProductID=OrderDetail.ProductID AND --Pk=Fk
      Customers.CustomerID=Orders.CustomerID AND --Pk=Fk
      Employees.EmployeeID=Orders.EmployeeID AND --Pk=Fk
      Shippers.ShipperID=Orders.ShipVia AND --Pk=Fk
      Orders.OrderID = 10250

--We could then encapsulate this query in a stored procedure
--and parameterize the OrderID