College Student & Course Management System: A Hands-On SQL Tutorial 📚💾

Introduction 🚀

Welcome to this exciting journey into building a College Student & Course Management System using SQL on Oracle! 🎓 This tutorial walks you through creating a database to manage students, courses, enrollments, and faculty, showcasing essential database concepts like table creation, data insertion, constraints, queries, joins, views, and stored procedures. Whether you're a beginner or sharpening your SQL skills, this hands-on guide is for you! 😊

We’ll use Oracle SQL (via tools like Oracle LiveSQL) to implement a system that tracks student information, course enrollments, and faculty details. Let’s dive in! 🏊‍♂️

---

Database Schema 🏗️

Our system consists of four tables: Students, Courses, Enrollments, and Faculty. Below is the schema with SQL commands to create them.

Students Table

Stores student details like ID, name, department, date of birth, and email.

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

Courses Table

Holds course information, including course ID, name, and credits.

CREATE TABLE Courses (
    CourseID NUMBER PRIMARY KEY,
    CourseName VARCHAR2(50) NOT NULL,
    Credits NUMBER(2)
);

Enrollments Table

Manages the many-to-many relationship between students and courses, including grades.

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

Faculty Table

Stores faculty information, created as part of our tasks.

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

---

1. Data Insertion 📥

Let’s populate the Students and Courses tables with sample data to work with. We’ll also add some enrollments to simulate student-course relationships.

Inserting Students

Three students with different departments:

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(1, 'Alice Smith', 'Computer Science', TO_DATE('2000-05-15', 'YYYY-MM-DD'), '[e-mail]');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(2, 'Bob Johnson', 'Mathematics', TO_DATE('1999-08-22', 'YYYY-MM-DD'), '[e-mail]');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(3, 'Carol Lee', 'Physics', TO_DATE('2001-03-10', 'YYYY-MM-DD'), '[e-mail]');

Inserting Courses

Three courses with varying credits:

INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (101, 'Databases', 4);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (102, 'Algorithms', 3);
INSERT INTO Courses (CourseID, CourseName, Credits) VALUES (103, 'Physics', 5);

Inserting Enrollments

Linking students to courses with grades:

INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES (1, 1, 101, 'A');
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES (2, 2, 102, 'B+');
INSERT INTO Enrollments (EnrollID, StudentID, CourseID, Grade) VALUES (3, 3, 103, 'A-');

---

2. Table Alterations and Constraints 🔧

Adding a Phone Number Column

Add a PhoneNo column to the Students table to store 10-digit phone numbers.

ALTER TABLE Students ADD PhoneNo CHAR(10);

Restricting Course Credits

Ensure Credits in the Courses table is between 1 and 5.

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

---

3. SQL Queries with Functions 🛠️

Let’s manipulate data using SQL functions.

*Uppercase Names and Email Length
*
Display student names in uppercase and the length of their email addresses.

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

Output:

4. Aggregate Functions 📊

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

SELECT 
    (SELECT AVG(Credits) FROM Courses) AS Average_Credits,
    (SELECT COUNT(DISTINCT StudentID) FROM Enrollments) AS Total_Students_Enrolled
FROM DUAL;

Output:

5. JOIN Queries 🤝

List all students, their enrolled courses, and grades.

SELECT s.Name AS StudentName, c.CourseName, e.Grade
FROM Students s
LEFT JOIN Enrollments e ON s.StudentID = e.StudentID
LEFT JOIN Courses c ON e.CourseID = c.CourseID
ORDER BY s.Name, c.CourseName;

Note: Using LEFT JOIN ensures all students appear, even if they have no enrollments (though all sample students are enrolled).

---

6. GROUP BY and HAVING Clause 📈

Count students per department, showing only departments with more than 2 students.

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

Output: (Empty, as no department has more than 2 students with current data.)

Let’s Add More Students for demonstration:

INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(4, 'David Brown', 'Computer Science', TO_DATE('2000-07-20', 'YYYY-MM-DD'), '[e-mail]');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(5, 'Eve White', 'Computer Science', TO_DATE('1999-12-01', 'YYYY-MM-DD'), '[e-mail]');

7. Views 👀

Create a view to simplify access to student-course-grade data.

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

8. Stored Procedure ⚙️

Create a procedure to update a student’s grade in the Enrollments table.

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;
    IF SQL%ROWCOUNT = 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'No enrollment found for the given StudentID and CourseID');
    END IF;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
/

Summary 🎉

This tutorial covered key SQL concepts in Oracle:
🛠️ Schema Design: Created tables with appropriate constraints.
📥 Data Manipulation: Inserted and modified data.
🔍 Queries: Used functions, aggregates, joins, and grouping.
👀 Views: Simplified data access.
⚙️ Stored Procedures: Automated updates with error handling.

Try running these scripts in Oracle LiveSQL to see the system in action! 🚀 Share your thoughts or questions in the comments below. Happy coding! 😄