Forem: Vishnupriya K

Hands-On with MongoDB: Performing CRUD Operations on a Student Collection 🎓

Vishnupriya K — Wed, 08 Oct 2025 18:15:23 +0000

As part of learning MongoDB, I decided to practice CRUD operations (Create, Read, Update, Delete) using a simple student collection. This hands-on approach helped me understand how MongoDB handles data in a flexible, JSON-like format.

Objective

Gain practical experience with MongoDB by performing CRUD operations on a students collection.

Student schema:

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

1️⃣ Create (Insert)

Insert 5 student records using insertOne() or insertMany():

db.students.insertOne({ "student_id": "S001", "name": "Santhosh", "age": 20, "department": "CSBS", "year": 2, "cgpa": 9 });
db.students.insertOne({ "student_id": "S002", "name": "Anjali", "age": 21, "department": "CSE", "year": 3, "cgpa": 8.5 });
db.students.insertOne({ "student_id": "S003", "name": "Ravi", "age": 19, "department": "ECE", "year": 1, "cgpa": 7.2 });
db.students.insertOne({ "student_id": "S004", "name": "Meera", "age": 22, "department": "CSE", "year": 3, "cgpa": 9.1 });
db.students.insertOne({ "student_id": "S005", "name": "Karthik", "age": 20, "department": "MECH", "year": 2, "cgpa": 6.8 });

2️⃣ Read (Query)
Display all students:
db.students.find().pretty();

Students with CGPA > 8:
db.students.find({ "cgpa": { $gt: 8 } }).pretty();

Students in Computer Science (CSE):
db.students.find({ "department": "CSE" }).pretty();

3️⃣ Update
Update CGPA for a student:
db.students.updateOne(
{ "student_id": "S001" },
{ $set: { "cgpa": 9.5 } }
);

Increment year for all 3rd-year students:
db.students.updateMany(
{ "year": 3 },
{ $inc: { "year": 1 } }
);

4️⃣ Delete
Delete a student by ID:
db.students.deleteOne({ "student_id": "S005" });

Delete students with CGPA < 7.5:
db.students.deleteMany({ "cgpa": { $lt: 7.5 } });

Final Thoughts

This hands-on exercise taught me:

How MongoDB manages JSON-like documents.
Performing CRUD operations is straightforward and powerful.

Querying, updating, and deleting data in MongoDB is very flexible compared to traditional SQL.

💡 Practicing with small datasets like a student collection is a great way to build confidence in NoSQL databases.

Indexing, Hashing & Query Optimization in SQL

Vishnupriya K — Sun, 05 Oct 2025 20:59:26 +0000

Efficient data retrieval is essential for database performance. In this tutorial, we’ll explore B-Tree, B+ Tree, and Hash indexing, and see how they optimize queries.

1. Create Sample Students Table
CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR2(50),
dept VARCHAR2(20),
cgpa NUMBER(3,2)
);

-- Insert 20 sample records
INSERT INTO Students VALUES (101, 'Alice', 'CSBS', 9.1);
INSERT INTO Students VALUES (102, 'Bob', 'MECH', 7.8);
INSERT INTO Students VALUES (103, 'Charlie', 'CSBS', 8.5);
INSERT INTO Students VALUES (104, 'David', 'ECE', 8.2);
INSERT INTO Students VALUES (105, 'Eve', 'CSBS', 9.0);
INSERT INTO Students VALUES (106, 'Frank', 'CIVIL', 7.5);
INSERT INTO Students VALUES (107, 'Grace', 'ECE', 8.9);
INSERT INTO Students VALUES (108, 'Hannah', 'CSBS', 9.2);
INSERT INTO Students VALUES (109, 'Ivy', 'MECH', 7.9);
INSERT INTO Students VALUES (110, 'Jack', 'CSBS', 8.6);
INSERT INTO Students VALUES (111, 'Kevin', 'CIVIL', 7.2);
INSERT INTO Students VALUES (112, 'Laura', 'ECE', 8.0);
INSERT INTO Students VALUES (113, 'Mallory', 'CSBS', 8.8);
INSERT INTO Students VALUES (114, 'Nina', 'MECH', 7.7);
INSERT INTO Students VALUES (115, 'Oscar', 'CSBS', 9.3);
INSERT INTO Students VALUES (116, 'Peggy', 'ECE', 8.1);
INSERT INTO Students VALUES (117, 'Quentin', 'CSBS', 8.9);
INSERT INTO Students VALUES (118, 'Rita', 'CIVIL', 7.6);
INSERT INTO Students VALUES (119, 'Steve', 'CSBS', 9.0);
INSERT INTO Students VALUES (120, 'Trudy', 'MECH', 7.8);

