In these drills, you'll practice executing basic CRUD (create, read, update, delete) operations in the psql shell.
Before you get started, download this SQL data dump. Create and populate a new database using the data dump (make sure to update the path with the location where you saved the file):
psql -U dunder_mifflin_admin -d restaurants-app -f ~/Downloads/nyc-restaurants-data-backup.sql
Take a moment to get familiar with this new database. You can use \dt+
to see a list of public tables and \d <TABLENAME>
to see a description of those tables.
Once you've successfully completed a drill, paste your query into a Gist to reference later on.
Write a query that returns all of the restaurants, with all of the fields.
Write a query that returns all of the Italian restaurants, with all of the fields
Write a query that gets 10 Italian restaurants, returning only the id and name fields.
Write a query that returns the number of Thai restaurants.
Write a query that returns the total number of restaurants.
Write a query that returns the number of Thai restaurants in the 11372 zip code.
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.
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'
Create a restaurant with values of your choosing, and return the id and name.
Create three restaurants using a single command, with values of your choosing, returning the id and name of each restaurant.
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'.
Delete the grade whose id
is 10.
Try deleting the restaurant with id
of 22
. What error do you get?
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.
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
.
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
.
Add a notes
field to the grades
table. notes
are not required, and are text.
Drop the inspectors
table from the database.
make sure [to] update [the] path with the location where you saved the file*