-- ============================================
-- SQL Lab - 3: Data Manipulation Language (DML)
-- 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 from Lab 2
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);

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

-- ============================================
-- TASK 1: INSERT Operations
-- ============================================

SELECT '===== TASK 1: INSERT OPERATIONS =====' AS '';

-- 1. Insert a new student record with your own details
SELECT '----- 1. Insert Student: Prashant Ranjan (Roll: 24MC3035) -----' AS '';
INSERT INTO students (student_id, name, department, year, marks) VALUES
(9, 'Prashant Ranjan', 'CSE', 2, 88);
SELECT * FROM students WHERE student_id = 9;

-- 2. Insert two students belonging to the CSE department
SELECT '----- 2. Insert Two CSE Students -----' AS '';
INSERT INTO students (student_id, name, department, year, marks) VALUES
(10, 'Vikram Sharma', 'CSE', 1, 75),
(11, 'Neha Gupta', 'CSE', 3, 85);
SELECT * FROM students WHERE department = 'CSE' ORDER BY student_id;

-- 3. Insert a student whose marks are less than 50
SELECT '----- 3. Insert Student with Marks < 50 -----' AS '';
INSERT INTO students (student_id, name, department, year, marks) VALUES
(12, 'Rajesh Kumar', 'ME', 1, 42);
SELECT * FROM students WHERE marks < 50;

-- 4. Insert a student from the ECE department studying in 1st year
SELECT '----- 4. Insert ECE 1st Year Student -----' AS '';
INSERT INTO students (student_id, name, department, year, marks) VALUES
(13, 'Kavita Singh', 'ECE', 1, 79);
SELECT * FROM students WHERE department = 'ECE' AND year = 1;

-- Show all records after INSERT operations
SELECT '----- All Records After INSERT Operations -----' AS '';
SELECT * FROM students ORDER BY student_id;

-- ============================================
-- TASK 2: UPDATE Operations
-- ============================================

SELECT '===== TASK 2: UPDATE OPERATIONS =====' AS '';

-- 1. Update the marks of the student whose student_id is 3
SELECT '----- 1. Update Marks of Student ID 3 (Ravi Kumar) -----' AS '';
SELECT 'Before Update:' AS '';
SELECT * FROM students WHERE student_id = 3;
UPDATE students SET marks = 85 WHERE student_id = 3;
SELECT 'After Update:' AS '';
SELECT * FROM students WHERE student_id = 3;

-- 2. Increase marks by 5 for all students of the ME department
SELECT '----- 2. Increase Marks by 5 for ME Department -----' AS '';
SELECT 'Before Update (ME Students):' AS '';
SELECT * FROM students WHERE department = 'ME';
UPDATE students SET marks = marks + 5 WHERE department = 'ME';
SELECT 'After Update (ME Students):' AS '';
SELECT * FROM students WHERE department = 'ME';

-- 3. Change the department of the student named Rahul to CSE
SELECT '----- 3. Change Rahul\'s Department to CSE -----' AS '';
SELECT 'Before Update:' AS '';
SELECT * FROM students WHERE name = 'Rahul Mehta';
UPDATE students SET department = 'CSE' WHERE name = 'Rahul Mehta';
SELECT 'After Update:' AS '';
SELECT * FROM students WHERE name = 'Rahul Mehta';

-- 4. Update the year of study to 4 for students having marks greater than 85
SELECT '----- 4. Update Year to 4 for Students with Marks > 85 -----' AS '';
SELECT 'Before Update (Marks > 85):' AS '';
SELECT * FROM students WHERE marks > 85;
UPDATE students SET year = 4 WHERE marks > 85;
SELECT 'After Update (Marks > 85):' AS '';
SELECT * FROM students WHERE marks > 85;

-- Show all records after UPDATE operations
SELECT '----- All Records After UPDATE Operations -----' AS '';
SELECT * FROM students ORDER BY student_id;

-- ============================================
-- TASK 3: DELETE Operations
-- ============================================

SELECT '===== TASK 3: DELETE OPERATIONS =====' AS '';

-- 1. Delete the record of the student whose student_id is 1
SELECT '----- 1. Delete Student with ID 1 (Amit Sharma) -----' AS '';
SELECT 'Before Delete:' AS '';
SELECT * FROM students WHERE student_id = 1;
DELETE FROM students WHERE student_id = 1;
SELECT 'After Delete - Checking if ID 1 exists:' AS '';
SELECT * FROM students WHERE student_id = 1;

-- 2. Delete all students belonging to the EEE department
SELECT '----- 2. Delete All EEE Department Students -----' AS '';
SELECT 'Before Delete (EEE Students):' AS '';
SELECT * FROM students WHERE department = 'EEE';
DELETE FROM students WHERE department = 'EEE';
SELECT 'After Delete - EEE Students Remaining:' AS '';
SELECT * FROM students WHERE department = 'EEE';

-- 3. Delete students who have marks below 40
SELECT '----- 3. Delete Students with Marks Below 40 -----' AS '';
SELECT 'Before Delete (Marks < 40):' AS '';
SELECT * FROM students WHERE marks < 40;
DELETE FROM students WHERE marks < 40;
SELECT 'After Delete - Checking for Marks < 40:' AS '';
SELECT * FROM students WHERE marks < 40;

-- 4. Delete a student whose name starts with the letter 'P'
SELECT '----- 4. Delete Students with Name Starting with \'P\' -----' AS '';
SELECT 'Before Delete (Names starting with P):' AS '';
SELECT * FROM students WHERE name LIKE 'P%';
DELETE FROM students WHERE name LIKE 'P%';
SELECT 'After Delete - Remaining Students:' AS '';
SELECT * FROM students ORDER BY student_id;

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

SELECT '===== FINAL TABLE STATE =====' AS '';
SELECT COUNT(*) AS remaining_students FROM students;
SELECT 'Final Student Records:' AS '';
SELECT * FROM students ORDER BY student_id;

-- ============================================
-- End of Lab - 3
-- ============================================