DEV Community: Poorvika N

🚀 AWS CodePipeline – Automating CI/CD in AWS Cloud

Poorvika N — Thu, 18 Dec 2025 17:10:48 +0000

AWS CodePipeline is an AWS service used to automate software release processes
It helps teams deliver applications faster, safer, and more consistently
Eliminates manual deployment errors and improves efficiency
Plays a key role in modern DevOps practices

🔍 Service Overview

AWS CodePipeline is a fully managed CI/CD service
Automatically builds, tests, and deploys applications
Triggers pipeline execution whenever source code changes
Pipelines are divided into stages like:

Source
Build
Test
Deploy

AWS manages infrastructure, scaling, and availability
Developers can focus on coding instead of managing servers

⚙️ Key Features

Fully managed CI/CD orchestration service
Automates build, test, and deployment workflows
Supports multiple source providers like AWS CodeCommit, GitHub, and Bitbucket
Seamlessly integrates with AWS CodeBuild, CodeDeploy, Elastic Beanstalk, and CloudFormation
Provides a visual pipeline view for monitoring execution status
Event-driven architecture that triggers pipelines automatically on code changes
Secure access and permission management using AWS IAM roles
Supports parallel execution and custom approval stages
Highly scalable, reliable, and fault-tolerant

☁️ AWS Category / Cloud Domain

AWS Category: Developer Tools

Cloud Domain:DevOps / Continuous Integration & Continuous Delivery (CI/CD)

AWS CodePipeline belongs to the Developer Tools category, which focuses on automating and simplifying application development and deployment processes.

🔄 Where It Fits in the Cloud / DevOps Lifecycle

AWS CodePipeline is at the core of the DevOps lifecycle, enabling continuous automation and feedback:
Source Stage – Fetches the latest code from repositories like GitHub or CodeCommit
Build Stage – Compiles source code, runs unit tests, and generates build artifacts
Test Stage – Executes automated tests to ensure quality and stability
Approval Stage (Optional) – Allows manual review before deployment
Deploy Stage – Deploys applications to services like EC2, Lambda, ECS, or Elastic Beanstalk
By automating these stages, CodePipeline helps reduce deployment failures, improves release frequency, and ensures faster feedback loops.

💻 Programming Language / Access Methods

AWS CodePipeline supports multiple access methods, making it flexible for different teams and workflows:

AWS Management Console – User-friendly web interface
AWS Command Line Interface (CLI)
AWS SDKs, including:

Python (Boto3)

Java

JavaScript (Node.js)

REST APIs
Infrastructure as Code (IaC) using:

AWS CloudFormation

AWS Cloud Development Kit (CDK)

This allows developers to manage pipelines programmatically and integrate them into automated workflows.

💰 Pricing Model

AWS CodePipeline follows a simple and transparent pay-as-you-go pricing model:
Charged per active pipeline per month
No upfront fees or long-term contracts
Costs remain predictable as pipelines scale
Additional charges may apply for related services such as:
AWS CodeBuild
Amazon S3
Amazon EC2
AWS Free Tier includes limited usage for new users
This pricing model makes it suitable for both students and enterprises.

📌 Real-World Use Case Example

A web application team uses GitHub as their source repository. Whenever a developer pushes new code:
CodePipeline automatically pulls the code
CodeBuild compiles and tests the application
CodeDeploy deploys the updated version to EC2 instances
The entire process completes without manual intervention
This ensures faster releases, fewer errors, and consistent deployments.

✅ Conclusion

AWS CodePipeline is a powerful and essential DevOps service that simplifies CI/CD automation in the AWS cloud. By automating the software delivery lifecycle, it helps organizations achieve faster deployment cycles, improved reliability, and better collaboration between development and operations teams. For students and professionals learning AWS Cloud Driven DevOps, CodePipeline provides a strong foundation in modern CI/CD practices.

🔧 Puppet: Automating Infrastructure as Code in DevSecOps

Poorvika N — Thu, 18 Dec 2025 16:41:56 +0000

