Marksheet Database Project in MySQL

The project

A marksheet database stores exam marks and produces a result — total, percentage, grade and rank. It is a focused version of a school database, centred on marks and results. The key idea is storing marks in a separate table, one row per subject.

Designing the tables

• students — id, name, roll_no.
• subjects — id, subject_name, max_marks.
• marks — id, student_id (FK), subject_id (FK), marks_obtained.

Marks link students to subjects. Keeping max_marks on subjects lets you compute percentage correctly even if subjects are out of different totals.

Creating the schema

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    roll_no INT UNIQUE
);
CREATE TABLE subjects (
    id INT PRIMARY KEY AUTO_INCREMENT,
    subject_name VARCHAR(30),
    max_marks INT DEFAULT 100
);
CREATE TABLE marks (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT, subject_id INT, marks_obtained INT,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (subject_id) REFERENCES subjects(id)
);

Sample data

INSERT INTO students (name, roll_no) VALUES ('Aman', 1), ('Diya', 2);
INSERT INTO subjects (subject_name, max_marks) VALUES ('Math', 100), ('Science', 100);
INSERT INTO marks (student_id, subject_id, marks_obtained) VALUES
    (1, 1, 88), (1, 2, 76), (2, 1, 95), (2, 2, 90);

The result query

SELECT s.name,
    SUM(m.marks_obtained) AS total,
    ROUND(AVG(m.marks_obtained), 2) AS percentage,
    CASE
        WHEN AVG(m.marks_obtained) >= 90 THEN 'A'
        WHEN AVG(m.marks_obtained) >= 75 THEN 'B'
        WHEN AVG(m.marks_obtained) >= 33 THEN 'C'
        ELSE 'F'
    END AS grade,
    CASE WHEN MIN(m.marks_obtained) >= 33 THEN 'Pass' ELSE 'Fail' END AS result
FROM students s
JOIN marks m ON m.student_id = s.id
GROUP BY s.id, s.name;

Ranking students

SELECT s.name, SUM(m.marks_obtained) AS total,
    RANK() OVER (ORDER BY SUM(m.marks_obtained) DESC) AS rank_no
FROM students s
JOIN marks m ON m.student_id = s.id
GROUP BY s.id, s.name;

In MySQL 8.0+, RANK() gives each student a class position by total marks. On older versions, use ORDER BY total DESC.

Common mistakes

• A column per subject. Use a marks table instead, for flexibility.
• Ignoring max_marks. Percentage needs the maximum, especially if it varies.
• Grading before the pass check. One failed subject should fail the result.
• Forgetting to JOIN. Marks alone lack the student names.

Summary

• A marksheet database uses students, subjects and a marks table.
• Store marks one row per student per subject.
• Compute total (SUM), percentage (AVG) and grade (CASE).
• Check pass/fail with MIN(marks).
• Rank with ORDER BY total or RANK() (MySQL 8.0+).

Next, build the query in report card query, or the full school database.