/* Day 4 - CRUD
Tim Pierson, Dartmouth CS61, Spring 2026
*/

use college;
-- ********************************************************************************
-- PRACTICE 1 slide 8
-- ********************************************************************************
-- if table made with GUI designer, use i icon for table, then go to DDL to get SQL to create table
CREATE TABLE `clubs` (
  `ClubName` VARCHAR(20) NOT NULL,
  `Budget` DECIMAL(8,2) DEFAULT '0.00',
  PRIMARY KEY (`ClubName`)
); 

INSERT INTO clubs (ClubName, Budget)
	VALUES 	('Database enthusiasts', 100000), 
			('Club2', 50);

-- SLIDE 11
-- see departments
SELECT * FROM department;

-- insert again with building and budget)
INSERT INTO department (dept_name, budget) 
	VALUES ('Database Systems', 1000000);

-- confirm entry 
SELECT * FROM department;

-- SLIDE 12
-- see instructors
SELECT * FROM instructor;

-- Try to insert into non-existent biology_instructor table
-- Errors out because no biology_instructor table
/*
INSERT INTO biology_instructor (ID, `name`, dept_name, salary)
	SELECT ID, `name`, dept_name, salary
		FROM instructor
		WHERE dept_name = 'Biology';
*/

-- SLIDE 13
-- create biology_instructor table using CREATE TABLE LIKE
CREATE TABLE biology_instructor LIKE instructor;

-- SLIDE 14
-- try insert again
INSERT INTO biology_instructor (ID, `name`, dept_name, salary)
	SELECT ID, name, dept_name, salary
		FROM instructor
		WHERE dept_name = 'Biology';

-- see if it works
SELECT * FROM biology_instructor; -- works!

-- SLIDE 15
-- drop table to show how to create a table using SELECT
DROP TABLE biology_instructor;

-- could also create biology_instructor using SELECT
CREATE TABLE biology_instructor
	SELECT ID, name, dept_name, salary
		FROM instructor
		WHERE dept_name = 'Biology';

-- SLIDE 16
SELECT * from instructor;

-- give instructors earning <= 90,000 a 5% raise
SET SQL_SAFE_UPDATES = 0;
UPDATE instructor
	SET salary = salary * 1.05
	WHERE salary <= 90000; 
-- confirm
SELECT * FROM instructor;
SET SQL_SAFE_UPDATES = 1;



-- SLIDE 17
SELECT * FROM instructor
WHERE dept_name = 'Biology'; -- Just Crick

-- delete biology instructors
DELETE FROM instructor
WHERE dept_name = 'Biology';

-- confirm
SELECT * from instructor; -- Crick removed





/* latitude and longitude have a mix of zeros and nulls
*/
-- ********************************************************************************
-- PRACTICE 2 slide 18
-- ********************************************************************************
-- find how how many of each
use nyc_data;
SELECT COUNT(*) FROM restaurant_inspections; -- 291,534
SELECT COUNT(*) FROM restaurant_inspections WHERE latitude IS NOT NULL; -- 291,534 rows returned
SELECT COUNT(*) FROM restaurant_inspections WHERE latitude IS NULL; -- 0 rows returned

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

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

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

-- Set zipcode = '' to NULL
SET SQL_SAFE_UPDATES = 0;
UPDATE restaurant_inspections
	SET zipcode = NULL
    WHERE zipcode = '';
SET SQL_SAFE_UPDATES = 1;


-- SLIDE 21
-- create BigTable combining instructors and departments to demonstrate why we use multiple tables
-- will drop this table after looking at results
use college;
CREATE TABLE BigTable
	SELECT ID, name, instructor.dept_name, salary, building, budget
		FROM instructor, department
		WHERE instructor.dept_name = department.dept_name; 

SELECT * FROM BigTable; -- see results
DROP TABLE BigTable; -- drop

-- SLIDE 38 demo UUID
-- first create a table to hold UUIDs and other data (here an item name)
CREATE TABLE UUIDDemo (
	PK BINARY(16),
    ItemName VARCHAR(20),
    PRIMARY KEY (PK));

-- insert items into table with PK of UUID    
INSERT INTO UUIDDemo(PK, ItemName) VALUES (UUID_TO_BIN(UUID()), 'Item1');
INSERT INTO UUIDDemo(PK, ItemName) VALUES (UUID_TO_BIN(UUID()), 'Item2');
INSERT INTO UUIDDemo(PK, ItemName) VALUES (UUID_TO_BIN(UUID()), 'Item3');

-- see results
SELECT BIN_TO_UUID(PK), ItemName 
	FROM UUIDDemo;

-- demo is over, DROP Table
DROP TABLE UUIDDemo;


-- SLIDE 40
use nyc_data;
SELECT * FROM restaurant_inspections;

-- SLIDE 41
CREATE TABLE Restaurants (
  RestaurantID BIGINT NOT NULL,
  RestaurantName VARCHAR(100) NULL DEFAULT NULL,
  Boro VARCHAR(20) NULL DEFAULT NULL,
  Building VARCHAR(20) NULL DEFAULT NULL,
  Street VARCHAR(100) NULL DEFAULT NULL,
  ZipCode INT NULL DEFAULT NULL,
  Phone BIGINT NULL DEFAULT NULL,
  Latitude DOUBLE NULL DEFAULT NULL,
  Longitude DOUBLE NULL DEFAULT NULL,
  CuisineDescription VARCHAR(100) DEFAULT NULL,
  PRIMARY KEY (RestaurantID)
  );
  -- NOTE: not creating columns for Community Board, Council District, Census Track, BIN, BBL, NTA, POINT
  
