Practice: JOIN Drills

Write the full student + course + score row. Practice table aliases e, s, c.

Drill 1 — INNER JOIN three tables

Write the full student + course + score row. Practice table aliases e, s, c.

Real-life example: Three-table JOIN is matching roll number, subject code, and marks from three separate registers.

Solution pattern

SELECT s.full_name, c.course_name, e.score
FROM enrollments e
INNER JOIN students s ON e.student_id = s.student_id
INNER JOIN courses c ON e.course_id = c.course_id;

Drill 2 — LEFT JOIN find missing rows

List courses with zero enrollments using LEFT JOIN and WHERE e.enrollment_id IS NULL.

Real-life example: Empty courses are classrooms with lights on but no students — still part of the building tour.

Courses with no students

SELECT c.course_id, c.course_name
FROM courses c
LEFT JOIN enrollments e ON c.course_id = e.course_id
WHERE e.enrollment_id IS NULL;

Drill 3 — SELF JOIN same grade peers

Pair students in the same grade_level without pairing a student with themselves.

Real-life example: Peer pairs for group projects — same class, different people.

Peer pairs in grade 10

SELECT a.full_name AS student_a, b.full_name AS student_b
FROM students a
INNER JOIN students b
  ON a.grade_level = b.grade_level
 AND a.student_id < b.student_id
WHERE a.grade_level = 10;

Drill 4 — JOIN + GROUP BY

Per student: how many courses and average score. Requires JOIN then aggregation.

Real-life example: Student transcript summary — three courses, 82.5 average — one line per student.

Enrollment stats per student

SELECT
  s.full_name,
  COUNT(e.enrollment_id) AS courses_taken,
  AVG(e.score) AS gpa_courses
FROM students s
LEFT JOIN enrollments e ON s.student_id = e.student_id
GROUP BY s.student_id, s.full_name
ORDER BY gpa_courses DESC;