Build the junction table

Turn a many-to-many relationship into a table, and let its key stop duplicates.

What to build

An enrollments junction table filled from course requests, then a count of courses per student.

Requirements

• Create enrollments with student_id and course_code, both required, both foreign keys, and together the primary key.
• Copy the requests in with the INSERT OR IGNORE given in the starter.
• Show every student's name and number of courses, sorted by name.

Tables

Each test fills these tables with its own rows, then runs your SQL:

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE courses (
  code TEXT PRIMARY KEY,
  title TEXT NOT NULL
);
CREATE TABLE requests (
  id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL,
  course_code TEXT NOT NULL
);

Output

Columns name and courses, in alphabetical order of name. Your result is compared with the expected one, column names included, so name each column exactly as asked (use AS).

Constraints

Every request names an existing student and course, and a student may ask for the same course more than once.

Example

When a test starts with:

INSERT INTO students VALUES (1, 'Rahim'), (2, 'Nadia'), (3, 'Karim');
INSERT INTO courses VALUES ('CSE101', 'Programming'), ('MAT101', 'Calculus');
INSERT INTO requests VALUES (1, 1, 'CSE101'), (2, 1, 'CSE101'), (3, 2, 'CSE101'), (4, 2, 'MAT101');

your SQL should print:

name   courses
-----  -------
Karim  0
Nadia  2
Rahim  1
(3 rows)

Why it works

The composite primary key allows each student and course pair once, so repeated requests are ignored. The LEFT JOIN from students keeps students with no enrollments, and they count 0.

Examples

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE courses (
  code TEXT PRIMARY KEY,
  title TEXT NOT NULL
);
CREATE TABLE requests (
  id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL,
  course_code TEXT NOT NULL
);
INSERT INTO students VALUES (1, 'Rahim'), (2, 'Nadia'), (3, 'Karim');
INSERT INTO courses VALUES ('CSE101', 'Programming'), ('MAT101', 'Calculus');
INSERT INTO requests VALUES (1, 1, 'CSE101'), (2, 1, 'CSE101'), (3, 2, 'CSE101'), (4, 2, 'MAT101');

name   courses
-----  -------
Karim  0
Nadia  2
Rahim  1
(3 rows)

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE courses (
  code TEXT PRIMARY KEY,
  title TEXT NOT NULL
);
CREATE TABLE requests (
  id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL,
  course_code TEXT NOT NULL
);
INSERT INTO students VALUES (1, 'A');
INSERT INTO courses VALUES ('X', 'x');
INSERT INTO requests VALUES (1, 1, 'X'), (2, 1, 'X'), (3, 1, 'X');

name  courses
----  -------
A     1
(1 row)

Each test makes its own tables, runs your SQL on them, and compares what your queries return. Changes never carry over from one test to the next.

Hints

Solution

CREATE TABLE enrollments (
  student_id INTEGER NOT NULL REFERENCES students(id),
  course_code TEXT NOT NULL REFERENCES courses(code),
  PRIMARY KEY (student_id, course_code)
);

INSERT OR IGNORE INTO enrollments (student_id, course_code)
SELECT student_id, course_code FROM requests ORDER BY id;

SELECT s.name, COUNT(e.course_code) AS courses
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.name;

Lesson: Designing a database: the ER model →