-- ============================================
-- SQL Practice Lab: Subqueries
-- Student: Prashant Ranjan
-- Roll Number: 24MC3035
-- Platform: mycompiler.io (MySQL)
-- Topic: Subqueries with Aggregate Functions
-- ============================================

-- ============================================
-- SETUP: Create and populate students table
-- ============================================

CREATE TABLE IF NOT EXISTS students (
    student_id INT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(30),
    study_year INT,
    marks INT
);

-- Insert comprehensive data for meaningful subquery results
INSERT INTO students (student_id, name, department, study_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),
(14, 'Deepak Patel', 'EEE', 3, 91),
(15, 'Sunita Rao', 'ECE', 2, 58),
(16, 'Manish Joshi', 'ME', 2, 62),
(17, 'Anjali Nair', 'EEE', 1, 77),
(18, 'Rohit Sharma', 'CSE', 4, 95),
(19, 'Divya Gupta', 'ECE', 4, 92),
(20, 'Sachin Tendulkar', 'ME', 4, 86);

-- Display base data
SELECT '===== BASE DATA (STUDENTS TABLE) =====' AS '';
SELECT * FROM students ORDER BY department, study_year, student_id;

-- Display summary statistics
SELECT '===== SUMMARY STATISTICS =====' AS '';
SELECT 
    COUNT(*) AS total_students,
    ROUND(AVG(marks), 2) AS overall_avg_marks,
    MIN(marks) AS overall_min_marks,
    MAX(marks) AS overall_max_marks
FROM students;

-- Department-wise statistics
SELECT '===== DEPARTMENT-WISE STATISTICS =====' AS '';
SELECT 
    department,
    COUNT(*) AS student_count,
    ROUND(AVG(marks), 2) AS avg_marks,
    MIN(marks) AS min_marks,
    MAX(marks) AS max_marks
FROM students
GROUP BY department
ORDER BY department;

-- Study year-wise statistics
SELECT '===== STUDY YEAR-WISE STATISTICS =====' AS '';
SELECT 
    study_year,
    COUNT(*) AS student_count,
    ROUND(AVG(marks), 2) AS avg_marks
FROM students
GROUP BY study_year
ORDER BY study_year;

-- ============================================
-- PROBLEM 1: Departments where average marks > overall average
-- ============================================

SELECT '===== PROBLEM 1 =====' AS '';
SELECT 'Departments where average marks are greater than overall average marks of all students' AS '';

SELECT 
    department,
    ROUND(AVG(marks), 2) AS department_avg,
    ROUND((SELECT AVG(marks) FROM students), 2) AS overall_avg
FROM students 
GROUP BY department 
HAVING AVG(marks) > (SELECT AVG(marks) FROM students);

-- ============================================
-- PROBLEM 2: Students who scored more than their department's average
-- ============================================

SELECT '===== PROBLEM 2 =====' AS '';
SELECT 'Students who scored more than the average marks of their department' AS '';

SELECT 
    name, 
    department, 
    marks,
    ROUND((SELECT AVG(marks) FROM students s2 WHERE s2.department = s1.department), 2) AS dept_avg
FROM students s1 
WHERE marks > (SELECT AVG(marks) FROM students s2 WHERE s2.department = s1.department)
ORDER BY department, marks DESC;

-- ============================================
-- PROBLEM 3: Departments having the highest average marks
-- ============================================

SELECT '===== PROBLEM 3 =====' AS '';
SELECT 'Departments having the highest average marks' AS '';

SELECT 
    department,
    ROUND(AVG(marks), 2) AS average_marks
FROM students 
GROUP BY department 
HAVING AVG(marks) = (
    SELECT MAX(avg_marks) 
    FROM (SELECT AVG(marks) AS avg_marks FROM students GROUP BY department) t
);

-- ============================================
-- PROBLEM 4: Students who have the maximum marks in their respective departments
-- ============================================

SELECT '===== PROBLEM 4 =====' AS '';
SELECT 'Students who have the maximum marks in their respective departments' AS '';

SELECT 
    name, 
    department, 
    marks
FROM students s1 
WHERE marks = (SELECT MAX(marks) FROM students s2 WHERE s2.department = s1.department)
ORDER BY department;

-- ============================================
-- PROBLEM 5: Study years where average marks < overall average
-- ============================================

SELECT '===== PROBLEM 5 =====' AS '';
SELECT 'Study years where the average marks are less than the overall average marks' AS '';

SELECT 
    study_year,
    ROUND(AVG(marks), 2) AS year_avg,
    ROUND((SELECT AVG(marks) FROM students), 2) AS overall_avg
FROM students 
GROUP BY study_year 
HAVING AVG(marks) < (SELECT AVG(marks) FROM students);

-- ============================================
-- PROBLEM 6: Departments having more students than CSE department
-- ============================================

SELECT '===== PROBLEM 6 =====' AS '';
SELECT 'Departments having more students than the CSE department' AS '';

SELECT 
    department,
    COUNT(*) AS student_count,
    (SELECT COUNT(*) FROM students WHERE department = 'CSE') AS cse_count
FROM students 
GROUP BY department 
HAVING COUNT(*) > (SELECT COUNT(*) FROM students WHERE department = 'CSE');

-- ============================================
-- PROBLEM 7: Students whose marks > minimum marks of CSE department
-- ============================================

SELECT '===== PROBLEM 7 =====' AS '';
SELECT 'Students whose marks are greater than the minimum marks of the CSE department' AS '';

SELECT 
    name, 
    department,
    study_year,
    marks
FROM students 
WHERE marks > (SELECT MIN(marks) FROM students WHERE department = 'CSE')
ORDER BY marks DESC;

-- ============================================
-- PROBLEM 8: Departments where all students scored above overall minimum marks
-- ============================================

SELECT '===== PROBLEM 8 =====' AS '';
SELECT 'Departments where all students scored above the overall minimum marks' AS '';

SELECT 
    department,
    MIN(marks) AS department_min,
    (SELECT MIN(marks) FROM students) AS overall_min
FROM students 
GROUP BY department 
HAVING MIN(marks) > (SELECT MIN(marks) FROM students);

-- ============================================
-- BONUS: Additional verification queries
-- ============================================

SELECT '===== BONUS: VERIFICATION =====' AS '';

-- Show CSE department details for reference
SELECT 'CSE Department Details:' AS '';
SELECT * FROM students WHERE department = 'CSE' ORDER BY marks;

-- Show which students are above/below their department average
SELECT 'Each Student vs Department Average:' AS '';
SELECT 
    name,
    department,
    marks,
    ROUND((SELECT AVG(marks) FROM students s2 WHERE s2.department = s1.department), 2) AS dept_avg,
    CASE 
        WHEN marks > (SELECT AVG(marks) FROM students s2 WHERE s2.department = s1.department) THEN 'Above Average'
        WHEN marks < (SELECT AVG(marks) FROM students s2 WHERE s2.department = s1.department) THEN 'Below Average'
        ELSE 'At Average'
    END AS comparison
FROM students s1
ORDER BY department, marks DESC;

-- ============================================
-- END OF PRACTICE LAB
-- ============================================

SELECT '===== ALL 8 SUBQUERY PROBLEMS COMPLETED SUCCESSFULLY =====' AS '';