DEV Community: Raksh !!

Cursors & Triggers in DBMS

Raksh !! — Tue, 11 Nov 2025 17:56:19 +0000

dbms #powerautomate #cursors

data

Introduction
Databases are the backbone of almost every software application today. From banking systems to e-commerce sites, managing and manipulating data efficiently is crucial. While SQL gives us powerful tools for querying and updating data, there are advanced features in DBMS like Cursors and Triggers that allow us to handle complex situations with ease.

In this blog, we will explore:

What Cursors are, why we need them, and how to use them.
What Triggers are, how they help automate actions, and practical examples.
Cursors in DBMS
What is a Cursor?

A Cursor in DBMS is a database object that allows us to fetch and process query results row by row. Normally, SQL works with sets of data all at once, but sometimes we need to handle each row individually. That’s where cursors come in.

Think of it like a pointer that moves through the rows of a result set one at a time.

Why Use Cursors?

When we need row-by-row processing.
To perform custom logic on each record.
To store results temporarily and use them later.
Example: Cursor for High Salary Employees
Suppose we have an Employee table:

CREATE TABLE Employee (
emp_id INT PRIMARY KEY,
emp_name VARCHAR(100),
salary DECIMAL(10,2)
);

INSERT INTO Employee VALUES
(1, 'Bob Smith', 60000),
(2, 'Charlie Brown', 55000),
(3, 'Edward Wilson', 75000),
(4, 'Fiona Davis', 52000),
(5, 'Hannah Lee', 60000);
(6,'naveens',67000);
Now, let’s create a stored procedure with a cursor that selects employees earning more than 50,000.

DELIMITER $$

CREATE PROCEDURE get_high_salary_employees()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE v_emp_name VARCHAR(100);

DECLARE emp_cursor CURSOR FOR
    SELECT emp_name FROM Employee WHERE salary > 50000;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

CREATE TABLE IF NOT EXISTS high_salary_emp(emp_name VARCHAR(100));
TRUNCATE TABLE high_salary_emp;

OPEN emp_cursor;

read_loop: LOOP
    FETCH emp_cursor INTO v_emp_name;
    IF done = 1 THEN
        LEAVE read_loop;
    END IF;
    INSERT INTO high_salary_emp VALUES (v_emp_name);
END LOOP;

CLOSE emp_cursor;

SELECT * FROM high_salary_emp;

END$$

DELIMITER**** ;

CALL get_high_salary_employees();
Output:

This shows how a cursor fetches each row one by one and stores it into another table.

Triggers in DBMS
A Trigger is a special type of stored program that automatically executes when a specific event occurs in a table. Events can be:

INSERT
UPDATE
DELETE
In simple words, triggers are rules that fire automatically when data changes.

Why Use Triggers?

To maintain data integrity.
To automatically log changes.
To enforce business rules.
Example 1: AFTER INSERT Trigger (Audit Log)
We want to keep track of all new employees added. So, we create an audit table:

