/* Day 5: Joins
Tim Pierson, Dartmouth CS61, Spring 20
*/

use silberschatzbook;

-- demonstrate Catesian product of tables
-- notice Comp Sci. instructors teaching classes in biology, finance, music, etc
-- notice no WHERE clause here, so each row in instructor table matched with each row in teaches table
SELECT i.*, t.*
FROM instructor i, teaches t -- notice no WHERE clause!  
ORDER BY name;

-- add the WHERE clause to get only classes taught by an instructor
SELECT i.*, t.*
FROM instructor i, teaches t
WHERE i.ID = t.ID;


use nyc_inspections;

-- At the end of last class we ran a script to break restaurant_inspection into several tables
-- Let's get a feel for the data in Restaurnts and Inspections tables
SELECT * FROM Restaurants; -- each row has details about one restaurant
SELECT * FROM Inspections; -- each row has a summary of an inspection of one restaurant

-- joins
-- slide 15
-- count how many time each bakery has been inspected
-- first figure out which restaurants are bakeries
-- notice that restaurants have a CuisineID as a FK into Cusine table
SELECT * FROM Restaurants;
-- see what types of cuisines we have
SELECT * FROM Cuisine; -- bakeries have CuisineID = 5

-- now find out how many times each bakery has been inspected (there are 775 of them)
-- uses old style join in WHERE clause
SELECT r.RestaurantID, RestaurantName, count(*)
FROM Restaurants r, Inspections i 
WHERE r.RestaurantID = i.RestaurantID
AND r.CuisineID = 5 -- from looking up bakery CuisineID in Cuisine table
GROUP BY RestaurantID;

-- now do the same thing, but look up bakery id in a sub query
SELECT r.RestaurantID, RestaurantName, count(*)
FROM Restaurants r, Inspections i 
WHERE r.RestaurantID = i.RestaurantID
AND r.CuisineID = (SELECT CuisineID FROM Cuisine WHERE CuisineDescription = 'Bakery') 
GROUP BY RestaurantID;

-- now do the same query, but use a JOIN instead of joining in the WHERE clause
SELECT r.RestaurantID, RestaurantName, count(*)
FROM Restaurants r JOIN Inspections i ON r.RestaurantID = i.RestaurantID
WHERE r.CuisineID = (SELECT CuisineID FROM Cuisine WHERE CuisineDescription = 'Bakery')  -- can still use WHERE to limit results
GROUP BY RestaurantID;

-- another way is to use NATURAL JOIN, which matches on columns with same name in both tables
-- same results as prior query
-- I prefer the prior query, I know for sure what it is joining on!
SELECT RestaurantID, RestaurantName, count(*)
FROM Restaurants NATURAL JOIN Inspections 
WHERE CuisineID = (SELECT CuisineID FROM Cuisine WHERE CuisineDescription = 'Bakery') 
GROUP BY RestaurantID;





-- demo of inner vs. outer join (slides 17-20)
-- create some temporary tables to demonstrate
CREATE TABLE TableA (ID INT, A1 VARCHAR(1));
INSERT INTO TableA VALUES (1,'m'), (2,'n'),(4,'o');

CREATE TABLE TableB (ID INT, A2 VARCHAR(1));
INSERT INTO TableB VALUES (2,'p'), (3,'q'),(5,'r');

-- natural join demo
-- SQL joins where names are the same in both tables (ID here)
-- notice ID column is not repeated
SELECT * FROM TableA a NATURAL JOIN TableB b; 
-- returns:
-- ID 	A1 	A2
-- 2	n	p
-- 
-- note: no duplicate attribute ID
-- Only ID 2 is present in both tables, so only row returned

-- INNER JOIN demo (INNER is default if join type not specified)
-- does the same as NATURAL JOIN, but ID column is duplicated
SELECT * FROM TableA a JOIN TableB b ON a.ID=b.ID; 
-- returns:
-- ID 	A1 	ID 	A2
-- 2  	n  	2  	p
-- 
-- note: duplicate ID columns, one from each table
-- Only ID 2 is present in both tables, so only row returned

