sql-basics-drills

-- Get all restaurants
SELECT * FROM restaurants;

-- Get Italian restaurants
SELECT * FROM restaurants
	WHERE cuisine = 'Italian';

-- 10 Italian, subset
SELECT id, name FROM restaurants
	WHERE cuisine = 'Italian'
	LIMIT 10;

-- Count of Thai restaurants
SELECT count(*) FROM restaurants
	WHERE cuisine = 'Thai';

-- Count of restaurants
SELECT count(*) FROM restaurants;

-- Count of Thai in zip code
SELECT count(*) FROM restaurants
	WHERE address_zipcode = '11372';

-- Italian restaurants in zip codes
SELECT id, name FROM restaurants
	WHERE address_zipcode = '10012'
	OR address_zipcode = '10013'
	OR address_zipcode = '10014'
	AND cuisine = 'Italian'
	ORDER BY name ASC
	LIMIT 5;

-- Create a restaurant
INSERT INTO restaurants
  (name, borough, cuisine, address_building_number, 
    address_street, address_zipcode) VALUES
    ('Byte Cafe', 'Brooklyn', 'coffee', '123', 'Atlantic Avenue', '11231');

-- Create a restaurant and return id and name
INSERT INTO restaurants
  (name, borough, cuisine, address_building_number,
    address_street, address_zipcode) VALUES
    ('Sons of Thunder', 'Manhattan', 'poke', '225', 'E 38th Street', '10016') RETURNING id, name;

-- Create three restaurants and return id and name
INSERT INTO restaurants
  (name, borough, cuisine, address_building_number,
    address_street, address_zipcode) VALUES
    ('Shake Shack', 'Manhattan', 'burgers', '300', 'E 40th Street', '10016'),
    ('Bagel Boss', 'Manhattan', 'bagels', '441', '3rd Avenue', '10016'),
    ('Bare Burger', 'Manhattan', 'burgers', '232', '3rd Avenue', '10016')
    RETURNING id, name;

-- Update record
UPDATE restaurants
  SET name = 'DJ Reynolds Pub and Restaurant'
  WHERE nyc_restaurant_id = '30191841';

-- Delete by id
DELETE FROM grades WHERE id = 10;

-- A blocked delete
DELETE FROM restaurants WHERE id = 22;

ERROR:  update or delete on table "restaurants" violates foreign key constraint "grades_restaurant_id_fkey" on table "grades"
DETAIL:  Key (id)=(22) is still referenced from table "grades".

-- Create a table
CREATE TABLE inspectors(
  id serial PRIMARY KEY,
  first_name text NOT NULL,
  last_name text NOT NULL,
  borough borough_options);

-- Update a table
ALTER TABLE grades
  ADD COLUMN notes text;

-- Drop a table
DROP TABLE inspectors;