DEV Community: ROHITH

🎉 MongoDB Atlas CRUD Operations Made Easy! 🚀

ROHITH — Mon, 06 Oct 2025 05:09:07 +0000

Hey folks! Today, let's play with MongoDB Atlas and Compass UI to handle our student database through CRUD operations: Create, Read, Update, and Delete. Ready? Let’s jump in! 🏊‍♂️

🟢 1. Create – Adding New Students!
Want to add a new student to your collection? Just click “Insert Document” and fill in the details for your student, like student_id, name, department, year, cgpa, and age. Hit the save button and boom – your student joins the list! ✨

🔍 2. Read – Finding Students With Filters

Curious to know which students have a CGPA greater than 8? Just use a filter like:

{ cgpa: { $gt: 8 } }
MongoDB Compass instantly grabs the matching documents for you! 🎯

(See example filter and the highlight of top performers!)

( find students only in the IT department? Use:
{ department: "IT" }
(Here’s the query result filtered for IT students!)

🪄 3. Update – Make a Change!

Need to update a student’s CGPA? Easy! Search for the student by student_id (like S005), click the field (say, CGPA), edit it, and save. The new score is live! 📝

(Watch updating a student's CGPA in action!)

🗑️ 4. Delete – Say Goodbye!

Want to remove a record? Just filter by student_id, click the delete option, and confirm! The document will be flagged for deletion, and once confirmed, it’s wiped from the collection. No more clutter! 🧹

(See the deletion process and confirmation popup!)

CRUD operations in MongoDB Atlas using Compass are super friendly and visual. You can build, explore, modify, and clean your data – all with just a few clicks and simple queries. Hope these screenshots made each step clearer! 🚦

Ready to take control of your data? Drop questions below or share your own CRUD hacks! 🙌

🚀 Speed Up Your SQL: Indexing, Hashing & Query Optimization

ROHITH — Sun, 05 Oct 2025 19:59:49 +0000

Modern databases handle thousands (or millions!) of queries daily. Ever wondered how they stay so fast and efficient? In this post, let’s break down indexing, hashing, and query optimization with SQL and student database examples—powered by actual screenshots!

📑 1. What is Indexing?
Indexes make pinpointing rows in a table super fast—like turning to the right page using a book’s index instead of flipping every page!
You define indexes on columns that are frequently searched or sorted.

In Action:
Let’s create a Students table and add an index for roll_no. This index speeds up lookups by roll number dramatically.

Result: Queries searching by roll_no are now much faster, even on a large student dataset!

⚡ 2. Hashing for Rapid Lookup
Hashing converts data (like a student’s roll number) into a fixed-size value (the hash), so a lookup becomes direct.
Think of hashing as a super-charged lookup table: enter a roll number, instantly get the location!
While general SQL doesn’t expose low-level hash functions for search, many database systems use hash indexes internally on key columns for speedy retrieval—especially on primary keys.

🤖 3. Query Optimization: Smarter Execution
The database query optimizer figures out the fastest way to answer your SQL queries.
It considers available indexes, statistics, filters, and table relationships to use the quickest plan.

Example:

Querying all students from the CSBS department:

Find a record with a specific roll number:

Get all students with a CGPA above 8.0:

These queries are instantly responsive thanks to thoughtful indexing and the optimizer picking the best plan!

🏆 Pro Tips for Developers
Index columns you filter (WHERE), JOIN, or ORDER BY most often
Avoid too many indexes—they slow down INSERTs and consume memory
Regularly analyze and update statistics for your tables
Use query EXPLAIN plans to see which indexes and join methods your database uses

🎯 Final Thoughts
Performance isn’t magic—it’s smart data design!
Using indexing, understanding hashing, and trusting your query optimizer will keep your applications fast, even with growing data.

Questions? Drop them below, or share your own indexing/optimization war stories! 🚦

📊 Exploring SQL Transactions, Deadlocks & Log Based Recovery

ROHITH — Sun, 05 Oct 2025 19:36:25 +0000

Working with databases is more than just storing data—it’s about managing changes reliably! In this post, let’s explore SQL transaction operations step by step, using actual Oracle Live SQL screenshots from a banking accounts scenario.

🏦 Step 1: Creating Accounts
First, we establish our account table and insert Alice, Bob, and Charlie’s starting balances.

Alice: 1000

Bob: 1500

Charlie: 2000

🔀 Step 2: Updating Balances
Transactions are vital for balance adjustments. Check out how we use UPDATE SQL to debit and credit in action.

Alice's account debited by 300:

Bob receives a 500 credit using a transaction block

📋 Step 3: Viewing Account Status
After the transactions, let’s check balances to see instant results! The SELECT * FROM Accounts; SQL displays exactly how each transaction impacts records.

Current balances for Alice, Bob, Charlie:

🔄 Step 4: Rollback and Transaction Control
Sometimes, errors or changes require rolling back the last transaction. The ROLLBACK command ensures that any accidental modifications are safely undone, keeping the database consistent.

🔒 Step 5: Isolation with UPDATE and SELECT FOR UPDATE
Real-world systems often require locking for isolated updates, preventing two users from updating the same record simultaneously. The SQL SELECT ... FOR UPDATE in the screenshots shows how databases handle this:

📝 Final Thoughts
By visualizing each operation, it’s clear how SQL transactions like INSERT, UPDATE, ROLLBACK, and SELECT interact to maintain data integrity.
Try these examples in Oracle Live SQL, and see how transaction control can safeguard your app’s critical data!

Drop questions or share how you use transactions below! 💬

🟢 ACID Properties in Databases: Explained with Real SQL Examples

ROHITH — Sun, 05 Oct 2025 19:19:22 +0000

Databases are everywhere—but how do they guarantee data doesn’t get corrupted or lost, especially when things go wrong? The answer lies in ACID properties: Atomicity, Consistency, Isolation, Durability. Let’s make these simple with hands-on Oracle SQL examples and visuals from a live session! 🚀

1️⃣ Atomicity 💥
Definition: All steps in a transaction succeed or none do. No halfway changes!

SQL Example & Image:
Suppose you want to update a loan and then decide to rollback. The operation either rolls back the whole change, or commits completely—never leaves the DB in a half-done state!

2️⃣ Consistency ✅
Definition: Any transaction must bring the database from one valid state to another, respecting all rules (like constraints).

SQL Example & Image:
If you try to insert a negative loan amount, the system checks the constraint and rejects invalid data. The DB only keeps things consistent!

3️⃣ Isolation 🧩
Definition: Transactions do not interfere with each other. Each runs as if it’s the only one.

SQL Example & Image:
While one transaction is updating a customer’s repayment, no other can disturb its data till it finishes. Operations are shielded from one another!

4️⃣ Durability 🔒
Definition: Once a transaction commits, its data change is permanent—even if there’s a crash right after.

SQL Example & Image:
After making a successful insert and committing the transaction, your data stays—even if the system restarts!

📸 SQL in Action: Table Creations & Updates
You’ll see plenty of these actions in the screenshots:
New loan entries for Alice, Bob, Charlie
Updates to principals and repayments
Constraint checks rejecting bad data!

🧑‍💻 Why ACID Matters for Your Projects
Without ACID, databases risk losing, corrupting, or exposing broken data. Whether you’re building a finance app or a basic record keeper, understanding these four pillars keeps your data reliable!

✨ Final Tips
Always use COMMIT and ROLLBACK wisely.
Set meaningful constraints to guarantee consistency.
Check how your DBMS handles transactions and isolation levels for your use case.

Any questions? Drop them below! And let’s make data safer, one property at a time. 👨‍💻🔥

Understanding Cursors and Triggers in Oracle SQL with Practical Examples

ROHITH — Sun, 05 Oct 2025 19:02:56 +0000

Databases are the backbone of modern applications, enabling efficient data storage, retrieval, and powerful manipulation. When working with Oracle SQL, two powerful features—cursors and triggers—help automate tasks and process data row by row. In this post, I’ll explain both concepts, show you practical examples, and share screenshots of implementation using Oracle Live SQL.

What is a Cursor?
A cursor in SQL acts like a pointer that allows row-by-row processing of result sets. It’s especially useful when you need to perform operations on each row individually, which standard SQL queries can’t accomplish directly.

Example: Printing Employees with High Salaries
Suppose we have an Employee table with fields: EmpID, EmpName, and Salary. Our goal is to print names of employees who earn more than 50,000.

PL/SQL Block:

sql

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;

This script fetches names one by one and prints them if their salary is above 50,000. In my test run, employees Priya, Kiran, and Rahul were displayed.

What is a Trigger?
A trigger is a special type of stored procedure that automatically executes (or “fires”) in response to certain events on a database table or view, such as insert, update, or delete. Triggers are great for enforcing data integrity, logging actions, or handling business logic.

Example: Auditing Student Records on Insert
Let’s say we want to track all inserts into a Students table. We’ll do this by creating an audit table and a trigger.

Step1: Create Audit Table

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

Step2: Create the Trigger

sql
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;
/

Now, every time a new student is inserted into the Students table, their details are copied into Student_Audit with a
timestamp—no manual intervention needed!

Screenshots
List of employees and their salary data (used for cursor demo)
PL/SQL block (cursor) with successful output
Table creation and row insert examples
Student audit table contents after trigger fires

Why Use Cursors and Triggers?
Cursors: Required for row-by-row logic, batch processing, or reporting use cases.

Triggers: Ideal for automatic logging, enforcing rules, and enabling real-time reactions to data changes.

Final Thoughts:
Mastering cursors and triggers unlocks new automation possibilities in your SQL workflow. Try these examples in Oracle Live SQL, and expand the concepts to fit your own applications. If you have questions, drop them in the comments!

Happy querying and automating!

Understanding Normalization in DBMS: Oracle Live SQL Practical Guide

ROHITH — Sun, 05 Oct 2025 18:29:02 +0000

Understanding Normalization in DBMS: Oracle Live SQL Practical Guide
Database normalization is a key concept in designing efficient, consistent, and scalable relational databases. In this post, let's explore normalization—why it's important, the various normal forms, and how to implement normalized structures using Oracle Live SQL with real-world code samples.

What is Normalization?
Normalization is the process of organizing data and tables in a database to reduce redundancy and improve data integrity. By breaking down larger tables into smaller, related tables and defining relationships among them, we can avoid data anomalies and ensure efficient storage.

Why Normalize Your Database?
Reduces data redundancy: Prevents duplicate data.
Prevents update anomalies: Makes updates easier and more consistent.
Improves data integrity: Ensures accuracy and consistency.

Step-by-Step Example: Oracle Live SQL
Let's walk through a practical example with student-course-instructor data.

1.Unnormalized Table
Suppose all data is stored in a single table:

StudentID	StudentName	CourseID	CourseName	InstructorName	InstructorPhone
S01	Arun	C101	Cloud	Dr.sandy	9876533310
S01	Arun	C102	Business Comm	Dr.pritheem	9876555310
S02	manoj	C101	Cloud	Dr.sandy	9876533310

2.First Normal Form (1NF)
Split repeating groups into individual rows so that each field contains atomic values. This is already achieved in our sample.

3.Second Normal Form (2NF)
Remove partial dependencies—every non-key attribute must depend on the full primary key. Break the above into multiple related tables:

STUDENTS Table:

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

INSERT INTO STUDENTS_ VALUES
('S01', 'Arun'),
('S02', 'manoj'),
('S03', 'ramanas');
INSTRUCTORS Table:

sql
CREATE TABLE INSTRUCTORS (
InstructorID INT PRIMARY KEY,
InstructorName VARCHAR(50),
InstructorPhone VARCHAR(15)
);

INSERT INTO INSTRUCTORS VALUES
(1, 'Dr.sandy', '9876533310'),
(2, 'Dr.shanmugam', '9116533310'),
(3, 'Dr.pritheem', '9876555310');
COURSES Table:

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

INSERT INTO COURSES_ VALUES
('C101', 'cloud', 'Dr.sandy', '9876533310'),
('C102', 'business communication', 'Dr.pritheem', '9876555310'),
('C103', 'data science', 'Dr.shanmugam', '9116533310');
STUDENTCOURSES Table (Associative/Mapping Table):

sql
CREATE TABLE STUDENTCOURSES (
StudentID VARCHAR(10),
CourseID VARCHAR(10)
);

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

4.Third Normal Form (3NF)
Remove transitive dependencies. In this example, instructor information is best stored in the INSTRUCTORS table, referenced by course, rather than repeated in COURSES.

How to Query the Normalized Database
Retrieve complete information using JOINs:

sql
SELECT s.StudentName, c.CourseName, i.InstructorName, i.InstructorPhone
FROM StudentCourses sc
JOIN STUDENTS_ s ON sc.StudentID = s.StudentID
JOIN COURSES_ c ON sc.CourseID = c.CourseID
JOIN INSTRUCTORS i ON c.InstructorPhone = i.InstructorPhone;

Sample Result:

StudentName	CourseName	InstructorName	InstructorPhone
manoj	cloud	Dr.sandy	9876533310
Arun	cloud	Dr.sandy	9876533310
Arun	business communication	Dr.pritheem	9876555310
ramanas	data science	Dr.shanmugam	9116533310

Key Takeaways
Normalization structures your database for efficiency and integrity.
Separate data by entities (students, courses, instructors) and use relationships (foreign keys, mapping tables).
Use JOINs to re-combine related data as needed.
Try these SQL snippets in Oracle Live SQL to practice normalization hands-on!

Posted using Oracle Live SQL screenshots and real coding experience. Happy normalizing!

🎓 College Student & Course Management System with SQL (Step by Step)

ROHITH — Thu, 21 Aug 2025 05:05:15 +0000

🎓 College Student & Course Management System with SQL (Step by Step)

Managing students, courses, and faculty is one of the most common real-world scenarios in colleges and universities. In this blog post, we’ll design and implement a College Student & Course Management System using Oracle SQL.

We’ll go through 10 practical SQL tasks that will help you strengthen your skills:
✅ Table creation (DDL)
✅ Data insertion (DML)
✅ Altering & constraints
✅ Queries with functions
✅ Aggregates & GROUP BY
✅ Joins & Views
✅ Stored Procedures

By the end, you’ll have a mini student management system that can be extended for larger projects.

🏗 Database Schema

We start with three core tables:

👨‍🎓 Students → Stores student details

📚 Courses → Stores course information

📝 Enrollments → Many-to-many relationship (students taking multiple courses)

And later, we’ll add Faculty.

🚀 Implementation – Step by Step
1️⃣ Create Faculty Table
CREATE TABLE Faculty (
FacultyID NUMBER PRIMARY KEY,
FacultyName VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
Email VARCHAR2(50) UNIQUE
);

2️⃣ Insert Students
INSERT INTO Students (StudentID, NAME_, Dept, DOB, Email)
VALUES (1, 'Ramana Raj', 'CSE', TO_DATE('2003-05-12', 'YYYY-MM-DD'), 'ramana123@gmail.com');

INSERT INTO Students (StudentID, NAME_, Dept, DOB, Email)
VALUES (2, 'Thimbesh', 'ECE', TO_DATE('2002-09-21', 'YYYY-MM-DD'), 'thimbesh123@gmail.com');

INSERT INTO Students (StudentID, NAME_, Dept, DOB, Email)
VALUES (3, 'Mouliswaran', 'Mechanical', TO_DATE('2001-12-02', 'YYYY-MM-DD'), 'mouliswaran44@gmail.com');

3️⃣ Alter Students Table (Add Phone No.)
ALTER TABLE Students ADD PhoneNo NUMBER(10);

4️⃣ Add Constraint to Courses
ALTER TABLE Courses
ADD CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5);

5️⃣ Functions – Names in UPPERCASE & Email Length
SELECT UPPER(Name) AS StudentNameUpper,
LENGTH(Email) AS EmailLength
FROM Students;

6️⃣ Aggregates – Avg Credits & Total Students Enrolled
SELECT AVG(Credits) AS AvgCredits FROM Courses;

SELECT COUNT(DISTINCT StudentID) AS TotalStudentsEnrolled
FROM Enrollments;

7️⃣ JOIN – Students with Courses & Grades
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;

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

9️⃣ Create View (StudentCoursesView)
CREATE VIEW StudentCoursesView AS
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;

🔟 Stored Procedure – Update Grade
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;

🎯 Summary

In this blog post, we built a mini College Student & Course Management System using SQL:

🏗 Designed tables for Students, Courses, Faculty & Enrollments

✏ Inserted and managed data with DDL & DML

🔒 Applied constraints for data integrity

📊 Queried using functions, aggregates, joins, and group by

👀 Created a View for simplified reporting

⚙ Automated updates with a Stored Procedure

👉 This project covers almost all core SQL concepts and can easily be extended to include:

Attendance tracking

Faculty-course mapping

Timetable management

Reports & dashboards

💡 If you’re preparing for SQL interviews or college DBMS projects, this example is a solid foundation to practice and showcase.