SQL Syntax
Quick Guide

-- Sort ascending (default)
SELECT * FROM employees
ORDER BY last_name ASC;

-- Sort descending
SELECT * FROM employees
ORDER BY salary DESC;

-- Multi-column sort
SELECT * FROM employees
ORDER BY department ASC, salary DESC;

-- SQL Execution Order:
-- 1. FROM / JOIN   2. WHERE
-- 3. GROUP BY      4. HAVING
-- 5. SELECT        6. DISTINCT
-- 7. ORDER BY      8. LIMIT/OFFSET

-- IN: match list of values
WHERE department IN ('Sales', 'IT', 'HR');
WHERE id NOT IN (1, 2, 3);

-- BETWEEN: inclusive range
WHERE salary BETWEEN 40000 AND 80000;
WHERE hire_date BETWEEN
      '2020-01-01' AND '2023-12-31';

-- LIKE: pattern matching
WHERE name LIKE    'A%';   -- starts with A
WHERE name LIKE    '%son'; -- ends with son
WHERE email LIKE   '%@gmail%';
WHERE code LIKE    'A_C';  -- _ = one char

-- Case-insensitive (MySQL)
WHERE LOWER(name) LIKE '%john%';

-- Simple CASE
SELECT name,
  CASE department
    WHEN 'IT'    THEN 'Tech'
    WHEN 'HR'    THEN 'People'
    ELSE               'Other'
  END AS dept_group
FROM employees;

-- Searched CASE
SELECT name, salary,
  CASE
    WHEN salary < 40000  THEN 'Junior'
    WHEN salary < 80000  THEN 'Mid'
    ELSE                         'Senior'
  END AS level
FROM employees;

-- CASE in ORDER BY
ORDER BY
  CASE status
    WHEN 'urgent' THEN 1
    WHEN 'normal' THEN 2
    ELSE 3
  END;

-- RIGHT JOIN: all from right table
SELECT e.name, d.dept_name
FROM employees e
RIGHT JOIN departments d
  ON e.dept_id = d.id;

-- FULL OUTER JOIN: all rows, both
SELECT *
FROM employees e
FULL OUTER JOIN departments d
  ON e.dept_id = d.id;

-- CROSS JOIN: cartesian product
SELECT a.color, b.size
FROM colors a
CROSS JOIN sizes b;

-- SELF JOIN: table joins itself
SELECT e.name, m.name AS manager
FROM employees e
LEFT JOIN employees m
  ON e.manager_id = m.id;

-- Three-table join
SELECT
  o.order_id,
  c.name       AS customer,
  p.product_name
FROM  orders   o
JOIN  customers c ON o.cust_id    = c.id
JOIN  products  p ON o.product_id = p.id;

-- JOIN with multiple conditions
JOIN prices pr
  ON p.id   = pr.product_id
 AND pr.region = 'US';

-- JOIN with subquery
JOIN (
  SELECT dept_id, AVG(salary) avg_sal
  FROM  employees
  GROUP BY dept_id
) ds ON e.dept_id = ds.dept_id;

-- GROUP BY: summarise per group
SELECT
  department,
  COUNT(*)        AS headcount,
  AVG(salary)     AS avg_salary,
  MAX(salary)     AS top_salary
FROM  employees
GROUP BY department
ORDER BY headcount DESC;

-- HAVING: filter on aggregated values
-- (use HAVING not WHERE for aggregates)
SELECT department, COUNT(*) AS cnt
FROM  employees
GROUP BY department
HAVING COUNT(*) > 5;

-- WHERE + GROUP BY + HAVING
SELECT dept, SUM(sales) AS total
FROM  orders
WHERE  year = 2024
GROUP BY dept
HAVING SUM(sales) > 100000
ORDER BY total DESC;

-- EXISTS: check if any rows exist
SELECT * FROM customers c
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.cust_id = c.id
);

-- NOT EXISTS
WHERE NOT EXISTS (
  SELECT 1 FROM orders o
  WHERE o.cust_id = c.id
);

-- ANY / ALL comparisons
WHERE salary > ANY (
  SELECT salary FROM managers
);
WHERE salary > ALL (
  SELECT salary FROM managers
);

-- Basic CTE
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) avg_sal
  FROM  employees
  GROUP BY dept_id
)
SELECT e.*, da.avg_sal
FROM  employees e
JOIN  dept_avg  da ON e.dept_id = da.dept_id;

-- Multiple CTEs
WITH
  top_earners AS (
    SELECT * FROM employees
    WHERE salary > 100000
  ),
  their_depts AS (
    SELECT DISTINCT dept_id
    FROM top_earners
  )
SELECT * FROM their_depts;

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

-- Modify column type / constraint
ALTER TABLE employees
  MODIFY COLUMN salary DECIMAL(12,2) NOT NULL;

-- Rename a column
ALTER TABLE employees
  RENAME COLUMN phone TO mobile;

-- Drop a column
ALTER TABLE employees
  DROP COLUMN mobile;

-- Add a constraint
ALTER TABLE employees
  ADD CONSTRAINT chk_salary
  CHECK (salary >= 0);

-- Rename the table
ALTER TABLE employees
  RENAME TO staff;

-- DROP TABLE (permanent!)
DROP TABLE employees;
DROP TABLE IF EXISTS employees;

