DEV Community: Rokesh . S

MONGODB CRUD IN DBMS

Rokesh . S — Tue, 07 Oct 2025 09:23:41 +0000

Getting Hands-On with MongoDB Atlas: CRUD Operations Made Simple
Hey devs

I recently dove into MongoDB Atlas to explore how CRUD operations work in a cloud-hosted NoSQL environment—and I wanted to share a few insights and screenshots from my journey. If you're just getting started with MongoDB or curious about how Atlas simplifies database management, this post is for you!

What I Built
I created a database named CRUD and a collection called students. Using the Atlas UI, I performed basic operations like:

Insert: Added student records with fields like name, age, year, and department.

Query: Filtered documents using operators like $gt, $or, and range queries with $gte and $lte.

Update: Modified fields directly in the UI—super intuitive!

Delete: Flagged documents for deletion with a simple click.

Sample Queries I Used
Here are a few examples of queries I ran:

js
// Find students with student_id greater than 5000
{ "student_id": { "$gt": "5000" } }

// Find students aged either 1 or 3
{ "$or": [ { "age": 1 }, { "age": 3 } ] }

// Find students aged between 1.5 and 7.5
{ "age": { "$gte": 1.5, "$lte": 7.5 } }
Screenshots
I’ve included screenshots showing:

How documents are structured in MongoDB Atlas

How filters are applied and results are displayed

How easy it is to modify or delete documents

These visuals really help demystify the process for beginners.

Why MongoDB Atlas?
No setup headaches—just launch and go.

Built-in UI for managing data without writing shell commands.

Great for prototyping and learning NoSQL concepts.

Indexing, Hashing & Query Optimization

Rokesh . S — Tue, 07 Oct 2025 07:59:41 +0000

Step-1: 1. Setting Up Your Table
First, you'll create a simple table to store student information and fill it with some data.

Create a table Students: You need a table with columns for roll number (roll_no), name, department (dept), and cgpa.

Insert at least 20 sample records: You'll add 20 or more rows of fake student data into this table.

This gives you a dataset to work with. Without any indexes, if you search for a student, the database has to look through every single row one by one, which is slow.

Step-2: B-Tree Index (For Specific Lookups)
This part shows how to speed up searches for a single, specific record.

Create a B-Tree index on the roll_no column: A B-Tree index is like the index at the back of a textbook. If you want to find a specific topic (like roll_no = 110), you don't read the whole book; you look it up in the index, which tells you the exact page number (or in this case, the row's location). This is very fast for finding unique values.

Execute a query to fetch the details of a student with roll_no = 110: When you run this query, the database will use the B-Tree index you just created to instantly find the student with roll_no = 110 instead of scanning the whole table

Step-3: B+ Tree Index (For Range-Based Searches)
This part focuses on searches that look for a range of values.

Create a B+ Tree index on the cgpa column: A B+ Tree is a special type of B-Tree where all the final data pointers are stored at the bottom level (leaf nodes) and are linked to each other. This structure makes it extremely efficient to scan through a sequence of data.