Modern cloud-driven DevOps environments require automation to handle large-scale infrastructure efficiently.
Manual infrastructure management is time-consuming, error-prone, and difficult to scale.
Configuration management tools help automate infrastructure operations and maintain consistency.
Puppet is one of the most widely used configuration management tools in DevOps.
In the DevSecOps Periodic Table, Puppet is listed as Element 61.
Its position highlights its importance in infrastructure automation and security-focused DevOps practices.

Overview of Puppet

Puppet is a configuration management and infrastructure automation tool.
It helps organizations define, deploy, and manage infrastructure in a consistent and repeatable manner.
Administrators write code to describe the desired state of systems instead of configuring servers manually.
Puppet continuously monitors systems and ensures they remain in the defined desired state.
It follows a declarative approach, focusing on what the system should look like rather than how to configure it.
This approach reduces human errors and improves system reliability and stability.
Puppet is widely used in: Cloud environments Data centers Hybrid infrastructures
It efficiently manages servers, applications, and services at scale.

Key Features of Puppet

1. Infrastructure as Code (IaC)

Puppet enables teams to manage infrastructure using code, making configurations version-controlled, auditable, and repeatable. This aligns infrastructure management with software development best practices.

2. Automated Configuration Management

Puppet automatically enforces system configurations across multiple machines, ensuring consistency. If any configuration drifts from the desired state, Puppet detects and corrects it automatically.

3. Declarative Language

Using Puppet’s Domain Specific Language (DSL), users describe the desired system state. Puppet handles the implementation details internally, simplifying complex configurations.

4. Scalability

Puppet is designed to manage thousands of nodes efficiently, making it suitable for large enterprise environments as well as growing cloud infrastructures.

5. Compliance and Reporting

Puppet provides reporting and auditing capabilities that help organizations meet security and compliance requirements. This is especially valuable in regulated industries.

6. Agent-Based Architecture

Puppet uses a client-server model where agents run on managed nodes and periodically communicate with a Puppet server to apply configurations.

How Puppet Fits into DevOps and DevSecOps

Role in DevOps:
In DevOps practices, Puppet helps automate infrastructure provisioning and configuration, enabling faster and more reliable deployments. By eliminating manual setup, Puppet reduces deployment time, minimizes configuration errors, and supports continuous integration and continuous delivery (CI/CD) pipelines.

Role in DevSecOps:

In DevSecOps, security is integrated throughout the development lifecycle. Puppet supports DevSecOps by:

Enforcing security baselines and system hardening

Applying patches and updates consistently

Ensuring compliance with organizational security policies

Detecting and correcting configuration drift that could introduce vulnerabilities

By embedding security rules directly into infrastructure code, Puppet ensures that security is automated and not treated as an afterthought.

Programming Language Used in Puppet

Puppet uses a Domain Specific Language (DSL) designed specifically for configuration management. This DSL is simple, readable, and declarative, making it accessible even to users with limited programming experience.

Internally, Puppet is built using Ruby, which provides flexibility and extensibility to the tool. Knowledge of Ruby is beneficial for advanced Puppet usage, such as writing custom modules.

Parent Company of Puppet

Puppet is developed and maintained by Puppet, Inc., a company founded in 2005. Puppet, Inc. focuses on infrastructure automation and has contributed significantly to the evolution of DevOps and DevSecOps practices worldwide.

Open Source or Paid

Puppet offers both open-source and commercial versions:

Puppet Open Source: Free to use, suitable for individuals, students, and small teams.

Puppet Enterprise: A paid version that includes advanced features such as enhanced reporting, role-based access control, and enterprise-grade support.

This dual model allows users to start with the open-source version and scale to enterprise features as organizational needs grow.

Conclusion

Puppet (61) plays a vital role in the DevSecOps ecosystem by enabling automated, secure, and scalable infrastructure management. Its ability to enforce consistency, integrate security practices, and support Infrastructure as Code makes it an essential tool for modern DevOps teams. As organizations increasingly adopt cloud and hybrid environments, tools like Puppet continue to be critical in achieving reliable and secure software delivery.

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

Poorvika N — Wed, 08 Oct 2025 17:58:55 +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

💾 Transactions, Deadlocks & Log-Based Recovery in Oracle SQL

Poorvika N — Wed, 08 Oct 2025 12:26:30 +0000

