/* Day 2: Intro to the relational model
Tim Pierson, Dartmouth CS61, Spring 20
*/

-- first run script to load New York City health inspection data
-- use file restaurant_inspections.sql
-- data is also loaded on sunapee server

-- 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


-- use statement tells database to use this schema
-- don't need to qualify schema now
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

-- 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

-- lets see how the Met does
SELECT dba AS RestaurantName, building, street, score, `inspection date` AS InspectionDate, Grade
FROM restaurant_inspections
WHERE DBA LIKE 'Metropolitan Museum%' 
ORDER BY dba, `inspection date`;
-- no trustee's dining room!?!!?

-- 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
-- find Ray's Pizza variants
-- note the use of three wildcard %
SELECT * FROM restaurant_inspections 
WHERE DBA LIKE "%Ray%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';

-- 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` = 'Y';

-- find Ray's with critical flag at Columbus Ave
-- note two ands
SELECT * FROM restaurant_inspections 
WHERE DBA LIKE "%Ray%Pizza%"
AND `critical flag` = 'Y'
AND street = 'Columbus Ave';
-- would you eat there?  filth flies on 9/19/19, mice on 2/14/19!


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

