MySQL School Database Project: Students, Courses, Grades

Project goal: a school that tracks which students take which courses and how they perform. The central idea is a many-to-many relationship with its own data (the enrollment, with a grade). The reports use aggregates and window functions.

Requirements

Students have a name, an email and a class (grade level).
Teachers teach courses. Each course has one teacher and a number of credits.
A student can enroll in many courses, and a course has many students. A student can enroll in a course only once per term.
Each enrollment gets a score from 0 to 100 (empty until graded).
Reports: class lists, the average score of each course, a ranking inside each course, student averages and students below a pass mark.

Tables and relationships

Table	Relationship
`teachers` 1 — N `courses`	A course has one teacher
`students` N — N `courses` through `enrollments`	The junction table carries `term` and `score`

The schema

query.sql

SQL

CREATE TABLE teachers (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    email VARCHAR(100) NOT NULL,
    UNIQUE KEY uq_teachers_email (email)
);

CREATE TABLE courses (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    code VARCHAR(10) NOT NULL,
    title VARCHAR(80) NOT NULL,
    credits TINYINT UNSIGNED NOT NULL,
    teacher_id INT UNSIGNED NOT NULL,
    UNIQUE KEY uq_courses_code (code),
    CONSTRAINT chk_courses_credits CHECK (credits BETWEEN 1 AND 10),
    CONSTRAINT fk_courses_teacher FOREIGN KEY (teacher_id) REFERENCES teachers (id)
);

CREATE TABLE students (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(60) NOT NULL,
    email VARCHAR(100) NOT NULL,
    class_level TINYINT UNSIGNED NOT NULL,
    UNIQUE KEY uq_students_email (email)
);

CREATE TABLE enrollments (
    student_id INT UNSIGNED NOT NULL,
    course_id INT UNSIGNED NOT NULL,
    term CHAR(7) NOT NULL,
    score DECIMAL(5,2) NULL,
    PRIMARY KEY (student_id, course_id, term),
    KEY idx_enrollments_course (course_id, term),
    CONSTRAINT chk_enrollments_score CHECK (score IS NULL OR score BETWEEN 0 AND 100),
    CONSTRAINT fk_enrollments_student FOREIGN KEY (student_id) REFERENCES students (id) ON DELETE CASCADE,
    CONSTRAINT fk_enrollments_course FOREIGN KEY (course_id) REFERENCES courses (id)
);

Output

OUTPUT

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

Query OK, 0 rows affected

The primary key (student_id, course_id, term) is what enforces “once per course per term”. score is NULL until the work is graded. NULL means not graded yet, which is different from a score of 0 (NULL).

Sample data

query.sql

SQL

INSERT INTO teachers (name, email) VALUES ('Mr. Iyer', 'iyer@school.example'), ('Ms. Khan', 'khan@school.example');
INSERT INTO courses (code, title, credits, teacher_id) VALUES ('MATH101', 'Mathematics', 4, 1), ('SCI101', 'Science', 3, 2), ('ENG101', 'English', 2, 2);
INSERT INTO students (name, email, class_level) VALUES
('Maya', 'maya@school.example', 9), ('Rohan', 'rohan@school.example', 9), ('Isha', 'isha@school.example', 10), ('Kabir', 'kabir@school.example', 10), ('Zoya', 'zoya@school.example', 9);

INSERT INTO enrollments (student_id, course_id, term, score) VALUES
(1, 1, '2026-T1', 92.5), (1, 2, '2026-T1', 88.0), (1, 3, '2026-T1', 75.0),
(2, 1, '2026-T1', 58.0), (2, 2, '2026-T1', 64.5), (2, 3, '2026-T1', 70.0),
(3, 1, '2026-T1', 81.0), (3, 2, '2026-T1', 95.0),
(4, 2, '2026-T1', 42.0), (4, 3, '2026-T1', 55.5),
(5, 1, '2026-T1', NULL), (5, 3, '2026-T1', 90.0);

Output

OUTPUT

