DEV Community: Thushitha

MongoDB in Action: Building a Smart Student Database with CRUD Operations

Thushitha — Sun, 05 Oct 2025 14:18:46 +0000

MongoDB is a leading NoSQL database widely used for cloud-native and scalable applications. Unlike relational databases with rigid schemas, it stores data in flexible JSON-like documents, making it easy to model real-world entities such as students in a college database.

At its core, MongoDB supports CRUD operations — Create, Read, Update, and Delete — to add, retrieve, modify, and remove data.

In this blog, we’ll perform CRUD operations on a student database schema by:

Inserting multiple student records
Querying and filtering data
Updating details like CGPA and year
Deleting records with conditions

We’ll use MongoDB Atlas, the cloud-hosted service, for hands-on practice. By the end, you’ll have practical experience managing data in MongoDB — an essential skill for modern development.

🧱 Schema – Collection: students
Each student record (document) follows this structure:

{
"student_id": "ST001",
"name": "Aarav",
"age": 20,
"department": "AI & DS",
"year": 2,
"cgpa": 8.9
}

⚙️ 1️⃣ CREATE (INSERT)
In MongoDB Atlas → Collections → Insert Document

{
"student_id": "ST001",
"name": "Aarav",
"age": 20,
"department": "AI & DS",
"year": 2,
"cgpa": 8.9
},
{
"student_id": "ST002",
"name": "Diya",
"age": 19,
"department": "CSE",
"year": 1,
"cgpa": 9.2
},
{
"student_id": "ST003",
"name": "Rahul",
"age": 21,
"department": "ECE",
"year": 3,
"cgpa": 7.8
},
{
"student_id": "ST004",
"name": "Meera",
"age": 20,
"department": "IT",
"year": 2,
"cgpa": 9.5
},
{
"student_id": "ST005",
"name": "Vikram",
"age": 22,
"department": "MECH",
"year": 3,
"cgpa": 6.9
}

✅ Result in Atlas:
Inserted 5 documents successfully.

🔍 2️⃣ READ (QUERY)
Run the following queries

(a) Display all student records
find()

(b) Find all students with CGPA > 8
({ cgpa: { $gt: 8 } })

(c) Find students belonging to Computer Science departments
({ department: { $in: ["CSE", "AI & DS"] } })

📊 This helps identify top performers or department-based groups easily.

✏️ 3️⃣ UPDATE (MODIFY RECORDS)

(a) Update CGPA of a specific student
{ student_id: "ST002" },
{ $set: { cgpa: 9.6 } }

✅ Matched 1 document, modified 1 document.

(b) Increase the year of study for all 3rd-year students by 1
{ year: 3 },
{ $inc: { year: 1 } }

💡 $inc automatically increments numerical fields — perfect for promotions or increments.

🗑️ 4️⃣ DELETE (REMOVE RECORDS)

(a) Delete one student by ID
({ student_id: "ST005" })

OUTPUT:

(b) Delete all students with CGPA < 7.5
({ cgpa: { $lt: 7.5 } })

OUTPUT:

🧹 Removes low-performing or outdated records, keeping your data clean.

🎓 Learning Outcomes
By performing these CRUD operations, you’ll learn to:

Work with MongoDB Atlas Cloud Interface
Write and execute basic MongoDB queries
Update and delete data safely
Export and visualize your collections

🏦 Database Magic: Automating Tasks with Cursor and Trigger ✨

Thushitha — Sun, 05 Oct 2025 08:12:26 +0000

Working with databases isn’t just about storing data — it’s about automating how that data behaves.
In this blog, we’ll explore two key SQL features:

🎯 Cursor – to process query results row by row
⚙️ Trigger – to automatically perform actions after data changes

Let’s dive in! 🔍

💼 Part 1: Cursor – Display Employees with Salary > 50,000

A cursor helps you loop through query results one row at a time, perfect for selective data processing.

🧩 Step 1: Create Employee Table & Insert Data

CODE:

CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName VARCHAR(50),
Salary DECIMAL(10,2)
);

INSERT INTO Employee VALUES (1, 'Sophia', 72000.00);
INSERT INTO Employee VALUES (2, 'Ryan', 48000.00);
INSERT INTO Employee VALUES (3, 'Olivia', 83000.00);
INSERT INTO Employee VALUES (4, 'Liam', 39000.00);
INSERT INTO Employee VALUES (5, 'Emma', 65000.00);

