-- ============================================
-- SQL Lab - 5: JOIN Operations
-- Student: Prashant Ranjan
-- Roll Number: 24MC3035
-- Platform: mycompiler.io (MySQL)
-- ============================================

-- ============================================
-- SETUP: Create and populate students table (initial state)
-- ============================================

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

-- Insert initial 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),
(7, 'Rahul', 'ME', 3, 74),
(8, 'Pooja', 'ECE', 1, 80),
(9, 'Kunal', 'CSE', 4, 95),
(10, 'Divya', 'ECE', 3, 58);

-- Show initial data
SELECT '===== INITIAL STUDENTS TABLE =====' AS '';
SELECT * FROM students ORDER BY student_id;

-- ============================================
-- TASK A: Data Preparation using DML
-- Modify existing students table to match final expected data
-- Final expected: student_id 1-6 only with specific values
-- ============================================

SELECT '===== TASK A: DATA PREPARATION =====' AS '';

-- 1. Delete students with ID > 6 (not in final expected data)
SELECT '----- Deleting students with ID > 6 -----' AS '';
DELETE FROM students WHERE student_id > 6;

-- 2. Update Amit's study_year to 2 (already 2, but ensure)
SELECT '----- Updating Amit (student_id=1) to ensure study_year=2 -----' AS '';
UPDATE students SET study_year = 2 WHERE student_id = 1;

-- 3. Update Neha's study_year to 1 (already 1, ensure)
SELECT '----- Updating Neha (student_id=2) to ensure study_year=1 -----' AS '';
UPDATE students SET study_year = 1 WHERE student_id = 2;

-- 4. Update Priya's study_year to 3 (already 3, ensure)
SELECT '----- Updating Priya (student_id=4) to ensure study_year=3 -----' AS '';
UPDATE students SET study_year = 3 WHERE student_id = 4;

-- Verify final students table matches expected data
SELECT '----- FINAL STUDENTS TABLE (After DML) -----' AS '';
SELECT student_id, name, department, study_year, marks 
FROM students 
ORDER BY student_id;

-- ============================================
-- TASK B: Create and Populate enrolled Table
-- ============================================

SELECT '===== TASK B: CREATE AND POPULATE ENROLLED TABLE =====' AS '';

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

-- Insert records as per lab requirements
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);

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

-- Verify foreign key integrity (show students with enrollments)
SELECT '----- Students with their enrollments (for verification) -----' AS '';
SELECT s.student_id, s.name, e.course_name, e.credits
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
ORDER BY s.student_id;

-- ============================================
-- TASK C: JOIN Queries
-- ============================================

SELECT '===== TASK C: JOIN QUERIES =====' AS '';

-- 1. Display student name and course name for students who are enrolled (INNER JOIN)
SELECT '----- 1. INNER JOIN: Student Name and Course Name -----' AS '';
SELECT s.name, e.course_name
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
ORDER BY s.name;

-- 2. Display student name, department, and course name
SELECT '----- 2. Student Name, Department, and Course Name -----' AS '';
SELECT s.name, s.department, e.course_name
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
ORDER BY s.name;

-- 3. Display all students enrolled in the course 'DBMS'
SELECT '----- 3. Students Enrolled in DBMS -----' AS '';
SELECT s.name, s.department, e.course_name
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
WHERE e.course_name = 'DBMS';

-- 4. Display student names and marks for courses with more than 3 credits
SELECT '----- 4. Students in Courses with Credits > 3 -----' AS '';
SELECT s.name, s.marks, e.course_name, e.credits
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
WHERE e.credits > 3;

-- 5. Display all students and their enrolled courses (LEFT JOIN)
SELECT '----- 5. LEFT JOIN: All Students and Their Courses -----' AS '';
SELECT s.student_id, s.name, e.course_name, e.credits
FROM students s
LEFT JOIN enrolled e ON s.student_id = e.student_id
ORDER BY s.student_id;

-- 6. Display students who are not enrolled in any course
SELECT '----- 6. Students Not Enrolled in Any Course -----' AS '';
SELECT s.student_id, s.name, s.department
FROM students s
LEFT JOIN enrolled e ON s.student_id = e.student_id
WHERE e.enroll_id IS NULL;

-- 7. Display all courses along with student names (RIGHT JOIN)
SELECT '----- 7. RIGHT JOIN: All Courses and Student Names -----' AS '';
SELECT e.course_name, e.enroll_id, s.name
FROM students s
RIGHT JOIN enrolled e ON s.student_id = e.student_id
ORDER BY e.course_name;

-- 8. Display courses with no student enrollment (should return empty since all courses have students)
SELECT '----- 8. Courses with No Student Enrollment -----' AS '';
SELECT DISTINCT e.course_name
FROM enrolled e
WHERE e.course_name NOT IN (SELECT DISTINCT course_name FROM enrolled);

-- Alternative using LEFT JOIN (shows no extra courses)
SELECT '----- 8b. Verification: All courses have students -----' AS '';
SELECT e.course_name, COUNT(e.student_id) AS student_count
FROM enrolled e
GROUP BY e.course_name;

