DEV Community: Tharani Tharan

CRUD (Create, Read, Update, Delete)

Tharani Tharan — Mon, 06 Oct 2025 10:19:25 +0000

I’ve explored MongoDB CRUD (Create, Read, Update, Delete) operations using a simple college student schema. This project helped me understand how to perform real-world database operations using MongoDB Atlas.

INDEXING - HASHING - AND -QUERY

Tharani Tharan — Sun, 05 Oct 2025 13:38:21 +0000

Indexing is one of the most powerful ways to boost the performance of your SQL queries. In this tutorial, we’ll explore B-Tree, B+Tree, and Hash indexes step-by-step using a Students table example.

We’ll:

Create a Students table
Insert sample data
Build three types of indexes
Run queries using each index
Observe how indexing improves retrieval speed

Create the Table

Insert Sample Records

Create a B-Tree Index on roll_no

Query Using the B-Tree Index

Query Using the B+ Tree Index

Query Using the Hash Index

Conclusion

In this blog, we learned how to:
-Create B-Tree, B+Tree, and Hash indexes
-Execute optimized queries
-Understand which index type fits which use case

Indexes make retrieval blazing fast but also consume storage and slow down updates slightly. So, always index wisely!

ACID Properties in DBMS

Tharani Tharan — Sun, 05 Oct 2025 13:32:32 +0000

Databases power almost every application we use daily—from banking systems to e-commerce platforms. To ensure data remains reliable, databases follow the ACID principles: Atomicity, Consistency, Isolation, and Durability.

Setup the Schema
We’ll create a Loans table that stores customer loan detail

Enter the values

Result

ATOMICITY
All operations in a transaction must succeed or none should.

Update and roll back

Consistency

Transactions must bring the database from one valid state to another. Invalid data should not enter the system.

Alter

Isolation

Concurrent transactions should not affect each other’s intermediate results.

Isolation prevents dirty reads between transactions.

Durability

Atomicity → All or nothing.
Consistency→ Data always valid.
Isolation → Transactions don’t interfere.
Durability→ Data persists after commit.
By applying ACID principles, databases remain reliable, fault-tolerant, and consistent even in high-concurrency environments like banking systems.

Transactions, Deadlocks & Log Based Recovery

Tharani Tharan — Sun, 05 Oct 2025 13:22:40 +0000

In this tutorial, we’ll explore transactions, deadlocks, and log-based recovery using a simple Accounts table.

Let’s create a table

insert values

Transaction – Atomicity & Rollback

ROLLBACK

UPDATE Accounts

Deadlock Simulation

Deadlocks occur when two transactions block each other waiting for resources.

RESULT
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

Log-Based Recovery

Modern DBMS (MySQL/PostgreSQL) automatically maintain transaction logs. These logs help undo changes if a transaction fails.

UPDATE Accounts

SELECT * FROM Accounts;

Summary

Transactions guarantee atomicity; either all operations succeed or none.
Deadlocks occur when transactions block each other; they must be handled with care.
Log-based recovery ensures durability and recoverability.

Cursor and Trigger

Tharani Tharan — Sun, 05 Oct 2025 13:04:37 +0000

Cursor with condition → Fetch employee names whose salary > 50,000.

AFTER INSERT Trigger → Log every new student entry into an audit table.

Cursor Example (Process Cursor with Condition)

CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary INT
);

INSERT INTO Employee VALUES
(1, 'Arjun', 45000),
(2, 'Priya', 60000),
(3, 'Kiran', 75000),
(4, 'Meera', 48000),
(5, 'Rahul', 90000);

Output

Cursor Code (Employees with Salary > 50,000)
DECLARE
CURSOR high_salary_cursor IS
SELECT EmpName FROM Employee WHERE Salary > 50000;
v_name Employee.EmpName%TYPE;
BEGIN
OPEN high_salary_cursor;
LOOP
FETCH high_salary_cursor INTO v_name;
EXIT WHEN high_salary_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
END LOOP;
CLOSE high_salary_cursor;
END;

Trigger Example (AFTER INSERT Trigger)
Create Student and Audit Tables
CREATE TABLE Studt (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

CREATE TABLE Student_Audit (
AuditID INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
StudentID VARCHAR(10),
StudentName VARCHAR(50),
ActionDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

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

Insert Data into Studt
INSERT INTO Students VALUES ('S01', 'Arjun');
INSERT INTO Students VALUES ('S02', 'Priya');
INSERT INTO Students VALUES ('S03', 'Kiran');

SELECT * FROM Student_Audit;

SQL Normalization (1NF to 3NF) – Explained with Oracle Live SQL Implementation

Tharani Tharan — Sun, 05 Oct 2025 12:55:44 +0000

Base Table (Unnormalized)

We start with a single table that contains redundant data (students, courses, and instructors in one 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
1NF Implementation

Remove repeating groups and ensure atomicity.

2NF Implementation

Eliminate partial dependencies by separating students and courses

3NF Implementation

Remove transitive dependencies (InstructorPhone depends on Instructor, not Course).

Oracle Live SQL Screenshots

Normalization helps:

Remove redundancy

Maintain data integrity

Simplify updates and retrieval

Implemented using:

🔹 Oracle Live SQL
🔹 SQL DDL + JOIN Queries

🚀 Learning SQL with Oracle Live SQL – Student Database Example

Tharani Tharan — Fri, 22 Aug 2025 03:14:01 +0000

Hello Dev Community! 👋
Today I want to share my hands-on practice with SQL using Oracle Live SQL. I created a simple Students database schema and explored various SQL commands to strengthen my database skills. 🧑‍💻

🏗 Step 1: Creating the Students Table

I started by creating a table called Students with important constraints:

CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
Name VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
DOB DATE,
Email VARCHAR2(50) UNIQUE
);

🔑 Key Points:

PRIMARY KEY ensures each student has a unique ID.

NOT NULL prevents empty names.

UNIQUE on Email avoids duplicates.

📞 Step 2: Adding a Phone Number Column

Later, I altered the table to include a PhoneNo column that can store 10-digit numbers:

ALTER TABLE Students
ADD PhoneNo NUMBER(10);

This way, each student record can also include a contact number.

✍ Step 3: Inserting Data

I inserted sample records into the table, making sure each student belongs to a different department:

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Arjun Kumar', 'Computer Science', TO_DATE('2003-05-15', 'YYYY-MM-DD'), 'arjun.kumar@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Meera Sharma', 'Electronics', TO_DATE('2002-11-20', 'YYYY-MM-DD'), 'meera.sharma@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Rahul Singh', 'Mechanical', TO_DATE('2004-02-10', 'YYYY-MM-DD'), 'rahul.singh@example.com');

🔎 Step 4: Playing with Queries

I experimented with some interesting queries:

✅ Display names in UPPERCASE & email length

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

✅ Show each department with student count (only if more than 2 students)

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

🎯 Key Learnings

How to create and alter tables with constraints

Using aggregate functions with GROUP BY and HAVING

Applying string functions like UPPER() and LENGTH()

Writing meaningful queries to analyze data

🌟 Final Thoughts

This small project was a great way to solidify my SQL fundamentals. Practicing on Oracle Live SQL helped me understand how constraints, functions, and queries work in real scenarios.

💡 Next, I plan to extend this schema by adding a Courses table and explore JOIN operations.

👉 What do you think about this simple schema?
Would love to hear your suggestions and improvements!

SQL #Oracle #Database #Learning #DevCommunity #100DaysOfCode

Would you like me to also add an ER diagram illustration for the blog (like Students table structure) so the post looks more visual on dev.to?