We will explore key DBMS concepts: Transactions, Deadlocks, and Log-Based Recovery using an Accounts table in Oracle SQL.

1️⃣ Schema Setup

CODE:

BEGIN
EXECUTE IMMEDIATE 'DROP TABLE Accounts';
EXCEPTION
WHEN OTHERS THEN
IF SQLCODE != -942 THEN
RAISE;
END IF;
END;
/

CREATE TABLE Accounts (
acc_no NUMBER PRIMARY KEY,
name VARCHAR2(50),
balance NUMBER
);

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

COMMIT;

Explanation:

NUMBER is used for numeric columns in Oracle.
VARCHAR2 is used for text columns.
COMMIT finalizes the inserts so other sessions can see the data.
After this, the Accounts table has three accounts with initial balances.

2️⃣ Transaction – Atomicity & Rollback

CODE:

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

Explanation

Oracle automatically starts a transaction with any DML statement.
Atomicity ensures that either all updates succeed or none do.
ROLLBACK cancels changes.
After rollback, balances of Alice and Bob remain unchanged, ensuring data consistency.

3️⃣ Deadlock Simulation

Session 1:

UPDATE Accounts SET balance = balance - 100 WHERE name = 'Alice';

Session 2:

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

Explanation

Session 1 locks Alice’s account.
Session 2 locks Bob’s account.
When each session tries to update the other’s locked row, Oracle detects a deadlock.
Oracle automatically rolls back one transaction to resolve the deadlock.

4️⃣ Log-Based Recovery

CODE:

UPDATE Accounts SET balance = 2500 WHERE name = 'Charlie';
ROLLBACK;
SELECT * FROM Accounts;

Explanation

Oracle writes all changes to redo logs.
When we ROLLBACK, Oracle uses the logs to undo changes.
This ensures the database stays consistent even if a failure occurs.

Database Magic: Automating Tasks with Cursor and Trigger✨

Poorvika N — Wed, 08 Oct 2025 11:12:39 +0000

🔹 Cursor:

A cursor in SQL is used to retrieve and process rows one by one from a result set.
It is mainly used when we want to perform operations row-by-row instead of all at once.

Example Use Case:
Display employee names whose salary > 50,000 by processing each record individually.

🔹 Trigger:

A trigger is a stored program that is automatically executed in response to certain events (like INSERT, UPDATE, or DELETE) on a table.

Example Use Case:
When a new student is inserted into the Students table, automatically insert a record in the Student_Audit table to log the registration.

1️⃣ Cursor: Process Cursor with Condition

Problem Statement:

We want to iterate through all employees whose salary > 50,000 and print or log their names (or do something per-employee).

CODE:

CREATE TABLE Employee (
EmpID INT PRIMARY KEY,
EmpName VARCHAR(100),
Salary DECIMAL(18,2)
);

INSERT INTO Employee (EmpID, EmpName, Salary) VALUES
(1, 'Alice', 60000),
(2, 'Bob', 48000),
(3, 'Charlie', 75000),
(4, 'David', 50000),
(5, 'Eve', 90000);

DECLARE @vEmpName VARCHAR(100);
DECLARE @vSalary DECIMAL(18,2);

DECLARE EmployeeCursor CURSOR FOR
SELECT EmpName, Salary
FROM Employee
WHERE Salary > 50000;

OPEN EmployeeCursor;

FETCH NEXT FROM EmployeeCursor INTO @vEmpName, @vSalary;

WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'Employee: ' + @vEmpName + ', Salary = ' + CAST(@vSalary AS VARCHAR(20));

FETCH NEXT FROM EmployeeCursor INTO @vEmpName, @vSalary;
END;

CLOSE EmployeeCursor;
DEALLOCATE EmployeeCursor;

Explanation:

We create a cursor EmployeeCursor selecting EmpName, Salary for those employees whose salary > 50,000.
OPEN the cursor, then FETCH NEXT row by row into variables.
@@FETCH_STATUS = 0 means the fetch succeeded.
Inside the loop, we do whatever per-employee logic (here just PRINT).
Finally, CLOSE and DEALLOCATE to free resources.

2️⃣ Trigger: AFTER INSERT Trigger on Student → Audit Log

