STUDENTS DATA BASE MANAGEMENT

Purpose: This project demonstrates a comprehensive Student Database Management

System by structuring student records, course details, enrollments, grades, and student
classifications. The SQL queries efficiently handle data insertion, retrieval, and classification,
making student management more systematic and insightful

1️ Database Setup
CREATE DATABASE Students;
USE Students;

USE Students;

• Purpose: First, we create a database named Students.

• Explanation: The CREATE DATABASE command sets up a new database. The USE
command tells the system to switch to this newly created database for further
operations.

2️ Table Creation

Create Student Table

CREATE TABLE STUDENT (
STUDENTID INT PRIMARY KEY,
STUDENT_NAME VARCHAR (100),
GENDER VARCHAR (100),
CONTACT INT, ADMISSION_YEAR INT);

• Purpose: We are creating a table called Student to store information about each
student.

• Explanation: The studentid is unique for each student, and it's the primary key. The
name, gender, contact, and admission_year are details about the student. The ENUM
data type is used for the gender column, where we only allow 'Male', 'Female', or
'Others'.

Create Courses Table

CREATE TABLE COURSES (
COURSEID INT PRIMARY KEY,
COURSE_NAME VARCHAR (100),
CREDITS INT);

• Purpose: This table stores information about courses.

• Explanation: courseid is the unique identifier for each course. The course_name is
the name of the course, and credits show how many credits the course offers.
Create Enrollments Table
CREATE TABLE ENROLLMENTS (
ENROLLMENT_ID INT PRIMARY KEY, studentid int, courseid int,
FOREIGN KEY (studentid) REFERENCES student(studentid),
FOREIGN KEY (courseid) REFERENCES courses(courseid));
);.

• Purpose: This table stores which Student are enrolled in which courses.

• Explanation: The enrollment_id is the unique identifier for each enrollment. The
studentid and courseid are references to the Student and courses tables, establishing
a relationship between Student and the courses they are enrolled in.

Create Grades Table

CREATE TABLE GRADES (
GRADEID INT, STUDENTID INT,
COURSEID INT, MARKS INT,
GRADE VARCHAR (20),
FOREIGN KEY (STUDENTID) REFERENCES STUDENT (STUDENTID),
FOREIGN KEY (COURSEID) REFERENCES COURSES (COURSEID));
• Purpose: This table stores grades for Student in their enrolled courses.

• Explanation: marks hold the marks obtained by the student, and it must be between
0 and 100. The grade column stores the letter grade. The studentid and courseid
again reference the student and courses tables.

3️ Data Insertion

Inserting Student values

INSERT INTO student (studentid, STUDENT_NAME, gender, contact, admission_year)
VALUES

(1, 'Ajay', 'Male', 876543210, 2022);

(2, 'Abhishek', 'Male', 765432109, 2023),
(3, 'Madhu', 'Female',654321098, 2022),
(4, 'Priya', 'Female', 543210987, 2023),
(5, 'Vikram', 'Male', 432109876, 2022),
(6, 'Neha', 'Female', 321098765, 2023),
(7, 'Rahul', 'Male', 310987654, 2022),
(8, 'Swati', 'Female',109876543, 2023),
(9, 'Kiran', 'Male', 198765432, 2022),
(10, 'Divya', 'Female',988776655, 2023),
(11, 'Rohan', 'Male', 877665544, 2022),
(12, 'Pooja', 'Female',766554433, 2023),
(13, 'Suresh', 'Male', 655443322, 2022),
(14, 'Meena', 'Female',544332211, 2023),
(15, 'Yash', 'Male', 443221100, 2022),
(16, 'Nidhi', 'Female',322110099, 2023),
(17, 'Arjun', 'Male', 211009988, 2022),
(18, 'Sneha', 'Female',100998877, 2023),
(19, 'Varun', 'Male', 998887776, 2022),
(20, 'Anjali', 'Female',887776665, 2023),
(21, 'Karthik', 'Male', 776665554, 2022),
(22, 'Ritika', 'Female',665554443, 2023),
(23, 'Harish', 'Male', 554443332, 2022),
(24, 'Sonia', 'Female',443332221, 2023),
(25, 'Gopal', 'Male', 332221110, 2022),
(26, 'Lavanya', 'Female', 221110009, 2023),
(27, 'Mohan', 'Male', 111009998, 2022),
(28, 'Aditi', 'Female',000998887, 2023),
(29, 'Vishal', 'Male', 999887776, 2022),
(30, 'Preeti', 'Female', 887776665, 2023);

• Purpose: Insert 30 Student into the Student table.

• Explanation: Each row contains data for one student, including their ID, name,
gender, contact number, and the year they joined.

Inserting Courses values

INSERT INTO courses (courseid, course_name, credits) VALUES
(101, 'Database Management', 4),
(102, 'Computer Networks', 3),
(103, 'Data Structures', 4),
(104, 'Artificial Intelligence', 3),
(105, 'Machine Learning', 4);

• Purpose: Insert 5 courses into the courses table.

• Explanation: Each row represents a course with a unique ID, name, and the number
of credits it offers.

Inserting Enrollments values

