btholt/complete-intro-to-sql

The Complete Intro to Postgres, as taught by Brian Holt for Frontend Masters

Learn to construct databases, write queries, and optimize SQL with industry veteran Brian Holt.

License

The code in this repo is licensed under the Apache 2.0 license.

The content is licensed under CC-BY-NC-4.0.

Course Icon License

SQL icons created by juicy_fish - Flaticon

complete-intro-to-sql's Issues

Unable to run docker image `btholt/complete-intro-to-sql` on ubuntu

docker run -e POSTGRES_PASSWORD=lol --name=sql -d -p 5432:5432 --rm btholt/complete-intro-to-sql
WARNING: The requested image's platform (linux/arm64/v8) does not match the detected host platform (linux/amd64) and no specific platform was requested
f3ddec7dba8f190bf81ed6634f3f992c117d09ff9266869a3c02a30f2b657662

SQL query hangs in omdb

I was following from here https://sql.holt.courses/lessons/the-movie-database/exercises

Database "recipeguru" does not exist

I'm getting a error: database "recipeguru" does not exist message when I try to run the completed exercise using the btholt/complete-intro-to-sql docker container

When I use psql to connect to the docker container I'm able to verify the recipeguru database exists and list the relations

Any idea what may be going on?

[Bug] Missing query in Many-to-Many Relationships

In Many-to-Many Relationships, this query is missing at the end:

SELECT
  i.title AS ingredient_title,
  i.image AS ingredient_image,
  i.type AS ingredient_type,
  r.title AS recipe_title,
  r.body AS recipe_body,
  r.recipe_id AS rid,
  i.id AS iid
FROM
  recipe_ingredients ri
INNER JOIN
  ingredients i
ON
  i.id = ri.ingredient_id
INNER JOIN
  recipes r
ON
  r.recipe_id = ri.recipe_id;

recipeguru database not appearing in btholt/complete-intro-to-sql docker image even after pulling latest

Hello, I also checked my image sha against the one you showed here, and it matches, but I do not see the recipeguru database still. I do get the amd64 warning, but I don't think that would be a factor.

$ docker pull btholt/complete-intro-to-sql:latest
latest: Pulling from btholt/complete-intro-to-sql
Digest: sha256:86f9d1e155bf35b82abe2a14b56e0141644d1660d391e6d97cc58a8db6f30950
Status: Image is up to date for btholt/complete-intro-to-sql:latest
docker.io/btholt/complete-intro-to-sql:latest

$ docker run -e POSTGRES_PASSWORD=lol --name=sql -p:5432:5432 -d --rm btholt/complete-intro-to-sql
WARNING: The requested image's platform (linux/arm64/v8) does not match the detected host platform (linux/amd64) and no specific platform was requested
a49337a8b95055511edc290c395c88c5345724a9242902461f0b909c59ef5ab6

$ docker exec -u postgres -it sql psql
psql (14.4 (Debian 14.4-1.pgdg110+1))
Type "help" for help.

postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 omdb      | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(4 rows)

I think I can just run your recipes.sql file as a workaround, but I am filing a bug here in case this is an issue for other people..

[Bug] Missing videos?

Hi,

I am watching the course replay. I think there is a missing video(s).

Day 2 - Part 5 ends abruptly while talking about pgAmin.
Day 2 - Part 6 picks up at GIN.

Missing under Query Performance:

Explain
Indexes
Create an Index

Also, Day 2 Parts 8 and 9 should be swapped. Currently, Part 8 is the conclusion.

Failing saving PSQL session data after turning off computer

while running docker and psql session , every things runs perfect but after closing pc all the data in the PC is losed,? what am i doing wrong any directions from here?

Can't start btholt/complete-intro-to-sql containerdocker

When I try to run
docker run -e POSTGRES_PASSWORD=lol --name=sql -p 5432:5432 -d --rm btholt/complete-intro-to-sql
I get
WARNING: The requested image's platform (linux/arm64/v8) does not match the detected host platform (linux/amd64) and no specific platform was requested e69219312f972c308fc979ae1227dd5562b77e5cc4045e96b923a381c02cfd09
and nothing happens.

I looked around for solutions and one that seemed to do anything was adding a --platform linux/arm64/v8 option, which shuts up the warning message, but either way both docker ps -a and docker container ls -a do not display the btholt/complete-intro-to-sql container. I am running Ubuntu.

[Bug] Update and Delete insert typo missing type

In Updates and Deletes, you have the following code

INSERT INTO ingredients
  (title, image)
VALUES
  ('not real 1', 'delete.jpg'),
  ('not real 2', 'delete.jpg');

This will cause an error: "ERROR: null value in column "type" of relation "ingredients" violates not-null constraint"

In the previous lesson Tables, we altered the table to have type be NOT NULL.

ALTER TABLE ingredients
ADD COLUMN image VARCHAR ( 255 ),
ADD COLUMN type VARCHAR ( 50 ) NOT NULL;

I suggest to change it to:

INSERT INTO ingredients
  (title, image, type)
VALUES
  ('not real 1', 'delete.jpg', 'fake'),
  ('not real 2', 'delete.jpg', 'fake');

[Bug] Wrong id used in Relationships

In Relationships, you have the code

SELECT title, body FROM recipes WHERE id = 4;

It should be

SELECT title, body FROM recipes WHERE recipe_id = 4;

Since this is the table

CREATE TABLE recipes (
  recipe_id INTEGER PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
  title VARCHAR ( 255 ) UNIQUE NOT NULL,
  body TEXT
);

[Bug] Need to re-insert recipes_photos data

In Joins and Constraints Project, it assumes you have data in recipes_photos when we don't anymore.

We need to seed the data in:

INSERT INTO recipes_photos
  (recipe_id, url)
VALUES
  (1, 'cookies1.jpg'),
  (1, 'cookies2.jpg'),
  (1, 'cookies3.jpg'),
  (1, 'cookies4.jpg'),
  (1, 'cookies5.jpg'),
  (2, 'empanada1.jpg'),
  (2, 'empanada2.jpg'),
  (3, 'jollof1.jpg'),
  (4, 'shakshuka1.jpg'),
  (4, 'shakshuka2.jpg'),
  (4, 'shakshuka3.jpg'),
  (5, 'khachapuri1.jpg'),
  (5, 'khachapuri2.jpg');
-- no pictures of xiao long bao

Optionally, you can add this data in on the Foreign Keys page when we drop and re-create the tables.