SQL Drills Practice

gistfile1.txt

--1. Get all restaurants
--Write a query that returns all of the restaurants, with all of the fields.

SELECT * FROM restaurants;

--2. Get Italian restaurants
--Write a query that returns all of the Italian restaurants, with all of the fields

SELECT * 
FROM restaurants
WHERE cuisine ILIKE 'italian';


--3. Get 10 Italian restaurants, subset of fields
--Write a query that gets 10 Italian restaurants, returning only the id and name fields.

SELECT id, name FROM restaurants
WHERE cuisine ILIKE 'italian'
LIMIT 10;

--4. Count of Thai restaurants
--Write a query that returns the number of Thai restaurants.


SELECT 
	COUNT(cuisine)
FROM 
	restaurants
WHERE 
	cuisine ILIKE 'thai'; 


--5. Count of restaurants
--Write a query that returns the total number of restaurants.

SELECT 
	COUNT (id)
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
	cuisine, address_zipcode,
	COUNT(DISTINCT id) --whatever you select you have to include in GROUP BY clause
FROM 
	restaurants
WHERE 
    address_zipcode ILIKE '11372' AND cuisine ILIKE 'thai'
GROUP BY 
	cuisine, address_zipcode;


--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, address_zipcode
FROM 
	restaurants
WHERE 
    address_zipcode IN ('10012', '10013', '10014') AND name != '' -- put in equality in empty not null text entry
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')

SELECT * FROM restaurants
WHERE name = 'Byte Cafe';

--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 ('Joanne"s Restaurant', 'Brooklyn', 'Filipino', '789', '5th Blvd', '10032')
	
SELECT name, id FROM restaurants
WHERE name = 'Joanne"s Restaurant'

--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 
	('A Restaurant', 'Manhattan', 'American', '1', '6th Blvd', '10012'),
	('B Restaurant', 'Brooklyn', 'Cafe', '7849', '4th Blvd', '10032'),
	('C Restaurant', 'TarryTown', 'Japanese', '7589', '5th Blvd', '10052')
	
SELECT name, id FROM restaurants
WHERE name = 'Joanne"s Restaurant'

--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 Reynold Pub And Restaurant'
WHERE nyc_restaurant_id = '30191841';

SELECT *
FROM restaurants 
WHERE name = 'DJ Reynold Pub And Restaurant';

--12. Delete by id
--Delete the grade whose id is 10.

SELECT * FROM grades

DELETE FROM grades
WHERE id = '10';

--13. A blocked delete
--Try deleting the restaurant with id of 22. What error do you get?

DELETE FROM restaurants 
WHERE id = 22;

--Paste the error text for the answer. We'll learn about foreign key constraints in the next reading, but take two seconds and come up with your own theory about what this message means.

--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 TYPE inspector_boroughs AS ENUM (
	'Bronx',
	'Brooklyn',
	'Mahattan',
	'Queens',
	'Staten Island'
);


CREATE TABLE inspectors (
    id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
	first_name TEXT NOT NULL,
	last_name TEXT NOT NULL,
	borough inspector_boroughs
);


--Note that the borough property requires you to use an enumerated type, which is a list of set values you can use for a property. You can use an existing enumerated type that will already be in the table: borough_options.

--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;