Forem: Kamwemba Tinashe C

MongoDB

Kamwemba Tinashe C — Wed, 08 Oct 2025 17:31:21 +0000

Schema (Collection: students)

[
  {
    "student_id": "S001",
    "name": "Santhosh",
    "age": 20,
    "department": "CSBS",
    "cgpa": 9.0,
    "year": 2
  },
  {
    "student_id": "S002",
    "name": "Ravi",
    "age": 21,
    "department": "CS",
    "cgpa": 8.5,
    "year": 3
  },
  {
    "student_id": "S003",
    "name": "Priya",
    "age": 19,
    "department": "ECE",
    "cgpa": 7.8,
    "year": 1
  },
  {
    "student_id": "S004",
    "name": "Kiran",
    "age": 22,
    "department": "CS",
    "cgpa": 6.9,
    "year": 4
  },
  {
    "student_id": "S005",
    "name": "Anita",
    "age": 20,
    "department": "MECH",
    "cgpa": 7.2,
    "year": 2
  }
]

** Read (Query)**

All Students with CGPA > 8

{ cgpa: { $gt: 8 } }

Students Belonging to the Computer Science Department:

{ department: "CS" }

Update

Update the CGPA of a specific student.
Increase the year of study for all 3rd year students by 1

/ Task 3️ — Update
const { MongoClient } = require("mongodb");

// connection string
const uri = "mongodb+srv://William:4dwQ454VgRgDtNSO@assignment.jfmqse7.mongodb.net/?retryWrites=true&w=majority&appName=Assignment";

async function run() {
    const client = new MongoClient(uri);

    try {
        await client.connect();
        console.log("Connected to MongoDB Atlas");

        const db = client.db("Assign6");
        const students = db.collection("students");

        // 1️ Update CGPA of a specific student (e.g., student_id = S002)
        const updateCGPAResult = await students.updateOne(
            { student_id: "S002" }, // filter
            { $set: { cgpa: 9.0 } } // new CGPA
        );
        console.log(`Updated CGPA for S002 — modifiedCount: ${updateCGPAResult.modifiedCount}`);

        // 2️ Increase year of all 3rd-year students by 1
        const increaseYearResult = await students.updateMany(
            { year: 3 }, // filter
            { $inc: { year: 1 } } // increment year by 1
        );
        console.log(`Increased year for ${increaseYearResult.modifiedCount} student(s)`);

        // Display updated collection
        const updatedStudents = await students.find().toArray();
        console.log("\nUpdated Students Collection:");
        console.table(updatedStudents.map(s => ({
            student_id: s.student_id,
            name: s.name,
            year: s.year,
            cgpa: s.cgpa
        })));

    } catch (err) {
        console.error("Error:", err);
    } finally {
        await client.close();
        console.log("Connection closed.");
    }
}

run();

Delete

Delete one student record by student_id.
Delete all students having CGPA < 7.5.

const { MongoClient } = require("mongodb");

const uri = "mongodb+srv://William:4dwQ454VgRgDtNSO@assignment.jfmqse7.mongodb.net/?retryWrites=true&w=majority&appName=Assignment";

async function run() {
  const client = new MongoClient(uri);

  try {
    await client.connect();
    console.log("Connected to MongoDB Atlas");

    const db = client.db("Assign6");
    const students = db.collection("students");

    // Delete one student record by student_id (example: S004)
    const deleteOneResult = await students.deleteOne({ student_id: "S004" });
    console.log("Deleted student S004 — deletedCount:", deleteOneResult.deletedCount);

    // Delete all students having CGPA < 7.5
    const deleteManyResult = await students.deleteMany({ cgpa: { $lt: 7.5 } });
    console.log("Deleted students with CGPA < 7.5 — deletedCount:", deleteManyResult.deletedCount);

    // Display remaining collection
    const remainingStudents = await students.find().toArray();
    console.log("Remaining Students Collection:");
    console.table(remainingStudents.map(s => ({
      student_id: s.student_id,
      name: s.name,
      year: s.year,
      cgpa: s.cgpa
    })));

  } catch (err) {
    console.error("Error:", err);
  } finally {
    await client.close();
    console.log("Connection closed.");
  }
}

run();

Indexing, Hashing & Query Optimization

Kamwemba Tinashe C — Tue, 07 Oct 2025 17:45:41 +0000

