College Student & Course Management System (Oracle LiveSQL)

sql #oracle #database #tutorial

Introduction

This blog covers my hands-on implementation of a simple College Student & Course Management System using SQL on Oracle LiveSQL.

It includes:

Table creation
Data insertion
Constraints & table alterations
SQL queries with string functions & aggregates
Joins
Views
Grouping with HAVING
Stored procedure

This use case manages students, faculty, courses, and enrollments in a college database.

Database Schema

I created four main tables:

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

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

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

Data Insertion

Students

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Arun Kumar', 'Computer Science', TO_DATE('2002-08-15','YYYY-MM-DD'), 'arun.kumar@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Divya R', 'Electronics', TO_DATE('2001-10-22','YYYY-MM-DD'), 'divya.r@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Mohan S', 'Mechanical', TO_DATE('2003-01-05','YYYY-MM-DD'), 'mohan.s@example.com');

Courses

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

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

INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (103, 'Physics', 5);

Enrollments

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

INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade)
VALUES (2, 2, 102, 'B+');

INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade)
VALUES (3, 3, 103, 'A-');

Table Alterations & Constraints

Add PhoneNo to Students

ALTER TABLE Students ADD PhoneNo VARCHAR2(10);

Add Credits Constraint to Courses

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

SQL Queries

Uppercase Names & Email Length

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

Department-wise Student Count (>2 only)

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

Average Credits & Total Enrolled Students

SELECT
(SELECT AVG(Credits) FROM Courses) AS AvgCredits,
(SELECT COUNT(DISTINCT StudentID) FROM Enrollments) AS TotalStudentsEnrolled
FROM dual;

Joins

Show student-course-grade details:

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;

Views

Created a view to simplify lookups:

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;

Stored Procedure

Procedure to update a student’s grade:

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;

Summary

Through this system, I learned: