Santhosh_M

Posted on Aug 21

College Management Course

🎓 College Database using Oracle LiveSQL

This project shows how to design and query a simple College Management System using Oracle LiveSQL. We’ll create tables, insert data, and run queries with joins, aggregates, views, and procedures.

🏗 Schema Design

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

👉 Used PRIMARY KEY, UNIQUE, and CHECK to maintain integrity.

📥 Insert Data

-- Students
INSERT INTO Students VALUES (1, 'Arjun', 'CSE', DATE '2004-05-15', 'arjun@college.com', 9876543210);
INSERT INTO Students VALUES (2, 'Meera', 'ECE', DATE '2003-11-20', 'meera@college.com', 9876543211);

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

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

👉 Added students, courses, and their enrollments.

🔍 Queries

1. 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, COUNT(*) AS TotalCourses
FROM Courses;

✔ Finds average credits & total courses.

2. 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;

✔ Shows each student’s course and grade.

3. Group By + Having

SELECT Dept, COUNT(*) AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT(*) > 0;

✔ Counts students per department.

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

✔ Reusable view for student-course-grade mapping.

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

✔ Easy way to update grades with parameters.

✅ Conclusion

This mini project demonstrates:

Table creation with constraints
Inserting sample data
Running string, aggregate, and join queries
Using GROUP BY and HAVING
Creating Views
Writing a Stored Procedure

A neat College Database built entirely on **Oracle

DEV Community