-- ============================================
-- SQL Lab - 4: ALTER, GROUP BY, and HAVING
-- Student: Prashant Ranjan
-- Roll Number: 24MC3035
-- Platform: mycompiler.io (MySQL)
-- ============================================

-- ============================================
-- SETUP: Create and populate table (required for new session)
-- ============================================

-- Create the students table
CREATE TABLE IF NOT EXISTS students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(30),
    year INT,
    marks INT
);

-- Insert base records
INSERT INTO students (student_id, name, department, year, marks) VALUES
(1, 'Amit Sharma', 'CSE', 1, 78),
(2, 'Neha Verma', 'ECE', 2, 65),
(3, 'Ravi Kumar', 'CSE', 2, 82),
(4, 'Priya Singh', 'ME', 1, 71),
(5, 'Ankit Gupta', 'CSE', 3, 90),
(6, 'Sneha Reddy', 'EEE', 2, 68),
(7, 'Rahul Mehta', 'ME', 3, 74),
(8, 'Pooja Nair', 'ECE', 1, 80),
(9, 'Prashant Ranjan', 'CSE', 2, 88),
(10, 'Vikram Sharma', 'CSE', 1, 75),
(11, 'Neha Gupta', 'CSE', 3, 85),
(12, 'Rajesh Kumar', 'ME', 1, 42),
(13, 'Kavita Singh', 'ECE', 1, 79);

-- Verify base data
SELECT '----- Base Data (Students Table) -----' AS '';
SELECT * FROM students ORDER BY student_id;

-- ============================================
-- TASK 1: ALTER TABLE Commands
-- ============================================

SELECT '===== TASK 1: ALTER TABLE OPERATIONS =====' AS '';

-- 1. Add a new column email of type VARCHAR(50)
SELECT '----- 1. Adding email Column -----' AS '';
ALTER TABLE students ADD COLUMN email VARCHAR(50);
SELECT 'Email column added successfully' AS '';

-- 2. Add a new column age of type INT
SELECT '----- 2. Adding age Column -----' AS '';
ALTER TABLE students ADD COLUMN age INT;
SELECT 'Age column added successfully' AS '';

-- 3. Modify the name column to VARCHAR(100)
SELECT '----- 3. Modifying name Column to VARCHAR(100) -----' AS '';
ALTER TABLE students MODIFY COLUMN name VARCHAR(100);
SELECT 'Name column modified to VARCHAR(100)' AS '';

-- 4. Rename the column year to study_year
SELECT '----- 4. Renaming year Column to study_year -----' AS '';
ALTER TABLE students RENAME COLUMN year TO study_year;
SELECT 'Year column renamed to study_year' AS '';

-- 5. Drop the column email from the table
SELECT '----- 5. Dropping email Column -----' AS '';
ALTER TABLE students DROP COLUMN email;
SELECT 'Email column dropped successfully' AS '';

-- 6. Display the structure of the modified table
SELECT '----- 6. Table Structure After ALTER Operations -----' AS '';
DESCRIBE students;

-- Show data after ALTER operations
SELECT '----- Data After ALTER Operations -----' AS '';
SELECT student_id, name, department, study_year, marks, age FROM students ORDER BY student_id;

-- ============================================
-- TASK 2: Aggregate Functions (Without GROUP BY)
-- ============================================

SELECT '===== TASK 2: AGGREGATE FUNCTIONS =====' AS '';

-- 1. Find the total number of students
SELECT '----- 1. Total Number of Students -----' AS '';
SELECT COUNT(*) AS total_students FROM students;

-- 2. Find the average marks of all students
SELECT '----- 2. Average Marks of All Students -----' AS '';
SELECT ROUND(AVG(marks), 2) AS average_marks FROM students;

-- 3. Find the maximum marks obtained
SELECT '----- 3. Maximum Marks Obtained -----' AS '';
SELECT MAX(marks) AS max_marks FROM students;

-- 4. Find the minimum marks obtained
SELECT '----- 4. Minimum Marks Obtained -----' AS '';
SELECT MIN(marks) AS min_marks FROM students;

-- ============================================
-- TASK 3: Aggregate Functions with GROUP BY (Without HAVING)
-- ============================================

SELECT '===== TASK 3: GROUP BY (Without HAVING) =====' AS '';

-- 1. Find the number of students in each department
SELECT '----- 1. Number of Students in Each Department -----' AS '';
SELECT department, COUNT(*) AS student_count 
FROM students 
GROUP BY department;