CREATE TABLE Employee_Audit (
audit_id INT AUTO_INCREMENT PRIMARY KEY,
emp_id INT,
emp_name VARCHAR(100),
salary DECIMAL(10,2),
action_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
Now, the trigger:

DELIMITER $$

CREATE TRIGGER after_employee_insert
AFTER INSERT ON Employee
FOR EACH ROW
BEGIN
INSERT INTO Employee_Audit (emp_id, emp_name, salary)
VALUES (NEW.emp_id, NEW.emp_name, NEW.salary);
END$$

DELIMITER ;
Test it:

INSERT INTO Employee VALUES (34, 'Meera', 72000);

SELECT * FROM Employee_Audit;
Output shows Meera automatically logged in Employee_Audit.

Conclusion

Cursors let us handle query results row by row, useful for special operations.
Triggers allow us to automatically enforce rules or log changes when data is inserted, updated, or deleted.
Together, they give DBMS more power, automation, and data integrity.

Acid Properties in SQL

Raksh !! — Tue, 11 Nov 2025 17:43:39 +0000

Understanding ACID Properties in SQL Transactions

beginners #database #sql #architecture

Intro
When working with databases, data integrity is everything. Imagine transferring money from one account to another — you wouldn’t want money to disappear or double by mistake, right?

That’s where ACID properties come in. They ensure our transactions are reliable, safe, and consistent, even when things go wrong.

In this blog, let’s break down ACID with SQL examples using a simple Accounts

What is ACID?
ACID stands for:

Atomicity → All or nothing (no partial transactions).
Consistency → Data must remain valid before and after a transaction.
Isolation → Transactions run independently of each other.
Durability → Once committed, changes are permanent (even after crash).
Setting up a Table

We’ll create an Accounts table with a balance check to avoid negative values.

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

INSERT INTO Accounts VALUES
(101, 'Santhosh', 5000),
(102, 'Ganges', 3000),
(103, 'Rakshanth', 7000),
(104, 'Jaswant', 6000);
Atomicity
Atomicity ensures that either the entire transaction happens or nothing happens.

Example: Money transfer (Rollback midway)

START TRANSACTION;

-- Deduct ₹1000 from Alice
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;

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

-- Oops! Something went wrong
ROLLBACK;

-- Check balances (should remain unchanged)
SELECT * FROM Accounts;
Since we rolled back, no changes were applied. That’s Atomicity in action!

Consistency
Consistency ensures that a transaction brings the database from one valid to another.

Example: Insert a record with negative balance

INSERT INTO Accounts VALUES (104, 'Naveens', -500);
This fails because of our CHECK (balance >= 0) constraint.
The database remains valid → Consistency maintained.

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

Imagine two sessions running at the same time:

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 2000 WHERE acc_no = 103;
-- Don’t commit yet
Session 2:

SELECT * FROM Accounts WHERE acc_no = 103;
On READ COMMITTED → Session 2 sees old balance.
On READ UNCOMMITTED → Session 2 may see uncommitted changes (dirty read).
On SERIALIZABLE → Session 2 waits until Session 1 commits.
Different isolation levels decide how much transactions can "see" each other’s work.

Durability
Durability ensures that once a transaction is committed, it survives crashes.

START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 102;
COMMIT;
Now restart your database and run:

SELECT * FROM Accounts WHERE acc_no = 102;
You’ll still see the updated balance.
That’s Durability — committed data is permanent.

Final Thoughts

The ACID properties are the backbone of reliable databases.

Atomicity → No partial updates
Consistency → Always valid data
Isolation → Independent transactions
Durability → Permanent changes
So next time you run a SQL transaction, remember: ACID is silently ensuring your data stays safe, even if things go wrong.

Transactions , Deadlocks & Log Based Recovery in SQL

Raksh !! — Tue, 11 Nov 2025 17:27:00 +0000

#acid #deadlock #logging #sql

Intro
In this blog, let’s explore three very important database concepts: Transactions, Deadlocks, and Log-Based Recovery. We’ll be using a simple Accounts table and running SQL queries to demonstrate these in action.

Schema Setup
First, let’s create a table Accounts and insert some sample data.

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);
acc_no name balance
1 Alice 1000
2 Bob 1500
3 Charlie 2000
Transaction – Atomicity & Rollback
Transactions are all-or-nothing operations. Either all changes are applied (commit) or none (rollback).

Let’s try transferring 500 from Alice to Bob but roll it back before committing.

-- Start transaction
START TRANSACTION;

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

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

-- Check balances before rollback
SELECT * FROM Accounts;

-- Rollback transaction
ROLLBACK;

-- Check balances after rollback
SELECT * FROM Accounts;
Result: Alice still has 1000, Bob has 1500.
No partial update happened – this is Atomicity.

Deadlock Simulation
Deadlocks happen when two transactions are waiting for each other’s resources.

We’ll simulate this using two sessions:

Session 1
START TRANSACTION;
-- Lock Bob
UPDATE Accounts SET balance = balance - 50 WHERE name = 'Bob';

-- Now try updating Alice (but Alice is locked by Session 1)
UPDATE Accounts SET balance = balance + 50 WHERE name = 'Alice';
At this point, both sessions are waiting for each other → Deadlock occurs.
The database will detect this automatically and kill one transaction, rolling it back.

Log-Based Recovery
Databases maintain logs (like binary log in MySQL or WAL in PostgreSQL) to ensure durability and recovery.

Let’s test this:

-- Start transaction
START TRANSACTION;

-- Update a record
UPDATE Accounts SET balance = balance + 200 WHERE name = 'Charlie';

-- Rollback
ROLLBACK;
Even though the update was rolled back, the log will record both the update and the undo operation.
This ensures that if the system crashes, recovery can undo uncommitted transactions and redo committed ones.

Key Takeaways

Atomicity: All or nothing – rollback prevents partial updates.
Deadlock: Two transactions waiting for each other’s lock → system resolves it.
Log-Based Recovery: Every change is logged so the DB can recover from crashes.

Indexing, Hashing & Query Optimization in SQL

Raksh !! — Tue, 11 Nov 2025 17:20:33 +0000

database #performance #algorithms

sql
Intro
When working with large databases, retrieving data efficiently becomes a big challenge. SQL provides indexing and hashing techniques to speed up query execution. In this blog, we’ll explore B-Tree, B+ Tree, and Hash indexes with hands-on SQL examples using a Students table.

Create the Students Table

CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(50),
dept VARCHAR(20),
cgpa DECIMAL(3,2)
);
Insert Sample Records

