College Student and Course Management System

Managing students, courses, and faculty is one of the most common use cases for a Database Management System (DBMS). In this blog, we’ll walk through how to build a mini Student & Course Management System using SQL.

By the end, you’ll learn:
✅ How to create tables (DDL)
✅ How to insert data (DML)
✅ How to update & delete data
✅ How to query and join tables for insights
✅ How to make your system practical & interesting

🏫 Step 1: Creating the Database

Let’s start by creating our database.

CREATE DATABASE CollegeDB;
USE CollegeDB;

👩‍🎓 Step 2: Creating the Tables

We’ll need 3 main tables:

1. Students – to store student details
2. Courses – to store course details
3. Enrollments – to link students with courses

Students Table
CREATE TABLE Students (
StudentID INT PRIMARY KEY AUTO_INCREMENT,
StudentName VARCHAR(100) NOT NULL,
Department VARCHAR(50),
Email VARCHAR(100) UNIQUE,
PhoneNo VARCHAR(15)
);

-- Courses Table
CREATE TABLE Courses (
CourseID INT PRIMARY KEY AUTO_INCREMENT,
CourseName VARCHAR(100) NOT NULL,
Credits INT NOT NULL
);

-- Enrollments Table (Relationship)
CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY AUTO_INCREMENT,
StudentID INT,
CourseID INT,
Semester VARCHAR(10),
Grade CHAR(2),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

📝 Step 3: Inserting Data

Let’s add some sample students and courses.

-- Insert Students
INSERT INTO Students (StudentName, Department, Email, PhoneNo) VALUES
('Rahul Kumar', 'Computer Science', '[e-mail]', '9876543210'),
('Priya Sharma', 'Commerce', '[e-mail]', '9876501234'),
('Amit Verma', 'Mechanical', '[e-mail]', '9876523456');

-- Insert Courses
INSERT INTO Courses (CourseName, Credits) VALUES
('Database Management', 4),
('Computer Networks', 3),
('Financial Accounting', 4),
('Thermodynamics', 3);

-- Enroll Students into Courses
INSERT INTO Enrollments (StudentID, CourseID, Semester, Grade) VALUES
(1, 1, 'Sem1', 'A'),
(1, 2, 'Sem1', 'B'),
(2, 3, 'Sem2', 'A'),
(3, 4, 'Sem1', 'B');

🔍 Step 4: Querying the Database

Now the fun part – let’s ask our database questions!

1. Show all students
   SELECT * FROM Students;

2. Find all courses taken by Rahul Kumar
   SELECT s.StudentName, c.CourseName, e.Semester, e.Grade
   FROM Enrollments e
   JOIN Students s ON e.StudentID = s.StudentID
   JOIN Courses c ON e.CourseID = c.CourseID
   WHERE s.StudentName = 'Rahul Kumar';

3. Count how many students are in each department
   SELECT Department, COUNT(*) AS TotalStudents
   FROM Students
   GROUP BY Department;

4. Find top performers (students with Grade 'A')
   SELECT s.StudentName, c.CourseName, e.Grade
   FROM Enrollments e
   JOIN Students s ON e.StudentID = s.StudentID
   JOIN Courses c ON e.CourseID = c.CourseID
   WHERE e.Grade = 'A';

⚡ Step 5: Updating & Deleting Data

Rahul changed his phone number? Let’s update it.

UPDATE Students
SET PhoneNo = '9998887776'
WHERE StudentName = 'Rahul Kumar';

Want to remove a student completely?
DELETE FROM Students
WHERE StudentID = 3;

🎯 Step 6: Why This System Matters

This Student & Course Management System is not just a practice project — it’s the foundation of real-world college ERP systems.

• Colleges use it to manage admissions, courses, results, and fees.
• You can extend it with features like faculty details, attendance tracking, and exam results.
• Add a web or app interface and you’ve got a full-fledged college management software!

🚀 Final Thoughts

We just built a mini college ERP system using SQL. You now know how to:
✔ Create and link tables
✔ Insert, update, and delete data
✔ Query with joins and conditions
✔ Extract insights from your database

This project is a perfect mini-project for college students in DBMS, SQL, or Software Engineering courses.