Joining tables · Lesson 8 of 20

Keys and relationships

Primary and foreign keys, one-to-many and many-to-many.

8 minutes2 games

Why not put everything in one table? If we wrote the course name next to every enrolled student, we'd repeat JavaScript a hundred times; renaming the course would mean a hundred edits, and missing one would leave the data contradicting itself.

The fix: each kind of thing gets its own table, and tables point at each other through keys.

enrollments (student_id, course_id, progress)
- student_id → students.id
  - students (id, name, city, xp)
- course_id → courses.id
  - courses (id, title, price)

enrollments is a junction table: each row says "this student takes this course".

Primary key (students.id): identifies each row; never repeated, never NULL.
Foreign key (enrollments.student_id): a column pointing at a primary key in another table.

Relationship kinds: one-to-many (a student has one mentor, a mentor has many students: mentor_id), and many-to-many (a student takes many courses, a course has many students), which needs a junction table like enrollments.

SQL

-- Which courses is Laila (id 1) taking?
SELECT * FROM enrollments WHERE student_id = 1;

-- The ids alone aren't readable; look them up:
SELECT id, title FROM courses WHERE id IN (1, 2, 5);

We did it by hand in two steps: get the ids, then look them up. The JOIN in the next lesson does both in one query.

Quick question

What kind of relationship links students and courses?

Exercise

From enrollments: get the course_id of courses student 4 has finished (progress = 100).

Expected result:

course_id
3

SQL

SELECT course_id
FROM enrollments;

Learn more from

Played 0 of 2 games

SQL