College Student & Course Management System

This blog shows how to create a simple College Management System using SQL on Oracle LiveSQL. It covers basic database tasks like:

Creating tables

Adding data

Setting rules with constraints

Writing queries using functions and totals

Linking t****ables with joins

Creating views

Using stored procedures

The system helps manage information about students, courses, enrollments, and faculty members.

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

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

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

Creates a new table Faculty to store faculty details with constraints on primary key, non-null name, and unique email.

DML
INSERT INTO Students VALUES (1, 'Alice Johnson', 'Computer Science', TO_DATE('2002-04-15', 'YYYY-MM-DD'), '[e-mail]');
INSERT INTO Students VALUES (2, 'Bob Smith', 'Mathematics', TO_DATE('2001-11-23', 'YYYY-MM-DD'), '[e-mail]');
INSERT INTO Students VALUES (3, 'Clara Lee', 'Physics', TO_DATE('2003-07-07', 'YYYY-MM-DD'), '[e-mail]');

This command inserts three rows of data into the student table.

Alter Students Table — Add PhoneNo
ALTER TABLE Students ADD PhoneNo VARCHAR2(10);

Adds a new column PhoneNo to Students table to store 10-digit phone numbers.

Add Constraint on Courses Credits
ALTER TABLE Courses
MODIFY Credits CHECK (Credits BETWEEN 1 AND 5);

Enforces that the Credits column in Courses can only have values between 1 and 5, ensuring data integrity.

SELECT with Functions — Names Uppercase & Email Length
SELECT
UPPER(Name) AS StudentName_Upper,
LENGTH(Email) AS Email_Length
FROM Students;

Demonstrates use of string functions by displaying student names in uppercase and counting the length of their email addresses.

Aggregate Functions — Average Credits & Total Enrollments
SELECT
AVG(Credits) AS Average_Credits,
(SELECT COUNT(*) FROM Enrollments) AS Total_Enrollments
FROM Courses;

Calculates the average number of credits for all courses and counts total student enrollments using aggregate functions.

JOIN — List Students with Enrolled Courses & Grades
SELECT
s.Name AS Student_Name,
c.CourseName AS Course_Name,
e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

Shows how to join multiple tables to display which students are enrolled in which courses along with their grades.

GROUP BY with HAVING — Departments with More Than 2 Students
SELECT
Dept,
COUNT() AS Student_Count
FROM Students
GROUP BY Dept
HAVING COUNT() > 2;

Groups students by department and filters to show only departments having more than two students.

Create View — StudentCoursesView
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 e.CourseID = c.CourseID;

Creates a virtual table (view) combining student names, their courses, and grades for easy repeated querying.

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;

END;
/

A stored procedure to update a student’s grade for a specific course, showing basic procedural SQL and parameter usage.

Optional: Run Stored Procedure Example
BEGIN
UpdateGrade(1, 101, 'A+');
END;
/

Summary

Insert data first to create meaningful relationships.

Use the above queries to test key SQL concepts: DDL, DML, Alter, Constraints, SELECT functions, Aggregates, JOINs, GROUP BY, Views, and Procedures.

Run queries on Oracle LiveSQL
, take screenshots of both query and output.