INSERT INTO Students VALUES
(101, 'Aarav', 'CSBS', 8.5),
(102, 'Meera', 'ECE', 7.2),
(103, 'Rohan', 'MECH', 6.9),
(104, 'Sita', 'CIVIL', 8.1),
(105, 'Vikram', 'CSE', 9.0),
(106, 'Priya', 'IT', 8.3),
(107, 'Arjun', 'CSBS', 7.5),
(108, 'Neha', 'ECE', 8.7),
(109, 'Kiran', 'CSE', 6.8),
(110, 'Rahul', 'CSBS', 9.2),
(111, 'Sneha', 'MECH', 7.9),
(112, 'Dev', 'CIVIL', 8.4),
(113, 'Pooja', 'CSE', 7.3),
(114, 'Varun', 'IT', 8.6),
(115, 'Isha', 'ECE', 9.1),
(116, 'Nikhil', 'MECH', 7.6),
(117, 'Ritu', 'CIVIL', 6.5),
(118, 'Sameer', 'CSE', 8.8),
(119, 'Tina', 'CSBS', 7.8),
(120, 'Yash', 'IT', 9.3);
Now we have 20 student records.

Create a B-Tree Index on roll_no
B-Tree indexes are default in most RDBMS. They make searching on primary/unique columns faster.

CREATE INDEX idx_rollno_btree ON Students(roll_no);

The DBMS uses the B-Tree index to find roll_no = 110 quickly without scanning the whole table.

Create a B+ Tree Index on cgpa
B+ Trees are widely used for range queries.

CREATE INDEX idx_cgpa_bplustree ON Students(cgpa);

Instead of checking each row, the B+ Tree index helps to traverse efficiently for CGPA values greater than 8.5 .

Create a Hash Index on dept
Hash indexes are best for equality lookups (e.g., = condition).

CREATE INDEX idx_dept_hash ON Students(dept) USING HASH;

Query Optimization

Without indexes → Full Table Scan (slow for large datasets).
With indexes → Optimized Execution Plan (quick lookup).
Use EXPLAIN to check whether your query uses the index.
Final Thoughts

B-Tree Index → Best for unique lookups & ordering.
B+ Tree Index → Best for range queries.
Hash Index → Best for equality lookups.
By using indexes wisely, we can reduce query execution time drastically in real-world applications.

Crud Operations in Mongodb

Raksh !! — Tue, 11 Nov 2025 17:14:58 +0000

CRUD OPERATIONS IN MONGODB

mongodb

database

data

coding
INTRODUCTION
MongoDB is one of the most popular NoSQL databases, widely used for its flexibility and scalability. Unlike relational databases, MongoDB stores data in collections as JSON-like documents.

In this blog, we’ll perform CRUD (Create, Read, Update, Delete) operations on a simple college student schema to understand MongoDB basics hands-on.

Here we are using MongoDB Shell for scripting in MongoDB Compass
run all the commands in MongoDB Shell. this will work great.

Student Schema
We’ll use a collection called students, and each document follows this basic structure:

{

Create (Insert)
Inserting 5 student records:

This will create 5 documents in the students collection.

Read (Query)
Display all student records:

db.students.find()

Find all students with CGPA > 8:

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

1NF , 2NF, 3NF in DBMS...

Raksh !! — Wed, 08 Oct 2025 05:12:20 +0000

//1 NF
CREATE TABLE StudentCourses (
StudentID VARCHAR(10),
StudentName VARCHAR(50),
CourseID VARCHAR(10),
CourseName VARCHAR(50),
Instructor VARCHAR(50),
InstructorPhone VARCHAR(15)
);

INSERT INTO StudentCourses VALUES ('S01', 'Arjun', 'C101', 'DBMS', 'Dr. Kumar', '9876543210');
INSERT INTO StudentCourses VALUES ('S01', 'Arjun', 'C102', 'Data Mining', 'Dr. Mehta', '9123456780');
INSERT INTO StudentCourses VALUES ('S02', 'Priya', 'C101', 'DBMS', 'Dr. Kumar', '9876543210');
INSERT INTO StudentCourses VALUES ('S03', 'Kiran', 'C103', 'AI', 'Dr. Rao', '9988776655');

///2 NF
CREATE TABLE Student (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

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

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

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

CREATE TABLE StudentCourse (
StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

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

////3 NF

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

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

CREATE TABLE Course (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
Instructor VARCHAR(50)
);

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

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

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

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

College Student and course management system

Raksh !! — Mon, 25 Aug 2025 17:27:27 +0000

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

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Arun Kumar', 'CSE', DATE '2003-05-14', 'arun.k@college.edu');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Priya Sharma', 'ECE', DATE '2002-08-20', 'priya.s@college.edu');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Rahul Verma', 'MECH', DATE '2001-12-10', 'rahul.v@college.edu');

ALTER TABLE Students
ADD PhoneNo NUMBER(10);

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

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

SELECT AVG(Credits) AS AvgCredits,
(SELECT COUNT(*) FROM Students) AS TotalStudents
FROM Courses;

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

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

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

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 Course