✅ This creates an Employee table and fills it with some data.
Notice that some employees have salaries above 50,000.

⚙️ Step 2: Declare and Process the Cursor

We’ll use a cursor to fetch and display names of employees whose salary is greater than 50,000.

CODE:

DELIMITER $$

CREATE PROCEDURE DisplayHighSalaryEmployees()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE empName VARCHAR(50);
DECLARE emp_cursor CURSOR FOR
SELECT EmployeeName FROM Employee WHERE Salary > 50000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN emp_cursor;
read_loop: LOOP
FETCH emp_cursor INTO empName;
IF done THEN
LEAVE read_loop;
END IF;
SELECT CONCAT('Employee: ', empName) AS Employee_Name;
END LOOP;
CLOSE emp_cursor;
END $$

DELIMITER ;

CALL DisplayHighSalaryEmployees();

🧾 Output:

✨ Explanation:

DECLARE emp_cursor defines a cursor to select employees with salary > 50,000.
The handler ensures the loop stops when all rows are fetched.
OPEN, FETCH, and CLOSE control the cursor’s lifecycle. Each employee’s name is displayed using a SELECT statement.
When you run the procedure, the cursor loops through all eligible records and prints employee names who earn more than ₹50,000.

⚡ 2️⃣ AFTER INSERT Trigger: Logging Student Registrations

A trigger automatically executes code when a certain event occurs in the database.
Here, we’ll create a trigger that logs every new student registration.

🧩 Step 1: Create Students and Audit Tables

CODE:

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

CREATE TABLE Student_Audit (
AuditID INT GENERATED ALWAYS AS IDENTITY,
StudentID INT,
Action VARCHAR2(50),
ActionTime TIMESTAMP );

⚙️ Step 2: Create the AFTER INSERT Trigger

This trigger runs automatically after a new student record is inserted.

CODE:

DELIMITER $$

CREATE TRIGGER trg_AfterStudentInsert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
INSERT INTO Student_Audit (StudentID, Action, ActionTime)
VALUES (NEW.StudentID, 'Registered', NOW());
END $$

DELIMITER ;

✅ The :NEW keyword refers to the newly inserted row in the Students table.
✅ SYSTIMESTAMP records the exact time of registration.

🧪 Step 3: Test the Trigger

CODE:

INSERT INTO Students VALUES (201, 'Aarav', 'Computer Science');
INSERT INTO Students VALUES (202, 'Priya', 'Mechanical Engineering');
INSERT INTO Students VALUES (203, 'Karan', 'Electronics');
SELECT * FROM Student_Audit;

🧾 Output:

✅ Conclusion

Cursors and Triggers are powerful features that help automate and control database workflows.

Use cursors for row-level processing.
Use triggers for event-based automation.

🚀 Experiment with these and watch your database become smarter and more interactive!

💾Database Reliability Explained: Transactions, Deadlocks & Log-Based Recovery

Thushitha — Sat, 04 Oct 2025 10:16:23 +0000

Modern databases are designed to be reliable, consistent, and fault-tolerant — even during crashes or concurrent access.
Three critical concepts that make this possible are:
Transactions, Deadlocks, and Log-Based Recovery.

This blog demonstrates how they work using a simple Accounts table example in SQL.

🧱 Step 1: Create the Accounts Table
We’ll begin by creating a basic table to simulate real-world banking operations.

To verify the data:

✅ Output:

This table will be used to understand how transactions and recovery work internally.

🔹 1️⃣ Transaction – Atomicity & Rollback

A transaction is a sequence of SQL operations executed as a single logical unit.
It follows the ACID properties — Atomicity, Consistency, Isolation, and Durability.

Let’s focus on Atomicity, which ensures that either all operations in a transaction succeed or none do.

Example: Money Transfer
Suppose Alice sends ₹500 to Bob.
We’ll perform the transfer and then roll it back before committing.

✅ Result:
Balances remain unchanged after rollback.
This confirms no partial update took place — a real demonstration of atomicity.

💡 Why It Matters:
If a power failure or crash occurs mid-transfer, rollback ensures that incomplete operations are undone automatically.

