SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[GROUP BY column]
[HAVING condition]
[ORDER BY column [ASC|DESC]]
[LIMIT n];

-- Select every column (convenient but fragile)
SELECT * FROM employees;

-- Select specific columns (recommended for production)
SELECT employee_id, first_name, last_name, email
FROM employees;

-- FAILS: alias 'annual_salary' is not yet defined when WHERE runs
SELECT salary * 12 AS annual_salary
FROM employees
WHERE annual_salary > 60000;  -- ERROR: Unknown column 'annual_salary'

-- Fix 1: repeat the expression in WHERE
SELECT salary * 12 AS annual_salary
FROM employees
WHERE salary * 12 > 60000;

-- Fix 2: wrap in a subquery (alias is now visible at the outer WHERE)
SELECT *
FROM (
  SELECT employee_id, salary * 12 AS annual_salary
  FROM employees
) AS sub
WHERE sub.annual_salary > 60000;

-- ORDER BY CAN reference SELECT aliases (it runs after SELECT)
SELECT salary * 12 AS annual_salary
FROM employees
ORDER BY annual_salary DESC;  -- valid

SELECT
  first_name AS "First Name",
  last_name  AS "Last Name",
  salary     AS monthly_salary
FROM employees;

-- AS keyword is optional but strongly recommended for clarity
SELECT first_name fn, last_name ln FROM employees;

-- Aliases with spaces or special chars must be quoted
SELECT CONCAT(first_name, ' ', last_name) AS "Full Name" FROM employees;

-- Alias reuse in HAVING (MySQL-specific extension)
SELECT department_id, AVG(salary) AS avg_sal
FROM employees
GROUP BY department_id
HAVING avg_sal > 50000;  -- MySQL allows alias in HAVING

SELECT
  product_name,
  price,
  price * 1.10                      AS price_with_tax,
  price * quantity                  AS line_total,
  ROUND(price * 0.9, 2)             AS discounted_price,
  ROUND((price - cost) / price * 100, 1) AS gross_margin_pct
FROM order_items;

-- String expressions
SELECT
  CONCAT(first_name, ' ', last_name)          AS full_name,
  CONCAT_WS(', ', last_name, first_name)       AS formal_name,
  UPPER(email)                                 AS email_upper,
  LENGTH(phone)                                AS phone_len,
  TRIM(description)                            AS clean_desc,
  SUBSTRING(sku, 1, 3)                         AS sku_prefix
FROM products;

-- Date expressions
SELECT
  NOW()                            AS current_datetime,
  CURDATE()                        AS today,
  YEAR(hire_date)                  AS hire_year,
  DATEDIFF(NOW(), hire_date)       AS days_employed,
  DATE_FORMAT(hire_date, '%b %Y')  AS hire_month
FROM employees;

-- Unique departments
SELECT DISTINCT department_id FROM employees;

-- Unique city + country combinations (DISTINCT on both columns together)
SELECT DISTINCT city, country FROM addresses;

-- DISTINCT with aggregate: count unique values
SELECT COUNT(DISTINCT department_id) AS num_departments
FROM employees;

-- DISTINCT vs GROUP BY — equivalent results, different intent
SELECT DISTINCT country FROM customers;
SELECT country FROM customers GROUP BY country;  -- same result

-- Searched CASE (arbitrary conditions)
SELECT
  product_name,
  stock,
  CASE
    WHEN stock = 0              THEN 'Out of stock'
    WHEN stock < 10             THEN 'Low stock'
    WHEN stock BETWEEN 10 AND 50 THEN 'Medium stock'
    ELSE                             'In stock'
  END AS stock_status
FROM products;

-- Simple CASE (equality only)
SELECT
  order_id,
  CASE status
    WHEN 'pending'   THEN 'Awaiting payment'
    WHEN 'paid'      THEN 'Processing'
    WHEN 'shipped'   THEN 'On the way'
    WHEN 'delivered' THEN 'Complete'
    ELSE                  'Unknown'
  END AS status_label
FROM orders;

-- CASE in ORDER BY: custom sort order
SELECT product_name, status
FROM products
ORDER BY
  CASE status
    WHEN 'featured' THEN 1
    WHEN 'active'   THEN 2
    WHEN 'draft'    THEN 3
    ELSE                 4
  END;