-- 2. Find the average marks department-wise
SELECT '----- 2. Average Marks Department-Wise -----' AS '';
SELECT department, ROUND(AVG(marks), 2) AS average_marks 
FROM students 
GROUP BY department;

-- 3. Find the maximum marks in each department
SELECT '----- 3. Maximum Marks in Each Department -----' AS '';
SELECT department, MAX(marks) AS max_marks 
FROM students 
GROUP BY department;

-- 4. Find the total number of students in each study year
SELECT '----- 4. Number of Students in Each Study Year -----' AS '';
SELECT study_year, COUNT(*) AS student_count 
FROM students 
GROUP BY study_year;

-- ============================================
-- TASK 4: GROUP BY with HAVING Clause
-- ============================================

SELECT '===== TASK 4: GROUP BY with HAVING =====' AS '';

-- 1. Display departments having more than one student
SELECT '----- 1. Departments with More Than One Student -----' AS '';
SELECT department, COUNT(*) AS student_count 
FROM students 
GROUP BY department 
HAVING COUNT(*) > 1;

-- 2. Display study years having more than two students
SELECT '----- 2. Study Years with More Than Two Students -----' AS '';
SELECT study_year, COUNT(*) AS student_count 
FROM students 
GROUP BY study_year 
HAVING COUNT(*) > 2;

-- 3. Display departments where average marks are greater than 75
SELECT '----- 3. Departments with Average Marks > 75 -----' AS '';
SELECT department, ROUND(AVG(marks), 2) AS average_marks 
FROM students 
GROUP BY department 
HAVING AVG(marks) > 75;

-- 4. Display departments where maximum marks are less than 90
SELECT '----- 4. Departments with Maximum Marks < 90 -----' AS '';
SELECT department, MAX(marks) AS max_marks 
FROM students 
GROUP BY department 
HAVING MAX(marks) < 90;

-- ============================================
-- TASK 5: Additional HAVING Clause Exercises
-- ============================================

SELECT '===== TASK 5: Additional HAVING Exercises =====' AS '';

-- 1. Departments where average marks > overall average marks
SELECT '----- 1. Departments with Average Marks > Overall Average -----' AS '';
SELECT department, ROUND(AVG(marks), 2) AS dept_avg, 
       ROUND((SELECT AVG(marks) FROM students), 2) AS overall_avg
FROM students 
GROUP BY department 
HAVING AVG(marks) > (SELECT AVG(marks) FROM students);

-- 2. Departments having at least one student scoring more than 85 marks
SELECT '----- 2. Departments with at Least One Student Scoring > 85 -----' AS '';
SELECT department, MAX(marks) AS highest_mark
FROM students 
GROUP BY department 
HAVING MAX(marks) > 85;

-- 3. Study years where maximum marks are greater than 90
SELECT '----- 3. Study Years with Maximum Marks > 90 -----' AS '';
SELECT study_year, MAX(marks) AS max_marks
FROM students 
GROUP BY study_year 
HAVING MAX(marks) > 90;

-- 4. Departments where difference between max and min marks is more than 20
SELECT '----- 4. Departments with Marks Range (Max-Min) > 20 -----' AS '';
SELECT department, MAX(marks) AS max_marks, MIN(marks) AS min_marks, 
       (MAX(marks) - MIN(marks)) AS marks_range
FROM students 
GROUP BY department 
HAVING (MAX(marks) - MIN(marks)) > 20;

-- 5. Departments having more than two students and average marks greater than 70
SELECT '----- 5. Departments with >2 Students and Average Marks > 70 -----' AS '';
SELECT department, COUNT(*) AS student_count, ROUND(AVG(marks), 2) AS average_marks
FROM students 
GROUP BY department 
HAVING COUNT(*) > 2 AND AVG(marks) > 70;

-- 6. Study years where average marks are between 65 and 80
SELECT '----- 6. Study Years with Average Marks Between 65 and 80 -----' AS '';
SELECT study_year, ROUND(AVG(marks), 2) AS average_marks
FROM students 
GROUP BY study_year 
HAVING AVG(marks) BETWEEN 65 AND 80;

-- ============================================
-- FINAL VERIFICATION
-- ============================================

SELECT '===== FINAL TABLE SUMMARY =====' AS '';
SELECT COUNT(*) AS total_students, 
       ROUND(AVG(marks), 2) AS overall_average,
       MAX(marks) AS highest_marks,
       MIN(marks) AS lowest_marks
FROM students;

SELECT '----- Final Data in Students Table -----' AS '';
SELECT student_id, name, department, study_year, marks, age 
FROM students 
ORDER BY student_id;

-- ============================================
-- End of Lab - 4
-- ============================================