Problem Statement:

Whenever a new student is inserted into the Student table, automatically insert a row into Student_Audit to log that registration (e.g. with timestamp, action, etc.).

CODE:

CREATE TABLE Student (
StudentID INT PRIMARY KEY,
StudentName VARCHAR(100),
Department VARCHAR(100),
YearOfStudy INT,
CGPA DECIMAL(4,2)
);
GO
CREATE TABLE Student_Audit (
AuditID INT IDENTITY(1,1) PRIMARY KEY,
StudentID INT,
StudentName VARCHAR(100),
Action VARCHAR(20),
ActionTime DATETIME
);
GO
CREATE TRIGGER trg_AfterStudentInsert
ON Student
AFTER INSERT
AS
BEGIN
SET NOCOUNT ON;
INSERT INTO Student_Audit (StudentID, StudentName, Action, ActionTime)
SELECT i.StudentID, i.StudentName, 'INSERT', GETDATE()
FROM inserted AS i;
END;
GO
INSERT INTO Student (StudentID, StudentName, Department, YearOfStudy, CGPA)
VALUES (101, 'Rahul', 'CSE', 1, 9.2);

INSERT INTO Student (StudentID, StudentName, Department, YearOfStudy, CGPA)
VALUES (102, 'Sita', 'ECE', 2, 8.8);
GO
SELECT * FROM Student_Audit;
GO

Explanation:

The AFTER INSERT trigger fires after the row(s) are inserted into Student.
The trigger uses the inserted pseudo-table (available in SQL Server) to get the newly inserted rows.
For each inserted row, it inserts a corresponding log row in Student_Audit with columns like Action = 'INSERT' and current timestamp.
So you always have a record of new students inserted.

ACID Properties

Poorvika N — Mon, 06 Oct 2025 16:52:34 +0000

Atomicity in Database Transactions

Atomicity is the “all or nothing” property of a database transaction.

It ensures that a transaction — which may include multiple SQL statements — is treated as a single indivisible unit of work.
That means either all operations of the transaction succeed, or none of them do.
If any part of the transaction fails (due to error, power loss, or system crash), the database rolls back to its previous stable state — ensuring that no partial updates occur.

1. Create a table Accounts(acc_no INT PRIMARY KEY, name VARCHAR(50), balance INT). Insert 3 sample rows.

2. Atomicity: Start a transaction that transfers money. Rollback midway → ensure no partial update remains.

Atomicity ensures all operations in a transaction succeed or none do.

Example: Transfer ₹1000 from Ravi → Priya and rollback midway

Result: No partial transfer — both balances remain same (proving Atomicity).

3. Consistency: Try inserting a record with negative balance → should be rejected.

Consistency ensures that data integrity rules (constraints) are not violated.

Example: Try inserting a record with negative balance

This will fail because of the CHECK (balance >= 0) constraint.
Result: Database remains in a consistent state.

4. Isolation: Run two sessions at once – one updating, the other reading → observe isolation.

Isolation ensures that concurrent transactions do not interfere with each other.

Depending on isolation level:

READ UNCOMMITTED → Session 2 might see uncommitted data (dirty read)
READ COMMITTED (default) → Session 2 sees only committed data
REPEATABLE READ / SERIALIZABLE → Session 2 waits or reads the old value until Session 1 commits Result: Each session’s visibility depends on the isolation level — proving isolation behavior.

5. Durability: Commit a transaction → restart DB → ensure data persists.

Durability ensures that once a transaction is committed, changes are permanent, even after a crash or restart.

Result: The updated balance remains — showing durability.

NORMALIZATION

Poorvika N — Mon, 06 Oct 2025 15:57:37 +0000

Define Normalization?

Normalization is a process in database design used to organize data efficiently. The main goal is to minimize redundancy (repeated data) and avoid inconsistencies. It ensures that each piece of data is stored only once, making the database more reliable and easier to maintain.

In practice, normalization involves breaking a large, complex table into smaller, simpler tables and linking them using foreign keys. This structure allows data to be stored logically while preserving relationships between tables.

Why is Normalization Needed?

Normalization provides several key benefits:

