/* Day 2 - Basic SQL
Tim Pierson, Dartmouth CS61, Spring 2026
*/

-- SLIDE 11
use college;
SELECT name
	FROM instructor
	WHERE dept_name = 'Physics';

-- first run script to load New York City health inspection data
-- use file queries/create_tables/nyc_data_restaurant_inspections25.sql
-- data is also loaded on Dartmouth server

-- ********************************************************************************
-- Explore
-- ********************************************************************************
-- see what database schemas are available
SHOW SCHEMAS; -- same as SHOW DATABASES
-- SHOW DATABASES; -- same as SHOW SCHEMAS

-- choose a database to use
use nyc_data;

-- see what tables are in this schema
SHOW TABLES;  -- for now just one table restaurant_inspections

-- see what attributes a table contains
DESCRIBE restaurant_inspections;

-- first get a feel for the data
-- note: qualifying which schema to use (nyc_data)
-- LIMIT is not required (MySQL Workbench will limit to 1000 by default)
SELECT * FROM nyc_data.restaurant_inspections LIMIT 100;
SELECT * FROM nyc_data.restaurant_inspections LIMIT 2000; -- get more than 100 (or 1000) rows


-- ********************************************************************************
-- Slide 24 to 27
-- ********************************************************************************
-- use statement tells database to use this schema
-- don't need to qualify schema now
-- SLIDE 24
USE nyc_data;
SELECT * 
	FROM restaurant_inspections LIMIT 100;
-- notice that the table has restaurants, each inspected at least one time, 
-- and each inspection might result in multiple health code violations

-- SLIDE 26
SELECT * FROM nyc_data.restaurant_inspections
	WHERE DBA IS NOT NULL
	ORDER BY DBA asc
	LIMIT 100;

-- SLIDE 27
SELECT dba as RestaurantName, `inspection date` as InspectionDate, Score,
	score/100 as ScorePercent, 0 as AdjustedScore
	FROM restaurant_inspections
	WHERE score IS NOT NULL;

-- NOTE: this does not work as expected (returns nothing, does not cause error)
-- use <field> IS NOT NULL instead of <field> <> NULL
/*
SELECT dba as RestaurantName, `inspection date` as InspectionDate, Score,
	score/100 as ScorePercent, 0 as AdjustedScore
	FROM restaurant_inspections
	WHERE score <> NULL;
*/


-- sort data by restaurant ID (camis) and then by inspection date
SELECT * FROM restaurant_inspections
	ORDER BY CAMIS, `inspection date`;
-- note: inspections are not in date order, they are in alphabetical order
-- SQL has a DATE type we will use later, which will allow us to sort by calendar date

-- ********************************************************************************
-- PRACTICE 1 (slide 29)
-- ********************************************************************************
-- lets see how the Metropolitan Museum of Art does
SELECT dba AS RestaurantName, building, street, score, `inspection date` AS InspectionDate, Grade
	FROM restaurant_inspections
	WHERE DBA LIKE '%Met%Museum%' 
	ORDER BY dba, `inspection date`;
-- no trustee's dining room or staff cafe!?!!?

-- try the Met with address instead of name
SELECT dba AS RestaurantName, building, street, score, `inspection date` AS InspectionDate, Grade
	FROM restaurant_inspections
	WHERE Building = '1000' AND Street = '5 AVENUE'
	ORDER BY dba, `inspection date`;

-- list the distinct dinning areas at the Met
SELECT DISTINCT camis, dba AS RestaurantName
	FROM restaurant_inspections
	WHERE Building = '1000' AND Street = '5 AVENUE'
	ORDER BY dba;




-- ********************************************************************************
-- PRACTICE 2 (slide 33)
-- ********************************************************************************
-- find Ray's Pizza variants
-- note the use of three wildcard %
-- GOTCHA Ray's has an apostrophe, can escape the apostrophe LIKE '%Ray\'s%'
SELECT * FROM restaurant_inspections 
	WHERE DBA LIKE "%Ray%";  -- returns lots of non-Ray's Pizza like Murray's Bagels and Gray's Papaya

SELECT * FROM restaurant_inspections
	WHERE dba LIKE '%Ray\'s%'
		AND `cuisine description` = 'Pizza';



-- find Ray's in the Bronx
-- note the 'and' in the where clause
SELECT * FROM restaurant_inspections 
	WHERE DBA LIKE "%Ray%Pizza%"
		AND boro = 'Bronx'; -- also try Queens and Manhattan

-- find Ray's with critical flag
-- note the ` around the two word attribute `critical flag`
SELECT * FROM restaurant_inspections 
	WHERE DBA LIKE "%Ray%Pizza%"
		AND `critical flag` = 'Critical';

-- find Ray's with critical flag at Columbus Ave
-- note two ands
SELECT * FROM restaurant_inspections 
	WHERE DBA LIKE "%Ray%Pizza%"
		AND `critical flag` = 'Critical'
		-- AND street LIKE 'Columbus%'
		-- AND street = 'Columbus Avenue'
	ORDER BY `inspection date`;
-- would you eat there?  Grade A on 5/13/2025, but had rats!


-- find distinct Ray's locations
SELECT DISTINCT DBA, Building, Street, boro
	FROM restaurant_inspections
	WHERE DBA LIKE "%Ray%Pizza%";

-- ********************************************************************************
-- END DAY 2
-- ********************************************************************************



