Forem: NISHANTH MOORTHI 24CB036

MongoDB CRUD

NISHANTH MOORTHI 24CB036 — Wed, 08 Oct 2025 14:48:37 +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.

TRANSACTION

NISHANTH MOORTHI 24CB036 — Wed, 08 Oct 2025 14:44:36 +0000

Understanding MySQL Transactions with Row-Level Locking: A Hands-On Guide
When working with relational databases, ensuring data consistency during concurrent operations is critical. MySQL offers powerful transaction control and row-level locking mechanisms that can help developers maintain integrity even in multi-user environments.

In this post, I’ll walk through a few real-world examples using MySQL’s START TRANSACTION, SELECT ... FOR UPDATE, and ROLLBACK commands to demonstrate how locking and rollback work in practice.

Scenario 1: Locking Rows with SELECT ... FOR UPDATE
sql
START TRANSACTION;

-- Lock Alice's row
SELECT * FROM Accounts WHERE name = 'Alice' FOR UPDATE;

-- Update Bob's balance (allowed if not locked by another session)
UPDATE Accounts SET balance = balance + 10 WHERE name = 'Bob';
Insight: SELECT ... FOR UPDATE locks the selected row until the transaction is committed or rolled back. This prevents other sessions from modifying it, ensuring safe concurrent operations.

Scenario 2: Rolling Back a Transaction
sql
START TRANSACTION;

Transfer funds from Alice to Bob
UPDATE Accounts SET balance = balance - 500 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 500 WHERE name = 'Bob';

something went wrong? Roll it back!
ROLLBACK;

Verify balances
SELECT * FROM Accounts;
Insight: ROLLBACK undoes all changes made during the transaction. This is especially useful when an error occurs mid-operation or a condition fails validation.

Scenario 3: Locking Bob, Updating Alice
sql
START TRANSACTION;

Lock Bob's row
SELECT * FROM Accounts WHERE name = 'Bob' FOR UPDATE;

Update Alice's balance
UPDATE Accounts SET balance = balance + 20 WHERE name = 'Alice';

Insight: Locking one row doesn’t block updates to other rows unless explicitly locked. This allows for fine-grained concurrency control.

HASHING

NISHANTH MOORTHI 24CB036 — Wed, 08 Oct 2025 14:43:24 +0000

Getting Hands-On with Oracle Live SQL: My Journey into Database Fundamentals
Hey devs!

I recently dove into Oracle Live SQL to sharpen my database skills, and I wanted to share some of the cool things I learned—especially around clusters, indexing, and querying data efficiently.

Creating a Clustered Setup
I started by experimenting with clusters, which allow related tables to be stored together physically. Here's a snippet of what I tried:

sql
CREATE CLUSTER emp_dept_cluster (
deptno NUMBER(3)
)
SIZE 512;

CREATE INDEX idx_emp_dept_cluster
ON CLUSTER emp_dept_cluster;
Then I created two tables—dept and emp—that share the deptno key and are clustered together:

sql
CREATE TABLE dept (
deptno NUMBER(3),
dname VARCHAR2(14),
loc VARCHAR2(13)
)
CLUSTER emp_dept_cluster (deptno);

CREATE TABLE emp (
empno NUMBER(4),
ename VARCHAR2(10),
job VARCHAR2(9),
mgr NUMBER(4),
hiredate DATE,
sal NUMBER(7,2),
comm NUMBER(7,2),
deptno NUMBER(3)
)
CLUSTER emp_dept_cluster (deptno);
Pro tip: If you get ORA-00955: name is already used by an existing object, it means you’ve already created that object—just drop it or rename it before retrying.

Building a Student Table and Querying Data
Next, I created a simple student table and practiced inserting and retrieving data:

sql
CREATE TABLE student (
roll_no NUMBER,
name VARCHAR2(20),
dept VARCHAR2(10),
cgpa NUMBER
);