🔹 2️⃣ Deadlock Simulation

A deadlock occurs when two transactions are waiting for each other’s locked resources.
This often happens in multi-user environments where concurrent access is common.

Real-World Analogy:
Imagine two people trying to withdraw money from two linked accounts at the same time — each holding a lock on one account and waiting for the other.

Session 1

Session 2

⏳ Both sessions wait indefinitely — creating a deadlock.

How Databases Handle It:
Most DBMSs (like MySQL, Oracle, and PostgreSQL) use deadlock detection algorithms to automatically abort one of the conflicting transactions.
This allows the other transaction to continue and keeps the database consistent.

💡 Tip to Avoid Deadlocks:

Access tables in a consistent order across transactions.
Keep transactions short and simple.
Avoid unnecessary locks.

🔹 3️⃣ Log-Based Recovery

Even with transactions and deadlock control, system failures can still occur.
That’s where log-based recovery ensures Durability — the “D” in ACID.

What It Means:
Databases maintain transaction logs (Binary Logs in MySQL, WAL in PostgreSQL) that record every change.
If a crash occurs, the DBMS uses these logs to redo committed transactions and undo uncommitted ones.

✅ Result:
The rollback operation is written to the log.
If a failure occurred, the system would automatically restore the database to the last consistent state.

💡 Insight:
Without logging, recovering from crashes or incomplete updates would be nearly impossible.
That’s why log-based recovery is essential for mission-critical systems like banking or e-commerce.

💡 Final Thoughts

Database reliability depends on how well it handles failures, concurrency, and recovery.

Transactions guarantee atomic and consistent operations.
Deadlocks teach us to manage concurrency wisely.
Log-Based Recovery ensures no data is lost, even after system crashes.

By experimenting with these SQL commands, you gain a real understanding of how databases ensure data integrity and fault tolerance in the real world. ⚡

The Backbone of Database Reliability: Exploring ACID in Action with SQL Transactions A Step-by-Step Guide

Thushitha — Sat, 04 Oct 2025 08:39:26 +0000

When working with relational databases, ACID properties ensure data is handled reliably, consistently, and safely.

ACID stands for:

Atomicity 🧩 – All-or-nothing transactions
Consistency ⚖️ – Database rules are preserved
Isolation 🚧 – Transactions operate independently
Durability 💪 – Committed transactions persist permanently

We’ll use an Accounts table to demonstrate each property step by step, with SQL examples you can try yourself.

⭐Step 1: Create the Accounts Table and Insert Sample Data

🧑‍💻CODE:

CREATE TABLE Accounts (
acc_no INT PRIMARY KEY,
name VARCHAR(50),
balance INT CHECK (balance >= 0));
INSERT INTO Accounts (acc_no, name, balance) VALUES
(101, 'Calindra', 5800),
(102, 'Thalorin', 4200),
(103, 'Veylith', 6900);
SELECT * FROM Accounts;

Explanation:

acc_no is the primary key, ensuring each account is unique 🔑
balance has a CHECK constraint to prevent negative values ❌
Sample data gives three accounts to work with for transactions ✅

⭐Step 2: Atomicity 🧩 – All-or-Nothing Transactions

Goal: Ensure that if part of a transaction fails, no partial updates occur.
Scenario: Transfer 1500 from Calindra to Thalorin, but simulate an error midway.

🧑‍💻CODE:

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

Observation:

After ROLLBACK, all balances remain unchanged 🔄
Prevents incomplete transactions, critical in banking systems 💳

⭐Step 3: Consistency ⚖️ – Enforcing Data Rules

Goal: Ensure the database always remains in a valid state.
Scenario: Try to insert a record with a negative balance:

🧑‍💻CODE:

INSERT INTO Accounts (acc_no, name, balance) VALUES (104, 'Elarion', -2000);

Result:

Explanation:

Database constraints ❌ prevent invalid data
Consistency ensures all business rules and constraints are preserved

Tip 💡: Use constraints, triggers, and validations to maintain consistent data, especially in critical systems like finance, healthcare, or inventory.

⭐Step 4: Isolation 🚧 – Transactions Don’t Interfere

Goal: Ensure simultaneous transactions don’t cause conflicts.
Scenario: Simulate two sessions:

