-- ============================================
-- SQL Lab - 6: Subqueries and Relational Division
-- Student: Prashant Ranjan
-- Roll Number: 24MC3035
-- Platform: mycompiler.io (MySQL)
-- Concepts: IN, NOT IN, EXISTS, NOT EXISTS, Relational Division
-- ============================================

-- ============================================
-- SETUP: Create and populate tables
-- ============================================

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

-- Insert student data
INSERT INTO students (student_id, name, department, study_year, marks) VALUES
(1, 'Amit', 'CSE', 2, 78),
(2, 'Neha', 'ECE', 1, 65),
(3, 'Ravi', 'CSE', 2, 82),
(4, 'Priya', 'ME', 3, 71),
(5, 'Ankit', 'CSE', 3, 90),
(6, 'Sneha', 'EEE', 2, 68);

-- Create enrolled table
CREATE TABLE IF NOT EXISTS enrolled (
    enroll_id INT PRIMARY KEY,
    student_id INT,
    course_name VARCHAR(50),
    semester INT,
    credits INT,
    FOREIGN KEY (student_id) REFERENCES students(student_id)
);

-- Insert enrollment data
INSERT INTO enrolled (enroll_id, student_id, course_name, semester, credits) VALUES
(101, 1, 'DBMS', 3, 4),
(102, 1, 'OS', 3, 3),
(103, 3, 'DBMS', 3, 4),
(104, 3, 'CN', 4, 3),
(105, 5, 'DBMS', 5, 4),
(106, 2, 'Maths', 1, 3),
(107, 4, 'Thermo', 5, 3);

-- Display base data
SELECT '===== BASE DATA =====' AS '';
SELECT '----- STUDENTS TABLE -----' AS '';
SELECT * FROM students ORDER BY student_id;

SELECT '----- ENROLLED TABLE -----' AS '';
SELECT * FROM enrolled ORDER BY enroll_id;

-- ============================================
-- PART 1: IN Operator
-- ============================================

SELECT '===== PART 1: IN OPERATOR =====' AS '';

-- Problem 1: Display students enrolled in DBMS using IN
SELECT '----- 1. Students Enrolled in DBMS (using IN) -----' AS '';
SELECT name, student_id, department 
FROM students 
WHERE student_id IN (SELECT student_id FROM enrolled WHERE course_name = 'DBMS');

-- ============================================
-- PART 2: NOT IN Operator
-- ============================================

SELECT '===== PART 2: NOT IN OPERATOR =====' AS '';

-- Problem 2: Display students not enrolled in DBMS using NOT IN
SELECT '----- 2. Students Not Enrolled in DBMS (using NOT IN) -----' AS '';
SELECT name, student_id, department 
FROM students 
WHERE student_id NOT IN (SELECT student_id FROM enrolled WHERE course_name = 'DBMS');

-- ============================================
-- PART 3: EXISTS Operator
-- ============================================

SELECT '===== PART 3: EXISTS OPERATOR =====' AS '';

-- Problem 3: Display students having at least one enrollment using EXISTS
SELECT '----- 3. Students with at Least One Enrollment (using EXISTS) -----' AS '';
SELECT s.name, s.student_id, s.department
FROM students s
WHERE EXISTS (SELECT 1 FROM enrolled e WHERE e.student_id = s.student_id);

-- ============================================
-- PART 4: NOT EXISTS Operator
-- ============================================

SELECT '===== PART 4: NOT EXISTS OPERATOR =====' AS '';

-- Problem 4: Display students not enrolled in any course using NOT EXISTS
SELECT '----- 4. Students with No Enrollments (using NOT EXISTS) -----' AS '';
SELECT s.name, s.student_id, s.department
FROM students s
WHERE NOT EXISTS (SELECT 1 FROM enrolled e WHERE e.student_id = s.student_id);

-- ============================================
-- PART 5: Advanced Subqueries (AND/IN with multiple conditions)
-- ============================================

SELECT '===== PART 5: ADVANCED SUBQUERIES =====' AS '';

