DEV Community: Prabanjan B

Hands-On MongoDB CRUD Operations with a College Student Sche

Prabanjan B — Sun, 05 Oct 2025 04:34:04 +0000

MongoDB is a powerful NoSQL database that allows flexible storage of JSON-like documents. In this blog, we’ll explore CRUD operations — Create, Read, Update, and Delete — using a simple college students collection.

Step 1: Create (Insert)

We start by inserting student records into the students collection. Each document follows this structure:

{
"student_id": "S001",
"name": "Soniya",
"age": 20,
"department": "CSBS",
"year": 2,
"cgpa": 9
}

Insert five students:

db.students.insertMany([
{ "student_id": "S001", "name": "Soniya", "age": 20, "department": "CSBS", "year": 2, "cgpa": 9 },
{ "student_id": "S002", "name": "Isha", "age": 21, "department": "CSE", "year": 3, "cgpa": 8.5 },
{ "student_id": "S003", "name": "Sashmi", "age": 22, "department": "ECE", "year": 4, "cgpa": 7.2 },
{ "student_id": "S004", "name": "Priya", "age": 19, "department": "CSBS", "year": 1, "cgpa": 9.3 },
{ "student_id": "S005", "name": "Alice", "age": 20, "department": "Mechanical", "year": 2, "cgpa": 6.8 }
]);

Step 2: Read (Query)

Display all student records:

db.students.find().pretty();

Find students with CGPA > 8:

db.students.find({ cgpa: { $gt: 8 } }).pretty();

Find students from the Computer Science department (CSBS):

db.students.find({ department: "CSBS" }).pretty();

Step 3: Update

Update CGPA of a specific student (e.g., student_id = "S002"):

db.students.updateOne(
{ student_id: "S002" },
{ $set: { cgpa: 8.8 } }
);

Increase the year of study for all 3rd year students by 1:

db.students.updateMany(
{ year: 3 },
{ $inc: { year: 1 } }
);

Step 4: Delete

Delete a student by student_id (e.g., "S005"):

db.students.deleteOne({ student_id: "S005" });

Delete all students with CGPA < 7.5:

db.students.deleteMany({ cgpa: { $lt: 7.5 } });

Key Takeaways

Create: Insert documents using insertOne or insertMany.

Read: Use find with filters to query documents.

Update: Modify single or multiple documents using updateOne/updateMany.

Delete: Remove documents with deleteOne or deleteMany.

MongoDB makes CRUD operations intuitive and flexible, especially for JSON-like data structures, making it perfect for applications like student management systems.

Next Steps

Take screenshots of your execution in MongoDB Atlas.

Export the final students collection as JSON or CSV for backup or further analysis.

Read (Query)

A.Display all students
{}

Students with CGPA > 8

{ "cgpa": { "$gt": 8 } }

Students in CSBS department

{ "department": { "$in": ["CSBS"] } }

Update

A.Update CGPA of a specific student (S002)
{ "student_id": "S002" }

Increase year of all 3rd year students by 1
{ "year": 3 }

Delete

A. Delete one student by student_id (S005)

{ "student_id": "S004" }

B. Delete all students with CGPA < 7.5

{ "cgpa": { "$lt": 7.5 } }

MongoDB #NoSQL #CRUD #DatabaseLearning #DataEngineering #DevCommunity

SQL Indexing, Hashing & Query Optimization with a Students Table

Prabanjan B — Sun, 05 Oct 2025 04:16:56 +0000

Indexes are one of the most powerful tools in SQL databases for improving query performance. In this blog, we’ll explore B-Tree Index, B+ Tree Index, and Hash Index using a simple Students table in Oracle LiveSQL.

Step 1: Create the Students Table

We start by creating a table Students with fields for roll number, name, department, and CGPA.

CREATE TABLE Students (
ROLL_NO NUMBER PRIMARY KEY,
NAME VARCHAR2(50),
DEPT VARCHAR2(20),
CGPA NUMBER(3,2)
);

Step 2: Insert Sample Records

Let’s insert 20 sample students across various departments with different CGPAs.