COMMIT;

2. B-Tree Index on roll_no

B-Tree indexes are ideal for exact match queries.

-- Create B-Tree index
CREATE INDEX idx_roll_no ON Students(roll_no);

-- Query using index
SELECT * FROM Students
WHERE roll_no = 110;

✅ Using this index, the database can quickly locate roll_no = 110 without scanning the full table.

3. B+ Tree Index on cgpa

B+ Tree indexes are great for range queries.

-- Create B+ Tree index
CREATE INDEX idx_cgpa ON Students(cgpa);

-- Query all students with cgpa > 8.0
SELECT * FROM Students
WHERE cgpa > 8.0
ORDER BY cgpa DESC;

Efficiently fetches multiple records in a range.
The ORDER BY clause benefits from B+ Tree’s sequential structure.

4. Hash Index on dept

Hash indexes work well for equality lookups on discrete values.

-- Create Hash index
CREATE INDEX idx_dept ON Students(dept) LOCAL; -- Oracle supports HASH-like functionality with GLOBAL or partitioned indexes

-- Query students from 'CSBS' department
SELECT * FROM Students
WHERE dept = 'CSBS';

✅ Quickly retrieves all students from a specific department without scanning the table.

5. Query Optimization Tips

Always create indexes on columns used in WHERE, JOIN, or ORDER BY clauses.
Use B-Tree/B+ Tree for range queries.
Use Hash indexes for equality searches on high-cardinality columns.
Check execution plans to ensure queries use indexes effectively.
Avoid over-indexing — it slows INSERT/UPDATE/DELETE operations.

Conclusion

Indexing is a key tool for query optimization. B-Tree, B+ Tree, and Hash indexes reduce table scans and improve performance. Combining indexes with query best practices ensures your database is fast and scalable.

Understanding Transactions, Deadlocks & Log-Based Recovery in SQL

Vishnupriya K — Sun, 05 Oct 2025 20:43:04 +0000

Database reliability is critical for any application. In this tutorial, we’ll explore Transactions, Deadlocks, and Log-Based Recovery using a simple Accounts table.

1. Setup: Accounts Table
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR2(50),
balance INT
);

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

COMMIT;

This table will be used to simulate transactions, deadlocks, and recovery.

2. Transaction – Atomicity & Rollback

Goal: Transfer 500 from Alice to Bob, then rollback to demonstrate atomicity.

-- Start transaction
BEGIN
-- Deduct 500 from Alice
UPDATE Accounts
SET balance = balance - 500
WHERE acc_no = 1;

-- Add 500 to Bob
UPDATE Accounts
SET balance = balance + 500
WHERE acc_no = 2;

-- Simulate a rollback
ROLLBACK;

END;
/

✅ Check balances:

SELECT * FROM Accounts;

Result: Balances remain unchanged. No partial update occurred — atomicity is preserved.

3. Deadlock Simulation

Goal: Demonstrate a deadlock scenario with two sessions.

Session 1:

-- Lock Alice’s account
UPDATE Accounts
SET balance = balance + 100
WHERE acc_no = 1;

-- Pause before committing (wait)

Session 2 (simultaneously):

-- Lock Bob’s account
UPDATE Accounts
SET balance = balance - 50
WHERE acc_no = 2;

-- Now try to update Alice’s account
UPDATE Accounts
SET balance = balance - 100
WHERE acc_no = 1;

Both sessions are waiting for the other to release locks → deadlock occurs.

Most DBMS detect deadlocks and abort one transaction to resolve it.

Key takeaway: Always acquire locks in the same order to avoid deadlocks in multi-session environments.

4. Log-Based Recovery

Goal: Ensure database can undo changes after rollback using logs.

In Oracle, all transactions are automatically logged in the Redo Log.

In MySQL/PostgreSQL, binary logs / WAL (Write-Ahead Log) track all changes.

