Forem: Deepana

☁️ Amazon S3 (Simple Storage Service)

Deepana — Thu, 18 Dec 2025 17:53:28 +0000

✨ Introduction

Amazon S3 is one of the most widely used storage services provided by Amazon Web Services (AWS). It is designed to store and retrieve any amount of data at any time from anywhere on the internet 🌍. Because of its scalability, durability, and security, Amazon S3 plays an important role in cloud computing and DevOps workflows.

🔍 Service Overview

Amazon S3 (Simple Storage Service) is an object storage service that allows users to store data such as files, images, videos, backups, and logs. It is highly scalable and reliable, making it suitable for both small applications and large enterprise systems.

In simple terms, Amazon S3 acts like an online storage space in the cloud ☁️.

⭐ Key Features of Amazon S3

📦 Object-based storage for any type of data
♾️ Virtually unlimited storage capacity
🔒 High durability and security
🌍 Accessible from anywhere
🔄 Versioning and lifecycle management
📊 Integration with other AWS services

🧩 AWS Category / Cloud Domain

AWS Category: Storage ☁️

Amazon S3 belongs to the Storage domain in AWS and is a core service used across many cloud architectures.

🔗 Where It Fits in Cloud / DevOps Lifecycle

In the Cloud and DevOps lifecycle, Amazon S3 is mainly used for:

📥 Storing application assets (images, videos, static files)
🗂️ Backup and disaster recovery
🚀 Storing build artifacts in CI/CD pipelines
📊 Log storage and data archiving

S3 supports automation and reliability, which makes it very useful in DevOps environments 🔁.

💻 Programming Language / Access Methods

Amazon S3 can be accessed using multiple methods:

🖥️ AWS Management Console
⌨️ AWS CLI
🧩 AWS SDKs (Python, Java, JavaScript, etc.)
🌐 REST APIs

💰 Pricing Model

Amazon S3 follows a pay-as-you-go pricing model 💳. Users are charged based on:

Amount of data stored
Data transfer
Requests made to S3

There is also a Free Tier, which provides limited storage for beginners and learners.

📝 Conclusion

Amazon S3 is a powerful and flexible storage service in AWS. Its high scalability, security, and integration with other AWS services make it an essential part of cloud and DevOps architectures. From simple file storage to complex data backup solutions, Amazon S3 supports a wide range of use cases in modern cloud computing 🚀.

🐳Docker in DevSecOps

Deepana — Thu, 18 Dec 2025 16:56:08 +0000

✨Introduction

Docker is one of the most popular tools in the DevOps and DevSecOps ecosystem. It helps developers package applications along with all their dependencies into lightweight containers. Because of this, applications run the same way in development, testing, and production environments.

🔍 Overview of Docker

Docker is a containerization platform used to build, ship, and run applications 🛠️. It allows applications to be isolated inside containers, which makes them portable, fast, and consistent across different systems.

In simple words, “It works on my machine” problem-a Docker solve pannum ✅.

⭐ Key Features of Docker

📦 Container-based application deployment
⚡ Lightweight and faster than virtual machines
🌍 Platform independent (runs on any OS with Docker support)
📈 Easy application scaling
🔄 Faster CI/CD pipelines
🏷️ Image-based versioning

🔗 How Docker Fits into DevOps / DevSecOps

🧑‍💻 In DevOps:

📦 Packaging applications
🔁 Ensuring environment consistency
🚀 Faster deployment using CI/CD pipelines

🔐 In DevSecOps:

🛡️ Scanning container images for vulnerabilities
🔍 Integrating security checks early in the pipeline
🚫 Reducing security risks by isolating applications

Thus, Docker plays a key role in automation 🤖, consistency 🔁, and security 🔐.

💻 Programming Language Used in Docker

Docker is mainly developed using Go (Golang) 🧩. Go helps Docker achieve high performance ⚡ and efficient concurrency.

🏢 Parent Company

Docker is developed and maintained by Docker Inc. 🏗️

💰 Open Source or Paid?

