Naveens K

Posted on Aug 22

College Student and Course Management System using SQL (Oracle)

#sqlserver #sql #oracle #database

Introduction

This is a simple, basic SQL project that demonstrates how to design and manage a small College Management System.

The idea is to create a database that stores information about:

Students (their details and department)
Courses (subjects offered)
Faculty (professors teaching in departments)
Enrollments (which student takes which course, along with grades)

Although this is a small-scale project, it reflects how real-world college databases are structured.

Why is this project useful?

Helps beginners understand how relational databases work.
Shows how different entities (students, courses, faculty) are linked through relationships.
Demonstrates SQL features like constraints, joins, views, and stored procedures.
Can be easily extended into a bigger college portal system (with attendance, GPA calculation, reports, etc.).

In short, this project is a foundation exercise that not only strengthens SQL skills but also gives a taste of how databases are applied in real educational institutions.

Database Design – Tables and Purpose

In this project, we created four main tables: Students, Courses, Enrollments, and Faculty.
Each table represents an important entity in a college system.
1. Students Table

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

Purpose:

Stores basic information about students like name, department, date of birth, and email.
StudentID acts as a primary key so that every student is uniquely identified.
The Email field is marked as UNIQUE, ensuring no two students have the same email

Learnings:

How to define primary keys for unique identification.
How to enforce data integrity using NOT NULL and UNIQUE constraints.
How to handle different data types (VARCHAR2 for text, DATE for birthdate).

2. Course Table

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

Purpose:

Stores all courses offered by the institution.
Each course has an ID, name, and credits.
CourseID uniquely identifies each course.

Learnings:

Designing a table that holds course catalog information.
Adding constraints like NOT NULL for required fields.
Understanding numeric columns (credits between 1–5).

3. Enrollments Table

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

Purpose:

Acts as a bridge table between Students and Courses.
Stores which student is enrolled in which course, along with their grade.
Uses foreign keys to connect to Students and Courses.

Learnings:

How to create relationships between tables.
Use of foreign keys for maintaining referential integrity.
Handling many-to-many relationships (one student can enroll in many courses, one course can have many students).

4. Faculty Table

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

Purpose:

Stores details of faculty members (professors).
Each faculty is linked to a department and has a unique email.

Learnings:

Designing another entity in the college system.
Importance of uniqueness (faculty emails).
Setting up department-wise grouping of faculty for future queries.

By creating these tables, we learn:

How to design normalized tables for real-world entities.

How to apply constraints (Primary Key, Unique, Not Null, Foreign Key).

How to connect multiple tables to model real-world relationships.

Inserting Data into Tables

1. Insert Data into Students

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(1, 'Rahul Sharma', 'Computer Science', DATE '2002-05-14', 'rahul.sharma@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(2, 'Priya Singh', 'Mechanical', DATE '2001-08-21', 'priya.singh@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(3, 'Amit Verma', 'Electronics', DATE '2003-01-10', 'amit.verma@example.com');

2. Insert Data into Courses

INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(104, 'Database Systems', 5);

INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(101, 'Operating Systems', 4);

INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(102, 'Thermodynamics', 3);

3. Insert Data into Enrollments

INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1001, 1, 101, 'A');

INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1002, 2, 103, 'B');

INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1003, 3, 102, 'A');

4. Insert Data into Faculty

INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(201, 'Dr. Neha Kapoor', 'Computer Science', 'neha.kapoor@example.com');

INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(202, 'Dr. Rajesh Iyer', 'Mechanical', 'rajesh.iyer@example.com');

INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(203, 'Dr. Sunita Rao', 'Electronics', 'sunita.rao@example.com');

Purpose:

To design a basic but realistic database that represents a college management system.
To define the main entities — Students, Courses, Enrollments, and Faculty — and establish relationships among them.
To insert sample records so the database is ready for practicing queries and analysis.

Learnings:

Database Design Principles – understanding how to model real-world entities (like students and courses) as database tables.
Constraints for Data Integrity – applying Primary Keys, Unique Keys, Not Null, and Foreign Keys to avoid duplicate or invalid data.
Relationships in Databases – creating one-to-many and many-to-many relationships (via Enrollments).
Data Insertion – learning how to insert records correctly, including handling dates, text fields, and numeric constraints.
Normalization Basics – separating data into different tables instead of storing everything in one, which avoids redundancy and makes queries more efficient.

In short, this phase helps us move from theory to practice:
we don’t just define tables, but also fill them with meaningful data that mirrors a real college environment, preparing the ground for more advanced SQL queries and procedures.

Queries and Learnings

1. Display Students with Uppercase Names and Email Length

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

Purpose:

Converts all student names to uppercase.
Shows the length of each student’s email address.

Learning:

Use of string functions (UPPER, LENGTH).
How SQL can transform and analyze text data.

2. Find Average Credits and Total Enrollments

SELECT 
   (SELECT AVG(CREDITS) FROM Courses) AS AvgCredits,
   (SELECT COUNT(DISTINCT EnrollID) FROM Enrollments) AS TotalEnrolledStudents
FROM dual;

Purpose:

Finds the average credits across all courses.
Counts how many students are enrolled overall.

Learning:

Use of aggregate functions (AVG, COUNT).
Writing subqueries inside a SELECT.
Using DUAL (special table in Oracle for single-row queries).

