-- Select all columns
SELECT * FROM users;

-- Select specific columns
SELECT id, name, email FROM users;

-- Filter with WHERE
SELECT * FROM users WHERE age > 18;

-- Multiple conditions
SELECT * FROM users 
WHERE age > 18 AND status = 'active';

-- Pattern matching
SELECT * FROM users 
WHERE name LIKE 'J%';

-- Sort ascending
SELECT * FROM users 
ORDER BY name ASC;

-- Sort descending
SELECT * FROM users 
ORDER BY created_at DESC;

-- Multiple sort criteria
SELECT * FROM users 
ORDER BY last_name ASC, first_name ASC;

-- Limit results
SELECT * FROM users 
LIMIT 10;

-- Limit with offset (pagination)
SELECT * FROM users 
LIMIT 10 OFFSET 20;

-- Count rows
SELECT COUNT(*) FROM users;

-- Count distinct values
SELECT COUNT(DISTINCT country) FROM users;

-- Sum values
SELECT SUM(salary) FROM employees;

-- Average
SELECT AVG(age) FROM users;

-- Minimum and maximum
SELECT MIN(salary), MAX(salary) 
FROM employees;

-- Group with aggregates
SELECT department, AVG(salary) 
FROM employees 
GROUP BY department;

-- INNER JOIN
SELECT u.name, o.order_date
FROM users u
INNER JOIN orders o ON u.id = o.user_id;

-- LEFT JOIN
SELECT u.name, o.order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;

-- RIGHT JOIN
SELECT u.name, o.order_date
FROM users u
RIGHT JOIN orders o ON u.id = o.user_id;

-- Multiple JOINs
SELECT u.name, o.order_date, p.product_name
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN products p ON o.product_id = p.id;

-- Basic GROUP BY
SELECT department, COUNT(*) 
FROM employees 
GROUP BY department;

-- GROUP BY with aggregate
SELECT department, AVG(salary) as avg_salary
FROM employees 
GROUP BY department;

-- HAVING clause
SELECT department, AVG(salary) as avg_salary
FROM employees 
GROUP BY department
HAVING AVG(salary) > 50000;

-- Multiple aggregates
SELECT 
  department,
  COUNT(*) as emp_count,
  AVG(salary) as avg_salary,
  MAX(salary) as max_salary
FROM employees 
GROUP BY department;

-- Subquery in WHERE
SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

-- Subquery in SELECT
SELECT 
  name,
  salary,
  (SELECT AVG(salary) FROM employees) as avg_salary
FROM employees;

-- IN with subquery
SELECT name
FROM employees
WHERE department_id IN (
  SELECT id FROM departments WHERE location = 'NYC'
);

-- EXISTS
SELECT name
FROM employees e
WHERE EXISTS (
  SELECT 1 FROM projects p 
  WHERE p.manager_id = e.id
);

-- INSERT single row
INSERT INTO users (name, email, age)
VALUES ('John Doe', 'john@example.com', 30);

-- INSERT multiple rows
INSERT INTO users (name, email, age)
VALUES 
  ('Jane Smith', 'jane@example.com', 25),
  ('Bob Wilson', 'bob@example.com', 35);

-- UPDATE data
UPDATE users 
SET age = 31, email = 'john.doe@example.com'
WHERE id = 1;

-- DELETE rows
DELETE FROM users 
WHERE status = 'inactive';

-- TRUNCATE table (remove all data)
TRUNCATE TABLE temp_data;

-- Basic table
CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL,
  email VARCHAR(255) UNIQUE,
  age INT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Table with foreign key
CREATE TABLE orders (
  id INT PRIMARY KEY AUTO_INCREMENT,
  user_id INT,
  amount DECIMAL(10,2),
  order_date DATE,
  FOREIGN KEY (user_id) REFERENCES users(id)
);

-- Table with check constraint
CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(100),
  salary DECIMAL(10,2) CHECK (salary > 0),
  department VARCHAR(50)
);

-- Add column
ALTER TABLE users 
ADD COLUMN phone VARCHAR(20);

-- Drop column
ALTER TABLE users 
DROP COLUMN phone;

-- Modify column
ALTER TABLE users 
MODIFY COLUMN email VARCHAR(320);

-- Add constraint
ALTER TABLE users 
ADD CONSTRAINT unique_email UNIQUE (email);

-- Drop constraint
ALTER TABLE users 
DROP CONSTRAINT unique_email;

-- Rename table
ALTER TABLE users 
RENAME TO customers;

-- Create index
CREATE INDEX idx_user_email ON users(email);

