Forem: Saifulhaq S

🚀 MongoDB CRUD Operations with a Student Schema

Saifulhaq S — Wed, 08 Oct 2025 10:15:00 +0000

In this post, we’ll explore how to perform CRUD (Create, Read, Update, Delete) operations in MongoDB using a simple college student schema.
📌 Student Schema
Collection: students
Each student document follows this structure:
{
"student_id": "S001",
"name": "Santhosh",
"age": 20,
"department": "CSBS",
"year": 2,
"cgpa": 9
}
1️⃣ Create (Insert)
Insert at least 5 student records:
db.students.insertMany([
{ "student_id": "S001", "name": "Santhosh", "age": 20, "department": "CSBS", "year": 2, "cgpa": 9 },
{ "student_id": "S002", "name": "Priya", "age": 19, "department": "CSE", "year": 1, "cgpa": 8.5 },
{ "student_id": "S003", "name": "Arun", "age": 21, "department": "ECE", "year": 3, "cgpa": 7.8 },
{ "student_id": "S004", "name": "Meena", "age": 20, "department": "IT", "year": 2, "cgpa": 9.2 },
{ "student_id": "S005", "name": "Ravi", "age": 22, "department": "CSBS", "year": 3, "cgpa": 6.9 }
]);

2️⃣ Read (Query)
👉 Display all student records:
db.students.find({});
👉 Find all students with CGPA > 8:
db.students.find({ cgpa: { $gt: 8 } });
👉 Find students belonging to the Computer Science department:
db.students.find({ department: "CSBS" });

3️⃣ Update
👉 Update the CGPA of a specific student (say S002):
db.students.updateOne(
{ student_id: "S002" },
{ $set: { cgpa: 9.0 } }
);

👉 Increase the year of study for all 3rd year students by 1:
db.students.updateMany(
{ year: 3 },
{ $inc: { year: 1 } }
);

4️⃣ Delete

👉 Delete one student record by student_id:
db.students.deleteOne({ student_id: "S005" });

👉 Delete all students having CGPA < 7.5:
db.students.deleteMany({ cgpa: { $lt: 7.5 } });
📸 Deliverables
MongoDB queries ✅
Screenshots of execution results from MongoDB Atlas ✅
Export of final students collection as JSON/CSV ✅

🚀 Conclusion
With just a few lines of MongoDB queries, we performed CRUD operations on our students collection. This hands-on exercise demonstrates how easy and powerful MongoDB is for handling structured JSON-like documents.
Would love to hear your feedback or see how you adapt this schema for your own use cases! 💡

Indexing, Hashing & Query Optimization in Databases

Saifulhaq S — Wed, 08 Oct 2025 03:36:40 +0000

When working with databases, simply storing data isn’t enough — efficient retrieval is essential. As your dataset scales to thousands or millions of entries, poorly optimized queries can slow down your system. Techniques like indexing and hashing are crucial tools to speed up lookups, just like the index section of a book helps you find topics quickly.

Let’s break down how these work, the differences between them, and when to use each.

⸻

What Is an Index?

An index is a specialized data structure that accelerates access to rows in a database table. Rather than scanning every record to satisfy a query, the database can use the index to “jump” directly to likely matches.

You can think of it like the index pages in a textbook: they don’t contain the whole content, but lead you to exactly where certain topics are discussed.

Types of Indexes
• Primary index: Automatically generated on the primary key column.
• Secondary index: Created by the user (or DBA) on non-primary columns to speed up queries.
• Clustering index: Controls how data is physically ordered on storage media.
• Non-clustering (or non-clustered) index: A separate data structure that points to the actual data without rearranging it.

B-Tree and B+Tree Indexing

Modern databases often use B-Tree or B+Tree structures:
• In B-Tree, both internal and leaf nodes may hold keys and pointers.
• In B+Tree, internal nodes only keep keys, and all actual data pointers reside in leaf nodes.
• Leaf nodes in a B+Tree are typically linked in sequence, making range queries (e.g. “between”) efficient.

These tree structures balance depth and breadth so that lookup, insert, and delete operations remain comparatively fast.

⸻

Hash Indexing

A hash index works differently. It applies a hash function to the key value and uses the resulting hash to determine which “bucket” the record belongs to.
• Strengths: Ideal for exact-match queries like column = constant.
• Weaknesses: Poor for range-based queries (BETWEEN, <, >, etc.) and sorting operations.

Because hashing effectively scatters data across buckets without inherent order, it’s not useful when query patterns involve ordering or searching within ranges.

❌ Not good for:
Range-based queries or sorting operations (like BETWEEN, <, >).

📘 Use Case: When your application frequently runs exact-match lookups.

🧾 Example: Students Table

Let’s create a sample table to see how indexing helps:

CREATE TABLE Students (
roll_no INT PRIMARY KEY,
name VARCHAR(100),
age INT,
grade CHAR(1)
);

Step 1: Create Indexes
-- B-Tree index (default)
CREATE INDEX idx_roll_btree ON Students (roll_no);