Docker is open source 🆓, and anyone can use Docker Community Edition for free. Docker also provides paid enterprise solutions 💼 like Docker Enterprise for large organizations.

📝 Conclusion

Docker is a powerful tool in the DevSecOps periodic table 🧪. It simplifies application deployment 🚀, improves consistency 🔁, and supports secure development practices 🔐. Because of its speed ⚡, portability 📦, and strong ecosystem 🌐, Docker has become an essential tool in modern software development.

📝 Understanding Transactions, Deadlocks & Log-Based Recovery in SQL 💾

Deepana — Mon, 27 Oct 2025 17:14:14 +0000

👋 Introduction

Have you ever wondered what happens when two people try to withdraw money from the same bank account at the same time?
Databases handle such cases using transactions, locks, and recovery logs
to make sure data always stays consistent.

In this blog, we’ll explore:

How transactions maintain atomicity
What a deadlock is and how it’s detected
How log-based recovery ensures data safety after a crash

CREATE TABLE Accounts (
    acc_no INT PRIMARY KEY,
    name VARCHAR(50),
    balance INT
);

INSERT INTO Accounts VALUES
(1, 'Alice', 1000),
(2, 'Bob', 1500),
(3, 'Charlie', 2000);

⚙️ 2️⃣ Transaction – Atomicity & Rollback

🧠 Concept:
A transaction ensures that all steps succeed or none at all.
If any step fails, the database will rollback to the previous stable state.

START TRANSACTION;
UPDATE Accounts
SET balance = balance - 500
WHERE name = 'Alice';
UPDATE Accounts
SET balance = balance + 500
WHERE name = 'Bob';
SELECT * FROM Accounts;
ROLLBACK;
SELECT * FROM Accounts;

✅ Result:
All updates are undone after rollback → no partial transaction is saved.
This proves Atomicity — either all or none of the operations take effect.

🔄 3️⃣ Deadlock Simulation

🧠 Concept:
A deadlock happens when two transactions wait forever because each holds a lock the other needs.
Let’s simulate it using two SQL sessions.
🪟 Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice'; 
UPDATE Accounts SET balance = balance + 100 WHERE name = 'Bob';

🪟 Session 2:

START TRANSACTION;
UPDATE Accounts SET balance = balance - 50 WHERE name = 'Bob';
UPDATE Accounts SET balance = balance + 50 WHERE name = 'Alice';

📜 4️⃣ Log-Based Recovery

🧠 Concept:
Every database maintains logs (like MySQL Binary Logs or PostgreSQL WAL) that record each change.
If a crash happens, the DB can redo committed and undo uncommitted transactions.

SHOW VARIABLES LIKE 'log_bin';
START TRANSACTION;
UPDATE Accounts SET balance = 900 WHERE name = 'Alice';
ROLLBACK;

Then check the logs:

SHOW BINLOG EVENTS LIMIT 10;

You’ll see records for the update and rollback.

✅ Result:
The undo operation is recorded, ensuring the database can recover correctly after any failure.

🧠 Conclusion

Through this activity, we learned:

Transactions ensure data integrity (Atomicity, Consistency)
Deadlocks are automatically detected and handled by DBMS
Logs help recover data during failures

💡 Key takeaway: Databases are smart enough to protect your data even when things go wrong!

ACID properties with SQL transactions

Deepana — Wed, 08 Oct 2025 15:40:35 +0000

Step 1: Create the Accounts Table & Insert Sample Data

CREATE TABLE Accounts (
    acc_no INT PRIMARY KEY,
    name VARCHAR(50),
    balance INT CHECK (balance >= 0) -- prevents negative balance
);

INSERT INTO Accounts (acc_no, name, balance) VALUES
(101, 'Alice', 5000),
(102, 'Bob', 3000),
(103, 'Charlie', 7000);

Step 2: Atomicity

Goal: Transfer money from Alice → Bob, but rollback midway to prevent partial update.

START TRANSACTION;
UPDATE Accounts SET balance = balance - 1000 WHERE acc_no = 101;
UPDATE Accounts SET balance = balance + 1000 WHERE acc_no = 102;
ROLLBACK;
SELECT * FROM Accounts;

