DEV Community: ATHEEBA PARVEEN J A 24CB002

Amazon Athena

ATHEEBA PARVEEN J A 24CB002 — Fri, 19 Dec 2025 00:32:31 +0000

SERVERLESS INTERACTIVE ANALYTICS ON AWS

In the era of cloud-native architectures and data-driven decision-making, organizations need fast and scalable ways to analyze large datasets. Amazon Athena, a serverless query service from AWS, addresses this need by enabling interactive analytics without managing infrastructure.

This blog explores Amazon Athena’s features, cloud domain, and its role in the cloud and DevOps lifecycle.

Service Overview

Amazon Athena is a serverless, interactive query service that allows users to analyze data directly stored in Amazon S3 using standard SQL.
Athena eliminates the need for setting up or managing databases and clusters. Users simply define the schema and start querying data on demand, making it ideal for ad-hoc analysis and log analytics.

Key Features of Amazon Athena

Serverless Architecture:
No infrastructure to provision, manage, or scale.
Standard SQL Support:
Uses ANSI SQL, making it easy for data analysts and engineers.
Direct S3 Querying:
Queries data directly from Amazon S3 without data movement.
Fast & Scalable:
Automatically scales to handle large datasets.
Integration with AWS Services:
Works seamlessly with AWS Glue Data Catalog, Amazon QuickSight, CloudWatch, and IAM.
Secure Access Control:
Uses IAM policies and encryption for secure data access.

AWS Category / Cloud Domain

Amazon Athena belongs to the Analytics category in AWS.

Cloud Domain:
1.Data Analytics
2.Big Data Processing
3.Serverless Computing

Role in Cloud & DevOps Lifecycle

Amazon Athena fits into multiple stages of the cloud and DevOps lifecycle:

Monitoring & Logging:
Analyze application and infrastructure logs stored in S3.
Data Analysis & Reporting:
Enable quick insights during development and production stages.
Security & Compliance:
Query security logs and audit trails for compliance checks.
DevOps Observability:
Supports troubleshooting and root cause analysis without complex setup.

In DevOps workflows, Athena provides fast feedback by enabling teams to analyze operational data efficiently.

Programming Language & Access Methods

Amazon Athena primarily uses SQL (ANSI-compliant)

Access Methods:

AWS Management Console
AWS CLI
AWS SDKs (Python, Java, JavaScript, etc.)
JDBC / ODBC drivers
Integration with BI tools like Amazon QuickSight

Pricing Model

Amazon Athena follows a pay-as-you-go pricing model.

Charged based on the amount of data scanned per query
No upfront costs or minimum fees
Cost can be optimized by:

Using columnar formats (Parquet, ORC)
Partitioning data
Compressing files

This makes Athena cost-effective for both small teams and large enterprises.

Why Amazon Athena Matters

Amazon Athena simplifies data analytics by removing infrastructure complexity. Its serverless nature, SQL compatibility, and seamless AWS integration make it a powerful tool for cloud engineers, DevOps teams, and data analysts.

For anyone learning cloud computing or DevOps, Amazon Athena is an essential service to understand for real-time analytics and operational insights.

Final Thoughts

Amazon Athena enables fast, scalable, and cost-efficient analytics directly on cloud storage. Its role in monitoring, analytics, and DevOps observability highlights its importance in modern cloud-native environments.

Azure DevOps Pipelines

ATHEEBA PARVEEN J A 24CB002 — Thu, 18 Dec 2025 15:12:18 +0000

A Core Tool in Modern DevOps & DevSecOps

In today’s fast-paced software development world, automation is the backbone of successful DevOps and DevSecOps practices. One such powerful automation tool featured in the DevSecOps Periodic Table is Azure DevOps Pipelines. This blog explores what Azure DevOps Pipelines is, its key features, and how it fits into DevOps and DevSecOps workflows.

Overview of Azure DevOps Pipelines

Azure DevOps Pipelines is a cloud-based CI/CD (Continuous Integration and Continuous Delivery) service provided as part of Azure DevOps. It enables developers to automatically build, test, and deploy applications to multiple environments.

