-- Comments start with two dashes

-- Data Definition Language (DDL)

-- Create a table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    hire_date DATE,
    salary DECIMAL(10, 2),
    department_id INT
);

-- Alter table
ALTER TABLE employees ADD COLUMN email VARCHAR(100);
ALTER TABLE employees DROP COLUMN email;
ALTER TABLE employees MODIFY COLUMN salary DECIMAL(12, 2);

-- Drop table
DROP TABLE employees;

-- Create index
CREATE INDEX idx_last_name ON employees(last_name);

-- Data Manipulation Language (DML)

-- Insert data
INSERT INTO employees (employee_id, first_name, last_name, hire_date, salary, department_id)
VALUES (1, 'John', 'Doe', '2023-01-15', 50000.00, 1);

-- Update data
UPDATE employees
SET salary = 55000.00
WHERE employee_id = 1;

-- Delete data
DELETE FROM employees
WHERE employee_id = 1;

-- Select data
SELECT * FROM employees;

SELECT first_name, last_name, salary
FROM employees
WHERE department_id = 1
ORDER BY salary DESC
LIMIT 10;

-- Joins
-- Inner Join
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.department_id;

-- Left Join
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id;

-- Right Join
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.department_id;

-- Full Outer Join (not supported in all databases)
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.department_id;

-- Cross Join
SELECT e.first_name, e.last_name, d.department_name
FROM employees e
CROSS JOIN departments d;

-- Self Join
SELECT e1.first_name AS employee, e2.first_name AS manager
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;

-- Subqueries
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Exists
SELECT department_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.department_id = d.department_id);

-- Aggregate functions
SELECT 
    department_id,
    COUNT(*) AS employee_count,
    AVG(salary) AS avg_salary,
    MAX(salary) AS max_salary,
    MIN(salary) AS min_salary,
    SUM(salary) AS total_salary
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

-- Window functions
SELECT 
    first_name,
    last_name,
    salary,
    department_id,
    AVG(salary) OVER (PARTITION BY department_id) AS dept_avg_salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank
FROM employees;

-- Common Table Expressions (CTE)
WITH high_salary_employees AS (
    SELECT *
    FROM employees
    WHERE salary > 100000
)
SELECT first_name, last_name, salary
FROM high_salary_employees
ORDER BY salary DESC;

-- UNION, INTERSECT, EXCEPT
SELECT first_name, last_name FROM employees
UNION
SELECT first_name, last_name FROM contractors;

SELECT department_id FROM departments
INTERSECT
SELECT DISTINCT department_id FROM employees;

SELECT department_id FROM departments
EXCEPT
SELECT DISTINCT department_id FROM employees;

-- Case statement
SELECT 
    first_name,
    last_name,
    salary,
    CASE
        WHEN salary < 50000 THEN 'Low'
        WHEN salary BETWEEN 50000 AND 100000 THEN 'Medium'
        ELSE 'High'
    END AS salary_category
FROM employees;

-- Date and time functions
SELECT 
    first_name,
    last_name,
    hire_date,
    EXTRACT(YEAR FROM hire_date) AS hire_year,
    DATE_ADD(hire_date, INTERVAL 1 YEAR) AS one_year_anniversary,
    DATEDIFF(CURRENT_DATE, hire_date) AS days_employed
FROM employees;

-- String functions
SELECT 
    first_name,
    last_name,
    UPPER(last_name) AS upper_last_name,
    LOWER(first_name) AS lower_first_name,
    CONCAT(first_name, ' ', last_name) AS full_name,
    LENGTH(last_name) AS last_name_length
FROM employees;

-- Transactions
BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
COMMIT;

-- Rollback
BEGIN TRANSACTION;
    UPDATE accounts SET balance = balance - 100 WHERE account_id = 1;
    UPDATE accounts SET balance = balance + 100 WHERE account_id = 2;
ROLLBACK;

-- Views
CREATE VIEW high_salary_employees AS
SELECT *
FROM employees
WHERE salary > 100000;

-- Stored Procedure (syntax may vary by database)
CREATE PROCEDURE increase_salary(IN employee_id INT, IN increase_amount DECIMAL(10, 2))
BEGIN
    UPDATE employees
    SET salary = salary + increase_amount
    WHERE employee_id = employee_id;
END;

-- Triggers (syntax may vary by database)
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
    IF NEW.salary < OLD.salary THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot be decreased';
    END IF;
END;

-- Indexes
CREATE INDEX idx_last_name ON employees(last_name);
CREATE UNIQUE INDEX idx_email ON employees(email);

-- Grant and Revoke permissions
GRANT SELECT, INSERT ON employees TO 'user'@'localhost';
REVOKE INSERT ON employees FROM 'user'@'localhost';

-- Explain plan
EXPLAIN SELECT * FROM employees WHERE last_name = 'Smith';

-- Pivot (not standard SQL, syntax varies by database)
-- Example for SQL Server
SELECT 
    department_name,
    [2021] AS sales_2021,
    [2022] AS sales_2022,
    [2023] AS sales_2023
FROM 
(
    SELECT 
        d.department_name,
        YEAR(s.sale_date) AS sale_year,
        s.sale_amount
    FROM 
        sales s
        JOIN departments d ON s.department_id = d.department_id
) AS SourceTable
PIVOT
(
    SUM(sale_amount)
    FOR sale_year IN ([2021], [2022], [2023])
) AS PivotTable;