/* Day 5 - Joins, dates
Tim Pierson, Dartmouth CS61, Spring 2026
*/

-- SLIDE 10
use college;
-- select courses taught by each instructor
SELECT name, course_id
	FROM instructor, teaches
	WHERE instructor.ID = teaches.ID;
    
-- SLIDE 11
SELECT name, course_id AS `course number`
	FROM instructor i, teaches t
    WHERE i.ID = t.ID AND  i.dept_name = 'Finance';
    
-- SLIDE 13
SELECT i.*, t.*
	FROM instructor i, teaches t
    WHERE i.ID = t.ID;
    
-- ********************************************************************************
-- PRACTICE 1 slide 20
-- ********************************************************************************
use nyc_data;
-- find the unique types of cuisine
SELECT DISTINCT CuisineDescription 
	FROM Restaurants
    WHERE CuisineDescription <> '';
    
-- find competition in Manhattan
SELECT RestaurantID, RestaurantName, CuisineDescription
	FROM Restaurants
    WHERE boro = 'Manhattan' 
		AND (CuisineDescription LIKE '%Fruit%' 
			OR CuisineDescription LIKE '%Vegetable%');
 
-- pick one competitor and get its inspection results
SELECT r.RestaurantID, r.RestaurantName, i.*
	FROM Restaurants r, restaurant_inspections i
    WHERE r.RestaurantID = i.CAMIS
		AND CAMIS = 50048030;
     
     
-- SLIDE 21
-- old style
SELECT r.RestaurantID, RestaurantName, i.*
	FROM Restaurants r, restaurant_inspections i 
	WHERE r.RestaurantID = i.CAMIS
		AND i.CAMIS = 50048030;

-- preferred
SELECT r.RestaurantID, RestaurantName, i.*
	FROM Restaurants r JOIN restaurant_inspections i ON r.RestaurantID = i.CAMIS
	WHERE i.CAMIS = 50048030;

 -- ********************************************************************************
-- JOINS SLIDE 23
-- ********************************************************************************   
DROP SCHEMA IF EXISTS test_schema;
CREATE SCHEMA test_schema;
use test_schema;
CREATE TABLE TableA (
	ID INT NOT NULL,
    A1 VARCHAR(5),
    PRIMARY KEY (ID)
    );
CREATE TABLE TableB (
	ID INT NOT NULL,
    A2 VARCHAR(5),
    PRIMARY KEY (ID)
    );

INSERT INTO TableA (ID, A1) VALUES (1, 'm'), (2, 'n'), (4, 'o');
INSERT INTO TableB (ID, A2) VALUES (2, 'p'), (3, 'q'), (5, 'r');
SELECT * FROM TableA;
SELECT * FROM TableB;	
     
-- SLIDE 23
-- TableA		TableB
-- ID	A1		ID	A2
-- 1	m		2	p
-- 2	n		3	q
-- 4	o		5	r

-- NATURAL JOIN uses attributes with the same name to perform join
use test_schema;
SELECT *
	FROM TableA NATURAL JOIN TableB;
	-- ID	A1	A2
	-- 2 	n 	p 
    -- NOTE: ID not duplicated, it only appears one time even though both tables have it as an attribute

-- what NATURAL JOIN does behind the scenes (except ID appears twice)    
SELECT *
	FROM TableA a JOIN TableB b ON a.ID = b.ID;
    -- ID	A1	ID	A2
    -- 2	n	2	p
    -- NOTE: ID attribute is repeated

-- get rid of extra ID attribute in the SELECT
SELECT a.ID, A1, A2 
	FROM TableA a JOIN TableB b ON a.ID = b.ID;
    -- ID	A1	A2
    -- 2	n	p
    -- NOTE: ID now only appears once
    
    
-- SLIDE 24 [INNER] JOIN -- rows common to both tables
SELECT * 
	FROM TableA a INNER JOIN TableB b ON a.ID=b.ID; 
	-- ID	A1	ID	A2
	-- 2	n 	2 	p

-- INNER is optional, can leave it out of command and returns the same as INNER JOIN
SELECT * 
	FROM TableA a JOIN TableB b ON a.ID=b.ID;  -- NOTE: 'INNER' left out
	-- ID	A1	ID	A2
	-- 2	n 	2 	p

-- INNER JOIN is equivalent to old style join in the WHERE clause    
SELECT *
	FROM TableA a, TableB b
    WHERE a.ID = b.ID;
    -- ID	A1	ID	A2
	-- 2	n 	2 	p
    
-- Can also use USING
SELECT *
	FROM TableA a JOIN TableB b USING (ID);
    -- ID	A1	A2
	-- 2 	n 	p 
    -- NOTE: ID not duplicated, it only appears one time even though both tables have it as an attribute
    
    

    
-- SLIDE 25 LEFT JOIN -- all rows from left table (A1 here)
SELECT * 
	FROM TableA a LEFT JOIN TableB b ON a.ID=b.ID; 
	-- ID	A1	ID	A2
    -- 1	m	nullnull
    -- 2	n	2	p 
    -- 4	o 	nullnull
    