-- CASE inside aggregate: conditional count
SELECT
  COUNT(*) AS total_orders,
  SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
  SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
  ROUND(
    SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1
  ) AS delivery_rate_pct
FROM orders;

-- Non-correlated scalar subquery: executes once for the whole query
SELECT
  e.first_name,
  e.salary,
  (SELECT AVG(salary) FROM employees) AS company_avg,
  e.salary - (SELECT AVG(salary) FROM employees) AS diff_from_avg
FROM employees e;

-- Correlated scalar subquery: executes once per row (expensive on big tables)
SELECT
  e.first_name,
  e.salary,
  (SELECT AVG(salary)
   FROM employees e2
   WHERE e2.department_id = e.department_id) AS dept_avg
FROM employees e;

-- Better: replace the correlated subquery with a JOIN to a derived table
SELECT
  e.first_name,
  e.salary,
  d.dept_avg
FROM employees e
JOIN (
  SELECT department_id, AVG(salary) AS dept_avg
  FROM employees
  GROUP BY department_id
) d ON d.department_id = e.department_id;

SELECT
  first_name,
  department_id,
  salary,
  AVG(salary)  OVER (PARTITION BY department_id) AS dept_avg,
  salary - AVG(salary) OVER (PARTITION BY department_id) AS diff_from_dept_avg,
  RANK()       OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS overall_rank
FROM employees;

-- Find rows with NULL in a required column
SELECT id, first_name, email
FROM customers
WHERE email IS NULL OR TRIM(email) = '';

-- Find duplicate email addresses
SELECT email, COUNT(*) AS occurrences
FROM customers
GROUP BY email
HAVING occurrences > 1
ORDER BY occurrences DESC;

-- Find out-of-range prices
SELECT id, product_name, price
FROM products
WHERE price <= 0 OR price > 100000;

-- Find orphaned orders (no matching customer)
SELECT o.order_id, o.customer_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

-- Summary of NULL counts per column
SELECT
  SUM(email    IS NULL) AS null_emails,
  SUM(phone    IS NULL) AS null_phones,
  SUM(address  IS NULL) AS null_addresses
FROM customers;

-- Export to CSV
SELECT id, first_name, last_name, email
FROM customers
WHERE active = 1
INTO OUTFILE '/tmp/customers_export.csv'
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
';

-- Check FILE privilege is required
SHOW GRANTS FOR CURRENT_USER();
-- Must show: GRANT FILE ON *.*

-- Import back with LOAD DATA INFILE
LOAD DATA INFILE '/tmp/customers_export.csv'
INTO TABLE customers_import
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '
'
(id, first_name, last_name, email);

-- Monthly sales summary: revenue, order count, AOV, new customers
SELECT
  DATE_FORMAT(o.created_at, '%Y-%m')              AS month,
  COUNT(DISTINCT o.order_id)                       AS total_orders,
  COUNT(DISTINCT o.customer_id)                    AS active_customers,
  ROUND(SUM(oi.quantity * oi.unit_price), 2)       AS gross_revenue,
  ROUND(AVG(o.total), 2)                           AS avg_order_value,
  SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END) AS cancellations,
  ROUND(
    SUM(CASE WHEN o.status = 'cancelled' THEN 1 ELSE 0 END)
    * 100.0 / COUNT(*), 1
  )                                                AS cancel_rate_pct,
  -- New customers: first order placed in this month
  COUNT(DISTINCT CASE
    WHEN o.created_at = (
      SELECT MIN(o2.created_at)
      FROM orders o2
      WHERE o2.customer_id = o.customer_id
    ) THEN o.customer_id END)                      AS new_customers
FROM orders      AS o
JOIN order_items AS oi ON oi.order_id = o.order_id
WHERE o.created_at >= DATE_SUB(CURDATE(), INTERVAL 12 MONTH)
GROUP BY DATE_FORMAT(o.created_at, '%Y-%m')
ORDER BY month;

SELECT 1 + 1 AS result;
SELECT NOW() AS server_time;
SELECT VERSION() AS mysql_version;
SELECT DATABASE() AS current_db;
SELECT USER() AS current_user;

-- Useful for testing functions
SELECT
  AES_ENCRYPT('secret', 'key') AS encrypted,
  SHA2('password', 256)        AS hashed;

-- DUAL is a special dummy table (compatible with Oracle)
SELECT 'Hello, MySQL!' AS greeting FROM DUAL;