SELECT
  10 + 3   AS addition,       -- 13
  10 - 3   AS subtraction,    -- 7
  10 * 3   AS multiplication, -- 30
  10 / 3   AS division,       -- 3.3333 (always float)
  10 DIV 3 AS int_division,   -- 3  (integer division, truncates toward zero)
  10 % 3   AS modulo,         -- 1
  10 MOD 3 AS modulo2,        -- 1  (same as %)
  -10      AS negation;       -- -10

-- Safe division: NULL instead of error when denominator is 0
SELECT price / NULLIF(quantity, 0) AS unit_cost FROM order_items;

-- Integer division edge cases
SELECT  7 DIV  2;   -- 3  (not 3.5)
SELECT -7 DIV  2;   -- -3 (truncates toward zero, not floor)
SELECT  7 MOD -2;   -- 1  (sign follows dividend in MySQL)
SELECT -7 MOD  2;   -- -1

SELECT
  5 = 5       AS eq,           -- 1 (true)
  5 != 3      AS neq,          -- 1
  5 <> 3      AS neq2,         -- 1 (same as !=)
  5 < 10      AS lt,           -- 1
  5 > 3       AS gt,           -- 1
  5 <= 5      AS lte,          -- 1
  5 >= 6      AS gte,          -- 0 (false)
  NULL = NULL AS null_eq,      -- NULL (not 1!)
  NULL <=> NULL AS null_safe;  -- 1 (NULL-safe equality)

-- String '10abc' converts to 10 for the comparison
SELECT '10abc' = 10;    -- returns 1 (TRUE!) — the string becomes 10
SELECT '10abc' = '10';  -- returns 0 — both are strings, compared as strings

-- This can silently match unintended rows
SELECT * FROM users WHERE id = '1 OR 1=1';
-- MySQL converts '1 OR 1=1' to 1 — finds user id=1 only (not an injection risk here)

-- But implicit coercion blocks index use
-- BAD: index on id (INT) cannot be used because of type mismatch
SELECT * FROM users WHERE id = '42';  -- implicit cast string to int
-- GOOD: same type, index used
SELECT * FROM users WHERE id = 42;

-- AND: true only when both sides are true
SELECT 1 AND 1,    -- 1
       1 AND 0,    -- 0
       0 AND 0,    -- 0
       NULL AND 1, -- NULL (unknown)
       NULL AND 0; -- 0   (false regardless of NULL)

-- OR: true when at least one side is true
SELECT 1 OR 0,    -- 1
       0 OR 0,    -- 0
       NULL OR 1, -- 1   (true regardless of NULL)
       NULL OR 0; -- NULL (unknown)

-- NOT: inverts the boolean
SELECT NOT 1,    -- 0
       NOT 0,    -- 1
       NOT NULL; -- NULL

-- XOR: true only when exactly one side is true
SELECT 1 XOR 1,  -- 0 (both true -> false)
       1 XOR 0,  -- 1 (one true -> true)
       0 XOR 0;  -- 0 (both false -> false)

-- Symbol equivalents
SELECT 1 && 1,  -- same as AND
       1 || 0,  -- same as OR
       !1;      -- same as NOT

-- Inclusive range: expr BETWEEN low AND high
SELECT * FROM products WHERE price BETWEEN 10.00 AND 99.99;

-- NOT BETWEEN
SELECT * FROM orders WHERE total NOT BETWEEN 0 AND 100;

-- Date range — BETWEEN is inclusive, so this includes all of June 30
SELECT * FROM events
WHERE event_date BETWEEN '2024-06-01' AND '2024-06-30';

-- Equivalent to:
SELECT * FROM events
WHERE event_date >= '2024-06-01' AND event_date <= '2024-06-30';

-- Match any value in the list
SELECT * FROM employees WHERE department_id IN (1, 2, 5);

-- Equivalent to multiple OR conditions (but cleaner)
SELECT * FROM orders WHERE status IN ('pending', 'processing', 'on_hold');

-- NOT IN
SELECT * FROM users WHERE role NOT IN ('admin', 'superuser');

-- IN with subquery
SELECT * FROM products
WHERE category_id IN (SELECT id FROM categories WHERE featured = 1);

