-- ================================
-- 1. DATABASE & TABLE CREATION
-- ================================

-- (MySQL syntax; in SQL Server/Oracle skip CREATE DATABASE if not supported)
CREATE DATABASE SchoolDB;
USE SchoolDB;

-- Students Table
CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name VARCHAR(50),
    Age INT,
    Grade VARCHAR(5),
    Email VARCHAR(100)
);

-- Departments Table
CREATE TABLE Departments (
    DeptID INT PRIMARY KEY,
    DeptName VARCHAR(50)
);

-- Employees Table
CREATE TABLE Employees (
    EmpID INT PRIMARY KEY,
    Name VARCHAR(50),
    Salary DECIMAL(10,2),
    DeptID INT,
    HireDate DATE,
    FOREIGN KEY (DeptID) REFERENCES Departments(DeptID)
);

-- Classes Table
CREATE TABLE Classes (
    ClassID INT PRIMARY KEY,
    ClassName VARCHAR(50),
    StudentID INT,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
);

-- ================================
-- 2. INSERT SAMPLE DATA
-- ================================
INSERT INTO Students VALUES
(1, 'Ali', 12, 'A', 'ali@example.com'),
(2, 'Sara', 13, 'B', 'sara@example.com'),
(3, 'John', 14, 'A', NULL),
(4, 'Meena', 15, 'C', 'meena@example.com'),
(5, 'Hasan', 12, 'B', NULL);

INSERT INTO Departments VALUES
(10, 'HR'),
(20, 'IT'),
(30, 'Finance');

INSERT INTO Employees VALUES
(101, 'Karim', 55000, 20, '2020-01-15'),
(102, 'Rahim', 45000, 10, '2019-06-01'),
(103, 'Rashid', 60000, 20, '2021-03-20'),
(104, 'Sonia', 70000, 30, '2018-12-05'),
(105, 'Nabila', 48000, 10, '2022-07-10');

INSERT INTO Classes VALUES
(1, 'Math', 1),
(2, 'Science', 2),
(3, 'History', 3),
(4, 'Math', 4),
(5, 'English', 5);

-- ================================
-- 3. PRACTICE QUERIES
-- ================================

-- Basic SELECT
SELECT * FROM Students;
SELECT Name, Grade FROM Students;

-- Filtering
SELECT * FROM Students WHERE Age > 12;
SELECT * FROM Students WHERE Grade = 'A' AND Age < 14;

-- Sorting
SELECT * FROM Students ORDER BY Name ASC;
SELECT * FROM Employees ORDER BY Salary DESC;

-- LIKE & Pattern Matching
SELECT * FROM Students WHERE Name LIKE 'A%';
SELECT * FROM Students WHERE Email IS NULL;

-- Aggregates
SELECT COUNT(*) AS TotalStudents FROM Students;
SELECT AVG(Salary) AS AvgSalary FROM Employees;
SELECT DeptID, SUM(Salary) AS TotalSalary FROM Employees GROUP BY DeptID;
SELECT DeptID, COUNT(*) AS EmployeeCount 
FROM Employees 
GROUP BY DeptID HAVING COUNT(*) > 1;

-- Joins
SELECT e.Name, d.DeptName, e.Salary
FROM Employees e
INNER JOIN Departments d ON e.DeptID = d.DeptID;

SELECT s.Name, c.ClassName
FROM Students s
LEFT JOIN Classes c ON s.StudentID = c.StudentID;

-- Subquery
SELECT Name, Salary
FROM Employees
WHERE Salary > (SELECT AVG(Salary) FROM Employees);

-- Update
UPDATE Students SET Age = 13 WHERE StudentID = 1;
UPDATE Employees SET Salary = Salary * 1.1 WHERE DeptID = 20;

-- Delete
DELETE FROM Students WHERE StudentID = 5;

-- Insert New Record
INSERT INTO Employees VALUES (106, 'Tariq', 52000, 30, '2023-01-01');

-- String Functions
SELECT UPPER(Name) AS UpperName FROM Students;
SELECT CONCAT(Name, ' - Grade ', Grade) AS StudentInfo FROM Students;

-- Date Functions
SELECT Name, HireDate, YEAR(HireDate) AS YearJoined FROM Employees;
SELECT NOW() AS CurrentDateTime; -- MySQL
-- In SQL Server use: SELECT GETDATE();
-- In Oracle use: SELECT SYSDATE FROM dual;

-- Distinct
SELECT DISTINCT Grade FROM Students;

-- IN & BETWEEN
SELECT * FROM Employees WHERE DeptID IN (10, 20);
SELECT * FROM Employees WHERE Salary BETWEEN 45000 AND 60000;

-- CASE (Conditional)
SELECT Name,
       CASE 
         WHEN Salary > 60000 THEN 'High Earner'
         WHEN Salary BETWEEN 50000 AND 60000 THEN 'Mid Earner'
         ELSE 'Low Earner'
       END AS SalaryCategory
FROM Employees;

-- Nested Subquery
SELECT Name FROM Students
WHERE Age = (SELECT MAX(Age) FROM Students);

-- Self Join (Find employees in same department)
SELECT e1.Name AS Emp1, e2.Name AS Emp2, e1.DeptID
FROM Employees e1
JOIN Employees e2 ON e1.DeptID = e2.DeptID
WHERE e1.EmpID <> e2.EmpID;

-- UNION Example
SELECT Name FROM Students
UNION
SELECT Name FROM Employees;

-- EXISTS Example
SELECT Name FROM Students s
WHERE EXISTS (SELECT 1 FROM Classes c WHERE s.StudentID = c.StudentID);

-- View
CREATE VIEW HighSalaryEmployees AS
SELECT Name, Salary FROM Employees WHERE Salary > 55000;

SELECT * FROM HighSalaryEmployees;

-- Index (DBA exercise; syntax varies slightly across DBs)
CREATE INDEX idx_salary ON Employees(Salary);

-- Drop (cleanup)
-- DROP TABLE Classes;