DEV Community: SAKTHI SREE N S 24CB051

mongoDB CRUD OPERATION IN SQL

SAKTHI SREE N S 24CB051 — Sun, 05 Oct 2025 16:48:48 +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 IN DBMS

SAKTHI SREE N S 24CB051 — Sun, 05 Oct 2025 16:40:33 +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 IN SQL

SAKTHI SREE N S 24CB051 — Sun, 05 Oct 2025 16:31:40 +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 PROPERTY IN SQL

SAKTHI SREE N S 24CB051 — Sun, 05 Oct 2025 16:21: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 IN SQL

SAKTHI SREE N S 24CB051 — Sun, 05 Oct 2025 16:12:25 +0000

My Hands-On Journey with Oracle Live SQL: Learning by Doing
Hey devs!

I recently started exploring Oracle’s Live SQL platform to level up my database skills, and I wanted to share some of my learnings, code snippets, and a few surprises I ran into. If you're diving into SQL or curious about Oracle's ecosystem, this post might be just what you need.

What Is Oracle Live SQL?
Oracle Live SQL is a browser-based SQL playground—no installation required. You can write, run, and test SQL and PL/SQL scripts instantly. It’s perfect for learners, hobbyists, and pros who want to prototype or practice.

What I Built

Creating Tables and Inserting Data sql CREATE TABLE EMPLOYEE ( EmpID NUMBER, EmpName VARCHAR2(50), EmpAge NUMBER, EmpSalary NUMBER );

INSERT INTO EMPLOYEE VALUES (101, 'John', 25, 50000);
INSERT INTO EMPLOYEE VALUES (102, 'Jane', 30, 60000);

Filtering with WHERE Clause
sql
SELECT employee_id, first_name, salary
FROM employees
WHERE salary > 50000;
Heads-up: The output included salaries below 50,000—possibly a data issue or caching quirk.
PL/SQL Scripting with Conditional Logic
sql
SET SERVEROUTPUT ON;
DECLARE
v_emp_salary NUMBER;
BEGIN
SELECT salary INTO v_emp_salary FROM employees WHERE employee_id = 100;

IF v_emp_salary > 5000 THEN
DBMS_OUTPUT.PUT_LINE('Very Good Salary: ' || v_emp_salary);
ELSE
DBMS_OUTPUT.PUT_LINE('Average Salary: ' || v_emp_salary);
END IF;
END;
Triggers for Auditing
sql
CREATE OR REPLACE TRIGGER STUDENT_AUDIT
AFTER INSERT ON STUDENTS
FOR EACH ROW
BEGIN
INSERT INTO AUDIT_LOG (Student_ID, Action, ActionDate)
VALUES (:NEW.Student_ID, 'INSERT', SYSDATE);
END;

NORMALIZATION IN DBMS

SAKTHI SREE N S 24CB051 — Sun, 05 Oct 2025 15:56:10 +0000

Getting Started with SQL: Building a Student-Course Database in Oracle Live SQL
If you're new to SQL or just exploring Oracle Live SQL, this post walks you through creating a simple relational database with students, courses, and instructors. We'll cover table creation, relationships, and basic queries—all in a hands-on way.

Step 1: Create Your Tables
Start by defining the core entities: Students, Courses, and Enrollments.

sql
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100),
Age INT
);

CREATE TABLE Courses (
CourseID INT PRIMARY KEY,
CourseName VARCHAR(100)
);

CREATE TABLE Enrollments (
EnrollmentID INT PRIMARY KEY,
StudentID INT,
CourseID INT,
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);
Step 2: Add Instructors (Optional but Useful)
To make it more realistic, let’s add an Instructors table and link it to Courses.

sql
CREATE TABLE Instructors (
InstructorID INT PRIMARY KEY,
InstructorName VARCHAR(100),
Department VARCHAR(50)
);

ALTER TABLE Courses
ADD InstructorID INT;

ALTER TABLE Courses
ADD FOREIGN KEY (InstructorID) REFERENCES Instructors(InstructorID);
Step 3: Insert Sample Data
Populate your tables with sample data to test relationships and queries.