Query OK, 2 rows affected
Records: 2  Duplicates: 0  Warnings: 0

Query OK, 3 rows affected
Records: 3  Duplicates: 0  Warnings: 0

Query OK, 5 rows affected
Records: 5  Duplicates: 0  Warnings: 0

Query OK, 12 rows affected
Records: 12  Duplicates: 0  Warnings: 0

Query 1: the class list of a course

query.sql

SQL

SELECT c.code, s.name, e.score
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE c.code = 'MATH101' AND e.term = '2026-T1'
ORDER BY s.name;

Output

OUTPUT

+---------+-------+-------+
| code    | name  | score |
+---------+-------+-------+
| MATH101 | Isha  | 81.00 |
| MATH101 | Maya  | 92.50 |
| MATH101 | Rohan | 58.00 |
| MATH101 | Zoya  |  NULL |
+---------+-------+-------+
4 rows in set

Zoya’s score is NULL: not graded yet.

Query 2: average score and pass rate per course

query.sql

SQL

SELECT c.code, t.name AS teacher, COUNT(e.score) AS graded, ROUND(AVG(e.score), 1) AS average, SUM(e.score >= 60) AS passed
FROM courses c
JOIN teachers t ON t.id = c.teacher_id
LEFT JOIN enrollments e ON e.course_id = c.id AND e.term = '2026-T1'
GROUP BY c.id, c.code, t.name
ORDER BY c.code;

Output

OUTPUT

+---------+----------+--------+---------+--------+
| code    | teacher  | graded | average | passed |
+---------+----------+--------+---------+--------+
| ENG101  | Ms. Khan |      4 |    72.6 |      3 |
| MATH101 | Mr. Iyer |      3 |    77.2 |      2 |
| SCI101  | Ms. Khan |      4 |    72.4 |      3 |
+---------+----------+--------+---------+--------+
3 rows in set

COUNT(e.score) counts only the graded rows (aggregates ignore NULL), and so does AVG. SUM(e.score >= 60) adds up the true conditions, because a comparison gives 1 or 0 in MySQL (Aggregates).

Query 3: a ranking inside each course

query.sql

SQL

SELECT c.code, s.name, e.score,
       RANK() OVER (PARTITION BY e.course_id ORDER BY e.score DESC) AS position_in_course
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE e.term = '2026-T1' AND e.score IS NOT NULL
ORDER BY c.code, position_in_course, s.name;

Output

OUTPUT

+---------+-------+-------+--------------------+
| code    | name  | score | position_in_course |
+---------+-------+-------+--------------------+
| ENG101  | Zoya  | 90.00 |                  1 |
| ENG101  | Maya  | 75.00 |                  2 |
| ENG101  | Rohan | 70.00 |                  3 |
| ENG101  | Kabir | 55.50 |                  4 |
| MATH101 | Maya  | 92.50 |                  1 |
| MATH101 | Isha  | 81.00 |                  2 |
| MATH101 | Rohan | 58.00 |                  3 |
| SCI101  | Isha  | 95.00 |                  1 |
| SCI101  | Maya  | 88.00 |                  2 |
| SCI101  | Rohan | 64.50 |                  3 |
| SCI101  | Kabir | 42.00 |                  4 |
+---------+-------+-------+--------------------+
11 rows in set

PARTITION BY e.course_id restarts the ranking in each course. RANK() gives equal scores the same position (Rank Functions).

Query 4: each student’s weighted average (GPA-style)

Courses have credits, so a four-credit course should count more than a two-credit one:

query.sql

SQL

SELECT s.name, s.class_level,
       ROUND(SUM(e.score * c.credits) / SUM(c.credits), 1) AS weighted_average,
       COUNT(*) AS graded_courses
FROM students s
JOIN enrollments e ON e.student_id = s.id AND e.score IS NOT NULL
JOIN courses c ON c.id = e.course_id
GROUP BY s.id, s.name, s.class_level
ORDER BY weighted_average DESC, s.name;

Output

OUTPUT

