DEV Community: GWEDE WILLIAM RUKUDZO 24CB065

MongoDB - Blog - dev.to + LinkedIn Post

GWEDE WILLIAM RUKUDZO 24CB065 — Wed, 08 Oct 2025 11:29:57 +0000

Objective

To gain hands-on experience in performing CRUD (Create, Read, Update, Delete) operations in MongoDB using a simple college student schema.

Create (Insert)

Insert at least 5 student records into the students collection.

// Task — Create (Insert)
const { MongoClient } = require("mongodb");

//  Atlas 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");

        // 5 student records
        const studentData = [
            { student_id: "S001", name: "John", age: 20, department: "CSBS", year: 2, cgpa: 9.0 },
            { student_id: "S002", name: "Emma", age: 21, department: "ECE", year: 3, cgpa: 8.7 },
            { student_id: "S003", name: "Liam", age: 19, department: "MECH", year: 1, cgpa: 8.3 },
            { student_id: "S004", name: "Olivia", age: 22, department: "CSBS", year: 4, cgpa: 9.2 },
            { student_id: "S005", name: "Noah", age: 20, department: "EEE", year: 2, cgpa: 7.8 }
        ];

        const result = await students.insertMany(studentData);
        console.log(`Successfully inserted ${result.insertedCount} student documents!`);

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

run();

Read (Query)

Display all student records.
Find all students with CGPA > 8.
Find students belonging to the Computer Science department.