Azure Pipelines supports modern development practices by automating the entire software delivery lifecycle—from code commit to production deployment—ensuring faster releases with high reliability.

It works seamlessly with popular version control systems such as:

Azure Repos
GitHub
GitHub Enterprise
Bitbucket

Key Features of Azure DevOps Pipelines

1. Continuous Integration (CI):
Automatically builds and tests code whenever changes are pushed to the repository.

2. Continuous Delivery (CD):
Enables automated deployment to environments such as development, staging, and production.

3. Multi-Platform Support:
Runs pipelines on Windows, Linux, and macOS agents.

4. YAML-based Pipelines:
Pipeline definitions are written as code using YAML, improving version control and reproducibility.

5. Cloud & On-Premise Deployment:
Supports deployment to Azure, AWS, Google Cloud, and on-premise servers.

6. Extensive Marketplace:
Provides ready-to-use extensions for testing, security scanning, containerization, and monitoring.

Azure DevOps Pipelines in DevOps & DevSecOps

Role in DevOps
Azure DevOps Pipelines plays a central role in DevOps by:

Automating build and release processes
Reducing manual errors
Enabling rapid and frequent deployments
Improving collaboration between development and operations teams

Role in DevSecOps
In DevSecOps, security is integrated early into the pipeline. Azure DevOps Pipelines supports this by:

Integrating security tools like SonarQube, Snyk, OWASP ZAP, and Trivy
Enabling automated vulnerability scans during CI/CD
Enforcing security policies through pipeline gates
Supporting Infrastructure as Code (IaC) security checks

This ensures security is not an afterthought but a continuous process.

Supported Programming Languages

Azure DevOps Pipelines supports almost all major programming languages, including:

Java
Python
JavaScript / Node.js
C# / .NET
C / C++
Go
Ruby
PHP

This flexibility makes it suitable for both enterprise-level applications and startups.

Parent Company

Azure DevOps Pipelines is developed and maintained by Microsoft, under the Azure cloud ecosystem.

Open Source or Paid?

Azure DevOps Pipelines is not fully open source
It follows a freemium pricing model

Pricing Overview:

Free tier includes:

1 Microsoft-hosted parallel job
Unlimited builds for open-source projects

Paid plans are required for:

Additional parallel jobs
Advanced enterprise-level features

This makes it accessible for students and small teams while scaling effectively for large organizations.

Why Azure DevOps Pipelines Matters

Azure DevOps Pipelines stands out as a reliable and scalable CI/CD tool that bridges development, security, and operations. Its flexibility, cloud-native design, and strong DevSecOps integration make it a key component in modern software delivery pipelines.

Whether you are a student, developer, or DevOps engineer, understanding Azure DevOps Pipelines provides a strong foundation for building secure, automated, and efficient software systems.

In a Nutshell

Azure DevOps Pipelines is more than just a CI/CD tool—it is a strategic enabler for DevOps and DevSecOps culture. Its inclusion in the DevSecOps Periodic Table highlights its importance in building secure and resilient applications.

MongoDB BLOG

ATHEEBA PARVEEN J A 24CB002 — Wed, 08 Oct 2025 16:34:00 +0000

Hands-On with MongoDB: Performing CRUD Operations on a College Student Database

MongoDB is a popular NoSQL database that stores data in JSON-like documents, making it flexible and easy to use. In this blog, we’ll explore CRUD operations (Create, Read, Update, Delete) using a simple college student schema.
This is a perfect exercise for anyone looking to get hands-on experience with MongoDB.

Student Schema

We’ll use a collection called students. Each document has the following structure:

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

Create (Insert) Records

Let’s start by adding 5 students to the database.

