Join tables for a study dashboard

Connect related tables, preserve unmatched rows and build a small revision-time report.

About 12 minutes · Beginner

Store a relationship

A study session belongs to a student through student_id. Keeping sessions in another table allows a student to have several sessions without repeating all their details.

In SQLite, enable foreign-key enforcement for each connection before relying on it. These sample inserts reference the students created in the introduction.

PRAGMA foreign_keys = ON;
CREATE TABLE sessions (
  id INTEGER PRIMARY KEY,
  student_id INTEGER NOT NULL REFERENCES students(id),
  minutes INTEGER NOT NULL CHECK (minutes >= 0)
);
INSERT INTO sessions (id, student_id, minutes) VALUES
  (1, 1, 25), (2, 1, 30), (3, 3, 40);

Match related rows

An INNER JOIN returns rows with matching keys on both sides. A student with two sessions appears twice because each session forms a match.

Aliases such as s and t shorten qualified column names. Qualifying id and name makes the source of each value explicit.

SELECT s.name, t.minutes
FROM students AS s
JOIN sessions AS t ON t.student_id = s.id
ORDER BY s.id, t.id;

Expected output

Asha | 25
Asha | 30
Meera | 40

Include students with no sessions

LEFT JOIN keeps every student, even when no session matches. SUM returns NULL for the unmatched student; COALESCE replaces it with zero for this report.

COUNT(t.id) counts actual session rows. COUNT(*) would also count the unmatched left-side row, incorrectly reporting one session.

SELECT s.name, COUNT(t.id) AS sessions,
       COALESCE(SUM(t.minutes), 0) AS total_minutes
FROM students AS s
LEFT JOIN sessions AS t ON t.student_id = s.id
GROUP BY s.id, s.name
ORDER BY s.id;

Expected output

Asha | 2 | 55
Ravi | 0 | 0
Meera | 1 | 40

Try it yourself

Change the dashboard query to show only students with at least 40 total study minutes.

Show a possible solution

SELECT s.name, SUM(t.minutes) AS total_minutes
FROM students AS s
JOIN sessions AS t ON t.student_id = s.id
GROUP BY s.id, s.name
HAVING SUM(t.minutes) >= 40
ORDER BY s.id;

Keep exploring: official SQL documentation.

0 of 5 completed on this device