Building a College Student & Course Management System with SQL

In every college, managing students, courses, and enrollments can become a real challenge. From storing student details to tracking who has enrolled in which course, a well-structured database makes life a lot easier. That’s where SQL comes in.

In this blog, let’s explore how we can design a College Student & Course Management System step by step—with both the concepts and the queries.

🏗 Designing the Foundation

We begin by creating the core tables:

Students Table
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
DOB DATE,
Email VARCHAR2(50) UNIQUE
);

Courses Table
CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2)
);

Enrollments Table (Many-to-Many)
CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);

This setup handles the many-to-many relationship between students and courses.

🔑 Creating the Faculty Table (DDL)

We can also add a faculty table to store professors’ details.

CREATE TABLE Faculty (
FacultyID NUMBER PRIMARY KEY,
FacultyName VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
Email VARCHAR2(50) UNIQUE
);

✍️ Inserting Data (DML)

Now, let’s add a few student records.

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Arun Kumar', 'CSE', DATE '2002-05-10', '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Priya Sharma', 'ECE', DATE '2001-11-23', '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Rahul Singh', 'MECH', DATE '2003-02-15', '[e-mail]');

📞 Altering Tables

If we need to add phone numbers for students:

ALTER TABLE Students
ADD PhoneNo NUMBER(10);

✅ Defining Constraints

We restrict course credits to only 1–5:

ALTER TABLE Courses
MODIFY Credits CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5);

🔍 Using Functions

Let’s see student names in uppercase and find email lengths:

SELECT UPPER(Name) AS StudentName_Upper,
LENGTH(Email) AS EmailLength
FROM Students;

📊 Aggregate Functions

Some useful statistics:

-- Average credits of courses
SELECT AVG(Credits) AS AvgCredits FROM Courses;

-- Total number of students enrolled
SELECT COUNT(DISTINCT StudentID) AS TotalEnrolled
FROM Enrollments;

🔗 JOIN Operation

List students with their courses and grades:

SELECT s.Name AS StudentName,
c.CourseName,
e.Grade
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

📑 Grouping Data

Count students in each department, showing only those with more than 2:

SELECT Dept, COUNT() AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT() > 2;

👀 Creating a View

Simplify repeated queries with a view:

CREATE VIEW StudentCoursesView AS
SELECT s.Name AS StudentName,
c.CourseName,
e.Grade
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

⚡ Stored Procedure

Automating grade updates:

CREATE OR REPLACE PROCEDURE UpdateGrade(
p_StudentID IN NUMBER,
p_CourseID IN NUMBER,
p_NewGrade IN CHAR
) AS
BEGIN
UPDATE Enrollments
SET Grade = p_NewGrade
WHERE StudentID = p_StudentID
AND CourseID = p_CourseID;
COMMIT;
END;
/

🎯 Conclusion

By building this system, we learned how to:

✅ Create relational tables (Students, Courses, Enrollments, Faculty)

✅ Insert, alter, and update records

✅ Enforce constraints for data integrity

✅ Use functions and aggregates for insights

✅ Apply joins and grouping for reports

✅ Simplify with views

✅ Automate with stored procedures

This is a mini real-world academic database project—great practice for students and professionals learning SQL.