Setup the Schema and Data

Create the Table:

CREATE TABLE Students (
    roll_no NUMBER PRIMARY KEY,
    name VARCHAR2(50),
    dept VARCHAR2(50),
    cgpa NUMBER
);

Insert at Least 20 Sample Records:

INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (101, 'Alice', 'CSBS', 8.5);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (102, 'Bob', 'ECE', 7.8);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (103, 'Charlie', 'CSBS', 9.0);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (104, 'David', 'MECH', 6.5);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (105, 'Eve', 'CSBS', 8.2);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (106, 'Frank', 'ECE', 7.9);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (107, 'Grace', 'CSBS', 8.7);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (108, 'Hank', 'MECH', 6.8);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (109, 'Ivy', 'CSBS', 9.1);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (110, 'Jack', 'ECE', 7.6);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (111, 'Kate', 'CSBS', 8.4);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (112, 'Leo', 'MECH', 6.9);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (113, 'Mia', 'CSBS', 8.9);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (114, 'Noah', 'ECE', 7.7);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (115, 'Olivia', 'CSBS', 8.3);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (116, 'Peter', 'MECH', 6.6);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (117, 'Quinn', 'CSBS', 9.2);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (118, 'Rose', 'ECE', 7.5);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (119, 'Sam', 'CSBS', 8.6);
INSERT INTO Students (roll_no, name, dept, cgpa) VALUES (120, 'Tom', 'MECH', 6.7);
COMMIT;

Verify Data

SELECT * FROM Students;

Task 1: Create a B-Tree Index on the roll_no Column

CREATE INDEX idx_roll_no ON Students(roll_no);

Task 2: Execute a Query to Fetch Details of a Student with roll_no = 110

SELECT * FROM Students WHERE roll_no = 110;

Task 3: Create a B+ Tree Index on the cgpa Column

Oracle uses B-Tree indexes by default, and B+ Tree is a variant not explicitly created but functionally similar. Create a standard B-Tree index:

CREATE INDEX idx_cgpa ON Students(cgpa);

Task 4: Write a Query to Display All Students with cgpa > 8.0

SELECT * FROM Students WHERE cgpa > 8.0 ORDER BY cgpa;

Task 5: Create a Hash Index on the dept Column

Oracle doesn’t support direct hash indexes, but you can simulate this with a function-based index using a hash function (e.g., STANDARD_HASH):

CREATE INDEX idx_dept_hash ON Students(STANDARD_HASH(dept));

Task 6: Run a Query to Retrieve All Students from the 'CSBS' Department

SELECT * FROM Students WHERE dept = 'CSBS' ORDER BY roll_no;

Transactions, Deadlocks & Log Based Recovery

Kamwemba Tinashe C — Tue, 07 Oct 2025 16:42:41 +0000

1. Create the Table

CREATE TABLE Accounts (
    acc_no INT PRIMARY KEY,
    name VARCHAR(50),
    balance INT
);

2. Insert Initial Data

INSERT INTO Accounts (acc_no, name, balance) VALUES
(1, 'Alice', 1000),
(2, 'Bob', 1500),
(3, 'Charlie', 2000);

Transaction

Start the Transaction (implicitly starts with the first DML):

No explicit START TRANSACTION needed; the transaction begins with the first UPDATE.

Debit from Alice

UPDATE Accounts SET balance = balance - 500 WHERE name = 'Alice';

credit to Bob

UPDATE Accounts SET balance = balance + 500 WHERE name = 'Bob';

Rollback the Transaction:

ROLLBACK;

Check Balances to Confirm No Changes

SELECT * FROM Accounts;

OUTPUT

Deadlock Simulation

Oracle supports row-level locking, which we can use to simulate a deadlock. Open two SQL*Plus sessions. Session 1 (Window 1):

Lock Alice's account:

UPDATE Accounts SET balance = balance WHERE name = 'Alice';
-- Don't commit yet; leave it locked

Try to update Bob's account (run this after starting Session 2):

UPDATE Accounts SET balance = balance WHERE name = 'Bob';

Session 2 (Window 2):

Lock Bob's account:

UPDATE Accounts SET balance = balance WHERE name = 'Bob';
-- Don't commit yet; leave it locked

Try to update Alice's account:

UPDATE Accounts SET balance = balance WHERE name = 'Alice';

