SQL Tutorial: Building a College Student & Course Management System (Step-by-Step Guide)

Student, course, and faculty management is a fundamental requirement in academic institutions. In this tutorial, we will design and implement a comprehensive College Management System using SQL.

The guide will take you step by step through key concepts such as Data Definition Language (DDL), Data Manipulation Language (DML), constraints, functions, joins, views, and stored procedures—providing a practical insight.

Schema Design:

To structure our College Management System effectively, we identify four key entities:

Students – Represent individuals enrolled in the institution who register for various courses.
Courses – Academic subjects or programs offered by different departments.
Enrollments – A bridge table capturing the many-to-many relationship between students and courses.
Faculty – Instructors responsible for teaching courses, each associated with a specific department.

Creating the Base Tables:
We’ll start by creating the Students, Courses and Enrollments tables.

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

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

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

👨‍🏫 Adding Faculty Table (DDL):

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

✍️ Inserting Sample Data (DML):
Let’s insert students, courses, faculty, and enrollments.

Students Table:
INSERT INTO Students VALUES (1, 'thush', 'CSb', DATE '2006-05-15', '[e-mail]');
INSERT INTO Students VALUES (2, 'kesh', 'CIVIL', DATE '2009-11-20', '[e-mail]');
INSERT INTO Students VALUES (3, 'Grace', 'IT', DATE '2004-01-10', '[e-mail]');

Courses Table:
INSERT INTO Courses VALUES (100, 'Database Systems', 4);
INSERT INTO Courses VALUES (101, 'Digital Electronics', 3);
INSERT INTO Courses VALUES (102, 'EDA, 3);

Faculty Table:
INSERT INTO Faculty VALUES (201, 'Dr. Richard', 'CSE', '[e-mail]');
INSERT INTO Faculty VALUES (202, 'Dr. Meena', 'ECE', '[e-mail]');
INSERT INTO Faculty VALUES (203, 'Dr. Kumar', 'MECH', '[e-mail]');

Enrollments Table:
INSERT INTO Enrollments VALUES (1, 1, 101, 'A');
INSERT INTO Enrollments VALUES (2, 1, 102, 'B');
INSERT INTO Enrollments VALUES (3, 2, 103, 'A');
INSERT INTO Enrollments VALUES (4, 3, 101, 'C');

The records have now been added to their respective tables.

🔧Altering Tables:
Let’s add a Phone Number column to Students.

ALTER TABLE Students
ADD PhoneNo CHAR(10);

The Phone Number column has now been included in the table.

✅Adding Constraints:
Ensure Credits in Courses are between 1 and 5.

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

🔍SELECT with Functions:
Display student names in uppercase and the length of their email IDs.

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

📊Aggregate Functions:
Find average credits and total students.

SELECT AVG(Credits) AS AvgCredits FROM Courses;
SELECT COUNT(*) AS TotalStudents FROM Students;

🔗JOIN Operation:
List all students with their enrolled courses and grades.

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

📌GROUP BY with HAVING:
Show departments with more than 2 students.

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

👁️Creating Views:
Create a view for student-course-grade info.

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

⚙️Stored Procedure:
Update a student’s grade dynamically.

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;

🎯 SQL Tutorial Summary:

🏗️ Created tables: Students, Courses, Enrollments, and Faculty (with constraints).
✍️ Inserted records into Students table with different departments.
🔧 Altered table to add PhoneNo (10-digit numbers).
📏 Applied constraints: Course credits between 1 and 5.
🔠 SELECT with functions: Displayed names in UPPERCASE & email length.
📊 Aggregate functions: Calculated average credits & total students.
🔗 JOIN operation: Listed Student Name, Course Name, and Grade.
🗂️ GROUP BY + HAVING: Department-wise student count (only >2).
👁️ View (StudentCoursesView): Simplified Student–Course–Grade display.
⚙️ Stored Procedure (UpdateGrade): Dynamically updated student grades.

✨ This tutorial covers DDL, DML, Constraints, Functions, Joins, Grouping, Views, and Stored Procedures — a complete SQL learning pack!🚀