LiveSQL Assignment - College Student & Course Management System

Working with SQL is one of the most effective ways to understand how real-world data management systems work. For this project, I used Oracle LiveSQL to implement and test queries for a College Student & Course Management System.

Use Case: College Student & Course Management System

-- Student 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 (Many-to-Many relationship)
CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);

1. DDL – Creating the Faculty Table

The first task was to create a new table for faculty members. The table includes a primary key, a unique email constraint, and ensures the FacultyName cannot be null.

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

2. DML – Insert Data

Insert three students into the Students table with different departments.

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES
(1, 'John Smith', 'Computer Science', TO_DATE('2002-03-15', 'YYYY-MM-DD'), '[e-mail]'),
(2, 'Emma Johnson', 'Mathematics', TO_DATE('2001-07-22', 'YYYY-MM-DD'), '[e-mail]'),
(3, 'Michael Chen', 'Physics', TO_DATE('2003-01-10', 'YYYY-MM-DD'), '[e-mail]'),

3. Alter Table

Add a new column PhoneNo to the Students table, ensuring it can store 10-digit numbers.

ALTER TABLE Students
ADD PhoneNo NUMBER(10);

4. Defining Constraints

Modify the Courses table so that Credits cannot be less than 1 or more than 5.

ALTER TABLE Courses
MODIFY (Credits CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5));

5. SELECT with Functions

Display the names of students in uppercase and show the length of their email IDs.

SELECT UPPER(Name) AS StudentName,
LENGTH(Email) AS EmailLength
FROM Students;

6. Aggregate Functions

Find the average credits of all courses and the total number of students enrolled.

SELECT AVG(Credits) AS AvgCredits FROM Courses;

SELECT COUNT(*) AS TotalStudents FROM Students;

7. JOIN Operation

List all students along with the courses they are enrolled in (Student Name, Course Name, Grade).

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;

8. GROUP BY with HAVING

Show each department and the count of students. Display only those departments with more than 2 students.

BEFORE FILTERING
SELECT Dept, COUNT() AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT();

AFTER FILTERING
SELECT Dept, COUNT() AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT() > 2;

9. Views

Create a view called StudentCoursesView that shows: StudentName, CourseName, Grade.

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

10. Stored Procedure

Write a procedure UpdateGrade that updates a student’s grade in the Enrollments table given StudentID, CourseID, and the NewGrade.

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;

Conclusion

This project gave me hands-on experience with SQL concepts such as DDL, DML, constraints, functions, joins, grouping, views, and stored procedures using Oracle LiveSQL. By building a College Student & Course Management System, I learned how different SQL features come together in practical database applications.