+-------+-------------+------------------+----------------+
| name  | class_level | weighted_average | graded_courses |
+-------+-------------+------------------+----------------+
| Zoya  |           9 |             90.0 |              1 |
| Maya  |           9 |             87.1 |              3 |
| Isha  |          10 |             87.0 |              2 |
| Rohan |           9 |             62.8 |              3 |
| Kabir |          10 |             47.4 |              2 |
+-------+-------------+------------------+----------------+
5 rows in set

The condition e.score IS NOT NULL is in the join: an ungraded course must be left out of both the numerator and the denominator.

Query 5: students who need attention

Students with any score below the pass mark of 60, with the courses:

query.sql

SQL

SELECT s.name, GROUP_CONCAT(CONCAT(c.code, ' (', e.score, ')') ORDER BY c.code SEPARATOR ', ') AS below_pass
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE e.score < 60
GROUP BY s.id, s.name
ORDER BY s.name;

Output

OUTPUT

+-------+--------------------------------+
| name  | below_pass                     |
+-------+--------------------------------+
| Kabir | ENG101 (55.50), SCI101 (42.00) |
| Rohan | MATH101 (58.00)                |
+-------+--------------------------------+
2 rows in set

Query 6: ungraded work and a teacher’s load

query.sql

SQL

SELECT s.name AS student, c.code FROM enrollments e JOIN students s ON s.id = e.student_id JOIN courses c ON c.id = e.course_id WHERE e.score IS NULL;

SELECT t.name, COUNT(DISTINCT c.id) AS courses, COUNT(e.student_id) AS enrollments
FROM teachers t JOIN courses c ON c.teacher_id = t.id LEFT JOIN enrollments e ON e.course_id = c.id
GROUP BY t.id, t.name ORDER BY t.name;

Output

OUTPUT

+---------+---------+
| student | code    |
+---------+---------+
| Zoya    | MATH101 |
+---------+---------+
1 row in set

+----------+---------+-------------+
| name     | courses | enrollments |
+----------+---------+-------------+
| Mr. Iyer |       1 |           4 |
| Ms. Khan |       2 |           8 |
+----------+---------+-------------+
2 rows in set

The rules protect the data

query.sql

SQL

1
2

INSERT INTO enrollments (student_id, course_id, term, score) VALUES (1, 1, '2026-T1', 50);
INSERT INTO enrollments (student_id, course_id, term, score) VALUES (1, 1, '2026-T2', 150);

Output

OUTPUT

1
2
3

ERROR 1062 (23000): Duplicate entry '1-1-2026-T1' for key 'enrollments.PRIMARY'

ERROR 3819 (HY000): Check constraint 'chk_enrollments_score' is violated.

The first statement is a duplicate enrollment (same student, course and term). The second has a score above 100. The database rejected both.

Common mistakes

A score of 0 for “not graded”. It lowers every average. Use NULL.
Averaging averages. The average of the course averages is not the average of all the scores when the courses have different sizes. Compute from the raw rows.
A UNIQUE on (student_id, course_id) without the term. A student could never retake a course in a later term.
Storing the teacher’s name in each course row. When the name changes, many rows need an update. Keep a teachers table.
Putting e.score IS NOT NULL in WHERE of a LEFT JOIN query, and losing courses with no graded work (LEFT JOIN).

Final result

query.sql

SQL

SELECT (SELECT COUNT(*) FROM students) AS students, (SELECT COUNT(*) FROM courses) AS courses, (SELECT COUNT(*) FROM enrollments) AS enrollments, (SELECT COUNT(*) FROM enrollments WHERE score IS NULL) AS ungraded;

Output

OUTPUT

+----------+---------+-------------+----------+
| students | courses | enrollments | ungraded |
+----------+---------+-------------+----------+
|        5 |       3 |          12 |        1 |
+----------+---------+-------------+----------+
1 row in set

Next steps: a terms table with dates, an attendance table (student, date, present), grade letters from a lookup table, and a view that gives each teacher access to only their own courses (Views).