SQL School

In this challenge you'll create a small school database from scratch: write the schema, load seed data, then make a series of modifications. This exercises SQL's data-definition (CREATE, ALTER) and data-modification (INSERT, UPDATE) sides — different from the SELECT-heavy queries you've been doing all week.

Starter files: create-schema.sql, seed-data.sql, plus a Dockerfile and setup.sh for running Postgres in Docker (same pattern as the other database repos this week).

Every database server supports these core commands. You'll touch all of them in this challenge.

Postgres supports multiple databases; the included Dockerfile creates a fresh, empty one named school inside a container called pg_school. Build the image, start the container, and connect in one step:

$ sh setup.sh

That drops you into a psql session connected to school. List tables — should be empty:

school=# \d
No relations found.

Leave this session open — you'll come back to it to inspect your work. (Done for the day? docker stop pg_school shuts it down; the container runs with --rm, so it cleans up after itself. Everything you build lives in your .sql files, so re-running setup.sh plus your scripts rebuilds it all.)

Open create-schema.sql. The students table is already written for you:

DROP TABLE IF EXISTS students;
CREATE TABLE students (
  id           serial PRIMARY KEY,
  first_name   varchar(255) NOT NULL,
  last_name    varchar(255) NOT NULL,
  birthdate    date NOT NULL,
  address_id   integer
);

Add CREATE TABLE statements for these three tables. Use the column-type table above to pick types, and decide which columns should be NOT NULL:

addresses — id, line_1, city, state, zipcode

classes — id, name, credits

enrollments — id, student_id, class_id, grade

Notes:

• serial is a special Postgres type that gives you an auto-incrementing integer — use it for every id column.
• Primary keys are NOT NULL by default; you don't need to spell it out.
• Give thought to which other columns make sense as NOT NULL.

Load and reload your schema as you go. From the repo directory, in a second terminal (leave the psql session from setup.sh open in the first):

$ docker exec -i pg_school psql -U postgres -d school < create-schema.sql

Re-run that command after every edit — the script DROPs its tables before creating them, so it's safe to run repeatedly.

After loading, switch back to the psql session setup.sh opened and inspect each table with \d:

school=# \d students
                                   Table "public.students"
   Column   |          Type          |                       Modifiers
------------+------------------------+------------------------------------------------
 id         | integer                | not null default nextval('students_id_seq'...)
 first_name | character varying(255) | not null
 ...

$ docker exec -i pg_school psql -U postgres -d school < seed-data.sql

You should see a stream of INSERT 0 1 lines. If you see errors, your schema probably doesn't match — fix the schema and rerun both files.

Verify with a SELECT on each table:

SELECT * FROM students;
SELECT * FROM addresses;
SELECT * FROM classes;
SELECT * FROM enrollments;

Write each as a SQL script and run it. You can put them all in a single modifications.sql or split them out — your call.

1. Insert an additional address into the addresses table.
2. Update the students table so the student without an address gets assigned to the new address.
3. Insert a sibling of that same student as a new row in students (same last name, different first name).
4. Create a new table extracurriculars (e.g. football, journalism, debate team) with id and name.
5. Insert at least 3 rows into extracurriculars.
6. Alter the students table to add a new column extracurricular_id referencing the extracurriculars table.
7. Update the students table to assign each student an extracurricular_id.

• Why use serial for primary keys instead of a column you might already have (e.g. social security number, ISBN)? What's the cost of "natural" keys vs synthetic ones?
• When should a column be NOT NULL? What's the tradeoff?
• After step 6, every existing row in students has NULL in the new column. How does Postgres handle that — and what would happen if you added the column as NOT NULL instead?
• DELETE vs DROP — what's the difference and when would you pick each?

• Add a CHECK constraint on students.birthdate to reject future dates.
• Add a UNIQUE constraint on (student_id, class_id) in enrollments so a student can't be enrolled in the same class twice.
• Constrain the existing grade column on enrollments to the values ('A', 'B', 'C', 'D', 'F', 'INC') with a CHECK constraint — look up Postgres CHECK constraints.
• Write a SELECT that joins all four (now five) tables and prints each student's name, their address city, their classes, and their extracurricular.

Stuck? Have a code error? Use the "4 Before Me" debugging checklist to help you solve it!