Credits per student

Show every student with the total credits of the courses they are enrolled in. A student with no courses has 0 credits and must still be in the list.

What to build

A query that totals the credits of every student, including students with no courses.

Requirements

• Start from students and use LEFT JOIN, so that no student is lost.
• Turn a missing total into 0 with COALESCE.
• Sort from the most credits down, then 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,
  credits INTEGER NOT NULL
);
CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(id),
  course_code TEXT REFERENCES courses(code),
  PRIMARY KEY (student_id, course_code)
);

Output

Columns name and credits, from the most credits down. Students with equal credits are 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

A student may have no enrollments at all.

Example

When a test starts with:

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

your SQL should print:

name   credits
-----  -------
Nadia  7
Rahim  3
Karim  0
(3 rows)

Why it works

LEFT JOIN keeps every student, and SUM over no rows is NULL, which COALESCE replaces with 0.

Examples

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

name   credits
-----  -------
Nadia  7
Rahim  3
Karim  0
(3 rows)

CREATE TABLE students (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
INSERT INTO students VALUES
  (1, 'Tania'),
  (2, 'Sumon');
CREATE TABLE courses (
  code TEXT PRIMARY KEY,
  title TEXT NOT NULL,
  credits INTEGER NOT NULL
);
INSERT INTO courses VALUES
  ('PHY101', 'Physics', 3);
CREATE TABLE enrollments (
  student_id INTEGER REFERENCES students(id),
  course_code TEXT REFERENCES courses(code),
  PRIMARY KEY (student_id, course_code)
);
INSERT INTO enrollments VALUES
  (2, 'PHY101');

name   credits
-----  -------
Sumon  3
Tania  0
(2 rows)

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 s.name, COALESCE(SUM(c.credits), 0) AS credits
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.id
LEFT JOIN courses c ON c.code = e.course_code
GROUP BY s.id, s.name
ORDER BY credits DESC, s.name;

Lesson: Designing a database: the ER model →