College Student & Course Management System

Introduction
Managing students, courses, and enrollments is one of the most common real-world applications of databases. To understand how these concepts work in practice, I have created a simple College Student & Course Management System using Oracle LiveSQL.

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

Inserting Data (DML)
INSERT INTO Students VALUES (1, 'Arun Kumar', 'CSE', DATE '2003-05-12', '[e-mail]');

Adding and modifying data (DML & ALTER)
ALTER TABLE Students
ADD PhoneNo NUMBER(10);

Defining Constraints – Credits Between 1 and 5
ALTER TABLE Courses
MODIFY Credits CHECK (Credits BETWEEN 1 AND 5);

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

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

JOIN Operation
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;

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

View – StudentCoursesView
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 – UpdateGrade
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;

output

📌 Conclusion
In this blog, I demonstrated the implementation of a simple College Student & Course Management System on Oracle LiveSQL.
The queries covered:

DDL, DML, ALTER, and Constraints
SELECT with functions
Aggregates and GROUP BY with HAVING
Joins and Views
Stored Procedure