-- Problem 5: Find students enrolled in both DBMS and OS
SELECT '----- 5. Students Enrolled in Both DBMS and OS -----' AS '';
SELECT s.name, s.student_id
FROM students s
WHERE s.student_id IN (SELECT student_id FROM enrolled WHERE course_name = 'DBMS')
  AND s.student_id IN (SELECT student_id FROM enrolled WHERE course_name = 'OS');

-- Alternative using JOIN and GROUP BY
SELECT '----- 5b. Alternative: Students in Both DBMS and OS -----' AS '';
SELECT s.name, s.student_id, COUNT(DISTINCT e.course_name) AS courses_taken
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
WHERE e.course_name IN ('DBMS', 'OS')
GROUP BY s.student_id, s.name
HAVING COUNT(DISTINCT e.course_name) = 2;

-- ============================================
-- PART 6: Relational Division - Students enrolled in all courses of semester 3
-- ============================================

SELECT '===== PART 6: RELATIONAL DIVISION =====' AS '';

-- Problem 6: Find students enrolled in all courses of semester 3
-- First, identify all semester 3 courses
SELECT '----- All Semester 3 Courses -----' AS '';
SELECT DISTINCT course_name FROM enrolled WHERE semester = 3;

-- Solution: Students who have taken every semester 3 course
SELECT '----- 6. Students Enrolled in ALL Semester 3 Courses -----' AS '';
SELECT s.name, s.student_id
FROM students s
WHERE NOT EXISTS (
    -- Select courses that are in semester 3
    SELECT DISTINCT e1.course_name 
    FROM enrolled e1 
    WHERE e1.semester = 3
    EXCEPT
    -- Select courses the student has taken that are in semester 3
    SELECT e2.course_name 
    FROM enrolled e2 
    WHERE e2.student_id = s.student_id AND e2.semester = 3
);

-- Note: MySQL doesn't support EXCEPT, so using NOT EXISTS with correlated subquery
SELECT '----- 6b. MySQL Compatible Solution -----' AS '';
SELECT s.name, s.student_id
FROM students s
WHERE NOT EXISTS (
    SELECT e1.course_name
    FROM (SELECT DISTINCT course_name FROM enrolled WHERE semester = 3) e1
    WHERE NOT EXISTS (
        SELECT 1
        FROM enrolled e2
        WHERE e2.student_id = s.student_id 
          AND e2.course_name = e1.course_name
          AND e2.semester = 3
    )
);

-- ============================================
-- PART 7: Students enrolled in every course taken by Amit
-- ============================================

-- Problem 7: Find students enrolled in every course taken by Amit (student_id = 1)
SELECT '----- 7. Students Enrolled in EVERY Course Taken by Amit -----' AS '';

-- First, show courses taken by Amit
SELECT 'Courses taken by Amit:' AS '';
SELECT DISTINCT course_name FROM enrolled WHERE student_id = 1;

-- Solution using double NOT EXISTS (Relational Division)
SELECT s.name, s.student_id
FROM students s
WHERE s.student_id != 1  -- Exclude Amit himself
  AND NOT EXISTS (
      -- For each course that Amit took
      SELECT e1.course_name
      FROM (SELECT DISTINCT course_name FROM enrolled WHERE student_id = 1) e1
      WHERE NOT EXISTS (
          -- Check if the student took that course
          SELECT 1
          FROM enrolled e2
          WHERE e2.student_id = s.student_id 
            AND e2.course_name = e1.course_name
      )
  );

-- ============================================
-- PART 8: Courses taken by all CSE students
-- ============================================

-- Problem 8: Find courses taken by all CSE students
SELECT '----- 8. Courses Taken by ALL CSE Students -----' AS '';

-- First, get all CSE students
SELECT 'All CSE Students:' AS '';
SELECT student_id, name FROM students WHERE department = 'CSE';

-- Solution: Courses where no CSE student is missing that course
SELECT e.course_name
FROM (SELECT DISTINCT course_name FROM enrolled) e
WHERE NOT EXISTS (
    -- Find CSE students who did NOT take this course
    SELECT s.student_id
    FROM students s
    WHERE s.department = 'CSE'
      AND NOT EXISTS (
          SELECT 1
          FROM enrolled e2
          WHERE e2.student_id = s.student_id 
            AND e2.course_name = e.course_name
      )
);