Write a query to display all students with cgpa > 8.0: This is a range query (you're not looking for one CGPA, but all CGPAs above a certain value). The B+ Tree allows the database to quickly find the first student with a CGPA of 8.0 and then just follow the linked list to get all the other students with higher CGPAs.

Step-4: Hash Index (For Exact Matches)
This part demonstrates an index that is super fast for finding records based on an exact value.

Create a Hash index on the dept column: A Hash index works like a dictionary or hash map in programming. It takes a value (e.g., 'CSBS'), converts it into a unique code (a "hash"), and uses that code to find the data's location directly.

Run a query to retrieve all students from the 'CSBS' department: This is an equality query. The hash index will instantly find all students in the 'CSBS' department. However, a hash index is not good for range queries (e.g., dept > 'CSBS').

Transactions, Deadlocks & Log Based Recovery

Rokesh . S — Tue, 07 Oct 2025 07:53:10 +0000

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;

Step-1: Atomicity & Rollback

Step-2: Deadlock Simulation
Session 1
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';
Session 2
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Bob';
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Alice';

One of the sessions will show:

ORA-00060: deadlock detected while waiting for resource
Step-3: Log-Based Recovery

ACID property with SQL transactions

Rokesh . S — Tue, 07 Oct 2025 07:12:16 +0000

ACID stands for:

A – Atomicity: Ensures that a transaction is “all or nothing.”

C – Consistency: Ensures data follows rules and constraints.

I – Isolation: Ensures transactions run independently without interfering.

D – Durability: Ensures committed changes are saved even after system failure

Step-1 : Create Table and Insert Records

Step-2: Atomicity – Transaction Rollback
Simulate a transfer where you rollback midway:

Step-3: Consistency – Reject Invalid Data
Try inserting invalid data
❌ You’ll get an error because of the CHECK(balance >= 0) constraint.

Step-4: solation – Two Sessions

In Oracle Live SQL, open two sessions:
Session 1: Start a transaction and update balance but don’t commit.
Session 2: Try reading the same row.

Step-5: Durability – Commit and Restart

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

INSERT INTO Accounts VALUES (101, 'Ravi', 5000);
INSERT INTO Accounts VALUES (102, 'Meena', 3000);
INSERT INTO Accounts VALUES (103, 'Arun', 7000);

SELECT * FROM Accounts;
-- Start a transaction
SAVEPOINT start_tx;

UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 102;

-- Oops! Suppose error or cancellation occurs
ROLLBACK TO start_tx;

SELECT * FROM Accounts;
INSERT INTO Accounts VALUES (104, 'Kiran', -2000);
-- Session 1
UPDATE Accounts SET balance = balance - 500 WHERE acc_no = 101;
-- Don’t commit yet

-- Session 2
SELECT * FROM Accounts WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 103;
COMMIT;

CURSOR + TRIGGER

Rokesh . S — Tue, 07 Oct 2025 07:04:13 +0000

CURSOR + TRIGGER

sql

database

tutorial
Cursor example
Step 1: Create a sample table (Employee)

Step 2: Insert some records.

Step 3: Write Cursor Program.

TRIGGER EXAMPLE STEP BY STEP

Step 4: Create an audit table.

Step 5: Create a trigger.

Step 6: Test the trigger.

Now check
SELECT * FROM Employee_Audit;
Step 7:A cursor was used to fetch and display employee records satisfying a condition, while a trigger was created to automatically log insert operations into an audit table. This demonstrates how cursors help in handling query results row by row and how triggers ensure automatic enforcement of business rules. Together, they show the importance of procedural extensions in SQL for effective database management.

-- Step 1: Create Employee table
CREATE TABLE Employee (
emp_id NUMBER,
emp_name VARCHAR2(50),
salary NUMBER
);

-- Step 2: Insert sample records
INSERT INTO Employee VALUES (1, 'Rahul', 60000);
INSERT INTO Employee VALUES (2, 'Priya', 45000);
INSERT INTO Employee VALUES (3, 'Kiran', 75000);
COMMIT;

-- Step 3: Cursor Program (fetch employees with salary > 50000)
DECLARE
v_id Employee.emp_id%TYPE;
v_name Employee.emp_name%TYPE;
v_sal Employee.salary%TYPE;

CURSOR c_emp IS
SELECT emp_id, emp_name, salary FROM Employee WHERE salary > 50000;
BEGIN
OPEN c_emp;
LOOP
FETCH c_emp INTO v_id, v_name, v_sal;
EXIT WHEN c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(v_id || ' | ' || v_name || ' | ' || v_sal);
END LOOP;
CLOSE c_emp;
END;
/

-- Step 4: Create Employee_Audit table (for trigger)
CREATE TABLE Employee_Audit (
emp_id NUMBER,
action VARCHAR2(20),
action_time DATE
);

-- Step 5: Create Trigger to log inserts
CREATE OR REPLACE TRIGGER trg_emp_insert
AFTER INSERT ON Employee
FOR EACH ROW
BEGIN
INSERT INTO Employee_Audit VALUES (:NEW.emp_id, 'INSERT', SYSDATE);
END;
/

-- Step 6: Test the trigger
INSERT INTO Employee VALUES (4, 'Sneha', 55000);
COMMIT;

-- Step 7: Check audit log
SELECT * FROM Employee_Audit;

Normalization

Rokesh . S — Mon, 06 Oct 2025 14:40:16 +0000

Step 1: Identify anomalies in the base table

Base 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

Anomalies:

Insertion anomaly: Cannot insert a new instructor without assigning a student.

Update anomaly: Updating InstructorPhone requires multiple rows to be updated (e.g., Dr. Kumar’s phone).

Deletion anomaly: Deleting the last student in a course deletes course & instructor info.

Step 2: Convert to 1NF

1NF Rule: Remove repeating groups and make all attributes atomic.

Our table already has atomic values, so 1NF is essentially the same table. But for clarity, we define a primary key on StudentID + CourseID.
CREATE TABLE StudentCourse_1NF (
StudentID VARCHAR2(10),
StudentName VARCHAR2(50),
CourseID VARCHAR2(10),
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15),
PRIMARY KEY (StudentID, CourseID)
);

