-- ============================================
-- SQL Lab - 2: DDL and Basic DQL
-- Student: Prashant Ranjan
-- Roll Number: 24MC3035
-- Platform: mycompiler.io (MySQL)
-- ============================================

-- ============================================
-- TASK 1: Create Table
-- ============================================

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

-- Clear old data (optional, for clean run)
DELETE FROM students;

-- ============================================
-- TASK 2: Insert Data
-- ============================================

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),
(9, 'Kunal Das', 'CSE', 4, 95),
(10, 'Divya Patil', 'ECE', 3, 58),
(11, 'Manish Joshi', 'ME', 2, 62),
(12, 'Swati Jain', 'EEE', 4, 88),
(13, 'Rajesh Yadav', 'CSE', 1, 45),
(14, 'Meera Iyer', 'ECE', 4, 92),
(15, 'Vikram Seth', 'ME', 1, 53),
(16, 'Anjali Menon', 'EEE', 3, 77),
(17, 'Saurabh Tyagi', 'CSE', 2, 81),
(18, 'Kavita Bansal', 'ECE', 2, 49),
(19, 'Rohan Desai', 'ME', 4, 86),
(20, 'Tanvi Khanna', 'EEE', 1, 91),
(21, 'Arjun Nair', 'CSE', 3, 67),
(22, 'Shreya Joshi', 'ECE', 3, 73),
(23, 'Gaurav Singh', 'ME', 2, 59),
(24, 'Nidhi Kaur', 'EEE', 4, 84),
(25, 'Harsh Vardhan', 'CSE', 4, 70);

-- ============================================
-- TASK 3 & 4: Formatted Output Queries
-- ============================================

-- 1. All records (formatted for readability)
SELECT '----- All Students -----' AS '';
SELECT * FROM students ORDER BY student_id;

-- 2. Only name and department
SELECT '----- Name & Department -----' AS '';
SELECT name, department FROM students ORDER BY student_id;

-- 3. CSE department students
SELECT '----- CSE Department Students -----' AS '';
SELECT * FROM students WHERE department = 'CSE' ORDER BY student_id;

-- 4. Students with marks > 75
SELECT '----- Students with Marks > 75 -----' AS '';
SELECT * FROM students WHERE marks > 75 ORDER BY marks DESC;

-- 5. Sorted by marks (descending)
SELECT '----- Students Sorted by Marks (Descending) -----' AS '';
SELECT student_id, name, department, marks FROM students ORDER BY marks DESC;

-- 6. Distinct departments
SELECT '----- Distinct Departments -----' AS '';
SELECT DISTINCT department FROM students ORDER BY department;

-- ============================================
-- TASK 4: Observation Questions (Formatted)
-- ============================================

-- 1. Total number of records
SELECT '----- Total Records Count -----' AS '';
SELECT COUNT(*) AS total_students FROM students;

-- 2. Student(s) with highest marks
SELECT '----- Student(s) with Highest Marks -----' AS '';
SELECT * FROM students 
WHERE marks = (SELECT MAX(marks) FROM students);

-- 3. Number of students in CSE
SELECT '----- Number of CSE Students -----' AS '';
SELECT COUNT(*) AS cse_students FROM students WHERE department = 'CSE';

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