-- To avoid duplicate ID column specify the columns we want 
-- now same as NATRUAL JOIN
SELECT a.*, b.A2 FROM TableA a JOIN TableB b on a.ID = b.ID;
-- returns:
-- ID 	A1  A2
-- 2  	n  	p

-- LEFT OUTER join demo (keyword OUTER is optional)
SELECT * FROM TableA a LEFT JOIN TableB b ON a.ID=b.ID; 
-- returns:
-- ID	A1 	 ID   A2
-- 2  	n  	 2	  p
-- 1	m	 NULL NULL
-- 4	o 	 NULL NULL	
-- 
-- note: duplicate ID columns, one from each table
-- All rows from left table (TableA) returned
-- matched with TableB if IDs match
-- otherwise values from right table (TableB) are NULL

-- RIGHT OUTER join demo (keyword OUTER is optional)
SELECT * FROM TableA a RIGHT JOIN TableB b ON a.ID=b.ID; 
-- returns:
-- ID	A1 	 ID   A2
-- 2  	n  	 2	  p
-- NULL	NULL 3	  q	
-- NULL NULL 5	  r		
-- 
-- note: duplicate ID columns, one from each table
-- All rows from right table (TableB) returned
-- matched with TableA if IDs match
-- otherwise values from left table (TableA) are NULL

-- FULL OUTER join demo (keyword OUTER is optional)
-- NOTE: MySQL does not support FULL JOIN, can emulate with UNION
SELECT * FROM TableA a LEFT OUTER JOIN TableB b ON a.ID=b.ID
UNION 
SELECT * FROM TableA a RIGHT OUTER JOIN TableB b ON a.ID=b.ID; 
-- returns:
-- ID	A1 	 ID   A2
-- 2  	n  	 2	  p
-- 1	m	 NULL NULL
-- 4	o 	 NULL NULL
-- NULL	NULL 3	  q	
-- NULL NULL 5	  r		
-- 
-- note: duplicate ID columns, one from each table
-- All rows from right table (TableA) and all rows from TableB returned
-- matched if IDs match

DROP TABLE TableA;
DROP TABLE TableB;




-- PRACTICE slide 21
use nyc_inspections;

-- first look around the data
SELECT count(*) FROM inspections; -- 145,751
SELECT count(*) FROM restaurants NATURAL JOIN inspections; -- 145,751 joins on RestaurantID

-- first get cuisineID for fruits/vegtables restaurants
SELECT * FROM Cuisine WHERE CuisineDescription LIKE '%fruit%'; -- fruit/vegetables is CuisineID 83

-- next insert Tim's Tasty Treats (mind the apostrophe)
-- delete if is already exists
DELETE FROM Restaurants WHERE RestaurantID = 1111;
INSERT INTO Restaurants (RestaurantID,RestaurantName,boro,CuisineID) 
VALUES (1111,'Tim''s Tasty Treats','Manhattan',83);

-- confirm insertion
SELECT * FROM Restaurants WHERE RestaurantID = 1111;

-- how many other fruit/vegtable restaurants are there? 7 including Tim's
SELECT * FROM Restaurants WHERE CuisineID = 83;

-- now count how many time each fruit/vegtable restaurnat has been inspected
-- Tim's has not been inspected yet
SELECT r.RestaurantID, RestaurantName, count(*)
FROM Restaurants r INNER JOIN Inspections i ON r.RestaurantID = i.RestaurantID
WHERE r.CuisineID = 83
GROUP BY RestaurantID;
-- Tim's doesn't show in the list, but is a restaurnt in our database!
-- Inner join did not return rows from Restaurants that have no entry in Inspections

-- try doing an old style join to see if we can see Tim's
SELECT r.RestaurantID, RestaurantName, count(*)
FROM Restaurants r, Inspections i 
WHERE r.RestaurantID = i.RestaurantID
AND r.CuisineID = 83
GROUP BY RestaurantID;
-- no luck!  that join is an inner join too

-- use outer join instead!  gives rows for restaurants with no inspections
-- gotcha is using count will return one row even if no inspections
-- this shows a count of 1 inspection for Tim's because there is one row returned for Tim's
-- after the join, but, Tim's hasn't been inspected yet!
SELECT r.RestaurantID, RestaurantName, Count(*) as InspectionCount
FROM Restaurants r LEFT JOIN Inspections i ON r.RestaurantID = i.RestaurantID
WHERE r.CuisineID = 83
GROUP BY r.RestaurantID;

