-- ============================================
-- SQL Lab - 7: Constraints, Views, NULL, CASE, Set Operations
-- Student: Prashant Ranjan
-- Roll Number: 24MC3035
-- Platform: mycompiler.io (MySQL)
-- ============================================

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

-- Drop existing tables if they exist (clean start for constraints demo)
DROP TABLE IF EXISTS demo_students;
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS enrolled;

-- Create students table (without constraints first, to demonstrate adding them)
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50),
    department VARCHAR(30),
    study_year INT,
    marks INT
);

-- Insert data (including some NULL marks for demonstration)
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, NULL),  -- NULL marks for demonstration
(7, 'Rahul', 'ME', 3, 74),
(8, 'Pooja', 'ECE', 1, NULL);  -- NULL marks for demonstration

-- Create enrolled table
CREATE TABLE enrolled (
    enroll_id INT PRIMARY KEY,
    student_id INT,
    course_name VARCHAR(50),
    semester INT,
    credits INT
);

-- 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: CONSTRAINTS
-- ============================================

SELECT '===== PART 1: CONSTRAINTS =====' AS '';

-- 1. Add NOT NULL constraint to name column
SELECT '----- 1. Adding NOT NULL Constraint to name Column -----' AS '';
-- Check current nullable status
SELECT 'Before: name column allows NULL' AS '';
-- ALTER TABLE students MODIFY name VARCHAR(50) NOT NULL;
-- Note: If there are no NULL values in name, this works
-- Since all names are present, we can add it
ALTER TABLE students MODIFY name VARCHAR(50) NOT NULL;
SELECT 'NOT NULL constraint added to name column successfully' AS '';

-- 2. Add CHECK constraint marks <= 100
SELECT '----- 2. Adding CHECK Constraint (marks <= 100) -----' AS '';
-- MySQL 8.0 supports CHECK constraints
ALTER TABLE students ADD CONSTRAINT chk_marks_max CHECK (marks <= 100);
SELECT 'CHECK constraint (marks <= 100) added successfully' AS '';

-- 3. Add DEFAULT value for department
SELECT '----- 3. Adding DEFAULT Value for department -----' AS '';
ALTER TABLE students ALTER COLUMN department SET DEFAULT 'CSE';
SELECT 'DEFAULT value \'CSE\' added to department column successfully' AS '';

-- Verify constraints by showing table structure
SELECT '----- Table Structure After Constraints -----' AS '';
DESCRIBE students;

-- Create demo_students table with all constraints at creation time
SELECT '----- Creating demo_students Table with All Constraints -----' AS '';
CREATE TABLE demo_students (
    student_id INT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department VARCHAR(20) DEFAULT 'CSE',
    marks INT CHECK (marks >= 0 AND marks <= 100)
);
SELECT 'demo_students table created with PRIMARY KEY, NOT NULL, DEFAULT, and CHECK constraints' AS '';

-- Insert valid data into demo_students
INSERT INTO demo_students (student_id, name, marks) VALUES (1, 'Test Student', 85);
SELECT '----- demo_students Table Data -----' AS '';
SELECT * FROM demo_students;

-- ============================================
-- PART 2: VIEWS
-- ============================================

SELECT '===== PART 2: VIEWS =====' AS '';

-- 4. Create view for students with marks > 75
SELECT '----- 4. Creating View for Students with Marks > 75 -----' AS '';
CREATE VIEW high_scorers AS
SELECT student_id, name, department, study_year, marks
FROM students
WHERE marks > 75;
SELECT 'View "high_scorers" created successfully' AS '';

-- 5. Display records from the created view
SELECT '----- 5. Displaying Records from high_scorers View -----' AS '';
SELECT * FROM high_scorers ORDER BY marks DESC;

-- Additional view examples
SELECT '----- Additional: CSE Students View -----' AS '';
CREATE VIEW cse_students_view AS
SELECT student_id, name, study_year, marks
FROM students
WHERE department = 'CSE';
SELECT * FROM cse_students_view;

SELECT '----- Additional: Enrolled Students View -----' AS '';
CREATE VIEW enrolled_students_view AS
SELECT DISTINCT s.student_id, s.name, s.department, COUNT(e.course_name) AS num_courses
FROM students s
INNER JOIN enrolled e ON s.student_id = e.student_id
GROUP BY s.student_id, s.name, s.department;
SELECT * FROM enrolled_students_view;

-- ============================================
-- PART 3: NULL Handling
-- ============================================

SELECT '===== PART 3: NULL HANDLING =====' AS '';

-- 6. Find students with NULL marks
SELECT '----- 6. Students with NULL Marks -----' AS '';
SELECT student_id, name, department, study_year, marks
FROM students
WHERE marks IS NULL;

-- 7. Find students with NOT NULL marks
SELECT '----- 7. Students with NOT NULL Marks -----' AS '';
SELECT student_id, name, department, study_year, marks
FROM students
WHERE marks IS NOT NULL
ORDER BY student_id;

-- Additional NULL handling examples
SELECT '----- Additional: Replacing NULL with Default Value -----' AS '';
SELECT student_id, name, 
       COALESCE(marks, 0) AS marks_with_default,
       CASE WHEN marks IS NULL THEN 'Marks Missing' ELSE 'Marks Recorded' END AS status
FROM students
ORDER BY student_id;

-- ============================================
-- PART 4: CASE Statement
-- ============================================

SELECT '===== PART 4: CASE STATEMENT =====' AS '';