INSERT INTO Students VALUES (101, 'Alice', 'CSBS', 8.5);
INSERT INTO Students VALUES (102, 'Bob', 'ECE', 7.9);
INSERT INTO Students VALUES (103, 'Charlie', 'MECH', 8.2);
INSERT INTO Students VALUES (104, 'David', 'CIVIL', 7.0);
INSERT INTO Students VALUES (105, 'Eva', 'CSBS', 9.0);
INSERT INTO Students VALUES (106, 'Frank', 'EEE', 6.8);
INSERT INTO Students VALUES (107, 'Grace', 'ECE', 8.3);
INSERT INTO Students VALUES (108, 'Hank', 'MECH', 7.2);
INSERT INTO Students VALUES (109, 'Ivy', 'CIVIL', 8.1);
INSERT INTO Students VALUES (110, 'Jack', 'CSBS', 9.0);
INSERT INTO Students VALUES (111, 'Kim', 'EEE', 7.5);
INSERT INTO Students VALUES (112, 'Leo', 'CSBS', 9.2);
INSERT INTO Students VALUES (113, 'Mia', 'MECH', 6.9);
INSERT INTO Students VALUES (114, 'Nina', 'ECE', 8.7);
INSERT INTO Students VALUES (115, 'Oscar', 'CSBS', 9.4);
INSERT INTO Students VALUES (116, 'Paul', 'EEE', 7.8);
INSERT INTO Students VALUES (117, 'Quinn', 'MECH', 8.0);
INSERT INTO Students VALUES (118, 'Rose', 'CIVIL', 7.3);
INSERT INTO Students VALUES (119, 'Sam', 'ECE', 8.8);
INSERT INTO Students VALUES (120, 'Tina', 'CSBS', 9.1);

Step 3: Create a B-Tree Index

B-Tree indexes are efficient for point queries and range queries.

CREATE INDEX idx_rollno_btree ON Students(ROLL_NO);

-- Fetch a student with a specific roll number
SELECT * FROM Students WHERE ROLL_NO = 110;

Output: Jack, CSBS, 9.0
The B-Tree index ensures this query runs efficiently without scanning the entire table.

Step 4: Create a B+ Tree Index on CGPA

B+ Tree indexes are ideal for range queries.

-- Example: fetch students with CGPA > 8.0
SELECT * FROM Students WHERE CGPA > 8.0;

This allows the database to quickly locate all students satisfying the CGPA condition.

Step 5: Create a Hash Index on Department

Hash indexes are perfect for exact match lookups.

CREATE INDEX idx_dept_hash ON Students(DEPT);

-- Fetch all students from the CSBS department
SELECT * FROM Students WHERE DEPT = 'CSBS';

Result: All CSBS students are returned efficiently, leveraging the hash index.

Key Takeaways

B-Tree Index: Fast for exact lookups and sorted range queries.

B+ Tree Index: Optimized for range scans; all values stored at leaf nodes.

Hash Index: Excellent for equality comparisons (e.g., department = 'CSBS').

Proper indexing dramatically improves query performance, especially with large datasets.

Final Thoughts

Understanding and using indexes effectively is crucial for query optimization. By combining B-Tree, B+ Tree, and Hash indexes, you can make your database queries faster and more efficient — a key skill for any data engineer or developer.

SQL #Database #Indexing #QueryOptimization #BTree #HashIndex #DataEngineering #DevCommunity

Exploring SQL Transactions, Deadlocks & Log-Based Recovery

Prabanjan B — Sun, 05 Oct 2025 04:11:54 +0000

Databases are designed to be reliable and consistent even in complex operations. Understanding transactions, deadlocks, and log-based recovery is key to mastering database management.

In this blog, we’ll explore these concepts using a simple Accounts table.

Step 1: Create the Accounts Table

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

SELECT * FROM Accounts;

Output:

acc_no name balance

1 Alice 1000
2 Bob 1500
3 Charlie 2000

Transaction – Atomicity & Rollback

Atomicity ensures that a transaction is treated as a single unit — either fully executed or not executed at all.

Let’s simulate a money transfer from Alice to Bob and roll it back before committing.

START TRANSACTION;

-- Transfer 500 from Alice to Bob
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 2;

-- Cancel the transaction
ROLLBACK;

SELECT * FROM Accounts;

After rollback, balances remain unchanged, confirming no partial update occurs.

Deadlock Simulation

Deadlocks happen when two transactions wait indefinitely for each other’s locks.

