- Presentations: Some basic commands, pg 5—12
- Checkpoint 11: Intro to Databases & Working with PostgreSQL
- Checkpoint 12: Intro to SQL
- gist:Command-line-postgres-express-notes.md
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.sqlTake 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.
restaurants-app=# \d grades
Table "public.grades"
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('grades_id_seq'::regclass)
date | timestamp with time zone | | not null |
grade | text | | not null |
score | integer | | not null |
restaurant_id | integer | | |
Indexes:
"grades_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"grades_restaurant_id_fkey" FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
restaurants-app=# \d restaurants
Table "public.restaurants"
Column | Type | Collation | Nullable | Default
-------------------------+-----------------+-----------+----------+-----------------------------------------
id | integer | | not null | nextval('restaurants_id_seq'::regclass)
name | text | | not null |
nyc_restaurant_id | integer | | |
borough | borough_options | | |
cuisine | text | | |
address_building_number | text | | |
address_street | text | | |
address_zipcode | text | | |
Indexes:
"restaurants_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "grades" CONSTRAINT "grades_restaurant_id_fkey" FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
restaurants-app=# \d restaurants_id_seq
Sequence "public.restaurants_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: public.restaurants.id
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.
SELECT *
FROM restaurants;
Write a query that returns all of the Italian restaurants, with all of the fields
SELECT *
FROM restaurants
WHERE cuisine='Italian';
Write a query that gets 10 Italian restaurants, returning only the id and name fields.
SELECT id, name
FROM restaurants
WHERE cuisine='Italian'
ORDER BY name
LIMIT 10;
Write a query that returns the number of Thai restaurants.
SELECT COUNT(*)
FROM restaurants
WHERE cuisine='Thai';
Write a query that returns the total number of restaurants.
SELECT COUNT(*)
FROM restaurants;
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';
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 > '10011'
AND address_zipcode < '10015'
ORDER BY name
LIMIT 5;
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'
Initially added restaurant...
INSERT INTO restaurants
(name, borough, cuisine, address_building_number, address_street, address_zipcode)
VALUES (
'Byte Cafe',
'Brooklyn',
'coffee',
'123',
'Atlantic Avenue',
'11231'
);
... but then noticed that the cuisine value was incorrect. It should be 'Café/Coffee/Tea'. So updated the value via...
UPDATE restaurants
SET cuisine='Café/Coffee/Tea'
WHERE name='Byte Cafe';
Create a restaurant with values of your choosing, and return the id and name.
I tried to enter a Brooklyn-based restaurant, but my statement got rejected...
[80933] ERROR: invalid input value for enum borough_options: "Williamsburg" at character 142
[80933] STATEMENT: INSERT INTO restaurants
(name, borough, cuisine, address_building_number, address_street, address_zipcode)
VALUES (
'OKONOMI // YUJI Ramen',
'Williamsburg',
'Japanese',
'150',
'Ainslie Street',
'11211'
);
ERROR: invalid input value for enum borough_options: "Williamsburg"
LINE 5: 'Williamsburg',
^
😕 ... \d restaurants to inspect the public table and noticed the borough TYPE was defined as borough_options...
Question: what is borough_options? How do I inspect it?
😄 (Partial) Answer: From Drill 14 > 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.
Table "public.restaurants"
Column | Type | Collation | Nullable | Default
-------------------------+-----------------+-----------+----------+-----------------------------------------
id | integer | | not null | nextval('restaurants_id_seq'::regclass)
name | text | | not null |
nyc_restaurant_id | integer | | |
borough | borough_options | | |
cuisine | text | | |
address_building_number | text | | |
address_street | text | | |
address_zipcode | text | | |
Indexes:
"restaurants_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "grades" CONSTRAINT "grades_restaurant_id_fkey" FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
... inspected the distinct values in DB for borough, to see there are only 6, which apparently I can't add to...
SELECT DISTINCT borough FROM restaurants;
borough
---------------
Missing
Bronx
Queens
Staten Island
Brooklyn
Manhattan
(6 rows)
Opted to add a Manhattan-borough restaurant. Initially did a DB search to find that Katz wasn't in the DB!!! No NYC restaurant DB is complete without Katz =D
SELECT * FROM restaurants WHERE name LIKE 'Katz';
and
SELECT * FROM restaurants WHERE cuisine LIKE '%Jewish%' ORDER BY name;
... so added (after some failings)...
INSERT INTO restaurants (
name, borough, cuisine, address_building_number, address_street, address_zipcode)
VALUES (
'Katz Delicatessen',
'Manhattan',
'Jewish/Kosher',
'205',
'E Houston Street',
'10002'
);
👍 Amendment: Note 'Katz''s to write Katz's (with an apostrophe)...
UPDATE restaurants SET name='Katz''s Delicatessen' WHERE id=25361;
💦 Aside: change in prompt to indicate in the midst of coding, going from restaurants-app=# to -#, (#, or '#.
😕 Question: how do (dynamically) generate a nyc_restaurant_id for restaurants that I INSERT? That column is currently blank.
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
('Sacha Shack', 'Manhattan', 'Delicatessen', '498', 'W 52nd Street', '10019')
, ('Sacha Shack', 'Brooklyn', 'Delicatessen', '500', 'S 5th Street', '11211')
, ('Sacha Shack', 'Staten Island', 'Delicatessen', '8825', 'Astoria Boulevard', '10314');
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';
Delete the grade whose id is 10.
DELETE FROM grades WHERE id='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.
restaurants-app=# DELETE FROM restaurants WHERE id='22';
[83240] ERROR: update or delete on table "restaurants" violates foreign key constraint "grades_restaurant_id_fkey" on table "grades"
[83240] DETAIL: Key (id)=(22) is still referenced from table "grades".
[83240] STATEMENT: DELETE FROM restaurants WHERE id='22';
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".
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.
CREATE TABLE inspectors (
id INTEGER PRIMARY KEY,
first_name TEXT NOT NULL,
last_name TEXT NOT NULL,
borough borough_options
);
💥 Initially, attempted to add an inspector without explictly adding an id, but that failed...
[83240] ERROR: null value in column "id" violates not-null constraint
[83240] DETAIL: Failing row contains (null, Mae, McGoo, Queens).
[83240] STATEMENT: INSERT INTO inspectors (
first_name, last_name, borough)
VALUES (
'Mae',
'McGoo',
'Queens'
);
ERROR: null value in column "id" violates not-null constraint
DETAIL: Failing row contains (null, Mae, McGoo, Queens).
😕 Question: Why can I insert a restaurant without defining id? Has that table been set up so primary key column automatically increments? e.g. id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
😮 During investigation, noticed:
\d=> an 'id_seq' (that is owned bypublic.restaurant.id) whoseTypesaresequence, nottable.- for
restaurant.id>Default>nextval('restaurants_id_seq'::regclass) - 😄
CREATE TABLE <db-name> ( id INTEGER PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY );will indeed automatically create a<db-name>_id_seqsequence associated with table column<db-name>.id. BUT what if want to add this id sequence after table created? 😕ALTER TABLE <table_name> ADD COLUMN <column_definition>;doesn't seem appropriate as I don't want to add a column, but rather modify an exisiting column. Perhaps modifying/mutating an existing column isn't really kosher with databases? - CREATE SEQUENCE: https://www.postgresql.org/docs/9.5/sql-createsequence.html
restaurants-app=# \d
List of relations
Schema | Name | Type | Owner
--------+--------------------+----------+----------------------
public | grades | table | dunder_mifflin_admin
public | grades_id_seq | sequence | dunder_mifflin_admin
public | inspectors | table | postgres
public | restaurants | table | dunder_mifflin_admin
public | restaurants_id_seq | sequence | dunder_mifflin_admin
(5 rows)
restaurants-app=# \d restaurants
Table "public.restaurants"
Column | Type | Collation | Nullable | Default
-------------------------+-----------------+-----------+----------+-----------------------------------------
id | integer | | not null | nextval('restaurants_id_seq'::regclass)
name | text | | not null |
nyc_restaurant_id | integer | | |
borough | borough_options | | |
cuisine | text | | |
address_building_number | text | | |
address_street | text | | |
address_zipcode | text | | |
Indexes:
"restaurants_pkey" PRIMARY KEY, btree (id)
Referenced by:
TABLE "grades" CONSTRAINT "grades_restaurant_id_fkey" FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
restaurants-app=# \d restaurants_id_seq
Sequence "public.restaurants_id_seq"
Type | Start | Minimum | Maximum | Increment | Cycles? | Cache
--------+-------+---------+---------------------+-----------+---------+-------
bigint | 1 | 1 | 9223372036854775807 | 1 | no | 1
Owned by: public.restaurants.id
Add a notes field to the grades table. notes are not required, and are text.
ALTER TABLE grades ADD COLUMN notes TEXT;
restaurants-app=# \d grades
Table "public.grades"
Column | Type | Collation | Nullable | Default
---------------+--------------------------+-----------+----------+------------------------------------
id | integer | | not null | nextval('grades_id_seq'::regclass)
date | timestamp with time zone | | not null |
grade | text | | not null |
score | integer | | not null |
restaurant_id | integer | | |
notes | text | | |
Indexes:
"grades_pkey" PRIMARY KEY, btree (id)
Foreign-key constraints:
"grades_restaurant_id_fkey" FOREIGN KEY (restaurant_id) REFERENCES restaurants(id)
Drop the inspectors table from the database.
DROP TABLE inspectors;
restaurants-app=# \dt
List of relations
Schema | Name | Type | Owner
--------+-------------+-------+----------------------
public | grades | table | dunder_mifflin_admin
public | restaurants | table | dunder_mifflin_admin
(2 rows)