This content originally appeared on DEV Community and was authored by krithikraja
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
);
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
Sample data was inserted into these tables to represent students, courses, and their enrollments:
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, ‘Alice Johnson’, ‘Computer Science’, TO_DATE(‘2002-04-15’, ‘YYYY-MM-DD’), ‘alice.johnson@example.com‘);
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, ‘Bob Smith’, ‘Mathematics’, TO_DATE(‘2001-11-23’, ‘YYYY-MM-DD’), ‘bob.smith@example.com‘);
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, ‘Cathy Brown’, ‘Physics’, TO_DATE(‘2003-07-02’, ‘YYYY-MM-DD’), ‘cathy.brown@example.com‘);
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);
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 and Constraints
Added a new column PhoneNo to Students and a CHECK constraint on the Credits column of Courses:
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
Listing student names in uppercase and length of their emails:
SELECT UPPER(Name) AS UppercaseName, LENGTH(Email) AS EmailLength FROM Students;
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;
Sample result:
UppercaseName EmailLength
ALICE JOHNSON 25
BOB SMITH 21
CATHY BROWN 23
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;
Sample result:
StudentName CourseName Grade
Alice Johnson Databases A
Bob Smith Algorithms B+
Cathy Brown Physics A-
GROUP BY and HAVING Clause
Counting students in each department and filtering departments with more than 2 students:
SELECT Dept, COUNT() AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT() > 2;
Views
Created a view to simplify student-course-grade lookup:
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 in enrollments:
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
Feel free to try the full script on Oracle LiveSQL to see these operations in action.
This content originally appeared on DEV Community and was authored by krithikraja