3. List Students with Their Courses and Grades

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

Purpose:

Displays each student along with the course they enrolled in and their grade.

Learning:

How to use INNER JOIN to combine multiple tables.
Understanding relationships (Students ↔ Enrollments ↔ Courses).

4. Department-Wise Student Count (Only if >2 Students)

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

Purpose:

Groups students by department.
Shows only departments having more than 2 students.

Learning:

Difference between WHERE and HAVING.
How to use GROUP BY with aggregate functions.

With these queries, we are able to:

Analyze text and numeric data.

Use joins to link tables.

Work with aggregate functions for reporting.

Apply filtering at both row-level (WHERE) and group-level (HAVING).

Views & Stored Procedures

1. Creating a View: StudentCoursesView

CREATE OR REPLACE 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 c.CourseID = e.CourseID;

Purpose:

A view is like a virtual table that stores the result of a query.
Instead of writing the long JOIN query every time, we can just select from the view.

Learning:

Views improve readability and reusability.
Good for reporting, since they provide a simplified version of complex joins.

Example Usage:

SELECT * FROM StudentCoursesView;

This directly shows all students with their enrolled courses and grades.

2. Creating a Stored Procedure: UpdateGrade

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

Purpose:

A stored procedure is a block of SQL code that can be executed with parameters.
Here, we use it to update a student’s grade in a course.

Learning:

How to create reusable PL/SQL procedures.
Passing parameters (IN) to make updates dynamic.
Importance of COMMIT to save changes permanently.

Example Usage:

EXEC UpdateGrade(1, 101, 'B+');

This updates Rahul Sharma’s grade in Database Systems to B+.

With Views & Procedures, we learn how to:

Simplify repeated queries using views.

Automate updates and tasks using stored procedures.

Combine SQL with PL/SQL programming concepts for more flexibility.

Complete Code:

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

SELECT * FROM STUDENTS;

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

SELECT * FROM COURSES;

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

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



-- Students
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(1, 'Rahul Sharma', 'Computer Science', DATE '2002-05-14', 'rahul.sharma@example.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(2, 'Priya Singh', 'Mechanical', DATE '2001-08-21', 'priya.singh@example.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(3, 'Amit Verma', 'Electronics', DATE '2003-01-10', 'amit.verma@example.com');

-- Courses
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(101, 'Database Systems', 5);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(102, 'Operating Systems', 5);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES
(103, 'Thermodynamics', 5);

-- Enrollments
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1001, 1, 101, 'A');
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1002, 2, 103, 'B');
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES
(1003, 3, 102, 'A');

-- Faculty
INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(201, 'Dr. Neha Kapoor', 'Computer Science', 'neha.kapoor@example.com');
INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(202, 'Dr. Rajesh Iyer', 'Mechanical', 'rajesh.iyer@example.com');
INSERT INTO Faculty (FacultyId, FacultyName, Dept, Email) VALUES
(203, 'Dr. Sunita Rao', 'Electronics', 'sunita.rao@example.com');

SELECT * FROM FACULTY ;

ALTER TABLE Students ADD PhoneNo VARCHAR2(10);

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


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

SELECT (SELECT AVG(CREDITS) FROM COURSES) AS AvgCredits ,(SELECT COUNT(DISTINCT ENROLLID) FROM ENROLLMENTS) AS TotalEnrolledStudents FROM dual; 

-- List all students along with the courses they are enrolled in (Student Name, Course Name, Grade).
SELECT s.Name, c.CourseName, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

-- Show each department and the count of students. Display only those departments with more than 2 students.

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

-- List all students along with the courses they are enrolled in (Student Name, Course Name, Grade).
SELECT s.Name, c.CourseName, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

-- Show each department and the count of students. Display only those departments with more than 2 students.

SELECT Dept, COUNT(*) AS StudentCount
FROM Students


CREATE OR REPLACE 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 c.CourseID = e.CourseID;


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

This project was a simple, beginner-friendly implementation of a College Management System using SQL.

We designed and implemented four key entities:

Students – storing student details.
Courses – listing subjects with credits.
Enrollments – linking students to their courses and grades.
Faculty – storing professor details.

We also:

Inserted sample data to simulate a real college environment.
Wrote queries to analyze and extract useful information.
Created a view for simplified reporting.
Built a stored procedure to dynamically update grades.

Learnings Recap

How to design normalized tables with primary & foreign key's.
How to use constraints (unique, not null,check).
Writing queries with joins, aggregates, and group filters.
Using views for** simplified queries.**
Creating procedures to automate tasks.

Future Improvements

Although this is a basic project, it can be extended with:

Attendance tracking (students’ daily presence).
Faculty-course mapping (which professor teaches which course).
GPA/CGPA calculation functions.
Triggers to automatically update data (like grade-based performance status).
More analytics reports (top students, most popular courses, etc.).

With this, we’ve shown how SQL can be used to build a realistic mini-database system that models everyday college operations.
Even though it’s simple, the same principles can be scaled up to design larger student management systems, portals, or ERP solutions.

DEV Community

College Student and Course Management System using SQL (Oracle)

Introduction

Database Design – Tables and Purpose

Inserting Data into Tables

Queries and Learnings

Views & Stored Procedures

Complete Code:

Conclusion

Learnings Recap

Future Improvements

Top comments (0)