Expected Result: No changes happen. Alice still has 5000, Bob 3000.
This shows atomicity — either all updates succeed, or none do.

Step 3: Consistency

Goal: Reject invalid state (negative balance).

INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'David', -500);

Expected Result: Error due to CHECK (balance >= 0).
This preserves data consistency — database rules are enforced.

Step 4: Isolation

Goal: Observe isolation between concurrent transactions.

Session 1:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 2000 WHERE acc_no = 103;

Session 2 (another terminal/session):

SELECT * FROM Accounts WHERE acc_no = 103;

Expected Result:

Depending on isolation level (REPEATABLE READ default in MySQL), Session 2 may not see uncommitted changes.
Session 1 commits → changes become visible.

COMMIT;

This shows isolation — transactions do not interfere unexpectedly.

Step 5: Durability

Goal: Data persists after commit.

START TRANSACTION;
UPDATE Accounts SET balance = balance + 500 WHERE acc_no = 102;
COMMIT;

Restart the database.
Run:

SELECT * FROM Accounts WHERE acc_no = 102;

Expected Result: Bob’s balance is updated permanently. ✅
This demonstrates durability — committed transactions survive crashes.

Conclusion

ACID properties ensure reliable and consistent database transactions. Atomicity prevents partial updates, Consistency enforces rules, Isolation keeps transactions independent, and Durability makes changes permanent. Practicing these ensures your data is always safe and trustworthy.

Indexing, Hashing & Query Optimization

Deepana — Wed, 08 Oct 2025 13:55:13 +0000

🎯 Objective

To understand how to improve query performance using B-Tree, B+ Tree, and Hash indexing in MySQL.

🧩 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, 'Alice', 'CSBS', 8.7),
(102, 'Rahul', 'ECE', 7.9),
(103, 'Priya', 'IT', 8.1),
(104, 'Kavin', 'CSBS', 9.0),
(105, 'Meena', 'EEE', 7.5),
(106, 'Deepak', 'CIVIL', 8.0),
(107, 'Sathya', 'MECH', 6.9),
(108, 'Ravi', 'IT', 7.8),
(109, 'Sneha', 'CSBS', 8.9),
(110, 'Manoj', 'ECE', 8.3),
(111, 'Divya', 'CSBS', 9.1),
(112, 'Hari', 'MECH', 7.4),
(113, 'Nithya', 'EEE', 7.8),
(114, 'Karthik', 'CIVIL', 8.2),
(115, 'Anu', 'CSBS', 8.4),
(116, 'Gokul', 'IT', 7.7),
(117, 'Vishnu', 'ECE', 8.0),
(118, 'Saran', 'EEE', 8.5),
(119, 'Preethi', 'CIVIL', 7.6),
(120, 'Varun', 'CSBS', 9.3);

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

CREATE INDEX idx_roll_no ON Students(roll_no);

✅ Query:

SELECT * FROM Students WHERE roll_no = 110;

💡 Explanation:

B-Tree indexes help in range-based and sorted searches efficiently.
The lookup time reduces from O(n) to O(log n).

🌿 Step 4: Create a B+ Tree Index on cgpa

CREATE INDEX idx_cgpa ON Students(cgpa);

✅ Query:

SELECT * FROM Students WHERE cgpa > 8.0;

⚙️ Step 5: Create a Hash Index on dept

CREATE INDEX idx_dept_hash USING HASH ON Students(dept);

✅ Query:

SELECT * FROM Students WHERE dept = 'CSBS';

💡 Explanation:

-Hash Index is best for exact match lookups (e.g., =).
-It is not used for range queries like > or <.

✨ Conclusion

In this experiment, we learned:

How to create and use B-Tree, B+ Tree, and Hash indexes
How these indexes improve query performance
Which index type fits each kind of query

🔹 CRUD Operations in MongoDB – A Hands-on College Student Example

Deepana — Mon, 06 Oct 2025 16:57:56 +0000

📘 Objective