Eliminates Data Duplication – Storing data only once reduces redundancy, preventing unnecessary repetition.
Ensures Data Consistency – Updating a single value automatically updates it everywhere it’s used, maintaining accuracy.
Simplifies Data Updates and Maintenance – Smaller, organized tables are easier to modify without errors.
Saves Storage Space – Avoiding repeated data reduces the storage needed.
Improves Query Performance – Structured data helps the database retrieve information faster.

Normal Forms

Normalization is done in stages, called normal forms. Each stage fixes certain types of problems in the data.

First Normal Form (1NF)

A table is in 1NF if each column contains atomic (indivisible) values.

Repeating groups or arrays are not allowed.
Each record (row) must be unique, usually ensured by a primary key.

Second Normal Form (2NF)

A table is in 2NF if it is in 1NF and no non-key attribute depends on part of a composite key.
Eliminates partial dependency, ensuring that non-key data depends on the whole primary key.

Third Normal Form (3NF)

A table is in 3NF if it is in 2NF and no non-key attribute depends on another non-key attribute.
Eliminates transitive dependency.

Boyce-Codd Normal Form (BCNF)

A stricter version of 3NF.
Every determinant must be a candidate key.
Helps remove rare anomalies not covered by 3NF.

Fourth Normal Form (4NF)

Addresses multi-valued dependencies.
Ensures that if one attribute depends on another, it doesn’t repeat unnecessarily with other independent attributes.

Fifth Normal Form (5NF)

Deals with join dependencies.
Ensures data can be reconstructed from smaller tables without redundancy.

We shall use the following data as the starting point:

1. Identify anomalies (insertion, update, deletion) in this table.

🔸 Insertion Anomaly

We can’t add a new course until at least one student registers for it, because all course data is mixed with student data.

e.g., Can’t add a new course C104 – ML – Dr. Sharma without a student.

🔸 Update Anomaly

If an instructor’s phone number changes, it must be updated in multiple rows.

e.g., Dr. Kumar’s phone number appears twice.

🔸 Deletion Anomaly

If all students drop DBMS, deleting those rows also deletes Dr. Kumar and the course DBMS information.

2. Convert the table to 1NF and write the SQL CREATE TABLE statement for it.

✅ The table already satisfies 1NF because:

All values are atomic.
Each row is unique (based on StudentID + CourseID).

We’ll explicitly define a composite primary key (StudentID, CourseID).

3. Convert the table to 2NF and write SQL CREATE TABLE statements for the resulting tables, including primary keys.

In the current table:

StudentName depends only on StudentID
CourseName, Instructor, and InstructorPhone depend only on CourseID

So we separate Student and Course information.

4. Convert the table to 3NF and write SQL CREATE TABLE statements, including foreign keys.

Here, in Courses, InstructorPhone depends on Instructor, not on CourseID.

So we separate instructor details into a new table.

5. Insert the sample data into the normalized tables using INSERT INTO statements.

6. Write a query to list all students along with their courses and instructor names using JOINs.

Indexing, Hashing & Query Optimization

Poorvika N — Mon, 06 Oct 2025 14:30:56 +0000

Indexing

Indexing is a method used to speed up the retrieval of data from a database. It works by creating a separate structure (called an index) that allows the database to find records quickly without scanning the entire table.

Common types of indexes include:

B-Tree Index – ideal for search operations and sorting data.

B+ Tree Index – optimized for queries that involve ranges.

Hash Index – best suited for exact match searches.

Hashing

Hashing involves applying a hash function to a key (such as a department name) to generate a hash value, which directly points to the location of the record.
It provides fast access for equality searches.
It is not efficient for range queries, since hash values do not maintain any order.

Query Optimization

Query optimization is the process of determining the most efficient way to run a database query.
The query optimizer examines indexes, join methods, and conditions.
The goal is to reduce execution time and improve overall performance.

1. Create a table Students with fields (roll_no, name, dept, cgpa)

2. Insert at least 20 sample records.

3. Create a B-Tree index on the roll_no column of the Students table.

4. Execute a query to fetch the details of a student with roll_no = 110.

5. Create a B+ Tree index on the cgpa column of the Students table.