// Task 2️ Read (Query)
const { MongoClient } = require("mongodb");

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

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

        // Display all student records
        const allStudents = await students.find().toArray();
        console.log("\nAll Students:");
        console.table(allStudents.map(s => ({
            student_id: s.student_id,
            name: s.name,
            age: s.age,
            department: s.department,
            year: s.year,
            cgpa: s.cgpa
        })));

        // Find all students with CGPA > 8
        const highCgpaStudents = await students.find({ cgpa: { $gt: 8 } }).toArray();
        console.log("\nStudents with CGPA > 8:");
        console.table(highCgpaStudents.map(s => ({
            student_id: s.student_id,
            name: s.name,
            cgpa: s.cgpa
        })));

        // Find students belonging to Computer Science department
        const csStudents = await students.find({ department: "CSE" }).toArray();
        console.log("\nComputer Science Department Students:");
        console.table(csStudents.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();

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

GWEDE WILLIAM RUKUDZO 24CB065 — Tue, 07 Oct 2025 06:06:47 +0000

Create a table Students

Create a table Students with fields (roll_no, name, dept, cgpa)

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

Insert at least 20 sample records.

BEGIN
  INSERT INTO Students VALUES (101, 'Aarav', 'CSBS', 8.5);
  INSERT INTO Students VALUES (102, 'Meera', 'ECE', 7.8);
  INSERT INTO Students VALUES (103, 'Ravi', 'MECH', 6.9);
  INSERT INTO Students VALUES (104, 'Lakshmi', 'CSBS', 9.1);
  INSERT INTO Students VALUES (105, 'Kiran', 'EEE', 7.5);
  INSERT INTO Students VALUES (106, 'Divya', 'CIVIL', 8.2);
  INSERT INTO Students VALUES (107, 'Vishal', 'CSBS', 8.9);
  INSERT INTO Students VALUES (108, 'Sneha', 'ECE', 7.2);
  INSERT INTO Students VALUES (109, 'Rahul', 'MECH', 6.8);
  INSERT INTO Students VALUES (110, 'Priya', 'CSBS', 9.3);
  INSERT INTO Students VALUES (111, 'Anjali', 'EEE', 7.4);
  INSERT INTO Students VALUES (112, 'Manoj', 'CIVIL', 8.0);
  INSERT INTO Students VALUES (113, 'Neha', 'CSBS', 8.7);
  INSERT INTO Students VALUES (114, 'Arjun', 'ECE', 7.6);
  INSERT INTO Students VALUES (115, 'Pooja', 'MECH', 6.5);
  INSERT INTO Students VALUES (116, 'Suresh', 'EEE', 7.9);
  INSERT INTO Students VALUES (117, 'Geeta', 'CIVIL', 8.3);
  INSERT INTO Students VALUES (118, 'Tarun', 'CSBS', 9.0);
  INSERT INTO Students VALUES (119, 'Bhavna', 'ECE', 7.1);
  INSERT INTO Students VALUES (120, 'Nikhil', 'MECH', 6.7);
  COMMIT;
END;

B-Tree index

Create a B-Tree index on the roll_no column of the Students table.

Oracle automatically creates a unique B-Tree index on any column declared as PRIMARY KEY

fetch the details

Execute a query to fetch the details of a student with roll_no = 110.

SELECT * FROM Students WHERE roll_no = 110;

B+ Tree index

Create a B+ Tree index on the cgpa column of the Students table.

CREATE INDEX idx_cgpa ON Students(cgpa);

display

Write a query to display all students with cgpa > 8.0.

Hash index

Create a Hash index on the dept column of the Students table.

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

display

Run a query to retrieve all students from the 'CSBS' department.

SELECT * FROM Students WHERE UPPER(dept) = 'CSBS';

Transactions, Deadlocks & Log Based Recovery

GWEDE WILLIAM RUKUDZO 24CB065 — Tue, 07 Oct 2025 06:03:52 +0000

Schema

Use a single table Accounts:

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

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

Transaction – Atomicity & Rollback

Start a transaction to transfer 500 from Alice to Bob.
Rollback the transaction before committing.
Check balances to ensure no partial update happened.

BEGIN
  -- Start transaction
  UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1; -- Alice
  UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2; -- Bob

  -- Rollback before committing
  ROLLBACK;

  -- Check balances
  DECLARE
    v_alice INT;
    v_bob INT;
  BEGIN
    SELECT balance INTO v_alice FROM Accounts WHERE acc_no = 1;
    SELECT balance INTO v_bob FROM Accounts WHERE acc_no = 2;

    DBMS_OUTPUT.PUT_LINE('Alice balance: ' || v_alice);
    DBMS_OUTPUT.PUT_LINE('Bob balance: ' || v_bob);
  END;
END;

Deadlock Simulation (using two browser tabs in Live SQL)

Open two sessions.
Session 1: Lock Alice’s account and try to update Bob’s.
Session 2: Lock Bob’s account and try to update Alice’s.
Observe deadlock.

-- SESSION 1
-- Lock Alice
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 1;

-- updating Bob (will wait if Bob is locked)
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 2;

-- SESSION 2
-- Lock Bob
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 2;

-- updating Alice (will wait if Alice is locked)
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 1;

-- Checking Output
DECLARE
  v_alice INT;
  v_bob INT;
BEGIN
  SELECT balance INTO v_alice FROM Accounts WHERE acc_no = 1;
  SELECT balance INTO v_bob FROM Accounts WHERE acc_no = 2;

  DBMS_OUTPUT.PUT_LINE('Alice balance: ' || v_alice);
  DBMS_OUTPUT.PUT_LINE('Bob balance: ' || v_bob);
END;

Log-Based Recovery

Enable logging (if using MySQL/PostgreSQL, binary log/WAL is default).
Start a transaction to update a record and rollback.
Check the log to confirm undo operation was recorded.

START TRANSACTION;
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 3;
ROLLBACK;

ACID property with SQL transactions

GWEDE WILLIAM RUKUDZO 24CB065 — Tue, 07 Oct 2025 06:00:22 +0000

Create a table Accounts(acc_no INT PRIMARY KEY, name VARCHAR(50), balance INT). Insert 3 sample rows.

-- Creating Account table
CREATE TABLE Accounts (
  acc_no   INT PRIMARY KEY,
  name     VARCHAR2(50),
  balance  INT CHECK (balance >= 0)  -- Enforces Consistency
);

-- Inserting values
BEGIN
  INSERT INTO Accounts VALUES (101, 'Aarav', 50000);
  INSERT INTO Accounts VALUES (102, 'Meera', 60000);
  INSERT INTO Accounts VALUES (103, 'Ravi', 70000);
  COMMIT;
END;

SELECT * FROM Accounts;

Atomicity: Start a transaction that transfers money. Rollback midway → ensure no partial update remains.

BEGIN
  -- Atomic transaction: transfer money
  UPDATE Accounts SET balance = balance - 10000 WHERE acc_no = 101;
  UPDATE Accounts SET balance = balance + 10000 WHERE acc_no = 102;

  -- Show updated balances
  DECLARE
    v_bal_101 INT;
    v_bal_102 INT;
  BEGIN
    SELECT balance INTO v_bal_101 FROM Accounts WHERE acc_no = 101;
    SELECT balance INTO v_bal_102 FROM Accounts WHERE acc_no = 102;

    DBMS_OUTPUT.PUT_LINE('Account 101 balance: ' || v_bal_101);
    DBMS_OUTPUT.PUT_LINE('Account 102 balance: ' || v_bal_102);
  END;

  -- Rollback to test atomicity
  ROLLBACK;
END;

Consistency: Try inserting a record with negative balance → should be rejected.

BEGIN
  -- Try inserting invalid data
  INSERT INTO Accounts VALUES (104, 'Lakshmi', -5000);  -- Violates CHECK constraint
  COMMIT;
END;

Isolation: Run two sessions at once – one updating, the other reading → observe isolation.

-- Session A (Updater)
BEGIN
  UPDATE Accounts SET balance = balance + 5000 WHERE acc_no = 103;
  -- Do NOT commit yet
END;

-- Session B (Reader)
SELECT * FROM Accounts WHERE acc_no = 103;

Durability: Commit a transaction → restart DB → ensure data persists.

-- Commit and Restart
BEGIN
  UPDATE Accounts SET balance = balance + 10000 WHERE acc_no = 102;
  COMMIT;
END;

-- After DB restart
SELECT * FROM Accounts WHERE acc_no = 102;

Cursor + Trigger Assignment

GWEDE WILLIAM RUKUDZO 24CB065 — Tue, 07 Oct 2025 05:58:57 +0000

Cursor with Condition

Quesn: Create a cursor that displays employee names whose salary is greater than 50,000 from the Employee table.

-- Creating the table
CREATE TABLE Employee (
  emp_id     NUMBER PRIMARY KEY,
  emp_name   VARCHAR2(100),
  salary     NUMBER
);

-- Initialising the table
BEGIN
  INSERT INTO Employee VALUES (1, 'Alice', 45000);
  INSERT INTO Employee VALUES (2, 'Bob', 52000);
  INSERT INTO Employee VALUES (3, 'Charlie', 60000);
  INSERT INTO Employee VALUES (4, 'Diana', 48000);
  INSERT INTO Employee VALUES (5, 'Ethan', 75000);
  COMMIT;
END;

SELECT * FROM Employee;

-- Creating the cursor and giving output
DECLARE
  CURSOR high_salary_cursor IS
    SELECT emp_name FROM Employee WHERE salary > 50000;

  v_name Employee.emp_name%TYPE;
BEGIN
  FOR emp_rec IN high_salary_cursor LOOP
    DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_rec.emp_name);
  END LOOP;
