/* Day 4: Multiple table CRUD
Tim Pierson, Dartmouth CS61, Spring 20
*/
use nyc_data;

/* latitude and longitude have a mix of zeros and nulls
*/
-- PRACTICE slide 11
-- find how how many of each
SELECT COUNT(*) FROM restaurant_inspections; -- 397,854
SELECT COUNT(*) FROM restaurant_inspections WHERE latitude IS NOT NULL; -- 397,432rows returned
SELECT COUNT(*) FROM restaurant_inspections WHERE latitude IS NULL; -- 422 rows returned

SELECT COUNT(*) FROM restaurant_inspections WHERE latitude = 0; -- 5,524 rows returned

-- update latitude and longitude to null if latitude is zero (assumes longtitude invalid too)
UPDATE restaurant_inspections 
SET latitude = NULL, longitude = NULL
WHERE latitude = 0; 
-- 5,524 row affected

-- safety check
SELECT * FROM restaurant_inspections WHERE latitude = 0; -- 0 rows returned as expected



-- PRACTICE slide 13
-- first see how many have NULL dba
SELECT * FROM restaurant_inspections WHERE dba IS NULL; -- 620 rows

-- delete null dba
DELETE FROM restaurant_inspections WHERE dba is NULL; -- 620 rows affected

-- safety check
SELECT * FROM restaurant_inspections WHERE dba IS NULL; -- 0 rows returned





/* instructor examples from slides
schema from book now posted on sunapee under cs61_silberschatz (adjust use statement accordingly)
*/
use silberschatz_book;
SELECT * FROM silberschatz_book.instructor;


-- create new department called Database Systems
INSERT INTO department(dept_name) VALUES ('Database Systems');

-- find average instructor salary
SELECT AVG(salary) FROM instructor;

-- create biology_instructor table (will fill from instructor)
CREATE TABLE `biology_instructor` (
  `ID` varchar(5) NOT NULL,
  `name` varchar(20) NOT NULL,
  `dept_name` varchar(20) DEFAULT NULL,
  `salary` decimal(8,2) DEFAULT NULL,
  PRIMARY KEY (`ID`),
  KEY `dept_name` (`dept_name`)
) ENGINE=InnoDB;

-- add new instructor to instructor table
INSERT INTO instructor (ID, `name`, dept_name, salary)
VALUES ('1','Pierson','Comp. Sci.',100000);


-- create a table for biology instructors and load data from instructor table
CREATE TABLE biology_instructor
	SELECT ID, `name`, dept_name, salary
	FROM instructor
	WHERE dept_name = 'Biology';

-- find instructors with salary below average
SELECT * FROM instructor
WHERE salary <  (SELECT AVG (salary) FROM instructor);
                    

-- find average salaries by department
SELECT dept_name, 
	FORMAT(AVG(salary),0) AS AvgSalary -- note format command adds commas, no decimals with 0 param
FROM instructor
GROUP BY dept_name
ORDER BY AvgSalary DESC;

-- find departments with avg salary > 65,000
SELECT dept_name, 
	FORMAT(AVG(salary),0) AS AvgSalary
FROM instructor
GROUP BY dept_name
HAVING AVG(salary) > 65000
ORDER BY AvgSalary DESC;

use nyc_data;
-- PRACTICE slide 35
-- create and fill Restaurants table
-- must refresh to see in MySQL Workbench
CREATE TABLE IF NOT EXISTS nyc_data.Restaurants (
RestaurantID INT NOT NULL,
RestaurantName VARCHAR(100) NULL DEFAULT NULL,
Building VARCHAR(20) NULL DEFAULT NULL,
Street VARCHAR(100) NULL DEFAULT NULL,
Boro VARCHAR(20) NULL DEFAULT NULL,
CuisineID INT DEFAULT NULL,
PRIMARY KEY (`RestaurantID`))
   SELECT DISTINCT camis AS RestaurantID, 
		dba AS RestaurantName, building, street, boro, NULL AS CuisineID
	FROM nyc_data.restaurant_inspections
	WHERE dba IS NOT NULL;

-- compare number of rows in restauant_inspectons table and Restaurants table
-- restaurant_inspections has one row for each violation in each inspection of each restaurant
-- Restaurants now has one entry for each distinct restaurant
SELECT COUNT(*) FROM restaurant_inspections; -- 397,854 number of inspections
SELECT COUNT(*) FROM Restaurants; -- 26,571 number of distinct restaurants
-- do the same in one query
SELECT (SELECT COUNT(*) FROM restaurant_inspections) AS restaurant_inspections_count,
(SELECT COUNT(*) FROM Restaurants) AS RestaurantCount;

-- create and fill Cuisine table
-- must refresh to see in MySQL Workbench
CREATE TABLE nyc_data.Cuisine (
CuisineID INT NOT NULL AUTO_INCREMENT, 
CuisineDescription VARCHAR(150), 
PRIMARY KEY (CuisineID))
	SELECT DISTINCT `cuisine description` AS CuisineDescription
	FROM restaurant_inspections
	WHERE `cuisine description` IS NOT NULL;

-- add foreign key in Restaurants that references Cuisine
ALTER TABLE Restaurants ADD FOREIGN KEY (CuisineID) REFERENCES Cuisine(CuisineID);

-- try to update CuisineID for a restaurant to invalid value
-- find a Restaurant to update
SELECT MIN(RestaurantID) FROM Restaurants; -- 30075445 = Morris Park Bake Shop

-- test update, set CusineID to valid number
UPDATE Restaurants SET CuisineID = (SELECT MIN(CuisineID) FROM Cuisine)
WHERE RestaurantID = 30075445;
-- confirm update worked, should see CuisineID = 1
SELECT * FROM Restaurants WHERE RestaurantID = 30075445;

-- test again, update to Cuisine max CuisineID in Cuisine Table (84)
UPDATE Restaurants SET CuisineID = (SELECT MAX(CuisineID) FROM Cuisine)
WHERE RestaurantID = 30075445;
-- confirm update worked, should see CuisineID = 84
SELECT * FROM Restaurants WHERE RestaurantID = 30075445;

-- test again, update CuisineID to one more than max (invalid foreign key)
-- note the plus one to max CuisineID
-- should cause foreign key constraint failure, CuisineID does not update to 85
UPDATE Restaurants SET CuisineID = (SELECT MAX(CuisineID) FROM Cuisine) +1
WHERE RestaurantID = 30075445;
-- confirm update worked, should see CuisineID still set to 84
SELECT * FROM Restaurants WHERE RestaurantID = 30075445;