At this point, Session 1 waits for Session 2's lock on Bob, and Session 2 waits for Session 1's lock on Alice, causing a deadlock. Oracle will detect this and roll back one transaction, raising an error like:

Log-Based Recovery

Step 1: Enable Logging

Logging is enabled by default in Oracle via redo logs and undo tablespaces. You don’t need to enable it manually unless it’s been disabled (unlikely in a standard setup). To confirm an undo tablespace exists (without DBA_TABLESPACES), try:

SELECT tablespace_name FROM USER_TABLESPACES WHERE tablespace_name LIKE '%UNDO%';

Step 2: Start a Transaction and Update a Record

UPDATE Accounts SET balance = balance + 100 WHERE name = 'Charlie';
-- Transaction starts implicitly

Step 3: Rollback the Transaction

ROLLBACK;

Step 4: Check the Log to Confirm Undo Operation

SELECT * FROM Accounts WHERE name = 'Charlie';

Cursor + Trigger

Kamwemba Tinashe C — Tue, 07 Oct 2025 07:05:37 +0000

Step 1: Create the Required Tables
To begin, we need to set up the database tables involved in the cursor and trigger tasks. This includes:

Employee table: For storing employee details like name and salary (used for the cursor).
Students table: For storing student details (used for the trigger).
Student_Audit table: For logging audit entries when a new student is added.

CREATE TABLE Employee (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    salary NUMBER
);

CREATE TABLE Students (
    student_id NUMBER PRIMARY KEY,
    name VARCHAR2(100)
);

CREATE TABLE Student_Audit (
    student_id NUMBER,
    registration_date DATE,
    action VARCHAR2(10)
);

Step 2: Insert Sample Data into the Employee Table
Next, populate the Employee table with sample data to test the cursor. I added four employees with salaries above and below 50,000 for variety.

INSERT INTO Employee (id, name, salary) VALUES (1, 'John Doe', 60000);
INSERT INTO Employee (id, name, salary) VALUES (2, 'Jane Smith', 40000);
INSERT INTO Employee (id, name, salary) VALUES (3, 'Alice Johnson', 55000);
INSERT INTO Employee (id, name, salary) VALUES (4, 'Bob Brown', 45000);
COMMIT;

Step 3: Implement and Execute the Cursor
The cursor task is to display employee names where salary > 50,000 from the Employee table.

DECLARE
    CURSOR emp_cursor IS
        SELECT name FROM Employee WHERE salary > 50000;
    emp_name VARCHAR2(100);
BEGIN
    OPEN emp_cursor;
    LOOP
        FETCH emp_cursor INTO emp_name;
        EXIT WHEN emp_cursor%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(emp_name);
    END LOOP;
    CLOSE emp_cursor;
END;

Step 4: Create the AFTER INSERT Trigger
Now, create the trigger on the Students table to automatically log insertions into Student_Audit.

CREATE OR REPLACE TRIGGER student_audit_trigger
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
    INSERT INTO Student_Audit (student_id, registration_date, action)
    VALUES (:NEW.student_id, SYSDATE, 'INSERT');
END;

**
Output:**

Step 5: Insert a New Student to Test the Trigger
Insert a sample student to activate the trigger.

INSERT INTO Students (student_id, name) VALUES (1, 'New Student');
COMMIT;

Output:
text

Step 6: Verify the Audit Log
Finally, query the Student_Audit table to see the trigger's effect.

SELECT * FROM Student_Audit;

Normalization

Kamwemba Tinashe C — Tue, 07 Oct 2025 06:29:25 +0000

Anomalies in the Base Table

Insertion Anomaly: Cannot add a new course or instructor without a student enrolled.
Update Anomaly: Changing an instructor's phone (e.g., Dr. Kumar's) requires updating multiple rows.
Deletion Anomaly: Deleting a student's enrollment might lose course or instructor data if it's the only record for that course.

SQL CREATE TABLE Statement for 1NF

CREATE TABLE Enrollment (
    StudentID VARCHAR(10),
    StudentName VARCHAR(50),
    CourseID VARCHAR(10),
    CourseName VARCHAR(50),
    Instructor VARCHAR(50),
    InstructorPhone VARCHAR(15),
    PRIMARY KEY (StudentID, CourseID)
);

Sample INSERT Statements for 1NF