END;

AFTER INSERT Trigger (Student Table)

Quesn: Whenever a new student is added to the Students table, we want to automatically insert a log entry into the Student_Audit table to keep track of the registration.

-- Creating table Students
CREATE TABLE Students (
  student_id   NUMBER PRIMARY KEY,
  student_name VARCHAR2(100),
  course       VARCHAR2(50)
);

-- Creating  Audit table for tracking registrations
CREATE TABLE Student_Audit (
  audit_id     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  student_id   NUMBER,
  student_name VARCHAR2(100),
  registration_date DATE
);

-- Inserting sample data into students table
BEGIN
  INSERT INTO Students (student_id, student_name, course) VALUES (1, 'Aarav', 'Physics');
  INSERT INTO Students (student_id, student_name, course) VALUES (2, 'Meera', 'Chemistry');
  INSERT INTO Students (student_id, student_name, course) VALUES (3, 'Ravi', 'Mathematics');
  COMMIT;
END;

SELECT * FROM Students;

-- Creating the AFTER INSERT Trigger
CREATE OR REPLACE TRIGGER trg_student_registration
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
  INSERT INTO Student_Audit (student_id, student_name, registration_date)
  VALUES (:NEW.student_id, :NEW.student_name, SYSDATE);
END;

-- Testing the trigger
BEGIN
  INSERT INTO Students (student_id, student_name, course) VALUES (4, 'Lakshmi', 'Biology');
  COMMIT;
END;

-- Viewing the Audit log
SELECT * FROM Student_Audit;

