Student Gradebook with Joins
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
Create the tables
Create students, courses, and grades tables. The grades table is a junction table linking students to courses with a score.
CodeCREATE 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
Insert sample data
Insert students, courses, and grades. Leave one student without any grades to test LEFT JOIN.
CodeINSERT 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
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.
CodeSELECT 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
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).
CodeSELECT 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
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
- Joins
Learn all join types.
- Aggregations
Learn aggregate functions.
Related Glossary
- INNER JOIN
Matching rows only.
- LEFT JOIN
All left rows.
Related Glossary
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
- INNER JOIN
Matching rows only.
- LEFT JOIN
All left rows.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.