COLLEGE STUDENT AND COURSE MANAGEMENT SYSTEM

Introduction

Databases are the backbone of any college or university management system, for tracking students, courses and grades, SQL makes it possible to store and retrieve data efficiently. In this blog,I implemented such a system using Oracle LiveSQL. It includes concepts like create tables, insert data, enforce constraints, run queries with functions, aggregates, joins, and grouping, and even write a stored procedure.

BASE SCHEMA

As part of my assignment i made this database which
Comprises of 4 tables ie Students, Courses, Enrollments and Faculty.

TABLE CREATION

Student 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)
);

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

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


INSERTING SAMPLE DATA

I inserted sample student data into student table as shown below:

`INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Sarah', 'Biomedical', DATE '2000-05-12', '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'James', 'Mechanical', DATE '2001-07-20', '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Charlie', 'Electrical', DATE '2002-02-14', '[e-mail]');
`

ALTER TABLE

This adds a column called gender to the student table
ALTER TABLE Students
ADD (Gender CHAR(10));

ADDING CONSTRAINTS

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

Using Functions in SELECT

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

Aggregate Functions

Average course credits
`SELECT AVG(Credits) AS AvgCredits FROM Courses;

SELECT COUNT(*) AS TotalStudents FROM Students;
`

JOIN Operation

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

This query lists all students with their courses and grades.

GROUP BY with HAVING

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

The above code shows departments with more than 2 students.

Views

`CREATE OR REPLACE VIEW StudentCoursesView AS
SELECT s.Name AS Name, c.CourseName, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;
Now you can query the view directly:

SELECT * FROM StudentCoursesView;

Stored Procedure

A stored procedure to update grades
`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;
END;
/

Call it in Oracle LiveSQL:

BEGIN
UpdateGrade(1, 101, 'A');
END;
/

`

Conclusion

In this blog, we explored the essential SQL operations that form the from creating and modifying tables using DDL and ALTER, inserting and managing records with DML, enforcing constraints to maintain data integrity, and applying functions and aggregates for insights, to performing joins, grouping with HAVING, building reusable views, and even automating tasks with stored procedures—each step plays a vital role in efficient database management.

By practicing these queries

1. you not only learn the syntax but also gain a deeper understanding of how databases ensure accuracy, consistency, and reliability