[WITH cte_name AS (select_statement) [, ...]]
SELECT [DISTINCT] result_column [, ...]
  [FROM table_or_subquery [join_clause ...]]
  [WHERE expression]
  [GROUP BY expression [, ...]]
  [HAVING expression]
  [WINDOW window_name AS (window_definition) [, ...]]
  [compound_operator select_statement]
  [ORDER BY ordering_term [, ...]]
  [LIMIT expression [OFFSET expression]]

-- All columns
SELECT * FROM employees;

-- Specific columns
SELECT name, department FROM employees;

-- Expressions and aliases
SELECT name, salary * 12 AS annual_salary FROM employees;

SELECT e.name, e.salary * 12 annual_pay
FROM employees AS e;

-- Single table
SELECT * FROM employees;

-- Subquery as table source
SELECT * FROM (SELECT name, salary FROM employees WHERE salary > 50000) AS high_earners;

SELECT * FROM employees WHERE department = 'Engineering';

SELECT * FROM orders WHERE total > 100 AND status != 'cancelled';

SELECT * FROM products
WHERE (category = 'Electronics' OR category = 'Books')
  AND price < 50
  AND NOT discontinued;

-- LIKE: case-insensitive pattern matching (% = any chars, _ = one char)
SELECT * FROM employees WHERE name LIKE 'J%';

-- GLOB: case-sensitive pattern matching (* = any chars, ? = one char)
SELECT * FROM files WHERE path GLOB '*.txt';

-- REGEXP: regular expression matching
SELECT * FROM logs WHERE message REGEXP '^ERROR:';

-- BETWEEN (inclusive on both ends)
SELECT * FROM orders WHERE total BETWEEN 100 AND 500;

-- IN with a list
SELECT * FROM employees WHERE department IN ('Engineering', 'Design', 'Product');

-- IN with a subquery
SELECT * FROM employees WHERE department_id IN (SELECT id FROM departments WHERE active = 1);

SELECT * FROM employees WHERE manager_id IS NULL;

SELECT * FROM employees WHERE phone IS NOT NULL;

SELECT name,
  CASE
    WHEN salary >= 100000 THEN 'Senior'
    WHEN salary >= 60000 THEN 'Mid'
    ELSE 'Junior'
  END AS level
FROM employees;

-- Simple CASE form
SELECT name,
  CASE department
    WHEN 'Engineering' THEN 'Eng'
    WHEN 'Marketing' THEN 'Mkt'
    ELSE 'Other'
  END AS dept_code
FROM employees;

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

SELECT e.name, m.name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.id;

SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;
-- Returns all employees (even without a department)
-- and all departments (even without employees)

SELECT * FROM orders NATURAL JOIN customers;
-- Joins on every column name shared between orders and customers

SELECT * FROM orders JOIN customers USING (customer_id);

SELECT o.id, c.name, p.product_name, oi.quantity
FROM orders o
JOIN customers c ON o.customer_id = c.id
JOIN order_items oi ON o.id = oi.order_id
JOIN products p ON oi.product_id = p.id
WHERE o.order_date > '2024-01-01';

SELECT department, COUNT(*) AS employee_count, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) >= 5;

SELECT
  CASE WHEN salary >= 80000 THEN 'High' ELSE 'Standard' END AS band,
  COUNT(*) AS count
FROM employees
GROUP BY CASE WHEN salary >= 80000 THEN 'High' ELSE 'Standard' END;

SELECT DISTINCT department FROM employees;

SELECT DISTINCT department, title FROM employees;

SELECT * FROM employees ORDER BY salary DESC;

-- Multiple sort keys
SELECT * FROM employees ORDER BY department ASC, salary DESC;

-- Ordering by column position
SELECT name, salary FROM employees ORDER BY 2 DESC;

-- Ordering by alias
SELECT name, salary * 12 AS annual FROM employees ORDER BY annual DESC;

-- NULLs at the end (default for ASC is NULLS LAST, for DESC is NULLS FIRST)
SELECT * FROM employees ORDER BY manager_id ASC NULLS LAST;

-- NULLs at the beginning
SELECT * FROM employees ORDER BY manager_id DESC NULLS FIRST;

-- Return at most 10 rows
SELECT * FROM employees ORDER BY salary DESC LIMIT 10;

-- Skip 20 rows, then return 10
SELECT * FROM employees ORDER BY salary DESC LIMIT 10 OFFSET 20;

SELECT name, salary,
  salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees;

SELECT * FROM products
WHERE category_id IN (SELECT id FROM categories WHERE active = 1);

SELECT * FROM products
WHERE category_id NOT IN (SELECT id FROM categories WHERE discontinued = 1);

SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id AND o.total > 1000
);

SELECT dept, avg_salary
FROM (
  SELECT department AS dept, AVG(salary) AS avg_salary
  FROM employees
  GROUP BY department
) AS dept_stats
WHERE avg_salary > 70000;

WITH regional_sales AS (
  SELECT region, SUM(amount) AS total_sales
  FROM orders
  GROUP BY region
)
SELECT region, total_sales
FROM regional_sales
WHERE total_sales > 100000
ORDER BY total_sales DESC;

WITH
  active_customers AS (
    SELECT * FROM customers WHERE active = 1
  ),
  recent_orders AS (
    SELECT * FROM orders WHERE order_date > '2024-01-01'
  )
SELECT ac.name, COUNT(ro.id) AS order_count
FROM active_customers ac
JOIN recent_orders ro ON ac.id = ro.customer_id
GROUP BY ac.name;

SELECT name, department, salary,
  SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS running_total
FROM employees;

SELECT name, department, salary,
  SUM(salary) OVER w AS dept_running_total,
  COUNT(*) OVER w AS dept_running_count
FROM employees
WINDOW w AS (PARTITION BY department ORDER BY salary)
ORDER BY department, salary;

function_name() OVER (
  [PARTITION BY expression [, ...]]
  [ORDER BY expression [ASC | DESC] [, ...]]
)

-- Customers who are also employees
SELECT name FROM customers
INTERSECT
SELECT name FROM employees;

-- All people from both tables, no duplicates
SELECT name, email FROM customers
UNION
SELECT name, email FROM employees;

-- Customers who are not employees
SELECT name FROM customers
EXCEPT
SELECT name FROM employees;

SELECT id, 'order' AS source FROM orders
UNION ALL
SELECT id, 'return' AS source FROM returns;

SELECT name FROM customers
UNION
SELECT name FROM employees
ORDER BY name;

SELECT
  d.department_name,
  COUNT(e.id) AS headcount,
  ROUND(AVG(e.salary), 2) AS avg_salary,
  MIN(e.hire_date) AS earliest_hire
FROM departments d
LEFT JOIN employees e ON d.id = e.department_id
GROUP BY d.department_name
HAVING COUNT(e.id) > 0
ORDER BY avg_salary DESC
LIMIT 10 OFFSET 0;

WITH high_value_orders AS (
  SELECT customer_id, COUNT(*) AS order_count, SUM(total) AS total_spent
  FROM orders
  WHERE order_date >= '2024-01-01'
  GROUP BY customer_id
  HAVING SUM(total) > 5000
)
SELECT c.name, h.order_count, h.total_spent
FROM customers c
JOIN high_value_orders h ON c.id = h.customer_id
ORDER BY h.total_spent DESC;

SELECT p.product_name, p.price, c.category_name
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
WHERE EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
)
ORDER BY p.price DESC
LIMIT 20;