Thinkful Mock Interview Prep

SQL basics drills

-- 1. Get all restaurants
SELECT * FROM restaurants;

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

-- 3. Get 10 Italian restaurants, subset of fields
SELECT id, name FROM restaurants
WHERE cuisine='Italian' 
LIMIT 10;

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

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

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

-- 7. Italian restaurants in one of several zip codes
SELECT id, name FROM restaurants
WHERE address_zipcode in ('10012', '10013', '10014')
ORDER BY name ASC
LIMIT 5;

-- 8. 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');

-- 9. Create a restaurant and return id and name
INSERT INTO restaurants
(name, borough, cuisine, address_building_number, address_street, address_zipcode)
VALUES 
('Hello', 'Brooklyn', 'milk', '555', 'Atlantic Avenue', '11231') RETURNING id, name;

-- 10. Create three restaurants and return id and name
INSERT INTO restaurants
(name, borough, cuisine, address_building_number, address_street, address_zipcode)
VALUES 
('Hi', 'Brooklyn', 'chocolate', '789', 'Atlantic Avenue', '11231'),
('Hey', 'Brooklyn', 'icecream', '456', 'Atlantic Avenue', '11231'),
('Goodbye', 'Brooklyn', 'oranges', '321', 'Atlantic Avenue', '11231') RETURNING id, name;

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

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

-- 13. 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".

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

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

-- 16. Drop a table
DROP TABLE inspectors;