🧑‍💻CODE:
START TRANSACTION;
UPDATE Accounts SET balance = balance - 700 WHERE acc_no = 101;
SELECT balance FROM Accounts WHERE acc_no = 101;
COMMIT;

Session 1: Update an account but do not commit yet:

Session 2: Read the same account’s balance:

Observation:

Session 2 sees the original balance until Session 1 commits 🔒

Session 1 Commit:

After commit, Session 2 sees the updated balance ✅

Tip 💡: Different isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) control how strict transaction isolation is.

⭐Step 5: Durability 💪 – Committed Transactions Persist

Goal: Ensure committed transactions are permanent, even after a crash or restart.
Scenario: Update Veylith’s balance and commit:

🧑‍💻CODE:

START TRANSACTION;
UPDATE Accounts SET balance = balance + 2500 WHERE acc_no = 103;
COMMIT;
SELECT acc_no, name, balance FROM Accounts WHERE acc_no = 103;

Observation:

After restart, Veylith’s balance retains the updated value 💾
Durability is typically ensured using write-ahead logs (WAL) and disk storage

💡 Closing Thoughts

In this guide, we explored ACID properties — Atomicity 🧩, Consistency ⚖️, Isolation 🚧, and Durability 💪 — using a simple Accounts table to illustrate real-world scenarios:

Atomicity: Transactions are all-or-nothing — rollback ensures no partial updates.
Consistency: Constraints like balance >= 0 keep data valid at all times.
Isolation: Concurrent transactions operate independently, preventing conflicts.
Durability: Committed changes survive database crashes and restarts.

By understanding and applying ACID principles, you can build robust, reliable, and secure database applications — whether in banking 💳, e-commerce 🛒, healthcare 🏥, or enterprise systems 🏢.

Mastering ACID not only ensures data integrity and safety but also gives you the confidence to handle real-world transactional challenges with SQL.

✅ Remember: ACID isn’t just a theory — it’s the backbone of trustworthy, dependable database systems.

Database Normalization in SQL — 1NF, 2NF, and 3NF Explained (Student–Course Case Study)

Thushitha — Sat, 04 Oct 2025 04:23:32 +0000

🎯 Objective

Database normalization is one of the most important concepts in database design. It ensures that data is stored efficiently, redundancy is minimized, and data integrity is maintained.

In this tutorial, we’ll walk through First Normal Form (1NF), Second Normal Form (2NF), and Third Normal Form (3NF) — step by step — with clear SQL examples using a Student–Course–Instructor scenario.

By the end, you’ll understand not only how to normalize a table, but why it matters.

🧱 Base Table — The Starting Point

Let’s begin with a simple (but flawed) table design that stores students, their enrolled courses, and instructor details:

At first glance, this table might look fine — it gives us all the details in one place. But let’s look deeper.

⚠️ Data Anomalies in the Base Table

Insertion anomaly: You can’t add a new course unless a student enrolls in it.
Update anomaly: If Dr. Kumar changes his phone number, you must update it in every row where he appears.
Deletion anomaly: If Priya withdraws from DBMS, all information about the course “DBMS” and Dr. Kumar may be lost.

To overcome these issues, we apply Normalization — a step-by-step process of structuring the database.

🧩 Step 1: Convert to 1NF (First Normal Form)

✅ Rule: Every attribute (column) must contain atomic values — no repeating groups or arrays.

Our table already follows 1NF since each field holds only a single value (no lists or sets).
However, we’ll still define it formally in SQL to set a proper structure.

➕ Insert Sample Data

💡 Note: Even though this satisfies 1NF, redundancy still exists — course and instructor details are repeated multiple times.

🧩 Step 2: Convert to 2NF (Second Normal Form)

✅ Rule: Remove partial dependencies — every non-key attribute should depend on the entire primary key, not just part of it.

In the current table, the composite key could be (StudentID, CourseID), but columns like CourseName, Instructor, and InstructorPhone depend only on CourseID.
This violates 2NF.
So, we’ll split the data into separate tables: Student, Course, and Enrollment.

🧮 Student Table
Stores only student-related details.

📘 Course Table
Stores course information and the instructor details.

🧾 Enrollment Table
Connects students and courses, forming a many-to-many relationship.

➕ Insert Data