Example:

BEGIN
UPDATE Accounts
SET balance = balance + 200
WHERE acc_no = 3;

ROLLBACK; -- Undo the update

END;
/

Check logs:

Oracle: V$LOGMNR_CONTENTS or redo logs capture the undo operation.
MySQL: Binary log will record both the original update and rollback action .

Result: Log ensures database can recover from failures without losing consistency.

5. Summary
Concept Explanation

Transaction (Atomicity) Either all operations succeed or none; rollback prevents partial updates.
Deadlock Two sessions waiting for each other’s locks; resolved by DBMS aborting one transaction.
Log-Based Recovery Logs record changes so rollback and crash recovery are possible.

Conclusion

By understanding transactions, deadlocks, and log-based recovery, you can ensure your database remains reliable, consistent, and recoverable. Always test transactions carefully, handle deadlocks gracefully, and leverage logging for safety.

💡 Tip: Simulate deadlocks in a controlled environment to learn how your DBMS resolves them. This is essential for multi-user applications.

Understanding ACID Properties in SQL with Examples

Vishnupriya K — Sun, 05 Oct 2025 20:29:04 +0000

ACID properties — Atomicity, Consistency, Isolation, Durability — ensure that database transactions are reliable, safe, and predictable. Let’s explore them with a practical Accounts table.

1. Create Sample Accounts Table
CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR2(50),
balance INT CHECK (balance >= 0) -- enforce positive balance
);

Insert sample data
INSERT INTO Accounts VALUES (101, 'Alice', 5000);
INSERT INTO Accounts VALUES (102, 'Bob', 3000);
INSERT INTO Accounts VALUES (103, 'Charlie', 7000);

COMMIT;

2. Atomicity

Definition: Either all operations in a transaction succeed, or none do.

Scenario: Transfer 1000 from Alice to Bob, but simulate a failure midway.

-- Start transaction
BEGIN
-- Deduct 1000 from Alice
UPDATE Accounts
SET balance = balance - 1000
WHERE acc_no = 101;

-- Simulate error (e.g., divide by zero)
-- This will cause the transaction to fail
-- Uncomment the following line to simulate failure
-- DECLARE x NUMBER := 1/0; END;

-- Add 1000 to Bob
UPDATE Accounts
SET balance = balance + 1000
WHERE acc_no = 102;

COMMIT; -- Will not reach here if error occurs

EXCEPTION
WHEN OTHERS THEN
ROLLBACK; -- Ensures no partial update
DBMS_OUTPUT.PUT_LINE('Transaction failed, rolled back.');
END;
/
✅ After rollback, balances remain unchanged — Atomicity is maintained.

3. Consistency

Definition: Transactions must leave the database in a valid state.

Scenario: Try inserting a negative balance.

-- This will fail due to the CHECK constraint
INSERT INTO Accounts VALUES (104, 'David', -500);

The database rejects this, ensuring Consistency.

4. Isolation

Definition: Concurrent transactions should not interfere with each other.

Scenario: Two sessions:

Session 1: Update Alice’s balance

UPDATE Accounts
SET balance = balance + 2000
WHERE acc_no = 101;
-- Do not commit yet

Session 2: Read Alice’s balance

SELECT balance FROM Accounts WHERE acc_no = 101;

Session 2 will not see the uncommitted change (depending on isolation level, default is READ COMMITTED).

This prevents dirty reads — demonstrating Isolation.

5. Durability

Definition: Once a transaction is committed, data persists even after a crash.

-- Commit a transaction
UPDATE Accounts
SET balance = balance + 1000
WHERE acc_no = 102;

COMMIT;

Restart the database.

Run:
SELECT * FROM Accounts;

Changes persist — Durability is guaranteed.

Summary Table
ACID Property Example Action
Atomicity Transfer money → rollback on error
Consistency CHECK constraint prevents negative balance
Isolation Concurrent reads/writes don’t interfere
Durability Committed changes persist after DB restart

💡Tip: Try changing isolation levels (READ COMMITTED, SERIALIZABLE) to observe how concurrent transactions behave differently.

Mastering Oracle SQL: Cursor and Trigger Examples

Vishnupriya K — Sun, 05 Oct 2025 20:13:13 +0000

