Student Marksheet in MySQL

Student Result Management System

STEP 1: Create Database

We create a new database to store all result-related tables.

				
					CREATE DATABASE student_result;

-- Verify database creation
SHOW DATABASES;

STEP 2: Use Database

Select the database so all tables are created inside it.

				
					USE student_result;

-- Check current database
SELECT DATABASE();

STEP 3: Create Students Table

This table stores student basic details.

  • student_id → unique ID (auto generated)
  • student_name → student name
				
					CREATE TABLE students (
    student_id INT PRIMARY KEY AUTO_INCREMENT,
    student_name VARCHAR(100)
);

-- Check table structure
DESC students;

STEP 4: Insert Students

Insert student records into the table.

				
					INSERT INTO students (student_name) VALUES
('Aman'),
('Priya'),
('Rahul'),
('Sneha'),
('Arjun');

-- Check inserted records
SELECT * FROM students;

STEP 5: Create Subjects Table

This table stores subject names separately.

				
					CREATE TABLE subjects (
    subject_id INT PRIMARY KEY AUTO_INCREMENT,
    subject_name VARCHAR(50)
);

-- Check table structure
DESC subjects;

STEP 6: Insert Subjects

Add subject names into subjects table.

				
					INSERT INTO subjects (subject_name) VALUES
('Hindi'),
('English'),
('Maths'),
('Science'),
('Economics');

-- Check inserted subjects
SELECT * FROM subjects;

STEP 7: Create Marks Table

This table stores marks of each student.

Special features:

  • Total is calculated automatically.
  • Percentage is calculated automatically.
  • Grade is calculated automatically.
  • student_id connects students and marks table.
				
					CREATE TABLE marks (
    student_id INT PRIMARY KEY,

    hindi INT,
    english INT,
    maths INT,
    science INT,
    economics INT,

    total INT GENERATED ALWAYS AS 
    (hindi + english + maths + science + economics) STORED,

    percentage DECIMAL(5,2) GENERATED ALWAYS AS 
    ((hindi + english + maths + science + economics)/5) STORED,

    grade VARCHAR(2) GENERATED ALWAYS AS (
        CASE
            WHEN ((hindi + english + maths + science + economics)/5) >= 90 THEN 'A+'
            WHEN ((hindi + english + maths + science + economics)/5) >= 75 THEN 'A'
            WHEN ((hindi + english + maths + science + economics)/5) >= 60 THEN 'B'
            WHEN ((hindi + english + maths + science + economics)/5) >= 50 THEN 'C'
            ELSE 'F'
        END
    ) STORED,

    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

-- Check table structure
DESC marks;

STEP 8: Insert Marks

Insert subject marks only.
Total, percentage and grade will be calculated automatically.

				
					INSERT INTO marks (student_id, hindi, english, maths, science, economics) VALUES
(1, 78, 82, 91, 85, 74),
(2, 88, 90, 84, 79, 92),
(3, 65, 70, 72, 68, 60),
(4, 92, 95, 89, 94, 90),
(5, 55, 60, 58, 62, 57);

-- Check marks table with calculated columns
SELECT * FROM marks;

STEP 9: View Complete Result Sheet

Combine student names with their marks.

				
					SELECT students.student_name,
       marks.hindi,
       marks.english,
       marks.maths,
       marks.science,
       marks.economics,
       marks.total,
       marks.percentage,
       marks.grade
FROM students
JOIN marks
ON students.student_id = marks.student_id;

-- Full result displayed

STEP 10: Rank Students (Highest Percentage First)

Sort students based on percentage.

				
					SELECT students.student_name,
       marks.percentage
FROM students
JOIN marks
ON students.student_id = marks.student_id
ORDER BY marks.percentage DESC;

-- Top student appears first

STEP 11: Subject-wise Topper

Find student who scored highest in Hindi.

				
					SELECT students.student_name, marks.hindi
FROM students
JOIN marks
ON students.student_id = marks.student_id
ORDER BY marks.hindi DESC
LIMIT 1;

-- Hindi topper displayed
Find student who scored highest in Maths.
SELECT students.student_name, marks.maths
FROM students
JOIN marks
ON students.student_id = marks.student_id
ORDER BY marks.maths DESC
LIMIT 1;

-- Maths topper displayed

🎯 What Students Learn From This Project

  • Creating database
  • Creating tables
  • Primary key
  • Foreign key
  • Inserting data
  • Generated columns
  • CASE statement
  • JOIN
  • ORDER BY
  • LIMIT
  • Basic ranking logic