db.students.insertMany([
  { "student_id": "S001", "name": "Santhosh", "age": 20, "department": "CSBS", "year": 2, "cgpa": 9 },
  { "student_id": "S002", "name": "Kriti", "age": 20, "department": "CSE", "year": 4, "cgpa": 7.8 },
  { "student_id": "S003", "name": "Meera", "age": 19, "department": "ECE", "year": 1, "cgpa": 7.8 },
  { "student_id": "S004", "name": "Rohit", "age": 22, "department": "CSE", "year": 3, "cgpa": 6.9 },
  { "student_id": "S005", "name": "Siddu", "age": 20, "department": "ECE", "year": 3, "cgpa": 9.5 }
])

Read (Query) Records

Find students with CGPA greater than 8

{ cgpa: { $gt: 8 } }

Find students from the Computer Science department

{"department":{"in": ["CSE"]} }

Update Records

Update the CGPA of a specific student (S001)

{ "$set: : {"cgpa":8.9}}

Increase the year of study for all 3rd year students by 1.

{ "$inc" : {"year" : 1}}

Delete

Delete one student record by student_id.

{"student_id" : S005 }

Delete all students having CGPA < 7.5.

{ "cgpa" : {"$lt" : 7.5}}

CONCLUSION

MongoDB makes it easy to handle data with flexible documents. Performing CRUD operations allows you to:

Insert multiple records quickly
Query data efficiently
Update records in bulk or individually
Delete unnecessary or outdated data

Practicing CRUD operations is a great way to get comfortable with NoSQL databases and start building your own projects.

NORMALIZATION

ATHEEBA PARVEEN J A 24CB002 — Wed, 08 Oct 2025 15:03:09 +0000

DATA NORMALIZATION

Data Normalization is a process in database design used to organize data to reduce redundancy and improve data integrity. It involves structuring a database in such a way that:

Each piece of information is stored only once.
Dependencies between data are logical and minimal.
Anomalies (insertion, update, deletion) are avoided.

WHAT WE DO

To understand 1NF, 2NF, and 3NF and implement them using SQL CREATE TABLE statements.

BASE TABLE

IDENTIFYING ANOMALIES

1.Insertion Anomaly
You cannot add a new course (e.g., “C104 - Cloud Computing”) unless a student enrolls in it.
Similarly, if a new instructor is assigned to a course but no student has registered yet, you cannot record that instructor’s details.
Example:
You want to add Dr. Sen teaching C104 (Cloud Computing), but since no student has enrolled, you can’t insert this record because StudentID is mandatory.

2.Update Anomaly
Instructor or course details are repeated in multiple rows.
If the instructor’s phone number changes, you must update it in all rows where the instructor appears.
Example:
If Dr. Kumar’s phone number changes, you need to update it in two rows (for Arjun and Priya).
If you miss one, the table becomes inconsistent.

3.Deletion Anomaly
If a student drops all courses, their record is deleted — and you might also lose information about a course or instructor.
Example:
If Priya (S02) withdraws, and she’s the only student taking DBMS, then deleting her record will also remove all information about DBMS and Dr. Kumar.

Convert to 1NF (First Normal Form)

Rule:
No repeating groups or arrays.
Each cell contains atomic values.
The given table is already in 1NF (all values are atomic).

CREATE TABLE StudentCourse (
    StudentID VARCHAR(10),
    StudentName VARCHAR(50),
    CourseID VARCHAR(10),
    CourseName VARCHAR(50),
    Instructor VARCHAR(50),
    InstructorPhone VARCHAR(15)
);

Convert to 2NF (Second Normal Form)

Rule:
Must be in 1NF.
No partial dependency — non-key attributes depend on the entire primary key.
Problem:
In 1NF, composite key = (StudentID, CourseID).
Attributes like StudentName depend only on StudentID, not on the full key.
Attributes like CourseName, Instructor, InstructorPhone depend only on CourseID.
Solution: Split into 3 tables
Student
Course
Enrollment (joins students and courses)

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)
);

Convert to 3NF (Third Normal Form)

Rule:
Must be in 2NF.
No transitive dependencies (non-key attribute depends on another non-key attribute).
Problem:
In the Course table, InstructorPhone depends on Instructor, not directly on CourseID.
Solution:
Split Instructor details into a separate table.

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)
);

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)
);

Insert Sample Data

-- ===============================
-- INSERT DATA INTO 3NF TABLES
-- ===============================