-- TRUNCATE: delete all rows fast
TRUNCATE TABLE employees;
-- Resets AUTO_INCREMENT; faster than DELETE

-- Drop database
DROP DATABASE mydb;

-- Constraint types summary
-- PRIMARY KEY  — unique, not null identifier
-- FOREIGN KEY  — reference to another table
-- UNIQUE       — no duplicate values
-- NOT NULL     — value required
-- DEFAULT      — fallback value
-- CHECK        — custom validation rule

-- Create table from SELECT
CREATE TABLE emp_backup
  AS SELECT * FROM employees;

-- Update a single column
UPDATE employees
SET   salary = 90000
WHERE id = 42;

-- Update multiple columns
UPDATE employees
SET
  salary    = salary * 1.10,  -- 10% raise
  is_active = TRUE
WHERE department = 'Sales';

-- Update using subquery
UPDATE employees
SET salary = (
  SELECT AVG(salary) FROM benchmarks
  WHERE  role = 'dev'
)
WHERE title = 'Developer';

-- ⚠️ Update ALL rows (no WHERE)
UPDATE products
SET tax_rate = 0.18;  -- every row!

-- Delete specific rows
DELETE FROM employees
WHERE id = 42;

-- Delete with condition
DELETE FROM employees
WHERE hire_date < '2010-01-01'
  AND is_active = 0;

-- Delete using subquery
DELETE FROM orders
WHERE customer_id IN (
  SELECT id FROM customers
  WHERE  country = 'Deleted'
);

-- ⚠️ Delete ALL rows (dangerous)
DELETE FROM logs;   -- no WHERE = all rows

-- Safe delete: check count first
SELECT COUNT(*) FROM employees
WHERE dept_id = 7; -- then DELETE

SELECT
  name, department, salary,

  -- ROW_NUMBER: unique 1,2,3...
  ROW_NUMBER() OVER (
    PARTITION BY department
    ORDER BY salary DESC
  ) AS row_num,

  -- RANK: gaps after ties (1,2,2,4)
  RANK() OVER (
    PARTITION BY dept
    ORDER BY salary DESC
  ) AS rank_num,

  -- DENSE_RANK: no gaps (1,2,2,3)
  DENSE_RANK() OVER (
    ORDER BY salary DESC
  ) AS dense,

  -- LAG/LEAD: previous/next row value
  LAG(salary, 1, 0) OVER (ORDER BY id) AS prev_sal,
  LEAD(salary, 1)    OVER (ORDER BY id) AS next_sal

FROM employees;

-- CREATE VIEW: saved query as virtual table
CREATE VIEW v_active_employees AS
  SELECT id, name, department, salary
  FROM   employees
  WHERE  is_active = 1;

-- Query a view like a table
SELECT * FROM v_active_employees;

-- Drop a view
DROP VIEW IF EXISTS v_active_employees;

-- Performance tips:
-- ✓ Index WHERE, JOIN, ORDER BY columns
-- ✓ Use LIMIT to avoid full scans
-- ✓ Avoid SELECT * in production
-- ✓ Use EXISTS over IN for large sets
-- ✓ Avoid functions on indexed columns
--   WHERE YEAR(date) = 2024  ← slow
--   WHERE date >= '2024-01-01' ← fast
-- ✓ Use covering indexes for hot queries

-- User-defined function
DELIMITER //
CREATE FUNCTION CalcBonus(p_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2) DETERMINISTIC
BEGIN
  RETURN p_salary * 0.15;
END //
DELIMITER ;

-- Use function in query
SELECT name, salary,
  CalcBonus(salary) AS bonus
FROM employees;

-- CREATE TRIGGER
CREATE TRIGGER before_emp_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END;

-- Set isolation level
SET TRANSACTION ISOLATION LEVEL
  READ UNCOMMITTED;  -- lowest
  READ COMMITTED;
  REPEATABLE READ;   -- MySQL default
  SERIALIZABLE;      -- highest

-- Row locking
SELECT * FROM accounts
WHERE id = 1
FOR UPDATE;        -- exclusive lock

SELECT * FROM accounts
FOR SHARE;         -- shared/read lock

NOW()                   -- 2024-06-01 12:30:00
CURDATE()               -- 2024-06-01
CURTIME()               -- 12:30:00

DATE(NOW())              -- extract date part
YEAR(hire_date)          -- 2022
MONTH(hire_date)         -- 6
DAY(hire_date)           -- 15
DAYNAME(hire_date)       -- Monday

DATEDIFF(NOW(), hire_date) -- days diff
TIMESTAMPDIFF(MONTH,
  hire_date, NOW())       -- months diff

DATE_ADD(NOW(),
  INTERVAL 30 DAY)        -- add 30 days
DATE_FORMAT(NOW(),
  '%d/%m/%Y')             -- 01/06/2024

-- UNION: combine, remove duplicates
SELECT id, name FROM customers
UNION
SELECT id, name FROM suppliers;

-- UNION ALL: keep duplicates (faster)
SELECT city FROM customers
UNION ALL
SELECT city FROM employees;

-- INTERSECT: rows in BOTH queries
SELECT email FROM customers
INTERSECT
SELECT email FROM newsletter;

-- EXCEPT / MINUS: in first but not second
SELECT email FROM customers
EXCEPT  -- use MINUS in Oracle
SELECT email FROM unsubscribed;

-- Rules: same # columns, compatible types