How many students in each course

Read a many-to-many relationship through its junction table.

What to build

A query that counts the students enrolled in every course.

Requirements

• Include every course, even one with no students.
• Count the enrollments of each course.
• Return the code, the title and the count, sorted by code.

Tables

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

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

Output

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

Constraints

A student is enrolled in a course at most once.

Example

When a test starts with:

INSERT INTO courses VALUES ('CSE101', 'Programming'), ('MAT101', 'Calculus'), ('PHY101', 'Physics');
INSERT INTO enrollments VALUES (1, 'CSE101'), (2, 'CSE101'), (2, 'MAT101');

your SQL should print:

code    title        students
------  -----------  --------
CSE101  Programming  2
MAT101  Calculus     1
PHY101  Physics      0
(3 rows)

Why it works

The LEFT JOIN keeps a course with no enrollments as one row with NULLs. COUNT of a column skips NULL, so that course gets 0, where COUNT(*) would wrongly give 1.

Examples

CREATE TABLE courses (
  code TEXT PRIMARY KEY,
  title TEXT NOT NULL
);
CREATE TABLE enrollments (
  student_id INTEGER NOT NULL,
  course_code TEXT NOT NULL REFERENCES courses(code),
  PRIMARY KEY (student_id, course_code)
);
INSERT INTO courses VALUES ('CSE101', 'Programming'), ('MAT101', 'Calculus'), ('PHY101', 'Physics');
INSERT INTO enrollments VALUES (1, 'CSE101'), (2, 'CSE101'), (2, 'MAT101');

code    title        students
------  -----------  --------
CSE101  Programming  2
MAT101  Calculus     1
PHY101  Physics      0
(3 rows)

CREATE TABLE courses (
  code TEXT PRIMARY KEY,
  title TEXT NOT NULL
);
CREATE TABLE enrollments (
  student_id INTEGER NOT NULL,
  course_code TEXT NOT NULL REFERENCES courses(code),
  PRIMARY KEY (student_id, course_code)
);
INSERT INTO courses VALUES ('X1', 'Art');

code  title  students
----  -----  --------
X1    Art    0
(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

SELECT c.code, c.title, COUNT(e.student_id) AS students
FROM courses c
LEFT JOIN enrollments e ON e.course_code = c.code
GROUP BY c.code, c.title
ORDER BY c.code;

Lesson: Designing a database: the ER model →