-- ============================================
-- PART 9: Students who enrolled in more courses than Ravi
-- ============================================

-- Problem 9: Find students who enrolled in more courses than Ravi (student_id = 3)
SELECT '----- 9. Students with More Enrollments Than Ravi -----' AS '';

-- First, get Ravi's course count
SELECT 'Ravi\'s course count:' AS '';
SELECT COUNT(*) AS ravi_course_count FROM enrolled WHERE student_id = 3;

-- Solution
SELECT s.name, s.student_id, COUNT(e.enroll_id) AS course_count
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
GROUP BY s.student_id, s.name
HAVING COUNT(e.enroll_id) > (SELECT COUNT(*) FROM enrolled WHERE student_id = 3)
ORDER BY course_count DESC;

-- ============================================
-- PART 10: Departments where all students enrolled in DBMS
-- ============================================

-- Problem 10: Find departments where all students enrolled in DBMS
SELECT '----- 10. Departments Where ALL Students Enrolled in DBMS -----' AS '';

-- Solution: Departments where no student is missing DBMS enrollment
SELECT DISTINCT s.department
FROM students s
WHERE NOT EXISTS (
    -- Find students in the department who are NOT enrolled in DBMS
    SELECT s2.student_id
    FROM students s2
    WHERE s2.department = s.department
      AND s2.student_id NOT IN (
          SELECT e.student_id 
          FROM enrolled e 
          WHERE e.course_name = 'DBMS'
      )
);

-- Alternative with more details
SELECT '----- 10b. Detailed View -----' AS '';
SELECT s.department,
       COUNT(*) AS total_students,
       SUM(CASE WHEN e.course_name = 'DBMS' THEN 1 ELSE 0 END) AS dbms_enrolled
FROM students s
LEFT JOIN enrolled e ON s.student_id = e.student_id AND e.course_name = 'DBMS'
GROUP BY s.department
HAVING COUNT(*) = SUM(CASE WHEN e.course_name = 'DBMS' THEN 1 ELSE 0 END);

-- ============================================
-- BONUS: Additional Verification Queries
-- ============================================

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

-- Summary of all enrollments per student
SELECT 'Enrollment Summary by Student:' AS '';
SELECT s.name, s.department, COUNT(e.enroll_id) AS num_courses,
       GROUP_CONCAT(DISTINCT e.course_name ORDER BY e.course_name SEPARATOR ', ') AS courses
FROM students s
LEFT JOIN enrolled e ON s.student_id = e.student_id
GROUP BY s.student_id, s.name, s.department
ORDER BY num_courses DESC;

-- Course enrollment summary
SELECT 'Course Enrollment Summary:' AS '';
SELECT e.course_name, COUNT(DISTINCT e.student_id) AS num_students,
       GROUP_CONCAT(DISTINCT s.name ORDER BY s.name SEPARATOR ', ') AS students
FROM enrolled e
INNER JOIN students s ON e.student_id = s.student_id
GROUP BY e.course_name
ORDER BY num_students DESC;

-- ============================================
-- END OF LAB - 6
-- ============================================

SELECT '===== ALL 10 PROBLEMS COMPLETED SUCCESSFULLY =====' AS '';
SELECT 'Problems Covered:' AS '';
SELECT '1. IN - Students in DBMS' AS '';
SELECT '2. NOT IN - Students not in DBMS' AS '';
SELECT '3. EXISTS - Students with at least one enrollment' AS '';
SELECT '4. NOT EXISTS - Students with no enrollments' AS '';
SELECT '5. Students in both DBMS and OS' AS '';
SELECT '6. Students in all semester 3 courses (Relational Division)' AS '';
SELECT '7. Students in every course taken by Amit' AS '';
SELECT '8. Courses taken by all CSE students' AS '';
SELECT '9. Students with more enrollments than Ravi' AS '';
SELECT '10. Departments where all students enrolled in DBMS' AS '';

-- ============================================
-- End of Lab - 6
-- ============================================