🎓 College Student & Course Management System with SQL (Step by Step)

#webdev #sql #learning

Managing students, courses, and faculty is one of the most common real-world scenarios in colleges and universities. In this blog post, we’ll design and implement a College Student & Course Management System using Oracle SQL.

We’ll go through 10 practical SQL tasks that will help you strengthen your skills:
✅ Table creation (DDL)
✅ Data insertion (DML)
✅ Altering & constraints
✅ Queries with functions
✅ Aggregates & GROUP BY
✅ Joins & Views
✅ Stored Procedures

By the end, you’ll have a mini student management system that can be extended for larger projects.

🏗 Database Schema

We start with three core tables:

👨‍🎓 Students → Stores student details

📚 Courses → Stores course information

📝 Enrollments → Many-to-many relationship (students taking multiple courses)

And later, we’ll add Faculty.

🚀 Implementation – Step by Step
1️⃣ Create Faculty Table
CREATE TABLE Faculty (
FacultyID NUMBER PRIMARY KEY,
FacultyName VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
Email VARCHAR2(50) UNIQUE
);

2️⃣ Insert Students
INSERT INTO Students (StudentID, NAME_, Dept, DOB, Email)
VALUES (1, 'Ramana Raj', 'CSE', TO_DATE('2003-05-12', 'YYYY-MM-DD'), 'ramana123@gmail.com');

INSERT INTO Students (StudentID, NAME_, Dept, DOB, Email)
VALUES (2, 'Thimbesh', 'ECE', TO_DATE('2002-09-21', 'YYYY-MM-DD'), 'thimbesh123@gmail.com');

INSERT INTO Students (StudentID, NAME_, Dept, DOB, Email)
VALUES (3, 'Mouliswaran', 'Mechanical', TO_DATE('2001-12-02', 'YYYY-MM-DD'), 'mouliswaran44@gmail.com');

3️⃣ Alter Students Table (Add Phone No.)
ALTER TABLE Students ADD PhoneNo NUMBER(10);

4️⃣ Add Constraint to Courses
ALTER TABLE Courses
ADD CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5);

5️⃣ Functions – Names in UPPERCASE & Email Length
SELECT UPPER(Name) AS StudentNameUpper,
LENGTH(Email) AS EmailLength
FROM Students;

6️⃣ Aggregates – Avg Credits & Total Students Enrolled
SELECT AVG(Credits) AS AvgCredits FROM Courses;

SELECT COUNT(DISTINCT StudentID) AS TotalStudentsEnrolled
FROM Enrollments;

7️⃣ JOIN – Students with Courses & 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;

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

9️⃣ Create View (StudentCoursesView)
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 – Update Grade
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;

🎯 Summary

In this blog post, we built a mini College Student & Course Management System using SQL:

🏗 Designed tables for Students, Courses, Faculty & Enrollments

✏ Inserted and managed data with DDL & DML

🔒 Applied constraints for data integrity

📊 Queried using functions, aggregates, joins, and group by

👀 Created a View for simplified reporting

⚙ Automated updates with a Stored Procedure

👉 This project covers almost all core SQL concepts and can easily be extended to include:

Attendance tracking

Faculty-course mapping