SQL Basic Drills

1. Get all restaurants:
  \x
  SELECT * FROM restaurants;

2. Get all Italian restaurants:
  SELECT * FROM RESTAURANTS WHERE cuisine = 'Italian';

3. Get 10 Italian restaurants, subsets of fields:
  SELECT id, name FROM restaurants WHERE cuisine = 'Italian' LIMIT 10;
  
4. Count of all thai restaurants:
  SELECT count(*) FROM restaurants WHERE cuisine = 'Thai';

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

6. Count of Thai restaurants in zip codde:
  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', '1
  0013', '10014') ORDER BY name ASC LIMIT 5;
  
8. Create a restaurant:
  INSERT INTO restaurants (name, borough, cuisine, address_building_number, add
  ress_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) VALUES ('Wing Stop', 'Manhattan') RETURNING id, name;

10. Create three restaurants and return id and name:
  INSERT INTO restaurants (name, borough) VALUES ('Rally''s', 'Queens'), ('KFC', 'Manhat
  tan'), ('The Pit', 'Brooklyn') 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:
  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 tex
  t NOT NULL, borough borough_options);
  
15. Update a table:
  ALTER TABLE grades ADD COLUMN notes text;

16. Drop a table:
  DROP TABLE inspectors;