-- instead of count(*) where each row counts, count an attribute from the Inspections table
-- in this case we can count the InspectionID
SELECT r.RestaurantID, RestaurantName, count(i.InspectionID) AS InspectionCount
FROM Restaurants r LEFT OUTER JOIN Inspections i ON r.RestaurantID = i.RestaurantID
WHERE r.CuisineID = 83
GROUP BY RestaurantID, RestaurantName;
-- now we see Tim's with zero inspections

-- another solution is to use a subselect to count inspections seperately, it will be 0 for Tim's
SELECT r.RestaurantID, RestaurantName, 
	(SELECT count(*) FROM Inspections WHERE RestaurantID = r.RestaurantID) AS Count
FROM Restaurants r LEFT OUTER JOIN Inspections i ON r.RestaurantID = i.RestaurantID
WHERE r.CuisineID = 83
GROUP BY RestaurantID;





-- some more practice not shown in slides

-- see which cusine types have the most restaurants
SELECT c.CuisineID, c.CuisineDescription, count(*) 
FROM restaurants r JOIN Cuisine c on r.CuisineID=c.CuisineID
GROUP BY CuisineID
ORDER BY count(*) desc; -- American most common, then Chinese in NYC dataset


-- find inspection scores and grades for bakeries in Staten Island
-- by using natural join SQL figures out which attributes to match
-- natural join does not return duplicate attributes
-- can string multiple joins together as shown
SELECT RestaurantID, RestaurantName, CuisineDescription,  Grade, Score 
FROM Restaurants NATURAL JOIN Inspections 
	NATURAL JOIN Cuisine
WHERE CuisineID = (SELECT CuisineID FROM Cuisine WHERE CuisineDescription = 'Bakery')
	AND boro = 'Staten Island'
ORDER BY RestaurantID;

-- I prefer to specify myself join atributes using on (especially if database gets complicated)
-- specifying which attributes to return moves duplicate attributes
-- otherwise if select *, you get multiple columns for RestaurantID and CuisineID
-- uncomment one of the next two lines to see
-- SELECT r.RestaurantID, r.RestaurantName, c.CuisineDescription,  i.Grade, i.Score 
SELECT *
FROM Restaurants r JOIN Inspections i ON r.RestaurantID=i.RestaurantID 
	JOIN cuisine c ON r.CuisineID = c.CuisineID
WHERE c.CuisineID = (SELECT CuisineID FROM Cuisine WHERE CuisineDescription = 'Bakery')
	AND r.boro = 'Staten Island'
ORDER BY r.RestaurantID;



-- CONDITIONAL EVALUATION
-- look at inspections table
SELECT * FROM Inspections; -- one entry each time a restaurant is inspected
-- some grades are null, replace that with 'N/A'
-- first lets focus on inspections of Morris Park Bake Shop (RestaurantID = 30075445)
SELECT * FROM Inspections WHERE RestaurantID = 30075445 ORDER BY InspectionDate;

-- now let's augment Inspections with name of Restaurant from Restaurant's table
-- to make things more readable for a human
SELECT i.RestaurantID, RestaurantName, InspectionDate, Score, 
	Grade, GradeDate
FROM Inspections i JOIN Restaurants r ON i.RestaurantID = r.RestaurantID
WHERE r.RestaurantID = 30075445
ORDER BY InspectionDate;

-- slide 23
-- two Grades are NULL for Morris Park Bake Shop
-- Note: returning Grade <> 'A' does NOT return the NULLS!
SELECT i.RestaurantID, RestaurantName, InspectionDate, Score, 
	Grade, GradeDate
FROM Inspections i JOIN Restaurants r ON i.RestaurantID = r.RestaurantID
WHERE r.RestaurantID = 30075445 
-- AND Grade <> 'A' -- comment this line out if using IS NULL line below
AND Grade IS NULL -- comment out prior line to see grade <> A, NULLs do not show up!
ORDER BY InspectionDate;

