STUDENT MANAGEMENT SYSTEM
PROJECT REPORT
Project Title : Student Management
System Using SQL
Name : Hemamalini . S
Institute Name : Besent Technology
(Electronic city)
Course Name : Data Analytics
Date of Submission : 30.05.2025
Table of Contents:
1. Introduction
2. Objective
3. Database Design
4. Table Structure
5. SQL Queries
6. Sample Outputs
7. Conclusion
Introduction:
The Student Management System is a database project developed
using SQL to manage student data, course information, enrollments,
instructors, and course assignments in a college or school environment.
Objective:
The main objective is to design and implement a relational database to
efficiently handle student-related data and perform SQL operations such
as querying, inserting, and joining data across multiple related tables.
Database Design:
Database Name: Student Management
Mention that it uses relational database concepts and includes five
Tables
1) Students
2) Courses
3) Enrollment
4) Instructors
5) Course_Assignments
Table Structures
Table Name: Students
Columns:
student_id INT PRIMARY KEY AUTO_INCREMENT
first_name VARCHAR(50) NOT NULL
last_name VARCHAR(50) NOT NULL
date_of_birth DATE NOT NULL
gender 'Male', 'Female', 'Other' NOT NULL
email VARCHAR(100) UNIQUE NOT NULL
phone_number VARCHAR(15) UNIQUE
address TEXT
Table Name: Courses
Columns:
course_id INT PRIMARY KEY AUTO_INCREMENT
course_name VARCHAR(100) NOT NULL
course_description TEXT
credit_hours INT NOT NULL
Table Name: Enrollment
Columns:
enrollment_id INT PRIMARY KEY AUTO_INCREMENT
student_id INT NOT NULL
course_id INT NOT NULL
enrollment_date DATE NOT NULL
Constraints:
FOREIGN KEY (student_id) REFERENCES Students(student_id)
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
Table Name: Instructors
Columns:
instructor_id INT PRIMARY KEY AUTO_INCREMENT
first_name VARCHAR(50) NOT NULL
last_name VARCHAR(50) NOT NULL
email VARCHAR(100) UNIQUE NOT NULL
phone_number VARCHAR(15) UNIQUE
Table Name: CourseAssignments
Columns:
assignment_id INT PRIMARY KEY AUTO_INCREMENT
course_id INT NOT NULL
instructor_id INT NOT NULL
Constraints:
FOREIGN KEY (course_id) REFERENCES Courses(course_id)
FOREIGNKEY(instructor_id)REFERENCES
Instructors(instructor_id)
SQL Queries:
1. Retrieve the full names and email addresses of all students enrolled
in the Computer Science course.
SELECT
CONCAT(first_name, ' ', last_name) AS fullname,
[Link],
[Link],
c.course_name
FROM students s
INNER JOIN
enrollment e ON s.student_id = e.student_id
JOIN
courses c ON c.course_id = e.course_id
WHERE
course_name = 'computer science';
2. List all courses along with the number of students enrolled in each
course.
SELECT
c.course_name,
COUNT(e.student_id) AS enrolled_students
FROM
Courses c
LEFT JOIN
Enrollment e ON c.course_id = e.course_id
GROUP BY
c.course_name;
OUTPUT:
3. Find the instructors who are teaching more than 2 courses.
SELECT
CONCAT(i.first_name, ' ', i.last_name) AS ful_name,
COUNT(course_id)as num_of_teaching
FROM
instructors i
JOIN
course_assignments c ON i.instructor_id = c.instructor_id
GROUP BY i.instructor_id
HAVING COUNT(course_id) > 2;
OUTPUT:
4. Retrieve the details of students (name, email) who are enrolled in a
course taught by Instructor Name.
SELECT
CONCAT(s.first_name, s.last_name) AS studentname,
[Link],
s.phone_number,
c.course_name,
CONCAT(i.first_name, i.last_name) AS instructorName
FROM students s
JOIN
enrollment e ON e.student_id = s.student_id
JOIN
courses c ON c.course_id = e.course_id
JOIN
course_assignments ca ON ca.course_id = c.course_id
JOIN
instructors i ON i.instructor_id = ca.instructor_id;
OUTPUT:
5. List all students who have not enrolled in any course
SELECT
CONCAT(s.first_name, ' ', s.last_name) AS students_name,
date_of_birth,
gender,
email
FROM students s
LEFT JOIN
enrollment e ON s.student_id = e.student_id
WHERE
e.enrollment_id IS NULL;
OUTPUT:
[Link] the list of courses with no assigned instructor.
SELECT
ca.instructor_id, c.course_name
FROM
courses c
LEFT JOIN
course_assignments ca ON c.course_id = ca.course_id
WHERE
ca.instructor_id IS NULL;
OUTPUT:
7. Retrieve the full details of the Students table, sorted by last name in
alphabetical order
SELECT *FROM
students
ORDER BY last_name ASC;
OUTPUT:
[Link] the total number of credit hours for a student enrolled in the
Database Systems and Web Development courses
SELECT
SUM(c.credit_hours) AS total_credit_hours
FROM
courses c
JOIN
enrollment e ON c.course_id = e.course_id
WHERE
c.course_name IN ('database system' , 'web development');
OUTPUT:
9. Identify the student with the maximum number of enrollments.
SELECT
s.student_id,
COUNT(e.enrollment_id) AS max_enrollments,
CONCAT(s.first_name, ' ', s.last_name) AS student_name
FROM
enrollment e
JOIN
students s ON e.student_id = s.student_id
GROUP BY s.student_id
LIMIT 1;
OUTPUT:
10. Get the details of students whose phone numbers are missing
SELECT *FROM
students
WHERE
phone_number IS NULL;
OUTPUT:
Conclusion:
This project helped me understand how to design normalized
databases, apply SQL constraints, and write advanced queries using
JOINs, aggregation, and filtering. It gave hands-on experience in building
real-time database solutions.