✅ Now: Each table has data that depends entirely on its key.
We’ve removed redundancy between students and courses, but one more dependency remains — between instructors and phone numbers.

🧩 Step 3: Convert to 3NF (Third Normal Form)

✅ Rule: Remove transitive dependencies — non-key attributes should not depend on other non-key attributes.
In our case, InstructorPhone depends on Instructor, not directly on CourseID.
That’s a transitive dependency.
To fix it, we’ll create a separate Instructor table.

👨‍🏫 Instructor Table
Stores instructor names and phone numbers independently.

📘 Course Table (Revised)
Now, instead of storing instructor details directly, we’ll link each course to its instructor via InstructorID.

➕ Insert Data

✅ Now: Each column depends only on the key — there’s no transitive dependency, and data updates are easier, safer, and cleaner.

🧮 Step 4: Query — Combining All Tables Using JOINs
Finally, let’s bring everything together and view the full data using JOIN statements.

🎯 Output:

✅ The query clearly shows the relationship between students, courses, and instructors — all retrieved efficiently from the normalized schema.

💡 Conclusion

Normalization is more than a theoretical rule — it’s a practical design principle that helps you build efficient, scalable, and error-free databases.

By applying 1NF → 2NF → 3NF:

We eliminated redundant data
Prevented update, insertion, and deletion anomalies
Created a clean, modular design for easier maintenance

Through this example, we’ve seen how a complex, redundant table can be systematically broken down into clean, well-structured tables following 1NF, 2NF, and 3NF.

🚀 Indexing, Hashing & Query Optimization in SQL (Tutorial Example on Students Table)

Thushitha — Fri, 03 Oct 2025 10:50:46 +0000

In database systems, query performance is critical. If a table has thousands (or millions) of rows, scanning the entire table for every query is slow. Indexes and hashing solve this problem by allowing faster data retrieval.

Indexing works like a textbook index — instead of scanning every row, the database can jump directly to the required data.
Hashing uses a hash function to quickly locate records based on a search key.

Types of Indexing We’ll Explore:

B-Tree Index → Best for equality and range queries.
B+ Tree Index → Optimized for sequential and range queries.
Hash Index → Very fast for equality searches but not for ranges.

In this tutorial, we’ll create a Students table and see how different indexes improve query performance.

📌 Step 1: Create the Students Table:

We created a database named College to store our tables. Using a separate database helps keep data organized and isolated from other projects.

The table consists of:

roll_no → Unique identifier for each student (primary key).
name → Student’s name.
dept → Department (e.g., CSE, CSBS).
cgpa → Cumulative grade point average (decimal). This table will be used to demonstrate indexing.

📌 Step 2: Insert Sample Records

Inserted 20 records covering different departments and CGPA values to demonstrate range and equality queries.

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

B-Tree indexes are excellent for equality lookups and range searches.

📌 Step 4:Query : Student by Roll Number

Searching for a specific roll_no is fast because the database can navigate the tree instead of scanning all rows.

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

B+ Tree indexes store all records in leaf nodes, making sequential and range queries efficient.

📌 Step 6:Query : Student by Cgpa>8.0

Queries like cgpa > 8.0 can quickly access only the relevant CGPA values without scanning the entire table.

📌Step 7: Create a Hash Index on dept

Hash indexes are extremely fast for equality searches (like dept = 'CSBS').

📌Step 8: Query : By Department

📌Step 9: Test Query Performance

EXPLAIN shows the query execution plan.
After creating indexes, the database uses the index instead of scanning all rows, improving speed.

🎯 Conclusion

Indexing and hashing are essential tools for query optimization in database systems. By creating the right type of index:

B-Tree helps quickly locate individual records and supports range queries.
B+ Tree is ideal for efficiently retrieving records over a range of values.
Hash Index provides lightning-fast lookup for equality searches.

Proper use of indexes can dramatically reduce query time, improve database performance, and make large datasets manageable. Always choose the index type based on your query patterns for optimal results.

[Boost]

Thushitha — Thu, 21 Aug 2025 13:34:15 +0000

Thushitha

Aug 21 '25

SQL Tutorial: Building a College Student & Course Management System (Step-by-Step Guide)

#programming #beginners #tutorial #learning

Comments

3 min read

