Build a College Database in Oracle LiveSQL – Step-by-Step Guide

🎓 College Database Management System – Oracle LiveSQL

This project demonstrates how to design and query a simple College Database using Oracle SQL on LiveSQL
.
We create tables, insert data, apply constraints, run queries, and even build views & stored procedures.

1. Schema Design

We define four tables:

Faculty – stores teacher details.

Students – student records with department, DOB, email & phone.

Courses – subject details with credits (1–5 only).

Enrollments – junction table mapping students ↔ courses with grades.

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

CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
DOB DATE,
Email VARCHAR2(50) UNIQUE,
PhoneNo NUMBER(10)
);

CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2) CHECK (Credits BETWEEN 1 AND 5)
);

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

✅ Here we use PRIMARY KEY, UNIQUE, and CHECK constraints to ensure data integrity.

1. Inserting Data

We add sample students, courses, and enrollments.

-- Students
INSERT INTO Students VALUES (1, 'Arjun', 'CSE', DATE '2004-05-15', '[e-mail]', 9876543210);
INSERT INTO Students VALUES (2, 'Meera', 'ECE', DATE '2003-11-20', '[e-mail]', 9876543211);
INSERT INTO Students VALUES (3, 'Rahul', 'Mechanical', DATE '2004-02-10', '[e-mail]', 9876543212);

-- Courses
INSERT INTO Courses VALUES (101, 'Database Systems', 4);
INSERT INTO Courses VALUES (102, 'Operating Systems', 3);
INSERT INTO Courses VALUES (103, 'Networks', 2);

-- Enrollments
INSERT INTO Enrollments VALUES (1, 1, 101, 'A');
INSERT INTO Enrollments VALUES (2, 2, 102, 'B');
INSERT INTO Enrollments VALUES (3, 3, 103, 'A');
INSERT INTO Enrollments VALUES (4, 1, 102, 'C');

1. Queries

Now, let’s explore the data.

a) String Functions & Aggregates
SELECT UPPER(Name) AS StudentNameUpper, LENGTH(Email) AS EmailLength
FROM Students;

👉 Converts names to uppercase & shows email length.

SELECT AVG(Credits) AS AvgCredits, (SELECT COUNT(*) FROM Students) AS TotalStudents
FROM Courses;

👉 Finds average course credits & total number of students.

b) Joins
SELECT s.Name, c.CourseName, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

👉 Displays each student with their enrolled course and grade.

c) Group By + Having
SELECT Dept, COUNT() AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT() > 0;

👉 Shows number of students per department (only departments with students).

1. Views CREATE OR REPLACE VIEW StudentCoursesView AS SELECT s.Name, c.CourseName, e.Grade FROM Students s JOIN Enrollments e ON s.StudentID = e.StudentID JOIN Courses c ON e.CourseID = c.CourseID;

👉 A view gives a reusable query showing student ↔ course ↔ grade.

1. Stored Procedure 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;

👉 Procedure to update a student’s grade in a course easily.

✅ Conclusion

This LiveSQL use case covers:
✔️ Table creation with constraints
✔️ Data insertion
✔️ String, aggregate & join queries
✔️ Group By with Having
✔️ Views for easy access
✔️ Stored procedure for updates

A neat mini College Database Management System! 🚀