-- % = zero or more characters, _ = exactly one character
SELECT * FROM customers WHERE last_name LIKE 'Mac%';  -- uses index
SELECT * FROM products WHERE sku LIKE 'A_01';
SELECT * FROM articles WHERE title LIKE '%performance%';  -- no index

-- NOT LIKE
SELECT * FROM users WHERE email NOT LIKE '%@internal.%';

-- Escape special characters with ESCAPE clause
SELECT * FROM messages WHERE body LIKE '50% off' ESCAPE '';

-- Email format validation
SELECT * FROM users
WHERE email REGEXP '^[a-zA-Z0-9._%+\-]+@[a-zA-Z0-9.\-]+\.[a-zA-Z]{2,}$';

-- Phone number format
SELECT * FROM contacts WHERE phone REGEXP '^\+?[0-9]{10,15}$';

-- Starts with a digit
SELECT * FROM products WHERE sku REGEXP '^[0-9]';

-- Anchors: ^ = start, $ = end
-- Character classes: [a-z], [0-9], [^abc] (negation)
-- Quantifiers: * (0+), + (1+), ? (0 or 1), {n,m}
-- Alternation: cat|dog matches 'cat' or 'dog'
SELECT 'hello' REGEXP '^(hello|world)$';  -- 1

-- Case-sensitive match
SELECT * FROM products WHERE sku REGEXP BINARY '^[A-Z]{2}[0-9]{3}$';

-- REGEXP_REPLACE (MySQL 8.0): strip non-digits from phone numbers
SELECT REGEXP_REPLACE(phone, '[^0-9]', '') AS digits_only FROM contacts;

-- Check for NULL (= NULL does NOT work)
SELECT * FROM employees WHERE manager_id IS NULL;

-- Not null
SELECT * FROM orders WHERE shipped_at IS NOT NULL;

-- In UPDATE
UPDATE tasks SET assigned_to = 1 WHERE assigned_to IS NULL;

-- Wrong: always returns 0 rows
SELECT * FROM employees WHERE manager_id = NULL;

-- Return display_name, fall back to username, then 'Anonymous'
SELECT COALESCE(display_name, username, 'Anonymous') AS name
FROM users;

-- Treat NULL stock as 0
SELECT product_name, COALESCE(stock, 0) AS available
FROM products;

-- Safe arithmetic: avoid NULL propagation
SELECT id, COALESCE(discount_pct, 0) * price AS discount_amount
FROM products;

-- Safe division: returns NULL instead of division by zero error
SELECT total_revenue / NULLIF(total_orders, 0) AS avg_order_value
FROM sales_summary;

-- Replace empty strings with NULL for cleaner data
SELECT NULLIF(TRIM(notes), '') AS clean_notes FROM tickets;

-- Return the largest / smallest value from a list of expressions
SELECT
  GREATEST(10, 25, 7, 42)  AS greatest_val,  -- 42
  LEAST(10, 25, 7, 42)     AS least_val;      -- 7

-- Practical: clamp a discount to valid range 0-50
SELECT
  product_id,
  GREATEST(0, LEAST(50, discount_pct)) AS clamped_discount
FROM promotions;

SELECT
  6 & 3     AS bitwise_and,  -- 2  (0110 & 0011 = 0010)
  6 | 3     AS bitwise_or,   -- 7  (0110 | 0011 = 0111)
  6 ^ 3     AS bitwise_xor,  -- 5  (0110 ^ 0011 = 0101)
  ~6        AS bitwise_not,  -- depends on int size (complement)
  1 << 3    AS left_shift,   -- 8  (1 * 2^3)
  16 >> 2   AS right_shift;  -- 4  (16 / 2^2)

-- Permission bitmask: 1=read, 2=write, 4=delete, 8=admin
CREATE TABLE user_perms (
  user_id     INT NOT NULL PRIMARY KEY,
  permissions TINYINT UNSIGNED NOT NULL DEFAULT 1
);

-- Grant write permission to user 42
UPDATE user_perms SET permissions = permissions | 2 WHERE user_id = 42;

-- Check if user has write permission (bit 2)
SELECT user_id FROM user_perms WHERE permissions & 2 = 2;

-- Revoke delete permission (bit 4)
UPDATE user_perms SET permissions = permissions & ~4 WHERE user_id = 42;

-- Check if user has BOTH read and write (bits 1 and 2)
SELECT user_id FROM user_perms WHERE permissions & 3 = 3;