-- SLIDE 26 RIGHT JOIN -- all rows from right table (A2 here)
SELECT * 
	FROM TableA a RIGHT JOIN TableB b ON a.ID=b.ID;
	-- ID	A1	ID	A2
    -- 2	n	2	p 
    -- null	null3	q
    -- null	null5	r 
    
-- SLIDE 27 
-- FULL JOIN not supported by MySQL
    
-- ********************************************************************************
-- PRACTICE SLIDE 28
-- ********************************************************************************

use nyc_data;

-- first look around the data
SELECT count(*) FROM restaurant_inspections; -- 291,533
SELECT count(*) 
	FROM restaurants r JOIN restaurant_inspections i ON r.RestaurantID = i.CAMIS; -- 291,533 joins on RestaurantID

    
-- first get cuisine description for fruits/vegtables restaurants
-- here we save the value into @description (I took out the requirement to set a variable on the slide from class)
SELECT DISTINCT CuisineDescription INTO @description 
	FROM Restaurants 
    WHERE CuisineDescription LIKE '%fruit%veg%'
    LIMIT 1; -- fruit/vegetables 
    
SELECT @description;
-- 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,CuisineDescription) 
	VALUES (1111,'Tim''s Tasty Treats','Manhattan',@description);

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

-- how many other fruit/vegtable restaurants are there? 8 including Tim's
SELECT * FROM Restaurants WHERE CuisineDescription = @description;

-- 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 restaurant_inspections i ON r.RestaurantID = i.CAMIS
	WHERE r.CuisineDescription = @description
	GROUP BY RestaurantID
    ORDER 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, restaurant_inspections i 
	WHERE r.RestaurantID = i.CAMIS
		AND r.CuisineDescription = @description
	GROUP BY RestaurantID
    ORDER 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 restaurant_inspections i ON r.RestaurantID = i.CAMIS
	WHERE r.CuisineDescription = @description
	GROUP BY r.RestaurantID
    ORDER BY 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.CAMIS) AS InspectionCount
	FROM Restaurants r LEFT OUTER JOIN restaurant_inspections i ON r.RestaurantID = i.CAMIS
	WHERE r.CuisineDescription = @description
	GROUP BY RestaurantID, RestaurantName
    ORDER BY RestaurantID;
	-- 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
-- works but takes a long time to run!
SELECT r.RestaurantID, RestaurantName, 
	(SELECT COUNT(*) FROM restaurant_inspections WHERE CAMIS = r.RestaurantID) AS Count
	FROM Restaurants r LEFT OUTER JOIN restaurant_inspections i ON r.RestaurantID = i.CAMIS
	WHERE r.CuisineDescription = @description
	GROUP BY RestaurantID
    ORDER BY RestaurantID;



-- some more practice not shown in slides
-- see which cusine types have the most restaurants
SELECT  CuisineDescription, COUNT(*) 
	FROM restaurants r
	WHERE CuisineDescription <> ''
	GROUP BY CuisineDescription
	ORDER BY COUNT(*) desc; -- American most common, then Chinese in NYC dataset


-- ********************************************************************************
-- PRACTICE SLIDE 32 Dates
-- ********************************************************************************
-- VARCHAR date fields are `inspection date`, `grade date`, and `record date`
-- make new fields for each of them
ALTER TABLE restaurant_inspections ADD InspectionDate DATE DEFAULT NULL;
ALTER TABLE restaurant_inspections ADD GradeDate DATE DEFAULT NULL;
ALTER TABLE restaurant_inspections ADD RecordDate DATE DEFAULT NULL;

-- update new DATE fields with dates from varchar date fields
SET SQL_SAFE_UPDATES = 0;
UPDATE restaurant_inspections 
	SET InspectionDate = IF(`inspection date` <> '',STR_TO_DATE(`inspection date`, '%m/%d/%Y'),NULL);
UPDATE restaurant_inspections 
	SET GradeDate = IF(`grade date` <> '',STR_TO_DATE(`grade date`, '%m/%d/%Y'),NULL);
UPDATE restaurant_inspections 
	SET RecordDate = IF(`record date` <> '',STR_TO_DATE(`record date`, '%m/%d/%Y'),NULL);
    

-- calculate days since last inspection (remember, I pulled this data in December 2025)
SELECT RestaurantID, RestaurantName, MAX(i.InspectionDate) AS LastInspection,
        DATEDIFF(CURDATE(), MAX(i.InspectionDate)) AS DaysSinceInspection
        FROM Restaurants r JOIN restaurant_inspections i ON r.RestaurantID = i.CAMIS
        GROUP BY RestaurantID, RestaurantName
        ORDER BY DaysSinceInspection asc;
        
        

-- ********************************************************************************
-- END DAY 5
-- ********************************************************************************





