Skip to content

Student Gradebook with Joins

Easy ~20 min

What you'll build

A database with students, courses, and grades tables, plus JOIN queries that produce a full gradebook report showing each student's grades across courses.

Goals

  • Create three related tables (students, courses, grades)
  • Use INNER JOIN to combine student and grade data
  • Use LEFT JOIN to include students with no grades
  • Compute average grades per student

Prerequisites

  • Foreign keys and relationships
  • Basic SELECT queries
  • Understanding of JOIN concepts

Steps

  1. 1

    Create the tables

    Create students, courses, and grades tables. The grades table is a junction table linking students to courses with a score.

    Code
    CREATE TABLE students (
      id   SERIAL PRIMARY KEY,
      name TEXT NOT NULL
    );
    
    CREATE TABLE courses (
      id   SERIAL PRIMARY KEY,
      name TEXT NOT NULL
    );
    
    CREATE TABLE grades (
      student_id  INTEGER REFERENCES students(id),
      course_id   INTEGER REFERENCES courses(id),
      score       NUMERIC(5,2),
      PRIMARY KEY (student_id, course_id)
    );

    Expected result

    Three tables are created with a composite primary key on grades.

  2. 2

    Insert sample data

    Insert students, courses, and grades. Leave one student without any grades to test LEFT JOIN.

    Code
    INSERT INTO students (name) VALUES ('Ada'), ('Grace'), ('Alan');
    INSERT INTO courses (name) VALUES ('Math'), ('Physics');
    INSERT INTO grades VALUES
      (1, 1, 95.0), (1, 2, 88.5),
      (2, 1, 78.0);

    Expected result

    Three students, two courses, and three grades are inserted. Alan has no grades.

  3. 3

    INNER JOIN: students with grades

    Write an INNER JOIN to list each student's name with their course and score. Students without grades are excluded.

    Code
    SELECT s.name AS student, c.name AS course, g.score
    FROM grades g
    INNER JOIN students s ON g.student_id = s.id
    INNER JOIN courses c ON g.course_id = c.id
    ORDER BY s.name, c.name;

    Expected result

    Ada and Grace appear with their grades. Alan is excluded (no grades).

  4. 4

    LEFT JOIN: all students with averages

    Write a LEFT JOIN with aggregation to show every student's average score, including students with no grades (NULL average).

    Code
    SELECT s.name, AVG(g.score) AS avg_score
    FROM students s
    LEFT JOIN grades g ON g.student_id = s.id
    GROUP BY s.name
    ORDER BY avg_score DESC NULLS LAST;

    Expected result

    All three students appear. Ada has the highest average, Alan has NULL.

  5. 5

    Add indexes to optimize queries

    Create indexes on frequently queried columns to improve query performance. Indexes are the primary means of database performance optimization, but be mindful of write overhead.

    Code
    -- Create indexes on frequently filtered columns
    CREATE INDEX idx_users_email ON users(email);
    CREATE INDEX idx_orders_user_id ON orders(user_id);
    CREATE INDEX idx_orders_created_at ON orders(created_at DESC);
    
    -- Verify the index is used
    EXPLAIN SELECT * FROM orders WHERE user_id = 42;

    Expected result

    EXPLAIN output shows an index scan using idx_orders_user_id instead of a full table scan.

Try it yourself

What you learned

You mastered INNER and LEFT JOINs in a real-world scenario, understanding when each is appropriate and how to combine joins with aggregation.

Related Resources

Related Lessons

Related Glossary

Related Glossary

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors

← Back to topic