INSERT INTO student VALUES (101, 'Alice', 'CS', 8.5);
INSERT INTO student VALUES (110, 'Bob', 'IT', 7.8);
-- more inserts...
SELECT * FROM student;
This helped me understand how to structure data and run basic queries.

Indexing for Performance
I also explored how indexes improve query performance:

sql
-- Primary key index is implicit
CREATE TABLE student (
roll_no NUMBER PRIMARY KEY,
name VARCHAR2(50),
dept VARCHAR2(50),
cgpa NUMBER(3,2)
);

-- Explicit index on name
CREATE INDEX idx_name ON student(name);

-- Composite index
CREATE INDEX idx_dept_cgpa ON student(dept, cgpa);
Using these indexes, I ran queries like:

sql
SELECT * FROM student WHERE name = 'Jack';
SELECT * FROM student WHERE dept = 'CSE' AND cgpa = 9.5;
The results were fast and efficient—Oracle Live SQL even shows you which indexes are being used!

Categorizing with CASE Statements
Finally, I played with the CASE clause to categorize products based on price:

sql
SELECT product_id, product_name,
CASE
WHEN list_price < 200 THEN 'Low'
WHEN list_price BETWEEN 200 AND 500 THEN 'Medium'
ELSE 'High'
END AS cost
FROM products
ORDER BY cost;
This is a great way to add logic directly into your queries.

ACID

NISHANTH MOORTHI 24CB036 — Wed, 08 Oct 2025 14:39:18 +0000

Demystifying SQL Constraints with Oracle Live SQL
Hey Devs

I’ve been diving into Oracle Live SQL lately, and it’s been a fascinating ride through the world of data integrity and transaction control. Thought I’d share a few nuggets from my journey—especially around constraints and how they shape reliable databases.

Why Constraints Matter
When designing tables, constraints are your first line of defense against bad data. Here's a simple example I used:

sql
CREATE TABLE ACCOUNTS (
account_id NUMBER,
account_type VARCHAR2(20),
balance NUMBER,
CONSTRAINT acc_pk PRIMARY KEY (account_id),
CONSTRAINT acc_balance_ck CHECK (balance >= 0) -- No negative balances!
);
This ensures:

Every account has a unique ID.

No one can sneak in a negative balance (because chaos isn’t a valid business model).

Transactions: Merge or Mayhem?
I also played around with inserting duplicate records and wrapping them in a transaction block. It’s a great way to test how your logic handles real-world scenarios like merging balances or rejecting duplicates.

sql
BEGIN
INSERT INTO Accounts VALUES ('David', 3000);
INSERT INTO Accounts VALUES ('David', 4000);
END;
Depending on your schema design, this could either:

Merge balances intelligently

Or throw a constraint violation

Pro Tip: Test Minimal Inserts
Try inserting a record without all required fields and see how your constraints react:

sql
INSERT INTO Accounts (acc_no, name)
VALUES (101, 'Pranav');
If your table requires a balance, this should fail—or default—depending on how you've set it up.

Cursor And Trigger

NISHANTH MOORTHI 24CB036 — Wed, 08 Oct 2025 14:37:31 +0000

My SQL Learning Journey: From Tables to Triggers
Hi Devs! I’m Nishanth Moorthi, and over the past few weeks, I’ve been diving deep into SQL using platforms like Oracle Live SQL and W3Schools. What started as curiosity quickly turned into a full-blown passion for understanding how data flows through relational databases. In this post, I’ll walk you through some of the key concepts I’ve learned—complete with code snippets and real examples.

Creating Tables Like a Pro
One of the first things I tackled was table creation. Here's a simple example:

sql
CREATE TABLE STUDENT (
StudentName VARCHAR(50),
StudentRollNo NUMBER(5),
StudentMarks NUMBER(5),
StudentGrade VARCHAR(2),
StudentResult VARCHAR(10)
);
Seeing “Table STUDENT created” in the output gave me a real sense of accomplishment!

