gistfile1.txt

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
Write a query that returns the number of Thai restaurants in the 11372 zip code.
SELECT count(*) FROM restaurants
WHERE cuisine = 'Thai'
AND address_zipcode = '11372';

7. Italian restaurants in one of several zip codes
Write a query that returns the id and name of five Italian restaurants in the 10012, 10013, or 10014 zip codes. The initial results (before limiting to five) should be alphabetically sorted.
SELECT id, name FROM restaurants
WHERE cuisine = 'Italian'
AND address_zipcode in ('10012','10013','10014')
ORDER BY name ASC
LIMIT 5;


8. Create a restaurant
Create a restaurant with the following properties:

name: 'Byte Cafe',
borough: 'Brooklyn',
cuisine: 'coffee',
address_building_number: '123',
address_street: 'Atlantic Avenue',
address_zipcode: '11231'
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
Create a restaurant with values of your choosing, and return the id and name.
INSERT INTO restaurants
  (name, borough, cuisine, address_building_number,
    address_street, address_zipcode) VALUES
    ('Byte Cafe', 'Brooklyn', 'coffee', '123', 'Atlantic Avenue', '11231') RETURNING id, name;

10. Create three restaurants and return id and name
Create three restaurants using a single command, with values of your choosing, returning the id and name of each restaurant.
INSERT INTO restaurants
  (name, borough, cuisine, address_building_number,
    address_street, address_zipcode) VALUES
    ('Byte Cafe', 'Brooklyn', 'coffee', '123', 'Atlantic Avenue', '11231'),
    ('Another Cafe', 'Brooklyn', 'coffee', '124', 'Atlantic Avenue', '11231'),
    ('Yet Another Cafe', 'Brooklyn', 'coffee', '125', 'Atlantic Avenue', '11231') RETURNING id, name;
    

11. Update a record
Update the record whose value for nyc_restaurant_id is '30191841'. Change the name from 'Dj Reynolds Pub And Restaurant' to 'DJ Reynolds Pub and Restaurant'.
UPDATE restaurants
  SET name = 'DJ Reynolds Pub and Restaurant'
  WHERE nyc_restaurant_id = '30191841';

12. Delete by id
Delete the grade whose id is 10.
DELETE FROM grades WHERE id = 10;

13. A blocked delete
Try deleting the restaurant with id of 22. What error do you get?
Key (id)=(22) is still referenced from table "grades".

14. Create a table
Create a new table called inspectors with the following properties:

first_name: String of inspector's first name, required
last_name: String of inspector's last name, required
borough: The borough the inspector works in, not required, one of Bronx, Brooklyn, Manhattan, Queens, Staten Island.
inspectors should also have a system generated primary key property, id.
CREATE TABLE inspectors(
  id serial PRIMARY KEY,
  borough borough_options,
  first_name text NOT NULL,
  last_name text NOT NULL
);

15. Update a table
Add a notes field to the grades table. notes are not required, and are text.
ALTER TABLE grades
  ADD COLUMN notes text;

16. Drop a table
Drop the inspectors table from the database.
DROP TABLE inspectors;