-- Instructors
INSERT INTO Instructor VALUES ('I01', 'Dr. Kumar', '9876543210');
INSERT INTO Instructor VALUES ('I02', 'Dr. Mehta', '9123456780');
INSERT INTO Instructor VALUES ('I03', 'Dr. Rao', '9988776655');

-- Students
INSERT INTO Studet VALUES ('S01', 'Arjun');
INSERT INTO Studet VALUES ('S02', 'Priya');
INSERT INTO Studet VALUES ('S03', 'Kiran');

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

CONCLUSION

Database normalization organizes data to reduce redundancy and maintain integrity.
1NF (First Normal Form): Ensures that each column contains atomic values and eliminates repeating groups.
2NF (Second Normal Form): Removes partial dependencies by making sure all non-key attributes depend on the entire primary key.
3NF (Third Normal Form): Eliminates transitive dependencies, so non-key attributes depend only on the primary key.
Normalization helps prevent insertion, update, and deletion anomalies, making the database more consistent, efficient, and easier to manage

Cursor + Trigger in MySQL

ATHEEBA PARVEEN J A 24CB002 — Sat, 04 Oct 2025 17:00:24 +0000

In this tutorial, we will demonstrate:

Cursor: Processing rows with a condition.
Trigger: Automatically inserting audit logs after an insert operation.

We’ll use simple examples with screenshots from an online MySQL editor.

Cursor Example: Employees with Salary > 50,000

Step 1: Create Employee table & Insert sample data

CREATE TABLE Employee (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    salary INT
);

INSERT INTO Employee VALUES
(1, 'Alice', 40000),
(2, 'Bob', 55000),
(3, 'Charlie', 70000),
(4, 'David', 45000);

Step 2: Use a Cursor with Condition

DELIMITER //

CREATE PROCEDURE GetHighSalaryEmployees()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE empName VARCHAR(50);
    DECLARE cur CURSOR FOR SELECT emp_name FROM Employee WHERE salary > 50000;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO empName;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SELECT empName AS High_Salary_Employee;
    END LOOP;

    CLOSE cur;
END//
DELIMITER ;

Step 3: Call the Procedure

CALL GetHighSalaryEmployees();

Trigger Example: Student Registration Audit

Step 1: Create Student & Audit tables

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE Student_Audit (
    audit_id INT AUTO_INCREMENT PRIMARY KEY,
    student_id INT,
    name VARCHAR(50),
    registered_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Step 2: Create AFTER INSERT Trigger

DELIMITER //

CREATE TRIGGER after_student_insert
AFTER INSERT ON Students
FOR EACH ROW
BEGIN
    INSERT INTO Student_Audit (student_id, name)
    VALUES (NEW.student_id, NEW.name);
END //

DELIMITER ;

Step 3: Insert Data into Students

INSERT INTO Students VALUES (1, 'John Doe');
INSERT INTO Students VALUES (2, 'Jane Smith');

Step 4: Check Audit Table

SELECT * FROM Student_Audit;

CONCLUSION

Cursor helps process row-by-row results under certain conditions.
Trigger automates actions (like auditing) whenever an event occurs in a table.

Both are powerful features in SQL for automation and data integrity.

Transactions, Deadlocks & Log-Based Recovery in MySQL

ATHEEBA PARVEEN J A 24CB002 — Fri, 03 Oct 2025 09:19:50 +0000

Introduction

Databases are the backbone of most applications. To maintain data integrity and consistency Transactions, Deadlocks, and Log-based recovery are crucial. In this tutorial, we’ll explore these concepts using a simple Accounts table in MySQL..

Schema Setup

We start by creating a single table Accounts and insert some sample data.

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);

This table will be used for all examples below.

Transaction – Atomicity & Rollback

Transactions ensure atomicity, meaning a series of operations either all succeed or all fail.

We will transfer 500 from Alice to Bob but rollback the transaction to ensure no partial updates occur.

START TRANSACTION;

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

UPDATE Accounts
SET balance = balance + 500
WHERE name = 'Bob';