-- Create unique index
CREATE UNIQUE INDEX idx_unique_email ON users(email);

-- Composite index
CREATE INDEX idx_name_age ON users(name, age);

-- Drop index
DROP INDEX idx_user_email ON users;

-- Show indexes
SHOW INDEX FROM users;

-- Concatenation
SELECT CONCAT(first_name, ' ', last_name) AS full_name 
FROM users;

-- Uppercase/Lowercase
SELECT UPPER(name), LOWER(email) FROM users;

-- Substring
SELECT SUBSTRING(name, 1, 3) AS initials FROM users;

-- Length
SELECT name, LENGTH(name) AS name_length FROM users;

-- Trim
SELECT TRIM('   hello   ') AS trimmed;

-- Replace
SELECT REPLACE(description, 'old', 'new') FROM products;

-- Current date/time
SELECT NOW(), CURDATE(), CURTIME();

-- Date formatting
SELECT DATE_FORMAT(created_at, '%Y-%m-%d') FROM users;

-- Date arithmetic
SELECT 
  created_at,
  DATE_ADD(created_at, INTERVAL 7 DAY) AS next_week,
  DATE_SUB(created_at, INTERVAL 1 MONTH) AS last_month
FROM users;

-- Date difference
SELECT DATEDIFF('2024-12-31', '2024-01-01') AS days_diff;

-- Extract parts
SELECT 
  YEAR(created_at) AS year,
  MONTH(created_at) AS month,
  DAY(created_at) AS day
FROM users;

-- ROW_NUMBER
SELECT 
  name,
  salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) as rank
FROM employees;

-- RANK and DENSE_RANK
SELECT 
  name,
  salary,
  RANK() OVER (ORDER BY salary DESC) as rank,
  DENSE_RANK() OVER (ORDER BY salary DESC) as dense_rank
FROM employees;

-- Partition by
SELECT 
  department,
  name,
  salary,
  AVG(salary) OVER (PARTITION BY department) as dept_avg
FROM employees;

-- Running total
SELECT 
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date) as running_total
FROM orders;

-- Basic CTE
WITH high_earners AS (
  SELECT * FROM employees WHERE salary > 100000
)
SELECT * FROM high_earners;

-- Multiple CTEs
WITH 
dept_stats AS (
  SELECT department, AVG(salary) as avg_salary
  FROM employees 
  GROUP BY department
),
high_paying_depts AS (
  SELECT department 
  FROM dept_stats 
  WHERE avg_salary > 80000
)
SELECT * FROM employees 
WHERE department IN (SELECT department FROM high_paying_depts);

-- Recursive CTE
WITH RECURSIVE numbers AS (
  SELECT 1 as n
  UNION ALL
  SELECT n + 1 FROM numbers WHERE n < 10
)
SELECT * FROM numbers;

-- Simple CASE
SELECT 
  name,
  CASE 
    WHEN age < 18 THEN 'Minor'
    WHEN age BETWEEN 18 AND 65 THEN 'Adult'
    ELSE 'Senior'
  END AS age_group
FROM users;

-- CASE in aggregation
SELECT 
  department,
  COUNT(*) as total,
  COUNT(CASE WHEN salary > 100000 THEN 1 END) as high_earners
FROM employees
GROUP BY department;

-- CASE in ORDER BY
SELECT name, salary
FROM employees
ORDER BY 
  CASE 
    WHEN department = 'IT' THEN 1
    WHEN department = 'HR' THEN 2
    ELSE 3
  END;

-- UNION (distinct)
SELECT name FROM active_users
UNION
SELECT name FROM inactive_users;

-- UNION ALL (all rows)
SELECT name FROM current_employees
UNION ALL
SELECT name FROM former_employees;

-- INTERSECT
SELECT product_id FROM online_orders
INTERSECT
SELECT product_id FROM store_orders;

-- EXCEPT
SELECT customer_id FROM all_customers
EXCEPT
SELECT customer_id FROM premium_customers;

-- Create view
CREATE VIEW active_users AS
SELECT id, name, email
FROM users
WHERE status = 'active';

-- Use view
SELECT * FROM active_users;

-- Update through view
UPDATE active_users 
SET email = 'new@example.com'
WHERE id = 1;

-- Create or replace view
CREATE OR REPLACE VIEW user_orders AS
SELECT u.name, o.order_date, o.amount
FROM users u
JOIN orders o ON u.id = o.user_id;

-- Drop view
DROP VIEW active_users;

-- Create procedure
DELIMITER //
CREATE PROCEDURE GetUserCount(IN status_filter VARCHAR(20))
BEGIN
  SELECT COUNT(*) as user_count
  FROM users
  WHERE status = status_filter;