Oracle SQL provides powerful features to automate and process data efficiently. In this post, we will explore Cursors and Triggers with practical examples that you can implement right away.

1. Cursors in Oracle SQL

A cursor is a pointer to a result set of a query. It allows you to process rows one by one in PL/SQL. Cursors are especially useful when you want to perform operations conditionally on each row.

Example: Display employees with salary > 50,000
DECLARE
CURSOR emp_cursor IS
SELECT EmployeeName, 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.EmployeeName || ' | Salary: ' || emp_record.Salary);
END LOOP;
CLOSE emp_cursor;
END;
/

✅ This code will output employees whose salary exceeds 50,000.

Key points:

CURSOR emp_cursor IS … defines the cursor.
FETCH emp_cursor INTO … retrieves one row at a time.
emp_cursor%NOTFOUND checks if all rows are processed.

2. Triggers in Oracle SQL

A trigger is a stored PL/SQL block that automatically executes in response to DML events (INSERT, UPDATE, DELETE) on a table. Triggers are ideal for auditing, validation, or automation.

Example: Automatically log new student registrations

We want to track student registrations in a separate Student_Audit table whenever a new student is added.

CREATE OR REPLACE TRIGGER trg_student_audit
AFTER INSERT ON Student
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit(AuditID, StudentID, StudentName, RegistrationDate)
VALUES (seq_audit_id.NEXTVAL, :NEW.StudentID, :NEW.StudentName, SYSDATE);
END;
/

How it works:

AFTER INSERT ON Student → Trigger fires after a new student is added.
FOR EACH ROW → Executes for every row inserted.
:NEW → References the new record being inserted.
Student_Audit automatically logs the student’s details with a timestamp.

3. Benefits of Using Cursors and Triggers

Cursor Process query results row by row, implement conditional logic, and automate complex tasks.
Trigger Automatically maintain audit logs, enforce rules, and reduce manual tasks.

Conclusion

Cursors and triggers are essential tools for any Oracle SQL developer. With cursors, you can handle row-level operations efficiently, while triggers help maintain data integrity and automation.

Start experimenting with your own tables and see how these features can save time and reduce errors in real-world applications.

💡 Tip: Combine cursors and triggers to build advanced automation, like automatically processing data after insert or update events.

🚀 Understanding Database Normalization (1NF, 2NF, 3NF) with Oracle SQL — Step-by-Step Example

Vishnupriya K — Sun, 05 Oct 2025 19:57:24 +0000

Database normalization is one of the most important concepts in DBMS. It helps in reducing redundancy and improving data integrity.
In this post, let’s understand 1NF, 2NF, and 3NF using a simple real-world example — and implement everything using Oracle Live SQL.

🧩The Base Table

We’ll start with this unnormalized table:

StudentID StudentName CourseID CourseName Instructor InstructorPhone
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

⚠️ Step 1 — Identify Anomalies

In the base table, we can observe three major problems:

🔸 Insertion Anomaly
You cannot insert a new course unless a student enrolls in it.
🔸 Update Anomaly
If Dr. Kumar changes his phone number, we must update it in multiple rows.
🔸 Deletion Anomaly
If all students drop the “AI” course, we lose Dr. Rao’s details completely.

So, let’s fix these issues one normalization step at a time.

🧱 Step 2 — Convert to 1NF (First Normal Form)

Rule:
Each cell must contain only atomic (single) values — no repeating groups.

✅ Our base table already satisfies 1NF, but we’ll create it formally in SQL.

CREATE TABLE BaseTable (
StudentID VARCHAR2(10),
StudentName VARCHAR2(50),
CourseID VARCHAR2(10),
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);

🧩 Step 3 — Convert to 2NF (Second Normal Form)

Rule:
Remove partial dependencies — no non-key attribute should depend on part of a composite key.

Here, the composite key is (StudentID, CourseID).

We separate the data into three smaller tables:

Student
Course
Enrollment

CREATE TABLE Student (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(50)
);