ROLLBACK;

SELECT * FROM Accounts;

✅ The rollback ensures atomicity—no partial updates happened.

Deadlock Simulation

A deadlock occurs when two transactions wait for each other indefinitely. Let’s simulate one using two sessions.

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

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

Log-Based Recovery

Most databases like MySQL use binary logs for recovery. Transactions are logged so that rollback operations can undo changes.

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

✅ The rollback restores, showing log-based recovery in action.

SUMMARY

Transactions ensure atomicity and consistency.
Deadlocks happen when two transactions wait on each other; the DBMS detects and resolves it.
Log-based recovery allows the database to undo changes after a rollback.

"Indexing, Hashing & Query Optimization with MySQL — Students Table Example"

ATHEEBA PARVEEN J A 24CB002 — Fri, 03 Oct 2025 08:29:53 +0000

INTRODUCTION

In this tutorial, we explore indexing, hashing, and query optimization using a Students table in MySQL. We will:

Create a table with sample student data.
Insert 20 records.
Create B-Tree and B+ Tree indexes.
Create a Hash index.
Run queries to demonstrate index usage.
Optimize queries using indexing.

Step 1: Create the Students Table

We start by creating the Students table with the following fields:

roll_no — integer, primary key.
name — varchar(100).
dept — varchar(20).
cgpa — decimal(3,2).

CREATE TABLE Students (
  roll_no INT NOT NULL PRIMARY KEY,
  name VARCHAR(100) NOT NULL,
  dept VARCHAR(20) NOT NULL,
  cgpa DECIMAL(3,2) NOT NULL
);

Step 2: Insert Sample Records

We insert 20 sample students:

INSERT INTO Students (roll_no, name, dept, cgpa) VALUES
(101,'Aisha Khan','CSBS',8.50),
(102,'Rahul Sharma','CSE',7.90),
(103,'Fatima Noor','ECE',8.10),
(104,'Vikram Patel','ME',6.80),
(105,'Sneha Iyer','CSE',9.00),
(106,'Arjun Das','CSBS',7.20),
(107,'Meera Nair','EE',8.75),
(108,'Kabir Ali','CSE',8.20),
(109,'Priya Singh','EEE',7.50),
(110,'Rohit Verma','CSBS',8.80),
(111,'Nisha Gupta','CSE',9.10),
(112,'Sanjay Rao','CSBS',6.95),
(113,'Lina Thomas','IT',8.05),
(114,'Tanuj Sinha','CSE',7.85),
(115,'Shaheen Khan','ECE',8.00),
(116,'Isha Kaur','ME',7.65),
(117,'Devansh Mehta','CSE',8.97),
(118,'Riya Bose','CSBS',9.25),
(119,'Aman Shah','EE',6.50),
(120,'Zara Patel','IT',8.30);

Step 3: Create a B-Tree Index on roll_no

A B-Tree index allows efficient searching on the roll_no column.

CREATE INDEX idx_roll_no ON Students (roll_no) USING BTREE;

Step 4: Query Student by Roll Number

Fetch details of the student with roll_no = 110:

SELECT * 
FROM Students
WHERE roll_no = 110;

Step 5: Create a B+ Tree Index on cgpa

A B+ Tree index improves performance for range queries like >, <, or BETWEEN.

CREATE INDEX idx_cgpa ON Students (cgpa) USING BTREE;

Step 6: Query Students with CGPA > 8.0

SELECT *
FROM Students
WHERE cgpa > 8.0
ORDER BY cgpa DESC;

Step 7: Create a Hash Index on dept

A Hash index is optimized for equality lookups (=).

CREATE INDEX idx_dept_hash ON Students (dept) USING HASH;

Step 8: Query implement by Department

SELECT *
FROM Students
WHERE dept = 'CSBS';

Conclusion

In this tutorial, we learned how indexing and hashing can significantly improve database performance in MySQL.

Key Takeaways

B-Tree indexes are efficient for equality searches and help speed up queries on primary keys like roll_no.