Step 3: Convert to 2NF

2NF Rule: Remove partial dependency. Here, StudentName depends only on StudentID and CourseName & Instructor info depends only on CourseID.
CREATE TABLE Students (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(50)
);
CREATE TABLE Courses (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(50),
Instructor VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);
CREATE TABLE Enrollments (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID)
);

Step 4: Convert to 3NF

3NF Rule: Remove transitive dependency. InstructorPhone depends on Instructor, so create a separate Instructors table
CREATE TABLE Students (
StudentID VARCHAR2(10) PRIMARY KEY,
StudentName VARCHAR2(50)
);
CREATE TABLE Instructors (
InstructorID VARCHAR2(10) PRIMARY KEY,
InstructorName VARCHAR2(50),
InstructorPhone VARCHAR2(15)
);
CREATE TABLE Courses (
CourseID VARCHAR2(10) PRIMARY KEY,
CourseName VARCHAR2(50),
InstructorID VARCHAR2(10),
FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID)
);
CREATE TABLE Enrollments (
StudentID VARCHAR2(10),
CourseID VARCHAR2(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

Step 5: Insert sample data into 3NF tables
-- Students
INSERT INTO Students VALUES ('S01', 'Arjun');
INSERT INTO Students VALUES ('S02', 'Priya');
INSERT INTO Students VALUES ('S03', 'Kiran');

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

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

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

Step 6: Query to list all students with courses and instructor names

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.Instruct

MASTERING SQL ON ORACLE LIVE SQL ( COLLEGE STUDENT AND COURSE MANGEMENT SYSTEM )

Rokesh . S — Fri, 22 Aug 2025 09:05:52 +0000

🚀 Mastering SQL on Oracle Live SQL: A Step-by-Step Guide

Working with SQL is a fundamental skill for developers and data professionals. Recently, I explored Oracle Live SQL, a web-based platform for writing and executing SQL scripts without installing any local database. In this post, I’ll walk you through the steps I took — from creating tables to writing queries and procedures.

✅ 1. Creating Tables with Constraints

First, I created multiple tables:

STUDENTS
COURSES
ENROLLMENTS

Here’s an example of creating the Enrollments table with foreign keys referencing Students and Courses:

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

This ensures referential integrity between students, courses, and enrollments.

✅ 2. Inserting Data

Next, I inserted sample data into the Students table:

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Alice Johnson', 'Computer Science', TO_DATE('2002-05-10','YYYY-MM-DD'), 'alice@univ.edu');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Bob Smith', 'Mechanical', TO_DATE('2001-11-15','YYYY-MM-DD'), 'bob@univ.edu');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Clara Adams', 'Physics', TO_DATE('2003-02-20','YYYY-MM-DD'), 'clara@univ.edu');

The TO_DATE function ensures proper date formatting for Oracle DB.

✅ 3. Altering Table Structure

What if you need to add a new column later? You can use ALTER TABLE:

ALTER TABLE Students
ADD PhoneNo NUMBER(10);

This adds a phone number column to the existing table.

✅ 4. Aggregating Data with GROUP BY and HAVING

To get departments with more than two students, I used:

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

This query filters out departments having fewer than three students.

✅ 5. Creating a Stored Procedure

Finally, I created a stored procedure to update student grades:

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;

This makes updating grades easy and reusable.

🔑 Key Learnings:

✔ Use foreign keys for data integrity.
✔ Use ALTER TABLE for schema changes without losing data.
✔ GROUP BY and HAVING are powerful for aggregations.
✔ Stored procedures automate repetitive tasks.

💬 Have you tried Oracle Live SQL or created similar setups? Share your experience in the comments!