SQL Tutorial: Building a College Student & Course Management System (Step-by-Step Guide)

Thushitha — Thu, 21 Aug 2025 13:32:32 +0000

Student, course, and faculty management is a fundamental requirement in academic institutions. In this tutorial, we will design and implement a comprehensive College Management System using SQL.

The guide will take you step by step through key concepts such as Data Definition Language (DDL), Data Manipulation Language (DML), constraints, functions, joins, views, and stored procedures—providing a practical insight.

Schema Design:

To structure our College Management System effectively, we identify four key entities:

Students – Represent individuals enrolled in the institution who register for various courses.
Courses – Academic subjects or programs offered by different departments.
Enrollments – A bridge table capturing the many-to-many relationship between students and courses.
Faculty – Instructors responsible for teaching courses, each associated with a specific department.

Creating the Base Tables:
We’ll start by creating the Students, Courses and Enrollments tables.

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

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

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

👨‍🏫 Adding Faculty Table (DDL):

CREATE TABLE Faculty (
FacultyID NUMBER PRIMARY KEY,
FacultyName VARCHAR2(50) NOT NULL,
Dept VARCHAR2(30),
Email VARCHAR2(50) UNIQUE
);

✍️ Inserting Sample Data (DML):
Let’s insert students, courses, faculty, and enrollments.

Students Table:
INSERT INTO Students VALUES (1, 'thush', 'CSb', DATE '2006-05-15', 'thush@college.dbms');
INSERT INTO Students VALUES (2, 'kesh', 'CIVIL', DATE '2009-11-20', 'kesh@college.dbms');
INSERT INTO Students VALUES (3, 'Grace', 'IT', DATE '2004-01-10', 'Grace@college.dbms');

Courses Table:
INSERT INTO Courses VALUES (100, 'Database Systems', 4);
INSERT INTO Courses VALUES (101, 'Digital Electronics', 3);
INSERT INTO Courses VALUES (102, 'EDA, 3);

Faculty Table:
INSERT INTO Faculty VALUES (201, 'Dr. Richard', 'CSE', 'richard@college.edu');
INSERT INTO Faculty VALUES (202, 'Dr. Meena', 'ECE', 'meena@college.edu');
INSERT INTO Faculty VALUES (203, 'Dr. Kumar', 'MECH', 'kumar@college.edu');

Enrollments Table:
INSERT INTO Enrollments VALUES (1, 1, 101, 'A');
INSERT INTO Enrollments VALUES (2, 1, 102, 'B');
INSERT INTO Enrollments VALUES (3, 2, 103, 'A');
INSERT INTO Enrollments VALUES (4, 3, 101, 'C');

The records have now been added to their respective tables.

🔧Altering Tables:
Let’s add a Phone Number column to Students.

ALTER TABLE Students
ADD PhoneNo CHAR(10);

The Phone Number column has now been included in the table.

✅Adding Constraints:
Ensure Credits in Courses are between 1 and 5.

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

🔍SELECT with Functions:
Display student names in uppercase and the length of their email IDs.

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

📊Aggregate Functions:
Find average credits and total students.

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

🔗JOIN Operation:
List all students with their enrolled courses and grades.

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;

📌GROUP BY with HAVING:
Show departments with more than 2 students.

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

👁️Creating Views:
Create a view for student-course-grade info.

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:
Update a student’s grade dynamically.

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;

🎯 SQL Tutorial Summary:

🏗️ Created tables: Students, Courses, Enrollments, and Faculty (with constraints).
✍️ Inserted records into Students table with different departments.
🔧 Altered table to add PhoneNo (10-digit numbers).
📏 Applied constraints: Course credits between 1 and 5.
🔠 SELECT with functions: Displayed names in UPPERCASE & email length.
📊 Aggregate functions: Calculated average credits & total students.
🔗 JOIN operation: Listed Student Name, Course Name, and Grade.
🗂️ GROUP BY + HAVING: Department-wise student count (only >2).
👁️ View (StudentCoursesView): Simplified Student–Course–Grade display.
⚙️ Stored Procedure (UpdateGrade): Dynamically updated student grades.

✨ This tutorial covers DDL, DML, Constraints, Functions, Joins, Grouping, Views, and Stored Procedures — a complete SQL learning pack!🚀