College Student & Course Management System (Oracle LiveSQL) sql #oracle #database #tutorial

Introduction

In this blog, I’ll walk you through my hands-on implementation of a College Student & Course Management System using Oracle LiveSQL.
This project helped me practice real-world SQL concepts such as table creation, data insertion, constraints, queries, and views.

What’s Covered?

✅ Table creation
✅ Data insertion
✅ Constraints & table alterations
✅ SQL queries with string functions & aggregates
✅ Joins
✅ Views
✅ Grouping with HAVING

1. Table Creation

I started by creating the core tables – 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. Data Insertion

Next, I inserted some sample data into the tables.

INSERT INTO Students VALUES (1, 'Aarav Kumar', 'Computer Science', TO_DATE('2004-05-12','YYYY-MM-DD'), 'aarav.cs@example.com');
INSERT INTO Students VALUES (2, 'Meera Reddy', 'Electrical', TO_DATE('2003-09-23','YYYY-MM-DD'), 'meera.ee@example.com');
INSERT INTO Students VALUES (3, 'Vikram Sharma', 'Mechanical', TO_DATE('2004-01-30','YYYY-MM-DD'), 'vikram.me@example.com');

3. Altering Tables

Adding a new column PhoneNo for storing 10-digit mobile numbers:

ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

4. Queries with String Functions

Example: Display student names in uppercase and the length of their email IDs.

SELECT UPPER(Name) AS StudentName_Uppercase,
LENGTH(Email) AS Email_Length
FROM Students;

5. Joins & Views

Created an Enrollments table to link students with courses and store grades.

CREATE TABLE Enrollments (
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade VARCHAR2(2)
);

Then created a view to see student-course-grade details:

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;