-- Hash index (if supported by your DBMS)
CREATE INDEX idx_roll_hash ON Students USING HASH (roll_no);

Step 2: Run Queries
-- Equality check (best for hash or B-tree)
SELECT * FROM Students WHERE roll_no = 50;

-- Range query (best for B-tree or B+Tree)
SELECT * FROM Students WHERE roll_no BETWEEN 10 AND 100;

👉 The B-Tree index handles both cases efficiently,
while the hash index excels only in equality lookups.

📊 When to Use Which Index
Use Case Best Index Reason
Equality lookups (=) Hash or B-Tree Hash is fastest for exact matches
Range queries B-Tree / B+Tree Maintains sorted order
Sequential access B+Tree Linked leaf nodes improve performance
Memory optimization Minimal indexing Too many indexes slow down inserts/updates
⚠️ Important Considerations

Storage Overhead: Every index consumes additional space.

Write Performance: More indexes = slower INSERT, UPDATE, DELETE.

Low-Cardinality Columns: Avoid indexing columns with few unique values (e.g., gender, status).

Maintenance: Indexes can fragment over time and may need rebuilding.

🧠 Query Optimization

Indexes are one of the most effective tools for query optimization.
But you can combine them with:

Query planning: Use EXPLAIN to analyze how queries execute.

Proper filtering: Avoid SELECT *; fetch only what’s needed.

Composite indexes: Combine multiple columns in one index for common query patterns.

✅ Summary
Concept Description
Indexing Data structure for faster lookups
B-Tree / B+Tree Supports ordering and range queries
Hash Indexing Best for equality checks
Query Optimization Uses indexes and execution plans for efficiency
🚀 Final Thoughts

Efficient indexing and query design are what make large-scale applications fast and reliable.
Understanding how and when to use B-Tree or Hash indexes can significantly improve your database performance.

Start small — analyze your queries, create the right indexes, and monitor performance.
A few thoughtful indexes can turn your slowest queries into instant results.

🚀 Indexing, Hashing & Query Optimization in SQL (with Examples)

Saifulhaq S — Mon, 06 Oct 2025 09:38:00 +0000

Database performance is a critical aspect of any application. Indexing, hashing, and query optimization techniques help speed up data retrieval and reduce execution time.
In this post, we’ll explore B-Tree, B+ Tree, and Hash indexing using a Students table.

📌 Step 1: Create the Students Table

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

📌 Step 2: Insert Sample Records

INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101, 'Arun', 'CSE', 8.5),
(102, 'Priya', 'IT', 7.9),
(103, 'Ravi', 'CSBS', 8.2),
(104, 'Sneha', 'ECE', 9.1),
(105, 'Karthik', 'EEE', 6.8),
(106, 'Divya', 'CSE', 7.5),
(107, 'Vikram', 'CSBS', 8.8),
(108, 'Meena', 'IT', 9.0),
(109, 'Hari', 'CSE', 7.2),
(110, 'Nisha', 'CSBS', 8.6),
(111, 'Suresh', 'ECE', 7.4),
(112, 'Lavanya', 'IT', 8.9),
(113, 'Manoj', 'EEE', 6.5),
(114, 'Geetha', 'CSE', 8.1),
(115, 'Ramesh', 'CSBS', 7.8),
(116, 'Anitha', 'IT', 8.4),
(117, 'Saravanan', 'ECE', 9.2),
(118, 'Preethi', 'CSE', 8.7),
(119, 'Ajay', 'CSBS', 7.3),
(120, 'Deepa', 'EEE', 8.0);

📌 Step 3: Create a B-Tree Index on roll_no

CREATE INDEX idx_name ON Students(name);

📌 Step 4: Query with B-Tree Index

SELECT * FROM Students WHERE roll_no = 110;

📌 Step 5: Create a B+ Tree Index on cgpa

CREATE INDEX idx_cgpa ON Students(cgpa);

📌 Step 6: Query with B+ Tree Index

SELECT * FROM Students WHERE cgpa > 8.0;

📌 Step 7: Create a Hash Index on dept

CREATE INDEX idx_dept ON Students(dept) USING HASH;

📌 Step 8: Query with Hash Index

SELECT * FROM Students WHERE dept = 'CSBS';

Cursor + Trigger

Saifulhaq S — Mon, 06 Oct 2025 04:42:07 +0000

🚀 Using Cursor and Trigger in SQL (with Examples)
In SQL, cursors and triggers are powerful features that allow you to process queries row by row and automate actions when data changes. Let’s learn how to use them with practical examples.

🔹 Cursor Example: Employees with Salary > 50,000
We want to display employee names whose salary is greater than 50,000 using a cursor.

Step 1: Employee Table
CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(50),
Salary DECIMAL(10,2)
);

-- Sample Data
INSERT INTO Employee VALUES (1, 'John', 60000);
INSERT INTO Employee VALUES (2, 'Alice', 45000);
INSERT INTO Employee VALUES (3, 'Bob', 75000);
INSERT INTO Employee VALUES (4, 'Emma', 52000);

