Session 2: Understanding JOINs

Focus: Practice JOINs step-by-step

Time: 15-20 minutes

📋 Database Schema (Always Visible)
Students (student_id, name, email, major)
Enrollments (enrollment_id, student_id, course_id, grade)
Courses (course_id, course_name, credits)

Exercise 1: Basic Query (No JOIN) EASY

Task: Show all students who study "Computer Science", sorted by name

Solution

SELECT name, major
FROM Students
WHERE major = 'Computer Science'
ORDER BY name;

Key Points: Only one table needed, use WHERE to filter, ORDER BY to sort.

Exercise 2: Simple JOIN MEDIUM

Task: Show student names and the courses they are enrolled in

Hint: You need to JOIN Students with Enrollments, then Enrollments with Courses

Solution

SELECT s.name, c.course_name
FROM Students s
JOIN Enrollments e ON s.student_id = e.student_id
JOIN Courses c ON e.course_id = c.course_id;

How it works:

  • Start with Students table
  • JOIN to Enrollments using student_id
  • JOIN to Courses using course_id
  • This connects students → enrollments → courses

Exercise 3: JOIN with COUNT HARD

Task: Show each course name and how many students are enrolled in it

Hint: Use COUNT and GROUP BY

Solution

SELECT c.course_name, COUNT(*) as student_count
FROM Courses c
JOIN Enrollments e ON c.course_id = e.course_id
GROUP BY c.course_id, c.course_name;

How it works:

  • JOIN Courses with Enrollments
  • GROUP BY groups all rows for the same course together
  • COUNT(*) counts how many enrollments for each course
← Back to SQL Skills Lab