6. Write a query to display all students with cgpa > 8.0.

7. Create a Hash index on the dept column of the Students table.

8. Run a query to retrieve all students from the 'CSBS' department.

COLLEGE STUDENT & COURSE MANAGEMENT SYSTEM

Poorvika N — Thu, 21 Aug 2025 17:33:39 +0000

Introduction:

In this blog, we explore the design and implementation of a College Student & Course Management System using Oracle SQL on the LiveSQL platform. The system highlights how different SQL operations can be applied to manage and organize academic data including students, courses, faculty, and enrollments.

The project mainly focuses on:
=>Creating tables to represent different entities.
=>Inserting records to populate the database with sample data. =>Applying constraints to maintain accuracy and consistency.
=>Running queries with functions, aggregates, joins, and group operations.
=>Building a view for simplified access to combined data.
=>Writing a stored procedure to handle automated updates.

By implementing this system, we get a clear understanding of how SQL can be used in a real-world college scenario to handle tasks such as student course registrations, faculty records, grade management, and department-level statistics.

SCHEMA DESIGN FOR COLLEGE MANAGEMENT SYSTEM:

The designed database has four core tables: Students, Courses, Enrollments, and Faculty.
Each table holds specific information and is connected through primary and foreign keys to maintain proper relationships.

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

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

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

1. DDL – Create a Table

=> DDL defines the structure of database objects (tables, views, indexes).
=> We created a Faculty table with FacultyID, FacultyName, Dept, and Email.

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

2. DML – Insert Data

=> DDML (Data Manipulation Language) is used to add, update, delete, or fetch records in a table.
=> We inserted 3 faculty members (Dr. Neha Sharma, Dr. Rajeev Nair, Dr. Meena Reddy) into the Faculty table using INSERT INTO.

INSERT INTO Faculty (FacultyID, FacultyName, Dept, Email)
VALUES (1, 'Dr. Neha Sharma', 'CSE', 'neha.sharma@college.com');

INSERT INTO Faculty (FacultyID, FacultyName, Dept, Email)
VALUES (2, 'Dr. Rajeev Nair', 'ECE', 'rajeev.nair@college.com');

INSERT INTO Faculty (FacultyID, FacultyName, Dept, Email)
VALUES (3, 'Dr. Meena Reddy', 'MECH', 'meena.reddy@college.com');

3. ALTER Table – Add Column

=> ALTER modifies the structure of an existing table (add/modify/drop columns).
=> We added a new column PhoneNo in the Students table.

ALTER TABLE Students
ADD PhoneNo NUMBER(10);

4. Defining Constraints

=> Constraints enforce rules on data to maintain integrity (like N NULL, UNIQUE, CHECK).
=> We applied a CHECK constraint so that Credits in Courses must be between 1 and 5.

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

5. SELECT with Functions

=> Functions in SELECT perform operations like text conversion, length calculation, etc.
=> We displayed student names in uppercase and showed the length of their email IDs.

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

6. Aggregate Functions

=> Aggregate functions are used to perform calculations on a set of values and return a single result. Common functions include AVG, COUNT, SUM, MIN, and MAX. They are often used in reporting and analysis.
=> SELECT AVG(Credits) AS Avg_Credits FROM Courses; → calculated the average credits of all courses.
=> SELECT COUNT(*) AS Total_Students FROM Students; → counted the total number of students in the Students table.

SELECT AVG(Credits) AS Avg_Credits
FROM Courses;

SELECT COUNT(*) AS Total_Students
FROM Students;

7. JOIN Operation

=> JOIN combines related data from multiple tables using common keys.
=> We listed students with the courses they are enrolled in and their 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;

8. GROUP BY with HAVING

=> GROUP BY groups rows based on column values, and HAVING filters groups.
=> We grouped students by department and displayed only departments with more than 2 students.

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

9. Views

=> A View is a virtual table created from a query for easier data access.
=> We created StudentCoursesView to show StudentName, CourseName, and Grade.

CREATE OR REPLACE 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;

10. Stored Procedure

=> A Stored Procedure is a saved block of SQL statements that can be executed when needed.
=> We wrote UpdateGrade procedure to update a student’s grade in Enrollments.

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;