CREATE TABLE departments (
    dept_id   INT PRIMARY KEY,
    dept_name VARCHAR(50)
);

CREATE TABLE employees (
    emp_id   INT PRIMARY KEY,
    name     VARCHAR(50),
    dept_id  INT,
    salary   INT,
    city     VARCHAR(30)
);

INSERT INTO departments VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Finance'),
(4, 'Marketing');

INSERT INTO employees VALUES
(1, 'Ayesha',  1, 50000, 'Pune'),
(2, 'Rohan',   2, 70000, 'Mumbai'),
(3, 'Sneha',   2, 65000, 'Pune'),
(4, 'Karan',   3, 80000, 'Delhi'),
(5, 'Priya',   NULL, 45000, 'Mumbai'),
(6, 'Dev',     1, 55000, 'Delhi');


--  1. SET OPERATORS

--  UNION  (removes duplicates)
SELECT city FROM employees
UNION
SELECT dept_name FROM departments;

--  UNION ALL  (keeps duplicates)
SELECT city FROM employees
UNION ALL
SELECT city FROM employees WHERE salary > 60000;

--  INTERSECT  (common rows)
SELECT city FROM employees WHERE dept_id = 1
INTERSECT
SELECT city FROM employees WHERE salary < 60000;

--  EXCEPT  (rows in first but NOT in second)
SELECT city FROM employees
EXCEPT
SELECT city FROM employees WHERE dept_id = 2;


--  2. ARITHMETIC OPERATORS

SELECT
    name,
    salary,
    salary + 5000   AS after_bonus,       
    salary - 3000   AS after_deduction,  
    salary * 12     AS annual_salary,    
    salary / 1000   AS salary_in_k,       
    salary % 10000  AS remainder         
FROM employees;


--  3. LOGICAL OPERATOR 

-- AND
SELECT * FROM employees
WHERE dept_id = 2 AND salary > 60000;

-- OR
SELECT * FROM employees
WHERE city = 'Pune' OR city = 'Delhi';

-- NOT
SELECT * FROM employees
WHERE NOT city = 'Mumbai';


--  4. COMPARISON OPERATORS

-- Greater Than
SELECT * FROM employees WHERE salary > 60000;

-- Less Than
SELECT * FROM employees WHERE salary < 60000;


--  5. JOINS

--  INNER JOIN  (only matching rows)
SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

--  LEFT JOIN  (all employees, NULL if no dept)
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id;

--  RIGHT JOIN  (all departments, NULL if no employee)
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d ON e.dept_id = d.dept_id;

--  CROSS JOIN  (every employee × every department)
SELECT e.name, d.dept_name
FROM employees e
CROSS JOIN departments d;

--  NATURAL JOIN  (auto-matches on common column: dept_id)
SELECT * FROM employees NATURAL JOIN departments;

--  SELF JOIN  (employees in the same city)
SELECT a.name AS emp1, b.name AS emp2, a.city
FROM employees a
JOIN employees b ON a.city = b.city AND a.emp_id < b.emp_id;


--  6. SUBQUERIES

--  Aggregate subqueries

-- SUM
SELECT SUM(salary) AS total_salary FROM employees;

-- MAX
SELECT MAX(salary) AS highest_salary FROM employees;

-- MIN
SELECT MIN(salary) AS lowest_salary FROM employees;

-- COUNT
SELECT COUNT(*) AS total_employees FROM employees;

-- AVG
SELECT AVG(salary) AS average_salary FROM employees;

-- IN  (employees in IT or HR)
SELECT * FROM employees
WHERE dept_id IN (SELECT dept_id FROM departments WHERE dept_name IN ('IT','HR'));

-- NOT IN  (employees NOT in IT or HR)
SELECT * FROM employees
WHERE dept_id NOT IN (SELECT dept_id FROM departments WHERE dept_name IN ('IT','HR'));

-- EXISTS  (departments that have at least one employee)
SELECT dept_name FROM departments d
WHERE EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);

-- NOT EXISTS  (departments with NO employees)
SELECT dept_name FROM departments d
WHERE NOT EXISTS (
    SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id
);

--  Single-value subquery
-- Employees earning above the average salary
SELECT name, salary FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);