-- 9. Simulate FULL OUTER JOIN to display all students and all enrollments
SELECT '----- 9. FULL OUTER JOIN Simulation (UNION of LEFT and RIGHT) -----' AS '';
SELECT s.student_id, s.name, e.enroll_id, e.course_name, e.credits
FROM students s
LEFT JOIN enrolled e ON s.student_id = e.student_id
UNION
SELECT s.student_id, s.name, e.enroll_id, e.course_name, e.credits
FROM students s
RIGHT JOIN enrolled e ON s.student_id = e.student_id
ORDER BY student_id;

-- 10. Find the number of students enrolled in each course
SELECT '----- 10. Number of Students Enrolled in Each Course -----' AS '';
SELECT e.course_name, COUNT(DISTINCT e.student_id) AS enrolled_students
FROM enrolled e
GROUP BY e.course_name
ORDER BY enrolled_students DESC;

-- 11. Find courses having more than two students enrolled
SELECT '----- 11. Courses with More Than Two Students Enrolled -----' AS '';
SELECT e.course_name, COUNT(DISTINCT e.student_id) AS enrolled_students
FROM enrolled e
GROUP BY e.course_name
HAVING COUNT(DISTINCT e.student_id) > 2;

-- 12. Find the average marks of students enrolled in each course
SELECT '----- 12. Average Marks of Students in Each Course -----' AS '';
SELECT e.course_name, ROUND(AVG(s.marks), 2) AS average_marks
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
GROUP BY e.course_name
ORDER BY average_marks DESC;

-- 13. Find courses where the average marks are greater than 70
SELECT '----- 13. Courses with Average Marks > 70 -----' AS '';
SELECT e.course_name, ROUND(AVG(s.marks), 2) AS average_marks
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
GROUP BY e.course_name
HAVING AVG(s.marks) > 70;

-- ============================================
-- TASK D: Thinking / Challenge Questions
-- ============================================

SELECT '===== TASK D: CHALLENGE QUESTIONS =====' AS '';

-- 1. Departments where average marks of students enrolled in DBMS > overall average marks
SELECT '----- 1. Departments with DBMS Student Avg > Overall Avg -----' AS '';
SELECT s.department, ROUND(AVG(s.marks), 2) AS dbms_avg
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
WHERE e.course_name = 'DBMS'
GROUP BY s.department
HAVING AVG(s.marks) > (SELECT AVG(marks) FROM students);

-- 2. Students enrolled in more than one course AND scored above department average
SELECT '----- 2. Students in >1 Course AND Above Dept Average -----' AS '';
SELECT s.student_id, s.name, s.department, s.marks, 
       (SELECT AVG(marks) FROM students s2 WHERE s2.department = s.department) AS dept_avg,
       COUNT(e.course_name) AS course_count
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
GROUP BY s.student_id, s.name, s.department, s.marks
HAVING COUNT(e.course_name) > 1 
   AND s.marks > (SELECT AVG(marks) FROM students s2 WHERE s2.department = s.department);

-- 3. Courses where average marks of enrolled students > overall average marks
SELECT '----- 3. Courses with Avg Marks > Overall Average -----' AS '';
SELECT e.course_name, ROUND(AVG(s.marks), 2) AS course_avg,
       ROUND((SELECT AVG(marks) FROM students), 2) AS overall_avg
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
GROUP BY e.course_name
HAVING AVG(s.marks) > (SELECT AVG(marks) FROM students);

-- 4. Departments having students enrolled in DBMS but none enrolled in CN
SELECT '----- 4. Departments with DBMS Students but No CN Students -----' AS '';
SELECT DISTINCT s.department
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
WHERE e.course_name = 'DBMS'
  AND s.department NOT IN (
      SELECT DISTINCT s2.department
      FROM students s2
      INNER JOIN enrolled e2 ON s2.student_id = e2.student_id
      WHERE e2.course_name = 'CN'
  );

-- 5. Study years where maximum marks of enrolled students > 80
SELECT '----- 5. Study Years with Max Marks of Enrolled Students > 80 -----' AS '';
SELECT s.study_year, MAX(s.marks) AS max_marks
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
GROUP BY s.study_year
HAVING MAX(s.marks) > 80;

-- 6. Courses where all enrolled students have scored more than 70 marks
SELECT '----- 6. Courses Where All Enrolled Students Scored > 70 -----' AS '';
SELECT e.course_name, MIN(s.marks) AS min_mark_in_course
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
GROUP BY e.course_name
HAVING MIN(s.marks) > 70;

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

SELECT '===== FINAL SUMMARY =====' AS '';

-- Show all students and their enrollment status
SELECT 'All Students with Enrollment Status:' AS '';
SELECT s.student_id, s.name, s.department, s.marks,
       CASE WHEN e.enroll_id IS NOT NULL THEN 'Enrolled' ELSE 'Not Enrolled' END AS status
FROM students s
LEFT JOIN enrolled e ON s.student_id = e.student_id
GROUP BY s.student_id, s.name, s.department, s.marks, e.enroll_id
ORDER BY s.student_id;

-- Statistics
SELECT 'Statistics:' AS '';
SELECT 
    (SELECT COUNT(*) FROM students) AS total_students,
    (SELECT COUNT(DISTINCT student_id) FROM enrolled) AS enrolled_students,
    (SELECT COUNT(*) FROM students WHERE student_id NOT IN (SELECT DISTINCT student_id FROM enrolled)) AS unenrolled_students,
    (SELECT COUNT(DISTINCT course_name) FROM enrolled) AS total_courses;

-- ============================================
-- End of Lab - 5
-- ============================================