Student Marksheet in MySQL

Complete step-by-step MySQL script for Students’ Marksheet making

Step 1: Create Database

				
					CREATE DATABASE school_db;

Step 2: Use the Database

				
					USE school_db;

Step 3: Create Table

We will:

  • Store subject marks out of 100.
  • Calculate Total, Percentage, and Grade automatically.
				
					CREATE TABLE students (
    s_no INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL,
    hindi INT CHECK (hindi BETWEEN 0 AND 100),
    eng INT CHECK (eng BETWEEN 0 AND 100),
    maths INT CHECK (maths BETWEEN 0 AND 100),
    science INT CHECK (science BETWEEN 0 AND 100),
    eco INT CHECK (eco BETWEEN 0 AND 100),
    
    total INT GENERATED ALWAYS AS (hindi + eng + maths + science + eco) STORED,
    
    percentage DECIMAL(5,2) GENERATED ALWAYS AS 
        ((hindi + eng + maths + science + eco)/5) STORED,
        
    grade VARCHAR(2) GENERATED ALWAYS AS (
        CASE
            WHEN (hindi + eng + maths + science + eco)/5 >= 90 THEN 'A+'
            WHEN (hindi + eng + maths + science + eco)/5 >= 80 THEN 'A'
            WHEN (hindi + eng + maths + science + eco)/5 >= 70 THEN 'B'
            WHEN (hindi + eng + maths + science + eco)/5 >= 60 THEN 'C'
            WHEN (hindi + eng + maths + science + eco)/5 >= 50 THEN 'D'
            ELSE 'F'
        END
    ) STORED
);

Step 4: Insert 20 Students Data

				
					INSERT INTO students (name, hindi, eng, maths, science, eco) VALUES
('Aman Sharma', 78, 82, 91, 85, 74),
('Priya Verma', 88, 90, 84, 79, 92),
('Rahul Singh', 65, 70, 72, 68, 60),
('Sneha Gupta', 92, 95, 89, 94, 90),
('Arjun Mehta', 55, 60, 58, 62, 57),
('Neha Yadav', 73, 77, 69, 75, 80),
('Vikas Kumar', 81, 85, 88, 84, 79),
('Kavita Roy', 67, 72, 70, 65, 68),
('Rohan Das', 90, 88, 94, 91, 89),
('Anjali Patel', 76, 74, 79, 82, 78),
('Manish Jain', 59, 61, 64, 60, 58),
('Pooja Singh', 85, 87, 83, 86, 88),
('Deepak Rana', 71, 69, 75, 73, 70),
('Ritu Malhotra', 93, 91, 95, 92, 94),
('Sanjay Verma', 62, 58, 65, 60, 63),
('Meena Kapoor', 80, 82, 78, 85, 81),
('Amit Tiwari', 74, 76, 72, 70, 75),
('Divya Nair', 89, 84, 90, 87, 88),
('Karan Malhotra', 66, 68, 63, 69, 67),
('Simran Kaur', 91, 93, 88, 90, 92);

Step 5: View Final Table

				
					SELECT * FROM students;