College Student & Course Management System.

Introduction
This blog demonstrates the implementation of a College Student & Course Management System using SQL on Oracle LiveSQL. It highlights important SQL concepts such as:

• Creating and altering tables
• Inserting records
• Defining constraints (PK, FK, CHECK, UNIQUE)
• Using functions and aggregate queries
• Performing joins
• Creating views
• Implementing stored procedures

The use case focuses on students, faculty, courses, and enrollments in a simplified academic environment.

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

Students Table
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
FirstName VARCHAR2(30) NOT NULL,
LastName VARCHAR2(30) NOT NULL,
Dept VARCHAR2(30),
DOB DATE,
Email VARCHAR2(60) UNIQUE
);

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

Courses Table
CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseTitle VARCHAR2(60) NOT NULL,
Credits NUMBER(2),
FacultyID NUMBER REFERENCES Faculty(FacultyID)
);

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

Data Insertion
INSERT INTO Students VALUES (101, 'Alice', 'Brown', 'Computer Science', TO_DATE('2002-04-15','YYYY-MM-DD'), '[e-mail]');
INSERT INTO Students VALUES (102, 'Brian', 'Taylor', 'Mechanical', TO_DATE('2001-09-20','YYYY-MM-DD'), '[e-mail]');
INSERT INTO Students VALUES (103, 'Chloe', 'Wilson', 'Mathematics', TO_DATE('2003-01-05','YYYY-MM-DD'), '[e-mail]');

INSERT INTO Faculty VALUES (201, 'Dr. Adams', 'Computer Science', '9998887777');
INSERT INTO Faculty VALUES (202, 'Dr. Johnson', 'Mechanical', '9991112222');

INSERT INTO Courses VALUES (301, 'Database Management', 4, 201);
INSERT INTO Courses VALUES (302, 'Thermal Engineering', 3, 202);
INSERT INTO Courses VALUES (303, 'Calculus II', 4, NULL);

INSERT INTO Enrollments VALUES (1, 101, 301, 'Spring', 'A');
INSERT INTO Enrollments VALUES (2, 102, 302, 'Fall', 'B');
INSERT INTO Enrollments VALUES (3, 103, 303, 'Spring', 'A');

Table Alterations and Constraints
ALTER TABLE Students ADD PhoneNo VARCHAR2(12);
ALTER TABLE Courses ADD CONSTRAINT chk_course_credits CHECK (Credits BETWEEN 2 AND 6);

SQL Queries with Functions and Aggregates
Example 1 – Display student full names and email domain
SELECT
FirstName || ' ' || LastName AS FullName,
SUBSTR(Email, INSTR(Email,'@')+1) AS EmailDomain
FROM Students;

Example 2 – Calculate average credits and total number of courses
SELECT
AVG(Credits) AS AvgCredits,
COUNT(CourseID) AS TotalCourses
FROM Courses;

JOIN Queries
Show students, the courses they are enrolled in, and their faculty:
SELECT s.FirstName || ' ' || s.LastName AS StudentName,
c.CourseTitle,
f.FacultyName,
e.Grade
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
LEFT JOIN Faculty f ON c.FacultyID = f.FacultyID;

GROUP BY and HAVING Clause
Count students per department with more than 1 student:
SELECT Dept, COUNT() AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT() > 1;

Views
CREATE OR REPLACE VIEW CourseEnrollmentsView AS
SELECT s.FirstName || ' ' || s.LastName AS StudentName,
c.CourseTitle,
e.Semester,
e.Grade
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

Stored Procedure
CREATE OR REPLACE PROCEDURE AssignFaculty (
p_CourseID IN NUMBER,
p_FacultyID IN NUMBER
) AS
BEGIN
UPDATE Courses
SET FacultyID = p_FacultyID
WHERE CourseID = p_CourseID;
COMMIT;
END;

Summary
This project demonstrated how to:

• Create and manage a relational database schema with Students, Faculty, Courses, and Enrollments
• Insert and manipulate records
• Apply constraints for data integrity
• Use SQL functions, aggregates, and joins
• Create views for simplified queries
• Implement stored procedures for reusability

You can run this script on Oracle LiveSQL to practice and explore these SQL features.