In this post, we’ll explore how to perform CRUD (Create, Read, Update, Delete) operations in MongoDB using a simple College Student schema.
This activity helped me understand how MongoDB stores data in collections and how to interact with it using queries.

🧩 Schema – Collection: students

Each document in the collection follows this structure:

{
  "student_id": "S001",
  "name": "Santhosh",
  "age": 20,
  "department": "CSBS",
  "year": 2,
  "cgpa": 9
}

⚙️ 1️⃣ Create (Insert)

Insert multiple student records:

db.students.insertMany([
  { "student_id": "S001", "name": "Santhosh", "age": 20, "department": "CSBS", "year": 2, "cgpa": 9 },
  { "student_id": "S002", "name": "Deepa", "age": 19, "department": "CSE", "year": 1, "cgpa": 8.7 },
  { "student_id": "S003", "name": "Meena", "age": 21, "department": "IT", "year": 3, "cgpa": 7.9 },
  { "student_id": "S004", "name": "Kavin", "age": 22, "department": "CSBS", "year": 3, "cgpa": 8.4 },
  { "student_id": "S005", "name": "Arun", "age": 20, "department": "EEE", "year": 2, "cgpa": 6.9 }
])

🔍 2️⃣ Read (Query)

📌 Display all student records:

db.students.find()

📌 Find students with CGPA > 8:

db.students.find({ cgpa: { $gt: 8 } })

📌 Find students belonging to the Computer Science

db.students.find({ department: { $regex: "CS", $options: "i" } })

✏️ 3️⃣ Update

📌 Update the CGPA of a specific student:

db.students.updateOne({ student_id: "S002" }, { $set: { cgpa: 9.1 } })

📌 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 (insert, find, update, delete)
✅ Screenshots from MongoDB Atlas showing query outputs
✅ Export of the final students collection in JSON/CSV format

💡 Summary

Through this exercise, I learned how MongoDB handles data in a document-based structure and how CRUD operations can be done easily using built-in commands.

🙏 Special Thanks

A special thanks to Santhosh NC Sir for his guidance and continuous support in this DBMS learning journey.

🏷️ Tags

mongodb #dbms #crud #students #database #learning

🧾 Transactions, Deadlocks & Log-Based Recovery in DBMS – A Practical Guide

Deepana — Sat, 04 Oct 2025 16:47:58 +0000

🧩 Schema Setup

We’ll use a single table called Accounts.

CREATE TABLE Student_Fees (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);

INSERT INTO Student_Fees VALUES (1, 'Alice', 1000);
INSERT INTO Student_Fees VALUES (2, 'Bob', 1500);
INSERT INTO Student_Fees VALUES (3, 'Charlie', 2000);

Check your table:
SELECT * FROM Student_Fees;

⚙️ 1️⃣ Transaction – Atomicity & Rollback

Concept:
Atomicity ensures that a transaction is all or nothing.
If any part fails or if we manually rollback, all changes are undone.

Steps:

SAVEPOINT start_point;
UPDATE Student_Fees SET balance = balance - 500 WHERE name = 'Alice';
UPDATE Accounts SET balance = balance + 500 WHERE name = 'Bob';
ROLLBACK TO start_point;
SELECT * FROM Student_Fees;

✅ Result:
Balances remain unchanged → proves Atomicity works!

🔁 2️⃣ Deadlock Simulation

Concept:
A deadlock happens when two transactions hold locks that each other needs.

Even though Oracle LiveSQL doesn’t support two sessions, we can understand the concept using example code:

💻 Session 1:
UPDATE Student_Fees SET fees_balance = fees_balance - 500 WHERE stud_name = 'Deepa';
UPDATE Student_Fees SET fees_balance = fees_balance + 500 WHERE stud_name = 'Meena';

💻 Session 2:
UPDATE Student_Fees SET fees_balance = fees_balance - 700 WHERE stud_name = 'Meena';
UPDATE Student_Fees SET fees_balance = fees_balance + 700 WHERE stud_name = 'Deepa';

Result: Both transactions wait on each other → deadlock detected → DBMS automatically rolls back one transaction.

Note: In LiveSQL, updates are sequential, so no deadlock error appears.
This section is for conceptual understanding.

