COLLEGE STUDENT & COURSE MANAGEMENT SYSTEM

Managing students, courses, and enrollments is one of the most common use cases in college management systems.
In this blog, we’ll design a Student & Course Management System in SQL and walk through 10 key operations: creating tables, inserting data, altering structure, defining constraints, using functions, joins, group by, views, and stored procedures.

Step 1 – Create the Students Table

We start by creating a Students table with basic details.
`CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
StudentName VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
DOB DATE,
Email VARCHAR2(50) UNIQUE
);

Step 2 – Insert Data into Students

Let’s insert some student records:
INSERT INTO Students (StudentID, StudentName, Dept, DOB, Email)
VALUES (101, 'Arun Kumar', 'CSE', DATE '2004-05-10', '[e-mail]');

INSERT INTO Students (StudentID, StudentName, Dept, DOB, Email)
VALUES (102, 'Priya Sharma', 'ECE', DATE '2003-11-15', '[e-mail]');

INSERT INTO Students (StudentID, StudentName, Dept, DOB, Email)
VALUES (103, 'Vikram Raj', 'Mechanical', DATE '2004-01-20', '[e-mail]');

Step 3 – Alter Table to Add Phone Number

We can add new columns later. Here we add a 10-digit PhoneNo.
`ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);
![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/hsdm7wyoe8lim0ohjpkf.png)


Step 4 – Create Courses Table

We’ll also need a Courses table to store course information.
CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2) CHECK (Credits BETWEEN 1 AND 5)
);

Step 5 – Enrollments Relationship (Many-to-Many)

A student can enroll in many courses, and a course can have many students.
So, we create an Enrollments table:
CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);

step 6 – Using SQL Functions

Example: display student names in uppercase and email lengths.
`SELECT UPPER(StudentName) AS StudentName_Upper,
LENGTH(Email) AS Email_Length
FROM Students;

Step 7 – Aggregate Functions

Find the average course credits and total number of students enrolled.
SELECT AVG(Credits) AS AvgCourseCredits FROM Courses
SELECT COUNT(DISTINCT StudentID) AS TotalStudentsEnrolled FROM Enrollments;

`

✅ Final Thoughts

With these steps, we have built a Student & Course Management System in SQL that includes:

Students table with constraints

Courses table

Enrollments table (many-to-many relationship)

Insert, alter, and constraints

Functions and aggregates

Joins and group by queries

Views for simplified reporting

Stored procedures for automation
Thankyou @santhoshnc sir for teaching about this and giving it as our assignment .This assignment is helpfull for me sir!!

This system forms the backbone of a college database project, and can easily be extended with Attendance, Results, or Faculty management.🚀

`

`