College Student & Course Management System

Introduction
This blog covers the implementation of a simple College Student & Course Management System using SQL on Oracle LiveSQL. It demonstrates key database concepts such as table creation, data insertion, constraint addition, queries with functions and aggregates, joins, views, and stored procedures.

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

Database Schema

The database contains four main tables: Students, Courses, Enrollments, and Faculty.

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

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
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Daniel Green', 'Information Technology', TO_DATE('2002-02-10', 'YYYY-MM-DD'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Emma White', 'Electronics', TO_DATE('2001-08-05', 'YYYY-MM-DD'), '[e-mail]');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Frank Lee', 'Mechanical', TO_DATE('2003-01-19', 'YYYY-MM-DD'), '[e-mail]');

INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (201, 'Operating Systems', 4);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (202, 'Digital Circuits', 3);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (203, 'Thermodynamics', 5);

INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES (1, 1, 201, 'A');
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES (2, 2, 202, 'B');
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES (3, 3, 203, 'A-');

Table Alterations and Constraints

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
Example 1: Listing student names in uppercase and length of their emails

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

UppercaseName	EmailLength
DANIEL GREEN	24
EMMA WHITE	22
FRANK LEE	21

Example 2: Calculating average course credits and counting enrolled students

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

AvgCredits	TotalStudentsEnrolled
4.0	3

JOIN Queries

Joining Students, Enrollments, and Courses to show which student is enrolled in which course along with 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;

StudentName	CourseName	Grade
Daniel Green	Operating Systems	A
Emma White	Digital Circuits	B
Frank Lee	Thermodynamics	A-

GROUP BY and HAVING Clause

Counting students in each department and filtering departments with more than 1 student:

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

Dept	StudentCount
(No dept with >1 student in current data)

Views

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

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

This assignment reinforced understanding of:

Creating and managing SQL database schemas

Writing data manipulation queries

Using SQL functions and aggregate operations

Performing joins to combine related data

Creating views and stored procedures to enhance SQL capabilities

You can try the full script on Oracle LiveSQL to see these operations in action.