Overview
In this article, I’ll share how I built a College Database Management System using Oracle LiveSQL.
The goal was to design a system that manages students, faculty, and courses while applying SQL concepts like constraints, joins, and views.
Key Highlights
🔹 Creating tables with proper constraints
🔹 Inserting records into tables
🔹 Altering table structures
🔹 Running queries with string & aggregate functions
🔹 Performing joins
🔹 Defining views for simplified access
1. Defining Tables
I began with three main entities: Students, Faculty, and Courses.
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
DOB DATE,
Email VARCHAR2(50) UNIQUE
);
CREATE TABLE Faculty (
FacultyID NUMBER PRIMARY KEY,
FacultyName VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
Email VARCHAR2(50) UNIQUE
);
CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2) CHECK (Credits BETWEEN 1 AND 5)
);
2. Populating the Tables
Sample student records:
INSERT INTO Students VALUES (1, 'Aarav Kumar', 'CSE', TO_DATE('2004-05-12','YYYY-MM-DD'), 'aarav.cs@example.com');
INSERT INTO Students VALUES (2, 'Meera Reddy', 'EEE', TO_DATE('2003-09-23','YYYY-MM-DD'), 'meera.ee@example.com');
INSERT INTO Students VALUES (3, 'Vikram Sharma', 'MECH', TO_DATE('2004-01-30','YYYY-MM-DD'), 'vikram.me@example.com');
3. Schema Modification
Later, I added a column for phone numbers to the Students table:
ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);
4. Working with Functions
Query to display names in uppercase and calculate the length of email IDs:
SELECT UPPER(Name) AS Student_Name_Upper,
LENGTH(Email) AS Email_Length
FROM Students;
5. Connecting Students and Courses
An Enrollments table was introduced to keep track of which student took which course, along with their grade.
CREATE TABLE Enrollments (
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade VARCHAR2(2)
);
To simplify, 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 s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;
6. Aggregate Queries
Example: average credits of all courses and student count:
SELECT AVG(Credits) AS AvgCredits,
COUNT(DISTINCT StudentID) AS TotalStudents
FROM Enrollments e
JOIN Courses c ON e.CourseID = c.CourseID;
Final Thoughts
This project helped me:
- Practice constraints and alterations
- Work with joins, views, and aggregates
- Get comfortable with Oracle LiveSQL environment
It’s a compact project idea for students who want to sharpen their SQL basics while building something meaningful. 🚀
Top comments (0)