B+ Tree indexes are ideal for range queries and ordering, such as fetching students with cgpa > 8.0.

Hash indexes provide fast access for exact match queries but are limited to equality lookups.

Using EXPLAIN helps you analyze query performance and see if your indexes are being used.

Properly designing and applying indexes is crucial for query optimization, especially as datasets grow larger.

By combining B-Tree, B+ Tree, and Hash indexes, you can ensure that your queries run efficiently and your MySQL database remains optimized.

College Student & Course Management System

ATHEEBA PARVEEN J A 24CB002 — Thu, 21 Aug 2025 17:42:23 +0000

OVERVIEW

This blog explains how I built a simple College Student & Course Management System using SQL in Oracle LiveSQL.

The mini-project demonstrates fundamental database concepts such as:

Creating tables with proper constraints
Inserting and managing data
Altering tables to add new columns and checks
Using SQL functions and aggregate queries
Joining tables to retrieve related data
Building views for simplified queries
Writing stored procedures for automated tasks

Database Schema

TThe system manages four main entities: Students, Courses, Enrollments, and Faculty.

The use case focuses on managing students, courses, enrollments, and faculty members with their related operations.

STUDENTS TABLE

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

COURSE TABLE

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

ENROLLMENT TABLE

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

FACULTY TABLE

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

DATA INSERTION
-- Students
INSERT INTO Students VALUES (1, 'Shinchan', 'Computer Science', DATE '2002-04-15', 'shinchannohara@gmail.com');
INSERT INTO Students VALUES (2, 'Mitsi', 'Mathematics', DATE '2001-11-23', 'mitsinohara@gmail.com');
INSERT INTO Students VALUES (3, 'Harry', 'Physics', DATE '2003-07-02', 'harrynohara@gmail.com');

-- Courses
INSERT INTO Courses VALUES (101, 'Databases', 4);
INSERT INTO Courses VALUES (102, 'Algorithms', 3);
INSERT INTO Courses VALUES (103, 'Physics', 5);

-- Enrollments
INSERT INTO Enrollments VALUES (1, 1, 101, 'A');
INSERT INTO Enrollments VALUES (2, 2, 102, 'B+');
INSERT INTO Enrollments VALUES (3, 3, 103, 'A-');

Table Alterations and Constraints

Added a new column for phone numbers in Students.
Ensured course credits always fall between 1 and 5. ALTER TABLE Students ADD PhoneNo VARCHAR2(10); ALTER TABLE Courses ADD CONSTRAINT chk_credits CHECK (Credits BETWEEN 1 AND 5);

SQL Queries with Functions and Aggregates

Names in Uppercase and Email Length
SELECT UPPER(Name) AS UppercaseName,
LENGTH(Email) AS EmailLength
FROM Students;
Average Credits and Total Enrollments
SELECT
(SELECT AVG(Credits) FROM Courses) AS AvgCredits,
(SELECT COUNT(DISTINCT StudentID) FROM Enrollments) AS TotalEnrolled
FROM dual;

JOIN QUERIES
List each student 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 c.CourseID = e.CourseID;

GROUP BY with HAVING
Count students in each department, only showing departments with more than 2 students:
SELECT Dept, COUNT() AS StudentCount
FROM Students
GROUP BY Dept
HAVING COUNT() > 2;

VIEWS
To simplify student-course-grade lookups, I created a view:
CREATE OR REPLACE VIEW StudentCoursesView AS
SELECT s.Name AS StudentName, c.CourseName, e.Grade
FROM Students s
JOIN Enrollments e ON e.StudentID = s.StudentID
JOIN Courses c ON e.CourseID = c.CourseID;

STORED PROCEDURE
A stored procedure to update grades in the Enrollments table:
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;

NUTSHELL
Through this project, I practiced:

Defining schemas and relationships between tables

Enforcing data integrity with constraints

Writing aggregate queries and joins

Using views to simplify queries

Implementing stored procedures for automation

This exercise on Oracle LiveSQL helped me strengthen my SQL foundation and understand how relational database systems are designed and queried in real-world scenarios 🚀