Simulate using two sessions:

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 100 WHERE acc_no = 1; -- Locks Alice
-- Try to update Bob
UPDATE Accounts SET balance = balance - 100 WHERE acc_no = 2;

Session 2:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 200 WHERE acc_no = 2; -- Locks Bob
-- Try to update Alice
UPDATE Accounts SET balance = balance - 200 WHERE acc_no = 1;

Both sessions wait on each other — a deadlock occurs.
Most DBMS detect this and abort one transaction automatically.

Log-Based Recovery

Databases maintain logs (binary log in MySQL, WAL in PostgreSQL) to recover from crashes.

Example:

START TRANSACTION;

UPDATE Accounts SET balance = balance + 300 WHERE acc_no = 3;

-- Rollback instead of commit
ROLLBACK;

The rollback is recorded in the log. If the database crashes, the system can undo uncommitted changes and restore consistency.

Key Takeaways

Concept Purpose / Behavior

Atomicity All-or-nothing execution; rollback undoes partial updates
Deadlocks Conflicting locks; DBMS resolves by aborting one transaction
Log-Based Recovery Maintains consistency after crashes by replaying/undoing transactions

Final Thoughts

Understanding transactions, deadlocks, and recovery mechanisms is crucial for building robust and reliable database applications.

Experimenting with these SQL operations gives hands-on insight into how real-world DBMS maintain data integrity and availability.

SQL #Database #Transactions #Deadlocks #Recovery #DevCommunity #LearningByDoing

Exploring ACID Properties in SQL with Practical Queries

Prabanjan B — Sun, 05 Oct 2025 04:04:06 +0000

Exploring ACID Properties in SQL with Practical Queries

Databases are designed to ensure that data remains accurate, reliable, and consistent even in the face of failures.
This reliability comes from the ACID properties — Atomicity, Consistency, Isolation, and Durability.

In this blog, let’s understand each property using a simple example: a bank Accounts table.

Step 1: Create the Accounts Table

We’ll begin by creating a table with three columns — account number, name, and balance.

CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0) -- prevents negative balance
);

The CHECK constraint ensures that no record can have a negative balance — maintaining data consistency.

Step 2: Insert Sample Records

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

SELECT * FROM Accounts;

✅ Output:

acc_no name balance

1 Alice 1000
2 Bob 1500
3 Charlie 2000

Step 3: Atomicity

Atomicity ensures that a transaction is treated as a single unit — either all changes happen, or none do.

Let’s simulate a money transfer between two accounts, then roll it back to ensure no partial changes occur.

START TRANSACTION;

UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 1;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 2;

-- Cancel the transaction
ROLLBACK;

SELECT * FROM Accounts;

✅ After rollback, both balances return to their original state.
That’s Atomicity in action — preventing partial updates.

Step 4: Consistency

Now, let’s check if our table enforces consistency by rejecting invalid data.

INSERT INTO Accounts VALUES (104, 'David', -2000);

❌ This statement will fail because of the CHECK (balance >= 0) constraint.

This demonstrates Consistency, ensuring that all data adheres to predefined rules.

Step 5: Isolation

Isolation ensures that concurrent transactions don’t interfere with each other.

Try this in two different sessions:

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 3;
-- Keep this transaction open

Session 2:

SELECT * FROM Accounts WHERE acc_no = 3;

Depending on your isolation level (e.g., READ COMMITTED, REPEATABLE READ), Session 2 may or may not see the uncommitted change.
That’s how Isolation controls visibility between transactions.

Step 6: Durability

Once a transaction is committed, its changes are permanent — even if the system crashes.

START TRANSACTION;
UPDATE Accounts SET balance = balance + 2000 WHERE acc_no = 3;
COMMIT;

SELECT acc_no, name, balance FROM Accounts WHERE acc_no = 3;

✅ After restarting your database, the updated balance for Charlie remains.
That’s Durability — ensuring committed data is never lost.

Summary

Property Description Example

Atomicity All or nothing execution of a transaction. Rollback test
Consistency Data remains valid before and after a transaction. Negative balance rejection
Isolation Transactions are executed independently. Two-session example
Durability Once committed, data persists permanently. Commit and restart DB

Final Thoughts

ACID properties form the foundation of reliable database systems.
By experimenting with simple SQL transactions, you can clearly see how Atomicity, Consistency, Isolation, and Durability maintain data integrity — even in complex systems.