INSERT INTO Enrollment (StudentID, StudentName, CourseID, CourseName, Instructor, InstructorPhone) 
VALUES ('s01', 'Arjun', 'c01', 'DBMS', 'Dr. Kumar', '9876543210');

INSERT INTO Enrollment (StudentID, StudentName, CourseID, CourseName, Instructor, InstructorPhone) 
VALUES ('s01', 'Arjun', 'c02', 'Data Mining', 'Dr. Mehta', '9172467800');

INSERT INTO Enrollment (StudentID, StudentName, CourseID, CourseName, Instructor, InstructorPhone) 
VALUES ('s02', 'Priya', 'c01', 'DBMS', 'Dr. Kumar', '9876543210');

INSERT INTO Enrollment (StudentID, StudentName, CourseID, CourseName, Instructor, InstructorPhone) 
VALUES ('s03', 'Kiran', 'c03', 'AI', 'Dr. Rao', '9988776655');

2NF Normalization

CREATE TABLE Students (
    StudentID VARCHAR(10) PRIMARY KEY,
    StudentName VARCHAR(50)
);

CREATE TABLE Courses (
    CourseID VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(50),
    Instructor VARCHAR(50),
    InstructorPhone VARCHAR(15)
);

CREATE TABLE Enrollment (
    StudentID VARCHAR(10),
    CourseID VARCHAR(10),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

3NF Normalization

CREATE TABLE Students (
    StudentID VARCHAR(10) PRIMARY KEY,
    StudentName VARCHAR(50)
);

CREATE TABLE Courses (
    CourseID VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(50),
    Instructor VARCHAR(50),
    FOREIGN KEY (Instructor) REFERENCES Instructors(Instructor)
);

CREATE TABLE Instructors (
    Instructor VARCHAR(50) PRIMARY KEY,
    InstructorPhone VARCHAR(15)
);

CREATE TABLE Enrollment (
    StudentID VARCHAR(10),
    CourseID VARCHAR(10),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Sample INSERT Statements for 3NF

INSERT INTO Students (StudentID, StudentName) VALUES ('s01', 'Arjun');
INSERT INTO Students (StudentID, StudentName) VALUES ('s02', 'Priya');
INSERT INTO Students (StudentID, StudentName) VALUES ('s03', 'Kiran');

INSERT INTO Instructors (Instructor, InstructorPhone) 
VALUES ('Dr. Kumar', '9876543210');
INSERT INTO Instructors (Instructor, InstructorPhone) 
VALUES ('Dr. Mehta', '9172467800');
INSERT INTO Instructors (Instructor, InstructorPhone) 
VALUES ('Dr. Rao', '9988776655');

INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES ('c01', 'DBMS', 'Dr. Kumar');
INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES ('c02', 'Data Mining', 'Dr. Mehta');
INSERT INTO Courses (CourseID, CourseName, Instructor) VALUES ('c03', 'AI', 'Dr. Rao');

INSERT INTO Enrollment (StudentID, CourseID) VALUES ('s01', 'c01');
INSERT INTO Enrollment (StudentID, CourseID) VALUES ('s01', 'c02');
INSERT INTO Enrollment (StudentID, CourseID) VALUES ('s02', 'c01');
INSERT INTO Enrollment (StudentID, CourseID) VALUES ('s03', 'c03');

SQL query to list all students along with their courses and instructor names using JOINs

SELECT 
    s.StudentID,
    s.StudentName,
    c.CourseID,
    c.CourseName,
    i.Instructor
FROM 
    Students s
INNER JOIN 
    Enrollment e ON s.StudentID = e.StudentID
INNER JOIN 
    Courses c ON e.CourseID = c.CourseID
INNER JOIN 
    Instructors i ON c.Instructor = i.Instructor;

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

Kamwemba Tinashe C — Thu, 21 Aug 2025 19:40:00 +0000

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'), 'alice.smith@university.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(2, 'Bob Johnson', 'Mathematics', TO_DATE('1999-08-22', 'YYYY-MM-DD'), 'bob.johnson@university.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(3, 'Carol Lee', 'Physics', TO_DATE('2001-03-10', 'YYYY-MM-DD'), 'carol.lee@university.com');

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'), 'david.brown@university.com');
INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES
(5, 'Eve White', 'Computer Science', TO_DATE('1999-12-01', 'YYYY-MM-DD'), 'eve.white@university.com');

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! 😄