CREATE TABLE Course (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Enrollment (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Now, data redundancy is reduced — but we can still do better!

🧠 Step 4 — Convert to 3NF (Third Normal Form)

Rule:
Remove transitive dependencies — non-key attributes should not depend on other non-key attributes.

Here, InstructorPhone depends on Instructor, not on CourseID.
So, we’ll create a separate Instructor table and link it to Course via an InstructorID.

CREATE TABLE Instructor (
InstructorID VARCHAR2(10) PRIMARY KEY,
InstructorName VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);

CREATE TABLE Course (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(50),
InstructorID VARCHAR2(10),
FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);

🧾 Step 5 — Final 3NF Schema Diagram

Tables:

Student(StudentID, StudentName)
Instructor(InstructorID, InstructorName, InstructorPhone)
Course(CourseID, CourseName, InstructorID)
Enrollment(EnrollID, StudentID, CourseID) This structure removes redundancy completely and makes the database consistent and scalable.

🧩 Step 6 — Sample Data Insertion
INSERT INTO Student VALUES ('S01', 'Arjun');
INSERT INTO Student VALUES ('S02', 'Priya');
INSERT INTO Student VALUES ('S03', 'Kiran');

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

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

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

🔍 Step 7 — Display Data using JOINs

Now, we can easily query all students, their courses, and their instructors:

SELECT
s.StudentID,
s.StudentName,
c.CourseName,
i.InstructorName,
i.InstructorPhone
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;

Output:

StudentID StudentName CourseName InstructorName InstructorPhone
S01 Arjun DBMS Dr. Kumar 9876543210
S01 Arjun Data Mining Dr. Mehta 9123456780
S02 Priya DBMS Dr. Kumar 9876543210
S03 Kiran AI Dr. Rao 9988776655

✅ Final Thoughts

Through normalization:

Data redundancy is minimized
Update, insertion, and deletion anomalies are eliminated
The database becomes more reliable and scalable

By implementing these steps in Oracle Live SQL, you can clearly visualize how normalization improves your database structure.

A special thanks to @santhoshnc sir for mentoring me on these Normalisation topics!

College Student & Course Management System (Oracle LiveSQL)

Vishnupriya K — Sat, 23 Aug 2025 13:15:17 +0000

sql #oracle #database #tutorial

Introduction

This blog covers my hands-on implementation of a simple College Student & Course Management System using SQL on Oracle LiveSQL.

It includes:

Table creation
Data insertion
Constraints & table alterations
SQL queries with string functions & aggregates
Joins
Views
Grouping with HAVING
Stored procedure

This use case manages students, faculty, courses, and enrollments in a college database.

Database Schema

I created four main tables:

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

Students

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Arun Kumar', 'Computer Science', TO_DATE('2002-08-15','YYYY-MM-DD'), 'arun.kumar@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Divya R', 'Electronics', TO_DATE('2001-10-22','YYYY-MM-DD'), 'divya.r@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Mohan S', 'Mechanical', TO_DATE('2003-01-05','YYYY-MM-DD'), 'mohan.s@example.com');

Courses

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

Enrollments

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 & Constraints

Add PhoneNo to Students

ALTER TABLE Students ADD PhoneNo VARCHAR2(10);

Add Credits Constraint to Courses

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

SQL Queries

Uppercase Names & Email Length

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

Department-wise Student Count (>2 only)

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

Average Credits & Total Enrolled Students

SELECT
(SELECT AVG(Credits) FROM Courses) AS AvgCredits,
(SELECT COUNT(DISTINCT StudentID) FROM Enrollments) AS TotalStudentsEnrolled
FROM dual;

Joins

Show student-course-grade details:

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;

Views

Created a view to simplify lookups:

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:

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

Through this system, I learned:

Creating tables with constraints
Inserting and managing data
Using string & aggregate functions
Applying JOIN, GROUP BY, HAVING
Building views for easier queries
Writing stored procedures for updates

Forem: Vishnupriya K

Hands-On with MongoDB: Performing CRUD Operations on a Student Collection 🎓

Indexing, Hashing & Query Optimization in SQL

Conclusion

Understanding Transactions, Deadlocks & Log-Based Recovery in SQL

Understanding ACID Properties in SQL with Examples

Mastering Oracle SQL: Cursor and Trigger Examples

🚀 Understanding Database Normalization (1NF, 2NF, 3NF) with Oracle SQL — Step-by-Step Example

College Student & Course Management System (Oracle LiveSQL)

sql #oracle #database #tutorial

Database Schema

This small project gave me strong practice in Oracle SQL and reinforced database concepts 💡.