Understanding Relationships with Foreign Keys
I then moved on to creating related tables:

sql
CREATE TABLE Students (
StudentID NUMBER PRIMARY KEY,
Name VARCHAR2(50),
Age NUMBER
);

CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50)
);

CREATE TABLE Enrollments (
EnrollmentID NUMBER PRIMARY KEY,
StudentID NUMBER,
CourseID NUMBER,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
This helped me understand how relational databases maintain integrity across tables.

Filtering Data with Date Ranges
I also learned how to filter records using BETWEEN and TO_DATE:

sql
SELECT auditid, studentname, actiondate
FROM student_audit
WHERE actiondate BETWEEN TO_DATE('01-OCT-2015','DD-MON-YYYY')
AND TO_DATE('05-OCT-2015','DD-MON-YYYY');
This query gave me a filtered view of student actions within a specific timeframe.

Auditing with Triggers
One of the coolest things I built was a trigger to audit changes in the STUDENTS table:

sql
CREATE OR REPLACE TRIGGER trg_student_audit
AFTER INSERT OR UPDATE OR DELETE ON STUDENTS
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO STUDENT_AUDIT (...) VALUES (...);
ELSIF UPDATING THEN
INSERT INTO STUDENT_AUDIT (...) VALUES (...);
ELSIF DELETING THEN
INSERT INTO STUDENT_AUDIT (...) VALUES (...);
END IF;
END;
This gave me a behind-the-scenes look at how changes are tracked in real-world systems.

Using Cursors in Procedures
Finally, I explored PL/SQL and created a procedure with a cursor:

sql
CREATE OR REPLACE PROCEDURE emp_proc IS
CURSOR emp_cursor IS SELECT empno, ename, sal FROM emp;
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('Empno: ' || emp_record.empno || ' Name: ' || emp_record.ename || ' Salary: ' || emp_record.sal);
END LOOP;
CLOSE emp_cursor;
END;
/
EXEC emp_proc;
Running this felt like unlocking

NORMALIZATION

NISHANTH MOORTHI 24CB036 — Wed, 08 Oct 2025 14:31:45 +0000

Getting Started with SQL: My Journey from Zero to Table Hero
Hey Devs! I recently dove into the world of SQL, and I wanted to share some insights, tips, and code snippets that helped me grasp the fundamentals of relational databases. Whether you're a total beginner or brushing up your skills, I hope this post gives you a solid starting point.

Why Learn SQL?
SQL (Structured Query Language) is the backbone of data manipulation. Whether you're building apps, analyzing data, or just curious about how databases work, SQL is a must-have skill.

Tools I Used
Oracle Live SQL: A free, browser-based platform to write and execute SQL scripts.

W3Schools SQL Editor: Great for quick practice and tutorials.

Both platforms offer interactive environments and instant feedback, which made learning super intuitive.

Sample Tables I Created
Here’s a snippet from one of my practice sessions:

sql
-- Creating an Employee table
CREATE TABLE Employee (
EmployeeID NUMBER PRIMARY KEY,
FirstName VARCHAR2(50),
LastName VARCHAR2(50),
Department VARCHAR2(50),
Salary NUMBER
);

-- Inserting sample data
INSERT INTO Employee VALUES (1, 'John', 'Doe', 'HR', 50000);
INSERT INTO Employee VALUES (2, 'Jane', 'Smith', 'Finance', 60000);

Joining Tables Like a Pro
Once I got comfortable with basic queries, I explored JOINs to combine data across tables. Here's a query that pulls student info along with course and instructor details:

sql
SELECT STUDENTID, STUDENTNAME, COURSEID, COURSENAME, INSTRUCTORNAME
FROM STUDENT S
JOIN COURSE C ON S.COURSEID = C.COURSEID
JOIN INSTRUCTOR I ON C.INSTRUCTORID = I