USE OLTP
--A sales and its product
SELECT s.id,p.code,s.sale_price,s.quantity_sold
FROM sales_with_pk as s JOIN products as p ON s.product_code=p.code

--Each sale only has a single product.
SELECT s.id,p.code,s.sale_price,s.quantity_sold
FROM sales_with_pk as s JOIN products as p ON s.product_code=p.code
WHERE s.id=1

--But each product can be sold lots of times
SELECT s.id,p.code,s.sale_price,s.quantity_sold
FROM sales_with_pk as s JOIN products as p ON s.product_code=p.code
WHERE code = 'H33'

--Table in reverse order
--many
SELECT p.code,s.id,s.sale_price,s.quantity_sold
FROM products as p JOIN sales_with_pk as s ON s.product_code=p.code
WHERE code = 'H33'
--to one
SELECT p.code,s.id,s.sale_price,s.quantity_sold
FROM products as p JOIN sales_with_pk as s ON s.product_code=p.code
WHERE id=1

--Sale has one date
SELECT s.id,s.date,t.year,t.month,t.day
FROM sales_with_pk as s JOIN times as t ON s.date=t.date
WHERE id = 1

--Date has many sales
SELECT s.id,s.date,t.year,t.month,t.day
FROM sales_with_pk as s JOIN times as t ON s.date=t.date
WHERE t.date = '2016-01-01'

--Now consider what happens if we didn’t do a distinct on our times table.
DROP TABLE times

SELECT date
	  ,datepart(year,date) as year
      ,datepart(month,date) as month
	  ,datepart(quarter,date) as quarter
	  ,datepart(day,date) as day
	  ,cast(null as nvarchar(2000)) as notes
INTO times
FROM sales

--Many dates for a sale
SELECT s.id,s.date,t.year,t.month,t.day
FROM sales_with_pk as s JOIN times as t ON s.date=t.date
WHERE id = 1

--Total number of sales for each year
SELECT t.year,count(*) number_of_sales
FROM sales_with_pk as s JOIN times as t ON s.date=t.date
GROUP BY t.year

--Restore uniqueness to times table
DROP TABLE times

SELECT distinct date
	  ,datepart(year,date) as year
      ,datepart(month,date) as month
	  ,datepart(quarter,date) as quarter
	  ,datepart(day,date) as day
	  ,cast(null as nvarchar(2000)) as notes
INTO times
FROM sales

--Now our totals are correct
SELECT t.year,count(*) number_of_sales
FROM sales_with_pk as s JOIN times as t ON s.date=t.date
GROUP BY t.year

--Students and Classes
CREATE TABLE classes(
id int identity primary key,
name char(30))

INSERT INTO classes
VALUES('Math')
INSERT INTO classes
VALUES('English')
INSERT INTO classes
VALUES('Science')

CREATE TABLE students (
id int identity primary key,
name char(120))

INSERT INTO students(name)
VALUES('a')
INSERT INTO students
VALUES('b')
INSERT INTO students
VALUES('c')

SELECT * FROM classes
SELECT * FROM students

--Bridging Table
go
CREATE TABLE enrolments
(student_id int,class_id int)
go
INSERT INTO enrolments(student_id,class_id)
SELECT 1,1
UNION
SELECT 1,2
UNION
SELECT 2,1
UNION
SELECT 2,3
UNION
SELECT 3,2
UNION
SELECT 3,3

SELECT * FROM enrolments

--Students and classes
SELECT s.name,c.name
FROM enrolments as e JOIN students as s ON e.student_id=s.id
                      JOIN classes as c ON e.class_id=c.id
