College Student & Course Management System

OVERVIEW

This blog explains how I built a simple College Student & Course Management System using SQL in Oracle LiveSQL.

The mini-project demonstrates fundamental database concepts such as:

• Creating tables with proper constraints
• Inserting and managing data
• Altering tables to add new columns and checks
• Using SQL functions and aggregate queries
• Joining tables to retrieve related data
• Building views for simplified queries
• Writing stored procedures for automated tasks

Database Schema

TThe system manages four main entities: Students, Courses, Enrollments, and Faculty.

The use case focuses on managing students, courses, enrollments, and faculty members with their related operations.

STUDENTS TABLE

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

COURSE TABLE

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

ENROLLMENT 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 VALUES (1, 'Shinchan', 'Computer Science', DATE '2002-04-15', '[e-mail]');
INSERT INTO Students VALUES (2, 'Mitsi', 'Mathematics', DATE '2001-11-23', '[e-mail]');
INSERT INTO Students VALUES (3, 'Harry', 'Physics', DATE '2003-07-02', '[e-mail]');

-- Courses
INSERT INTO Courses VALUES (101, 'Databases', 4);
INSERT INTO Courses VALUES (102, 'Algorithms', 3);
INSERT INTO Courses VALUES (103, 'Physics', 5);

-- Enrollments
INSERT INTO Enrollments VALUES (1, 1, 101, 'A');
INSERT INTO Enrollments VALUES (2, 2, 102, 'B+');
INSERT INTO Enrollments VALUES (3, 3, 103, 'A-');

Table Alterations and Constraints

• Added a new column for phone numbers in Students.
• Ensured course credits always fall between 1 and 5. ALTER TABLE Students ADD PhoneNo VARCHAR2(10); ALTER TABLE Courses ADD CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5);

SQL Queries with Functions and Aggregates

• Names in Uppercase and Email Length
  SELECT UPPER(Name) AS UppercaseName,
  LENGTH(Email) AS EmailLength
  FROM Students;

• Average Credits and Total Enrollments
  SELECT
  (SELECT AVG(Credits) FROM Courses) AS AvgCredits,
  (SELECT COUNT(DISTINCT StudentID) FROM Enrollments) AS TotalEnrolled
  FROM dual;

JOIN QUERIES
List each student with the courses they are enrolled in and their grades:
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;

GROUP BY with HAVING
Count students in each department, only showing departments with more than 2 students:
SELECT Dept, COUNT() AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT() > 2;

VIEWS
To simplify student-course-grade lookups, I created a view:
CREATE OR REPLACE VIEW StudentCoursesView AS
SELECT s.Name AS StudentName, c.CourseName, e.Grade
FROM Students s
JOIN Enrollments e ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

STORED PROCEDURE
A stored procedure to update grades in the Enrollments table:
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;

NUTSHELL
Through this project, I practiced:

Defining schemas and relationships between tables

Enforcing data integrity with constraints

Writing aggregate queries and joins

Using views to simplify queries

Implementing stored procedures for automation

This exercise on Oracle LiveSQL helped me strengthen my SQL foundation and understand how relational database systems are designed and queried in real-world scenarios 🚀