-- now let's replace grades of Null with 'N/A'
SELECT i.RestaurantID, RestaurantName, InspectionDate, Score,
	IF(Grade IS NULL,'N/A',Grade) AS Grade, GradeDate
FROM Inspections i JOIN Restaurants r ON i.RestaurantID = r.RestaurantID
WHERE r.RestaurantID = 30075445
ORDER BY InspectionDate;

-- does this work with NULL grades by using the IF?
SELECT i.RestaurantID, RestaurantName, InspectionDate, Score,
	IF(Grade IS NULL,'N/A',Grade) AS Grade, GradeDate
FROM Inspections i JOIN Restaurants r ON i.RestaurantID = r.RestaurantID
WHERE r.RestaurantID = 30075445
AND Grade <> 'A';  
-- NO!  the Grade is still NULL, only the returned value is replaced
-- the value of Grade is used in the AND Grade <> 'A'
-- fix with this (or use Grade IS NULL as we did above)
SELECT i.RestaurantID, RestaurantName, InspectionDate, Score,
	IF(Grade IS NULL,'N/A',Grade) AS Grade, GradeDate
FROM Inspections i JOIN Restaurants r ON i.RestaurantID = r.RestaurantID
WHERE r.RestaurantID = 30075445
AND IF (Grade IS NULL,'N/A',Grade) <> 'A'; -- includes N/A



-- slide 25 CASE statement
-- use case statement to categorize inspection frequency
SELECT i.RestaurantID, RestaurantName, count(*) AS `Total Inspections`,
CASE 
	WHEN count(*) < 10 THEN 'Infrequent inspections '
	WHEN count(*) BETWEEN 10 AND 15 THEN 'Moderate inspections'
    	ELSE 'Frequent inspections'
END AS Frequency
FROM Inspections i JOIN Restaurants r ON i.RestaurantID = r.RestaurantID
GROUP BY i.RestaurantID;



-- PRACTICE slide 26
-- count by grade
-- first see how many different grades there are
SELECT DISTINCT Grade FROM inspections ORDER BY grade; -- NULL, A, B, C, G, N, P, Z

-- now count sum how many times each grade appears
SELECT i.RestaurantID, RestaurantName, 
	SUM(CASE Grade WHEN 'A' THEN 1 ELSE 0 END) as 'A', 
    SUM(CASE Grade WHEN 'B' THEN 1 ELSE 0 END) as 'B',
    SUM(CASE Grade WHEN 'C' THEN 1 ELSE 0 END) as 'C',
    SUM(CASE Grade WHEN 'G' THEN 1 ELSE 0 END) as 'G',
    SUM(CASE Grade WHEN 'N' THEN 1 ELSE 0 END) as 'N',
    SUM(CASE Grade WHEN 'P' THEN 1 ELSE 0 END) as 'P',
    SUM(CASE Grade WHEN 'Z' THEN 1 ELSE 0 END) as 'Z', 
    SUM(CASE WHEN Grade IS NULL THEN 1 ELSE 0 END) as 'NULL', 
    count(*) as Total
FROM Inspections i JOIN Restaurants r ON i.RestaurantID = r.RestaurantID
GROUP BY i.RestaurantID;

-- slide 27
-- another way to count by grade using if and count (note NULL is not considered in count)
SELECT i.RestaurantID, RestaurantName, 
	COUNT(IF (Grade = 'A', Grade, NULL)) AS 'A', 
    COUNT(IF (Grade = 'B', Grade, NULL)) AS 'B', 
    COUNT(IF (Grade = 'C', Grade, NULL)) AS 'C', 
	COUNT(IF (Grade = 'G', Grade, NULL)) AS 'G', 
    COUNT(IF (Grade = 'N', Grade, NULL)) AS 'N', 
    COUNT(IF (Grade = 'P', Grade, NULL)) AS 'P', 
    COUNT(IF (Grade = 'Z', Grade, NULL)) AS 'Z', 
    COUNT(IF (Grade IS NULL, 1, NULL)) AS 'NULL', 
    COUNT(*) as Total
FROM Inspections i JOIN Restaurants r ON i.RestaurantID = r.RestaurantID
GROUP BY i.RestaurantID;