sql
INSERT INTO Students VALUES (501, 'Anjan', 21);
INSERT INTO Courses VALUES (C102, 'Data Mining');
INSERT INTO Enrollments VALUES (1, 501, C102);
Step 4: Run a JOIN Query
Now let’s retrieve meaningful data using JOIN:

sql
SELECT s.StudentID, s.Name, c.CourseName, i.InstructorName
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID
JOIN Instructors i ON c.InstructorID = i.InstructorID;
Why This Matters
This mini-project helps you:

Understand foreign key relationships

Practice JOIN operations

Build a foundation for real-world database design

Final Thoughts
Oracle Live SQL is a great playground for learning and experimenting. Whether you're prepping for interviews or building your first app backend, mastering SQL is a skill that pays off.

COLLEGE STUDENT AND COURSE MANAGEMENT SYSTEM.

SAKTHI SREE N S 24CB051 — Mon, 25 Aug 2025 10:12:50 +0000

In this blog we are going To college student and course management system database management system.

In this blog, we’ll learn how to create a simple Student-Course Database in Oracle SQL. This example is perfect for beginners who want hands-on practice with SQL commands, constraints, and queries.

Step 1: Creating the Students Table

We start by creating the Students table. It will store student details such as ID, Name, Department, Date of Birth, and Email.

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

Explanation:

PRIMARY KEY → Ensures each student has a unique ID.

NOT NULL → The Name cannot be empty.

UNIQUE → The Email must be different for each student.

Step 2: Creating the Courses Table

Next, we create a Courses table to store available courses.

CREATE TABLE Courses (
CourseID NUMBER PRIMARY KEY,
CourseName VARCHAR2(50) NOT NULL,
Credits NUMBER(2)
);

Explanation:

CourseID uniquely identifies each course.

Credits will later have a check constraint to ensure valid values.

Step 3: Creating the Enrollments Table

To link students and courses, we create an Enrollments table.

CREATE TABLE Enrollments (
EnrollID NUMBER PRIMARY KEY,
StudentID NUMBER REFERENCES Students(StudentID),
CourseID NUMBER REFERENCES Courses(CourseID),
Grade CHAR(2)
);

Explanation:

StudentID references the Students table.

CourseID references the Courses table.

This creates relationships between tables (foreign keys).

Step 4: Inserting Sample Data

Now let’s insert some students and courses.

Insert Students
INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (1, 'Sakthi', 'CB', TO_DATE('11/22/2006', 'MM-DD-YYYY'), 'Sakthi.cb@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (2, 'Varsha', 'IT', TO_DATE('03/15/2007', 'MM-DD-YYYY'), 'varsha.it@example.com');

INSERT INTO Students (StudentID, Name, Dept, DOB, Email)
VALUES (3, 'Divi', 'CS', TO_DATE('11/22/2006', 'MM-DD-YYYY'), 'divi.cs@example.com');

Insert Courses
INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (101, 'DBMS', 3);

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (102, 'OS', 4);

INSERT INTO Courses (CourseID, CourseName, Credits)
VALUES (103, 'Data Structures', 5);

Step 5: Altering Tables

ALTER TABLE Students
ADD PhoneNo VARCHAR2(10);

ALTER TABLE Courses
ADD CHECK (Credits BETWEEN 1 AND 5);

Step 6: Commit Changes

In Oracle, always commit after inserting or updating records.

COMMIT;

Step 7: Running Queries

Now let’s fetch some useful results from our database.

Display student names in uppercase with email length
SELECT
UPPER(Name) AS Student_Name,
LENGTH(Email) AS Email_Length
FROM Students;

Display all courses with credits
SELECT CourseID, CourseName, Credits
FROM Courses;

Display all student details
SELECT StudentID, Name, Dept, DOB, Email, PhoneNo
FROM Students;

Conclusion

In this tutorial, we:

Created Students, Courses, and Enrollments tables.

Learned about primary keys, foreign keys, unique constraints, and check constraints.

Inserted sample data and queried results.

Thank You @santhoshnc sir for guiding me.