KqmQZb6i

1-- get all restaurants
2select * from restaurants
3
4-- get all italian restaurants
5select * from restaurants
6where cuisine = 'Italian'
7
8-- Write a query that gets 10 Italian restaurants, returning only the id and name field
9select id, name 
10from restaurants
11where cuisine = 'Italian'
12limit 10
13
14-- Write a query that returns the number of Thai restaurants.
15select count(id) 
16from restaurants
17where cuisine = 'Thai'
18
19-- Write a query that returns the total number of restaurants
20select count(id) 
21from restaurants
22
23-- Write a query that returns the number of Thai restaurants in the 11372 zip code.
24select count(id) 
25from restaurants
26where cuisine = 'Thai'
27and address_zipcode = '11372'
28
29
30-- Write a query that returns the id and name of five Italian restaurants in the 10012, 10013, or 10014 zip codes.
31-- The initial results (before limiting to five) should be alphabetically sorted.
32select id, name
33from restaurants
34where cuisine = 'Italian' and address_zipcode in('10012', '10013', '10014')
35order by 2
36limit 5
37
38-- Create a restaurant with the following properties:
39insert into restaurants (name, borough, cuisine, address_building_number, address_street, address_zipcode)
40values('Byte Cafe','Brooklyn','coffee','123','Atlantic Avenue','11231')
41
42-- Create a restaurant with the following properties with returning id
43insert into restaurants (name, borough, cuisine, address_building_number, address_street, address_zipcode)
44values('Byte Cafe','Brooklyn','coffee','123','Atlantic Avenue','11231')
45returning id;
46
47--update a record
48update restaurants
49set name = 'DJ Reynolds Pub and Restaurant'
50where nyc_restaurant_id = '30191841'
51
52-- Delete by id
53delete from restaurants
54where id = 25361
55
56-- Delete by id fk constraint 
57delete from restaurants
58where id = 22
59
60-- create table using data type for borough
61create table if not exists inspectors (
62	first_name text not null,
63	last_name text not null,
64	borough borough_options
65)
66
67-- Add a notes field to the grades table. notes are not required, and are text.
68alter table grades
69add column notes text
70
71-- drop the inspectors table
72drop table if exists inspectors