END //
DELIMITER ;

-- Execute procedure
CALL GetUserCount('active');

-- Procedure with output parameter
DELIMITER //
CREATE PROCEDURE GetUserStats(
  OUT total_users INT,
  OUT active_users INT
)
BEGIN
  SELECT COUNT(*) INTO total_users FROM users;
  SELECT COUNT(*) INTO active_users FROM users WHERE status = 'active';
END //
DELIMITER ;

-- Call with output
CALL GetUserStats(@total, @active);
SELECT @total, @active;

-- Basic transaction
START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

-- Transaction with error handling
START TRANSACTION;

BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    RESIGNAL;
  END;

  INSERT INTO orders (user_id, amount) VALUES (1, 99.99);
  UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 123;
  
  COMMIT;
END;

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Top customers by spending
SELECT 
  u.name,
  u.email,
  COUNT(o.id) as order_count,
  SUM(o.amount) as total_spent,
  AVG(o.amount) as avg_order_value
FROM users u
JOIN orders o ON u.id = o.user_id
WHERE o.order_date >= DATE_SUB(NOW(), INTERVAL 30 DAY)
GROUP BY u.id, u.name, u.email
HAVING total_spent > 1000
ORDER BY total_spent DESC
LIMIT 10;

-- Recursive CTE for hierarchy
WITH RECURSIVE employee_hierarchy AS (
  -- Anchor: top-level managers
  SELECT 
    id,
    name,
    manager_id,
    1 as level,
    name as path
  FROM employees
  WHERE manager_id IS NULL
  
  UNION ALL
  
  -- Recursive: subordinates
  SELECT 
    e.id,
    e.name,
    e.manager_id,
    eh.level + 1,
    CONCAT(eh.path, ' -> ', e.name)
  FROM employees e
  JOIN employee_hierarchy eh ON e.manager_id = eh.id
)
SELECT 
  id,
  name,
  level,
  path
FROM employee_hierarchy
ORDER BY path;

-- Monthly sales with growth
WITH monthly_sales AS (
  SELECT 
    DATE_FORMAT(order_date, '%Y-%m') as month,
    SUM(amount) as total_sales,
    COUNT(*) as order_count
  FROM orders
  WHERE order_date >= DATE_SUB(NOW(), INTERVAL 12 MONTH)
  GROUP BY DATE_FORMAT(order_date, '%Y-%m')
)
SELECT 
  month,
  total_sales,
  order_count,
  LAG(total_sales) OVER (ORDER BY month) as prev_month_sales,
  ROUND(
    (total_sales - LAG(total_sales) OVER (ORDER BY month)) / 
    LAG(total_sales) OVER (ORDER BY month) * 100, 2
  ) as growth_percent
FROM monthly_sales
ORDER BY month DESC;

-- Cohort retention analysis
WITH user_cohorts AS (
  SELECT 
    user_id,
    DATE_FORMAT(MIN(order_date), '%Y-%m-01') as cohort_month
  FROM orders
  GROUP BY user_id
),
cohort_data AS (
  SELECT 
    uc.cohort_month,
    o.user_id,
    TIMESTAMPDIFF(MONTH, uc.cohort_month, o.order_date) as month_number
  FROM user_cohorts uc
  JOIN orders o ON uc.user_id = o.user_id
  GROUP BY uc.cohort_month, o.user_id, month_number
)
SELECT 
  cohort_month,
  month_number,
  COUNT(DISTINCT user_id) as active_users,
  ROUND(
    COUNT(DISTINCT user_id) * 100.0 / 
    MAX(CASE WHEN month_number = 0 THEN COUNT(DISTINCT user_id) END) OVER (PARTITION BY cohort_month),
    2
  ) as retention_rate
FROM cohort_data
GROUP BY cohort_month, month_number
ORDER BY cohort_month, month_number;

-- Use EXPLAIN to analyze queries
EXPLAIN SELECT * FROM users WHERE email = 'test@example.com';

-- Index frequently queried columns
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_orders_user_date ON orders(user_id, order_date);

-- Avoid SELECT *
SELECT id, name, email FROM users; -- Instead of SELECT *

-- Use WHERE instead of HAVING for filtering
SELECT department, AVG(salary)
FROM employees
WHERE salary > 50000  -- Filter before grouping
GROUP BY department;

-- Use EXISTS instead of IN for large datasets
SELECT name 
FROM employees e
WHERE EXISTS (
  SELECT 1 FROM departments d 
  WHERE d.manager_id = e.id
);