Normalisation Assignment

GWEDE WILLIAM RUKUDZO 24CB065 — Tue, 07 Oct 2025 05:50:46 +0000

BASE TABLE

1NF

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

INSERT INTO StudentCourse_1NF (StudentID, StudentName, CourseID, CourseName, Instructor, InstructorPhone) VALUES
('S01', 'Arjun', 'C101', 'DBMS', 'Dr. Kumar', '9876543210'),
('S01', 'Arjun', 'C102', 'Data Mining', 'Dr. Mehta', '9123456780'),
('S02', 'Priya', 'C101', 'DBMS', 'Dr. Kumar', '9876543210'),
('S03', 'Kiran', 'C103', 'AI', 'Dr. Rao', '9988776655');

SELECT * FROM StudentCourse_1NF;

2NF

--Student Table
CREATE TABLE Student (
    StudentID VARCHAR(10) PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL
);

INSERT INTO Student VALUES
('S01', 'Arjun'),
('S02', 'Priya'),
('S03', 'Kiran');

-- Course Table
CREATE TABLE Course (
    CourseID VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(50) NOT NULL,
    Instructor VARCHAR(50),
    InstructorPhone VARCHAR(15)
);

INSERT INTO Course VALUES
('C101', 'DBMS', 'Dr. Kumar', '9876543210'),
('C102', 'Data Mining', 'Dr. Mehta', '9123456780'),
('C103', 'AI', 'Dr. Rao', '9988776655');

-- Enrollment Table (Junction Table)
CREATE TABLE Enrollment (
    StudentID VARCHAR(10),
    CourseID VARCHAR(10),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

INSERT INTO Enrollment VALUES
('S01', 'C101'),
('S01', 'C102'),
('S02', 'C101'),
('S03', 'C103');

3NF

-- Student Table
CREATE TABLE Student (
    StudentID VARCHAR(10) PRIMARY KEY,
    StudentName VARCHAR(50) NOT NULL
);

-- Students
INSERT INTO Student VALUES
('S01', 'Arjun'),
('S02', 'Priya'),
('S03', 'Kiran');

-- Instructor Table
CREATE TABLE Instructor (
    InstructorID VARCHAR(10) PRIMARY KEY,
    InstructorName VARCHAR(50) NOT NULL,
    InstructorPhone VARCHAR(15)
);

-- Instructors
INSERT INTO Instructor VALUES
('I01', 'Dr. Kumar', '9876543210'),
('I02', 'Dr. Mehta', '9123456780'),
('I03', 'Dr. Rao', '9988776655');

-- Course Table
CREATE TABLE Course (
    CourseID VARCHAR(10) PRIMARY KEY,
    CourseName VARCHAR(50) NOT NULL,
    InstructorID VARCHAR(10),
    FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);

-- Courses
INSERT INTO Course VALUES
('C101', 'DBMS', 'I01'),
('C102', 'Data Mining', 'I02'),
('C103', 'AI', 'I03');

-- Enrollment Table (Junction Table)
CREATE TABLE Enrollment (
    StudentID VARCHAR(10),
    CourseID VARCHAR(10),
    PRIMARY KEY (StudentID, CourseID),
    FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);


-- Enrollments
INSERT INTO Enrollment VALUES
('S01', 'C101'),
('S01', 'C102'),
('S02', 'C101'),
('S03', 'C103');

USING JOIN

SELECT 
    s.StudentName,
    c.CourseName,
    i.InstructorName
FROM Enrollment e
JOIN Student s ON e.StudentID = s.StudentID
JOIN Course c ON e.CourseID = c.CourseID
JOIN Instructor i ON c.InstructorID = i.InstructorID
ORDER BY s.StudentName;

LiveSQL Assignment - College Student & Course Management System

GWEDE WILLIAM RUKUDZO 24CB065 — Thu, 21 Aug 2025 19:26:15 +0000

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'), 'john.smith@university.edu'),
(2, 'Emma Johnson', 'Mathematics', TO_DATE('2001-07-22', 'YYYY-MM-DD'), 'emma.johnson@university.edu'),
(3, 'Michael Chen', 'Physics', TO_DATE('2003-01-10', 'YYYY-MM-DD'), 'michael.chen@university.edu'),

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.