INSERT INTO enrollments (enrollment_id, studentid, courseid)
VALUES
(1, 1, 101), (2, 2, 102), (3, 3, 103), (4, 4, 104), (5, 5, 105),
(6, 6, 101), (7, 7, 102), (8, 8, 103), (9, 9, 104), (10, 10, 105),
(11, 11, 101), (12, 12, 102), (13, 13, 103), (14, 14, 104), (15, 15, 105),
(16, 16, 101), (17, 17, 102), (18, 18, 103), (19, 19, 104), (20, 20, 105),
(21, 21, 101), (22, 22, 102), (23, 23, 103), (24, 24, 104), (25, 25, 105),
(26, 26, 101), (27, 27, 102), (28, 28, 103), (29, 29, 104), (30, 30, 105);

• Purpose: Insert records that link Student to the courses they are enrolled in.

• Explanation: Each row represents an enrollment, where a student (e.g., studentid 1)

is enrolled in a course (e.g., courseid 101).

Inserting Grades values

INSERT INTO grades (gradeid, studentid, courseid, marks, grade)
VALUES
(1, 1, 101, 97, 'A+'), (2, 2, 102, 88, 'A'), (3, 3, 103, 92, 'A'),
(4, 4, 104, 68, 'C'), (5, 5, 105, 76, 'B'), (6, 6, 101, 78, 'B'),
(7, 7, 102, 90, 'A'), (8, 8, 103, 71, 'C+'), (9, 9, 104, 60, 'D+'),
(10, 10, 105, 96, 'A+'),(11, 11, 101, 82, 'B+'),(12, 12, 102, 72, 'C+'),
(13, 13, 103, 87, 'A'),(14, 14, 104, 38, 'F'),(15, 15, 105, 93, 'A'),
(16, 16, 101, 92, 'A'),(17, 17, 102, 77, 'B'),(18, 18, 103, 89, 'A'),
(19, 19, 104, 35, 'F'),(20, 20, 105, 98, 'A+'),(21, 21, 101, 84, 'A'),
(22, 22, 102, 28, 'F'), (23, 23, 103, 95, 'A+'), (24, 24, 104, 52, 'D+'),
(25, 25, 105, 91, 'A'), (26, 26, 101, 78, 'B'),(27, 27, 102, 17, 'F'),
(28, 28, 103, 72, 'C+'),(29, 29, 104, 31, 'F'),
(30, 30, 105, 36, 'F');

• Purpose: Insert grade data for each student in each course.

• Explanation: Each row has the student's grade for a specific course, including the
marks and the grade letter.

4️ Retrieve Data Queries

Retrieve All Student

SELECT * FROM STUDENT;

• Purpose: Get a list of all Student.

• Explanation: This query retrieves all columns for every student in the Student table.
Retrieve All Courses
SELECT * FROM COURSES;

• Purpose: Get a list of all courses.

• Explanation: This query retrieves all columns for every course in the courses table.

Retrieve Student in a Specific Course

--students in a specific course

SELECT student. student_name, student. contact, courses. Course_name

FROM student
JOIN enrollments ON student. Studentid = enrollments. Studentid
JOIN courses ON enrollments. Courseid = courses. Courseid
WHERE courses. Course_name = 'Database Management';

• Purpose: Find out which Student are enrolled in a specific course, e.g., "Database
Management".

• Explanation: We join the Student, enrollments, and courses tables to get the names
and contacts of Student who are enrolled in the "Database Management" course.

5️ Categorize Student Based on Their Grades

Create Student Classification Table

--Students Based on their grades

CREATE TABLE student_classification (

studentid INT PRIMARY KEY,
classification VARCHAR (20),
FOREIGN KEY grades(studentid)
REFERENCES student(studentid));
 • Purpose: Create a table to store classifications (like "Top Student", "Good Student",
etc.) based on student grades.

• Explanation: classification stores the classification of each student, and it is linked to

the student table by studentid.

Update Classification Based on Grades

--updating classification based on grades
INSERT INTO student_classification (studentid, classification)
SELECT student. Studentid,
CASE
WHEN [Link] = 'A+' THEN 'Top Student'
WHEN [Link] = 'A' THEN 'Good Student'
WHEN [Link] = 'B+' THEN 'Average Student'
WHEN [Link] = 'B' THEN 'Below Average'
ELSE 'Needs Improvement'
END AS classification
FROM student
JOIN grades ON student. Studentid = grades. Studentid;

SELECT * FROM student_classification;

 • Purpose: Classify Student based on their grades.

• Explanation: This query uses a CASE statement to classify Student according to their
grade (A+ as "Top Student", A as "Good Student", and so on).

Conclusion:
This Student Database Management project provides a structured and efficient way to
manage and analyse student-related data. By implementing SQL queries, the system enables
seamless operations for inserting and retrieving data about students, courses, enrollments,
and grades. Additionally, the project includes the classification of students based on their
performance, helping to categorize them into groups such as "Top Student," "Good
Student," and so on.

Key features of the project:

• Database Structure: The database consists of multiple tables (Student, Courses,

Enrollments, Grades, Student Classification) to store and manage different aspects of
student data.

• Data Integrity: The use of primary and foreign keys ensures data consistency and
integrity, linking students to their courses and grades.

• Dynamic Queries: SQL queries are designed to retrieve specific information, such as
which students are enrolled in particular courses or how they perform in their
respective courses.

• Student Classification: The project classifies students based on their grades, offering
insights into their academic performance and allowing easy categorization for further
actions.

The project can be expanded further by integrating additional features, such as tracking
student attendance or generating reports for academic advisors. Overall, this system offers a
robust approach to managing student data, making it systematic and insightful for both
students and academic administrators.

J. Ajay
ajayjunjipelli@[Link]