Step 2: Cursor with Condition

SET SERVEROUTPUT ON;

DECLARE
v_EmpName Employee.EmpName%TYPE; -- variable to hold employee name
CURSOR emp_cursor IS
SELECT EmpName FROM Employee WHERE Salary > 50000;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_EmpName;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee Name: ' || v_EmpName);
END LOOP;
CLOSE emp_cursor;
END;
/

🔹 Trigger Example: AFTER INSERT on Student Table
Whenever a new student is added, we want to log it automatically into an audit table.

Step 1: Create Student & Audit Tables

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

CREATE TABLE Student_Audit ( AuditID INT IDENTITY(1,1) PRIMARY KEY, StudentID INT, ActionTime DATETIME, ActionPerformed VARCHAR(50) );

Step 2: AFTER INSERT Trigger

CREATE OR REPLACE TRIGGER trg_AfterInsert_Student
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, ActionTime, ActionPerformed)
VALUES (:NEW.StudentID, SYSTIMESTAMP, 'INSERT');
END;
/

Step 3: Test the Trigger

INSERT INTO Students VALUES (1, 'Mike', 'CSE');
INSERT INTO Students VALUES (2, 'Sophia', 'IT');

SELECT * FROM Student_Audit;

🎯 Conclusion
Cursor lets us fetch data row by row and apply conditions.
Trigger automates actions after an event (INSERT/UPDATE/DELETE).
Together, they help in data processing and auditing in real-time.

NORMALISATION

Saifulhaq S — Mon, 06 Oct 2025 03:57:40 +0000

Understanding Normalization with SQL – 1NF, 2NF, 3NF
When working with databases, we often face redundancy, inconsistency, and anomalies. Normalization helps solve these issues by organizing data into well-structured tables.

📌 Base Table (Unnormalized Data)

🔎 Anomalies Identified
Insertion: Cannot add a new course unless a student registers.
Update: Changing instructor name requires multiple updates.
Deletion: Deleting a student may remove course data too.

✅ First Normal Form (1NF)
We ensure atomic values and primary keys.

✅ Second Normal Form (2NF)
We remove partial dependencies by separating Students and Courses.

✅ Third Normal Form (3NF)
We remove transitive dependencies by separating Instructors.

📥 Sample Data Insertions

🔗 Query with JOINs

🎯 Conclusion
By applying 1NF → 2NF → 3NF, we eliminated redundancy, prevented anomalies, and achieved a clean database design. This structured design makes querying and maintenance much easier.

Getting Started with SQL: My First Hands-On Experience

Saifulhaq S — Wed, 20 Aug 2025 16:38:16 +0000

Over the past few days, I started learning SQL (Structured Query Language) and performed some of the most essential operations that every beginner should know. Databases power almost every application we use today, and SQL is the language that helps us communicate with them.
I practiced queries on a simple Students–Courses database and here’s what I tried:

Creating Tables (DDL) I started with the CREATE TABLE command to define the structure of my tables. For example: CREATE TABLE Faculty ( FacultyID INT PRIMARY KEY, FacultyName VARCHAR2(100) NOT NULL, Dept VARCHAR2(50), Email VARCHAR2(100) UNIQUE ); This helped me understand how to set primary keys, constraints, and column types.
Inserting Data (DML) Next, I used the INSERT INTO statement to add records into my tables. For example, I added some students into different departments. INSERT INTO Students (StudentID, Name, Dept, DOB, Email) VALUES (1, 'Saifulhaq', 'CSBS', TO_DATE('13-12-2006','DD-MM-YYYY'), 'itsaifulhaq@gmail.com');
Altering Tables I learned how to modify table structures using the ALTER TABLE command. For instance, I added a phone number column: ALTER TABLE Students ADD PhoneNo VARCHAR2(10);
Using Functions in SELECT I also explored SQL functions to manipulate and display data. Example: SELECT UPPER(Name) AS StudentName, LENGTH(Email) AS EmailLength FROM Students; This query shows student names in uppercase and calculates the length of their email IDs.
Aggregate Functions SQL makes it easy to calculate statistics. I tried: SELECT AVG(Credits) AS AvgCredits FROM Courses; SELECT COUNT(*) AS TotalStudents FROM Students;
Joins Finally, I experimented with joins to combine data from multiple tables: SELECT s.Name, c.CourseName, e.Grade FROM Students s JOIN Enrollments e ON s.StudentID = e.StudentID JOIN Courses c ON e.CourseID = c.CourseID; This lists all students with the courses they’re enrolled in and their grades. Final Thoughts 💡 Writing and executing these SQL queries gave me a solid understanding of how relational databases work. It’s one thing to learn the theory, but actually running queries and seeing the results makes it click much faster. I’ll be sharing more of my learning journey soon — especially as I explore advanced SQL queries and dive deeper into database design.

Thank you @santhoshnc sir for your valuable guidance...