-- 8. Assign grade using CASE
SELECT '----- 8. Assigning Grades Using CASE -----' AS '';
SELECT student_id, name, marks,
    CASE 
        WHEN marks >= 90 THEN 'Excellent'
        WHEN marks >= 80 THEN 'Very Good'
        WHEN marks >= 70 THEN 'Good'
        WHEN marks >= 60 THEN 'Satisfactory'
        WHEN marks IS NULL THEN 'Not Available'
        ELSE 'Needs Improvement'
    END AS grade
FROM students
ORDER BY student_id;

-- Additional CASE examples
SELECT '----- Additional: Performance Category by Department -----' AS '';
SELECT department,
       COUNT(*) AS total_students,
       SUM(CASE WHEN marks >= 75 THEN 1 ELSE 0 END) AS high_scorers,
       SUM(CASE WHEN marks < 75 AND marks IS NOT NULL THEN 1 ELSE 0 END) AS low_scorers,
       SUM(CASE WHEN marks IS NULL THEN 1 ELSE 0 END) AS missing_marks
FROM students
GROUP BY department;

-- ============================================
-- PART 5: Set Operations (UNION, INTERSECT, EXCEPT)
-- ============================================

SELECT '===== PART 5: SET OPERATIONS =====' AS '';

-- 9. Display student_ids using UNION
SELECT '----- 9. UNION of student_id from students and enrolled -----' AS '';
SELECT student_id FROM students
UNION
SELECT student_id FROM enrolled
ORDER BY student_id;

SELECT '----- 9b. UNION ALL (includes duplicates) -----' AS '';
SELECT student_id FROM students
UNION ALL
SELECT student_id FROM enrolled
ORDER BY student_id;

-- 10. Display student_ids present in students but not enrolled (using NOT IN)
SELECT '----- 10. Students Not Enrolled (Present in students but not in enrolled) -----' AS '';
SELECT student_id, name, department
FROM students
WHERE student_id NOT IN (SELECT DISTINCT student_id FROM enrolled)
ORDER BY student_id;

-- Using EXCEPT equivalent (NOT EXISTS) - MySQL doesn't have EXCEPT
SELECT '----- 10b. Students Not Enrolled (using NOT EXISTS) -----' AS '';
SELECT s.student_id, s.name, s.department
FROM students s
WHERE NOT EXISTS (SELECT 1 FROM enrolled e WHERE e.student_id = s.student_id)
ORDER BY s.student_id;

-- Additional Set Operations
SELECT '----- Additional: INTERSECT Simulation (Students who are enrolled) -----' AS '';
SELECT s.student_id, s.name
FROM students s
WHERE EXISTS (SELECT 1 FROM enrolled e WHERE e.student_id = s.student_id)
ORDER BY s.student_id;

SELECT '----- Additional: Students vs Enrolled Comparison -----' AS '';
SELECT 'students_only' AS category, COUNT(*) AS count FROM students WHERE student_id NOT IN (SELECT DISTINCT student_id FROM enrolled)
UNION
SELECT 'both_tables', COUNT(*) FROM students WHERE student_id IN (SELECT DISTINCT student_id FROM enrolled)
UNION
SELECT 'enrolled_only', COUNT(*) FROM enrolled WHERE student_id NOT IN (SELECT student_id FROM students);

-- ============================================
-- BONUS: Drop Views (Cleanup demonstration)
-- ============================================

SELECT '===== BONUS: DROP VIEWS =====' AS '';

-- Drop the views we created
DROP VIEW IF EXISTS high_scorers;
DROP VIEW IF EXISTS cse_students_view;
DROP VIEW IF EXISTS enrolled_students_view;
SELECT 'Views dropped successfully' AS '';

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

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

-- Show final state of students table
SELECT 'Final Students Table:' AS '';
SELECT * FROM students ORDER BY student_id;

-- Statistics
SELECT 'Statistics:' AS '';
SELECT 
    COUNT(*) AS total_students,
    COUNT(marks) AS students_with_marks,
    COUNT(*) - COUNT(marks) AS students_with_null_marks,
    ROUND(AVG(marks), 2) AS average_marks,
    MIN(marks) AS min_marks,
    MAX(marks) AS max_marks
FROM students;

-- Grade distribution
SELECT 'Grade Distribution:' AS '';
SELECT 
    CASE 
        WHEN marks >= 90 THEN 'Excellent (90+)'
        WHEN marks >= 80 THEN 'Very Good (80-89)'
        WHEN marks >= 70 THEN 'Good (70-79)'
        WHEN marks >= 60 THEN 'Satisfactory (60-69)'
        WHEN marks IS NULL THEN 'Missing'
        ELSE 'Below 60'
    END AS grade_category,
    COUNT(*) AS count
FROM students
GROUP BY grade_category
ORDER BY grade_category;

-- ============================================
-- END OF LAB - 7
-- ============================================

SELECT '===== ALL 10 TASKS COMPLETED SUCCESSFULLY =====' AS '';
SELECT 'Tasks Completed:' AS '';
SELECT '1. NOT NULL constraint on name' AS '';
SELECT '2. CHECK constraint (marks <= 100)' AS '';
SELECT '3. DEFAULT value for department' AS '';
SELECT '4. View for students with marks > 75' AS '';
SELECT '5. Display records from view' AS '';
SELECT '6. Students with NULL marks' AS '';
SELECT '7. Students with NOT NULL marks' AS '';
SELECT '8. Assign grade using CASE' AS '';
SELECT '9. UNION of student_ids' AS '';
SELECT '10. Students in students but not enrolled' AS '';

-- ============================================
-- End of Lab - 7
-- ============================================