🧠 3️⃣ Log-Based Recovery (Undo Demonstration)

Oracle automatically maintains undo/redo logs. Rollback uses undo logs to restore old data.

SAVEPOINT log_demo;
UPDATE Student_Fees
SET fees_balance = fees_balance + 1000
WHERE stud_name = 'Meena';
ROLLBACK TO log_demo;
SELECT * FROM Student_Fees;

Explanation: Undo logs restored the previous state → log-based recovery works.

📘 What You’ll Learn

✅ How atomicity prevents partial updates
⚡ How deadlocks can occur in multi-session environments
🔄 How log-based recovery restores data safely

🙏 Special Thanks

A heartfelt thank you to Santhosh NC Sir for his guidance and continuous support throughout this DBMS assignment.

🏷️ Tags

dbms #oracle #sql #transactions #database #students #learning

Cursor + Trigger in SQL: A Beginner-Friendly Guide

Deepana — Sat, 04 Oct 2025 09:26:15 +0000

Cursor + Trigger in SQL

In this blog, we will clearly understand Cursor and Trigger with step-by-step explanation and examples.

🔹 Part 1: Cursor Example

📌 What is a Cursor?

A cursor is like a pointer which allows us to fetch rows one by one from a query result.
Useful when we want to process each record individually.

📝 Task

Create a cursor that displays employee names whose salary is greater than 50,000 from the Employee table.

💻 SQL Code

DECLARE
CURSOR emp_cursor IS
SELECT EmployeeName
FROM Employee
WHERE Salary > 50000;
v_emp_name Employee.EmployeeName%TYPE;
BEGIN
OPEN emp_cursor;
LOOP
FETCH emp_cursor INTO v_emp_name;
EXIT WHEN emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE('Employee: ' || v_emp_name);
END LOOP;
CLOSE emp_cursor;
END;
/

🔎 Explanation (Step by Step)

DECLARE Cursor → We define a cursor to select employees with salary > 50,000.
Variable → v_emp_name stores each fetched employee name.
OPEN Cursor → Cursor execution starts.
LOOP + FETCH → Each row is fetched and displayed.
EXIT WHEN NOTFOUND → Loop stops when no rows left.
CLOSE Cursor → Cursor is closed to release memory.

🔹 Part 2: Trigger Example

📌 What is a Trigger?

A trigger is a stored program that is automatically executed when a specific event (INSERT, UPDATE, DELETE) occurs on a table.

📝 Task

Whenever a new student is added to the Students table, automatically insert a log entry into the Student_Audit table.

💻 SQL Code

CREATE OR REPLACE TRIGGER trg_student_insert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit(StudentID, Action, ActionDate)
VALUES(:NEW.StudentID, 'INSERT', SYSDATE);
END;
/
INSERT INTO Students (StudentID, StudentName)
VALUES (102, 'Divya');
COMMIT;
SELECT * FROM Student_Audit;

📌 Explanation:

This trigger fires AFTER INSERT on Students.

For each new student, a record is automatically added in Student_Audit with date & action type.

✨ That’s it!
With these two examples, we learned:
How to use a Cursor with condition.
How to create an AFTER INSERT Trigger to maintain audit logs.

Normalization in DBMS with SQL Examples (1NF 2NF 3NF)

Deepana — Fri, 03 Oct 2025 11:37:35 +0000

In this blog, I am sharing my learnings about Normalization in DBMS.
As part of my coursework, I implemented 1NF, 2NF, and 3NF using SQL.
This blog will explain step by step with tables and queries.

Base Table

Anomalies

Insertion Anomaly: Cannot add a course without student.

Update Anomaly: Instructor’s phone number must be updated in many rows.

Deletion Anomaly: If last student leaves, course info also deleted.

step 1: 1NF (First Normal Form)
👉 Condition: No repeating groups / multivalued attributes.
Already table is in 1NF (since all fields atomic).