-- SLIDE 43
-- find distinct restaurants
SELECT DISTINCT camis AS RestaurantID, 
	dba AS RestaurantName, boro, building, street, 
	zipcode, phone, latitude, longitude, `cuisine description` AS CuisineDescription
	FROM restaurant_inspections;
    
-- count the number of distinct restaurants
SELECT COUNT(DISTINCT camis, 
	dba, boro, building, street, 
	zipcode, phone, latitude, longitude, `cuisine description`)
	FROM restaurant_inspections;
    -- 30479

-- SLIDE 44
-- fill restaurants table using prior query   
INSERT INTO Restaurants (RestaurantID, RestaurantName, Boro, Building, Street, ZipCode, Phone, Latitude, Longitude, CuisineDescription)
	SELECT DISTINCT camis AS RestaurantID, 
		dba AS RestaurantName, boro, building, street, 
		zipcode, phone, latitude, longitude, `cuisine description` AS CuisineDescription
	FROM restaurant_inspections
    WHERE dba IS NOT NULL;
    -- inserts 30479 rows


-- SLIDE 46
-- remove unneed columns from restaurant_inspections (Restaurants has them now)
ALTER TABLE restaurant_inspections 
DROP COLUMN `CUISINE DESCRIPTION`,
DROP COLUMN PHONE,
DROP COLUMN Latitude,
DROP COLUMN Longitude,
DROP COLUMN ZIPCODE,
DROP COLUMN STREET,
DROP COLUMN BUILDING,
DROP COLUMN BORO,
DROP COLUMN DBA,
DROP COLUMN `Community board`, -- drop extraneous attributes too!
DROP COLUMN `Council district`,
DROP COLUMN `Census tract`,
DROP COLUMN `BIN`,
DROP COLUMN `BBL`,
DROP COLUMN `NTA`,
DROP COLUMN `Location`
;

-- SLIDE 47
SELECT r.*, i.*
	FROM Restaurants r, restaurant_inspections i
    WHERE r.RestaurantID = i.CAMIS;
    
-- SLIDE 48
-- pick a restaurant and see details
SELECT * FROM Restaurants
WHERE RestaurantID = 40758684;

-- see inspection results for this restaurant
SELECT r.RestaurantName, i.*
	FROM Restaurants r, restaurant_inspections i
    WHERE r.RestaurantID = i.CAMIS AND CAMIS = 40758684;

-- SLIDE 49
-- Change scandalous name and move to building 73 from 72
UPDATE Restaurants
	SET RestaurantName = 'NON-JUICY LUCY\'s', Building = 73
    WHERE RestaurantID = 40758684;
    
-- SLIDE 50
-- confirm update occurred
SELECT * FROM Restaurants
	WHERE RestaurantID = 40758684;

-- see inspection results not changed, even though restaurant details did change
SELECT r.RestaurantName, i.*
	FROM Restaurants r, restaurant_inspections i
    WHERE r.RestaurantID = i.CAMIS AND CAMIS = 40758684 ;


-- SLIDE 52
-- create temporary table to show constraints, name not null, phone unique, salary > 0
CREATE TABLE Employees (
	EmployeeID	INT NOT NULL AUTO_INCREMENT,
	Name VARCHAR(20) NOT NULL,
	Phone INT UNIQUE,
	Salary INT,
	PRIMARY KEY (EmployeeID),
	CHECK (Salary > 0)
);

-- drop table after demo
DROP TABLE Employees;


-- SLIDE 57
-- add delete and update constraints so restaurant_inspections changes when Restaurants changes
ALTER TABLE restaurant_inspections
	ADD FOREIGN KEY(CAMIS) REFERENCES Restaurants(RestaurantID)
		ON DELETE CASCADE
        ON UPDATE CASCADE;


        
-- find a Restaurant that was only inspected one time (because we will delete it later)
/*
SELECT camis, dba, COUNT(*) FROM restaurant_inspections
GROUP BY camis
HAVING COUNT(*) = 1;
-- choose JUICY LUCY'S with CAMIS = 40758684
*/

-- SLIDE 59
SELECT r.RestaurantID, r.RestaurantName, i.* 
	FROM Restaurants r, restaurant_inspections i
	WHERE r.RestaurantID = i.CAMIS AND camis = 40758684;
    
-- SLIDE 60
-- update RestaurantID in Restaurants table      
UPDATE Restaurants
	SET RestaurantID = 1
    WHERE RestaurantID = 40758684;

-- check RestaurantName update in Restaurants
SELECT * FROM Restaurants WHERE RestaurantID = 1; -- NOT JUICY LUCY's

-- SLIDE 61
-- check change cascaded to restaurant_inspections
SELECT r.RestaurantID, r.RestaurantName, i.* 
	FROM Restaurants r, restaurant_inspections i
	WHERE r.RestaurantID = i.CAMIS AND camis = 1;
    
-- SLIDE 62
-- check delete from Restaurant cascades to delete from restaurant_inspections
DELETE FROM Restaurants 
	WHERE RestaurantID = 1;
    
-- confirm delete in Restaurants
SELECT * FROM Restaurants WHERE RestaurantID = 1; -- no results

-- confirm delete cascaded to restaurant_inspections
SELECT * FROM restaurant_inspections WHERE CAMIS = 1; -- no results
    
-- ********************************************************************************
-- END DAY 4
-- ********************************************************************************