💬 Try running these queries yourself and observe how your database ensures reliability step-by-step!

SQL #Database #ACID #Transactions #LearningByDoing #DevCommunity

SQL Cursor and Trigger Implementation — Step by Step Guide

Prabanjan B — Sun, 05 Oct 2025 03:51:23 +0000

In this post, we’ll explore two key SQL programming concepts:
1️⃣ Cursor with condition and
2️⃣ AFTER INSERT Trigger.
Both examples are implemented in Oracle Live SQL.

1️⃣ Cursor: Display Employees with Salary Greater than 50,000

A cursor is used to process each record returned by a query, one row at a time.

✅ Step 1: Create a Cursor

DECLARE
CURSOR emp_cursor IS
SELECT EmpName, Salary
FROM Employee
WHERE Salary > 50000;

emp_record emp_cursor%ROWTYPE;

BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO emp_record;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_record.EmpName ||
', Salary: ' || emp_record.Salary);
END LOOP;
CLOSE emp_cursor;
END;
/

🧠 Explanation:

CURSOR emp_cursor IS → Defines the SQL query.

emp_record emp_cursor%ROWTYPE → Declares a record to store fetched rows.

OPEN, FETCH, CLOSE → Manage cursor operations.

DBMS_OUTPUT.PUT_LINE → Displays each result.

🖥️ Output:

Employee: Arjun, Salary: 60000
Employee: Kiran, Salary: 80000

2️⃣ AFTER INSERT Trigger — Student Registration Audit

A trigger automatically performs an action when a specified database event occurs.

✅ Step 2: Create AFTER INSERT Trigger

CREATE OR REPLACE TRIGGER trg_student_insert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, StudentName)
VALUES (:NEW.StudentID, :NEW.StudentName);
END;
/

🧠 Explanation:

AFTER INSERT ON Students → Trigger runs after a new record is inserted.

:NEW → Refers to the new row being added.

Student_Audit Table → Logs inserted student records automatically.

✅ Step 3: Test the Trigger

INSERT INTO Students (StudentID, StudentName)
VALUES ('S01', 'Arjun');

🖥️ Output:

Trigger TRG_STUDENT_INSERT compiled
1 row inserted

And a new log entry will appear in the Student_Audit table 🎯

🏁 Summary

Feature Description Example

Cursor Used for row-by-row processing Displays employees earning > ₹50,000
Trigger Executes automatically after table events Logs new student registrations

💡 Conclusion

Cursors and Triggers are essential for database automation and record handling.
By using them effectively, you can make your SQL programs more powerful, reliable, and dynamic!

Step 1:

Step 2:

Understanding 1NF, 2NF, and 3NF in DBMS with SQL Examples

Prabanjan B — Sat, 04 Oct 2025 10:42:29 +0000

Base Table
Use the following data as the starting point:

When designing databases, normalization is essential to remove redundancy and anomalies (insertion, update, deletion). In this post, we’ll take a sample base table, identify anomalies, and step-by-step normalize it into 1NF, 2NF, and 3NF using SQL CREATE TABLE statements.

📌 Base Table

Let’s assume a base table with student-course-instructor details:

StudentID StudentName CourseID CourseName Instructor InstructorEmail

1 Alice C101 DBMS Dr. Smith smith@uni.edu
2 Bob C102 Networks Dr. Lee lee@uni.edu
3 Alice C103 AI Dr. Clark clark@uni.edu

🚨 Anomalies in this table

Insertion anomaly: Cannot add a new course until a student enrolls.

Update anomaly: If instructor’s email changes, must update multiple rows.

Deletion anomaly: Deleting the last student in a course removes course and instructor details too.

✅ Step 1: Convert to 1NF (First Normal Form)

➡️ Eliminate repeating groups and ensure atomic values.

CREATE TABLE StudentCourses_1NF (
StudentID INT,
StudentName VARCHAR(100),
CourseID VARCHAR(10),
CourseName VARCHAR(100),
Instructor VARCHAR(100),
InstructorEmail VARCHAR(100)
);

✅ Step 2: Convert to 2NF (Second Normal Form)

➡️ Remove partial dependency (attributes depending only on part of composite key).
We separate Students, Courses, and Enrollments.

CREATE TABLE Students (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100)
);

CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(100),
Instructor VARCHAR(100),
InstructorEmail VARCHAR(100)
);

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

✅ Step 3: Convert to 3NF (Third Normal Form)

➡️ Remove transitive dependency (InstructorEmail depends on Instructor, not CourseID).
So, we create a separate Instructors table.

CREATE TABLE Instructors (
InstructorID INT PRIMARY KEY,
InstructorName VARCHAR(100),
InstructorEmail VARCHAR(100)
);

CREATE TABLE Courses (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(100),
InstructorID INT,
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);

📝 Insert Sample Data

INSERT INTO Students VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO Instructors VALUES (1, 'Dr. Smith', 'smith@uni.edu'),
(2, 'Dr. Lee', 'lee@uni.edu'),
(3, 'Dr. Clark', 'clark@uni.edu');
INSERT INTO Courses VALUES ('C101', 'DBMS', 1),
('C102', 'Networks', 2),
('C103', 'AI', 3);
INSERT INTO Enrollments VALUES (1, 'C101'), (2, 'C102'), (1, 'C103');

🔗 Query with JOINS

List all students with their courses and instructors:

SELECT s.StudentName, c.CourseName, i.InstructorName
FROM Enrollments e
JOIN Students s ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
JOIN Instructors i ON c.InstructorID = i.InstructorID;

🚀 Final Thoughts

Database normalization helps eliminate redundancy, prevent anomalies, and improve efficiency. By following 1NF → 2NF → 3NF, we created a robust schema ready for real-world applications.

SQL #DBMS #DatabaseDesign #Normalization #BackendDevelopment

College Student & Course Management System

Prabanjan B — Mon, 25 Aug 2025 10:02:22 +0000

Introduction
This blog covers the implementation of a simple College Student & Course Management System using SQL on Oracle LiveSQL. It demonstrates key database concepts such as table creation, data insertion, constraint addition, queries with functions and aggregates, joins, views, and stored procedures.

The use case focuses on managing students, courses, enrollments, and faculty members with related operations.

Database Schema
The database contains four main tables: Students, Courses, Enrollments, and Faculty.

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

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

Data Insertion
Sample data was inserted into these tables to represent students, courses, and their enrollments:

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Alice Johnson', 'Computer Science', TO_DATE('2002-04-15', 'YYYY-MM-DD'), 'alice.johnson@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Bob Smith', 'Mathematics', TO_DATE('2001-11-23', 'YYYY-MM-DD'), 'bob.smith@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Cathy Brown', 'Physics', TO_DATE('2003-07-02', 'YYYY-MM-DD'), 'cathy.brown@example.com');

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

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

Table Alterations and Constraints
Added a new column PhoneNo to Students and a CHECK constraint on the Credits column of Courses:

ALTER TABLE Students ADD PhoneNo VARCHAR2(10);

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

SQL Queries with Functions and Aggregates
Listing student names in uppercase and length of their emails:
SELECT UPPER(Name) AS UppercaseName, LENGTH(Email) AS EmailLength FROM Students;

Calculating average course credits and counting enrolled students:
SELECT
(SELECT AVG(Credits) FROM Courses) AS AvgCredits,
(SELECT COUNT(DISTINCT StudentID) FROM Enrollments) AS TotalStudentsEnrolled
FROM dual;

Sample result:

UppercaseName EmailLength
ALICE JOHNSON 25
BOB SMITH 21
CATHY BROWN 23
AvgCredits TotalStudentsEnrolled
4.0 3
JOIN Queries
Joining Students, Enrollments, and Courses to show which student is enrolled in which course along with grades:

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

Sample result:

StudentName CourseName Grade
Alice Johnson Databases A
Bob Smith Algorithms B+
Cathy Brown Physics A-
GROUP BY and HAVING Clause
Counting students in each department and filtering departments with more than 2 students:

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

Views
Created a view to simplify student-course-grade lookup:

CREATE OR REPLACE 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 c.CourseID = e.CourseID;

Stored Procedure
Procedure to update a student's grade in enrollments:

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

Summary
This assignment reinforced understanding of:

Creating and managing SQL database schemas
Writing data manipulation queries
Using SQL functions and aggregate operations
Performing joins to combine related data
Creating views and stored procedures to enhance SQL capabilities
Feel free to try the full script on Oracle LiveSQL to see these operations in action.