SQL (2NF Tables)
CREATE TABLE StudentCourse (
StudentID VARCHAR(10),
StudentName VARCHAR(50),
CourseID VARCHAR(10),
CourseName VARCHAR(50),
Instructor VARCHAR(50),
InstructorPhone VARCHAR(15),
PRIMARY KEY (StudentID, CourseID)
);

Step 2: 2NF (Second Normal Form)

👉 Condition: No partial dependency (non-key attribute should depend on full primary key).
Here StudentName depends only on StudentID,
and CourseName, Instructor, InstructorPhone depend only on CourseID.

So we split:

Student table

Course table

Enrollment (Student-Course relation) table
CREATE TABLE Student (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

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

CREATE TABLE Enrollment (
StudentID VARCHAR(10),
CourseID VARCHAR(10),
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Student(StudentID),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

Step 3: 3NF (Third Normal Form)

👉 Condition: No transitive dependency.
Here InstructorPhone depends on Instructor, not on CourseID.

So we separate Instructor into another table.

SQL (3NF Tables)
CREATE TABLE Student (
StudentID VARCHAR(10) PRIMARY KEY,
StudentName VARCHAR(50)
);

CREATE TABLE Instructor (
InstructorID VARCHAR(10) PRIMARY KEY,
InstructorName VARCHAR(50),
InstructorPhone VARCHAR(15)
);

CREATE TABLE Course (
CourseID VARCHAR(10) PRIMARY KEY,
CourseName VARCHAR(50),
InstructorID VARCHAR(10),
FOREIGN KEY (InstructorID) REFERENCES Instructor(InstructorID)
);

step 4:Sample Data
INSERT INTO Student VALUES ('S01','Arjun'), ('S02','Priya'), ('S03','Kiran');
INSERT INTO Instructor VALUES ('I01','Dr. Kumar','9876543210'), ('I02','Dr. Mehta','9123456780'), ('I03','Dr. Rao','9988776655');
INSERT INTO Course VALUES ('C101','DBMS','I01'), ('C102','Data Mining','I02'), ('C103','AI','I03');
INSERT INTO Enrollment VALUES ('S01','C101'), ('S01','C102'), ('S02','C101'), ('S03','C103');

step 5:Query with JOIN
SELECT s.StudentName, c.CourseName, i.InstructorName
FROM Enrollment e
JOIN Student s ON e.StudentID = s.StudentID
JOIN Course c ON e.CourseID = c.CourseID
JOIN Instructor i ON c.InstructorID = i.InstructorID;

Conclusion
Through Normalization, we reduced redundancy and avoided anomalies.
1NF ensured atomicity, 2NF removed partial dependency, and 3NF removed transitive dependency.
This is how databases remain consistent and efficient.

College Student & Course Management System

Deepana — Fri, 22 Aug 2025 09:01:08 +0000

Introduction
Managing students, courses, and enrollments is one of the most common real-world applications of databases. To understand how these concepts work in practice, I have created a simple College Student & Course Management System using Oracle LiveSQL.

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

Inserting Data (DML)
INSERT INTO Students VALUES (1, 'Arun Kumar', 'CSE', DATE '2003-05-12', 'arun@gmail.com');

Adding and modifying data (DML & ALTER)
ALTER TABLE Students
ADD PhoneNo NUMBER(10);

Defining Constraints – Credits Between 1 and 5
ALTER TABLE Courses
MODIFY Credits CHECK (Credits BETWEEN 1 AND 5);

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

Aggregate Functions
SELECT AVG(Credits) AS AvgCredits FROM Courses;
SELECT COUNT(*) AS TotalStudents FROM Students;

JOIN Operation
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;

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

View – StudentCoursesView
CREATE VIEW StudentCoursesView AS
SELECT s.Name AS StudentName, c.CourseName, e.Grade
FROM Students s
JOIN Enrollments e ON s.StudentID = e.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

Stored Procedure – UpdateGrade
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;

output

📌 Conclusion
In this blog, I demonstrated the implementation of a simple College Student & Course Management System on Oracle LiveSQL.
The queries covered:

DDL, DML, ALTER, and Constraints
SELECT with functions
Aggregates and GROUP BY with HAVING
Joins and Views
Stored Procedure