SELECT ABS(-42);    -- 42
SELECT ABS(42);     -- 42
SELECT ABS(-3.14);  -- 3.14

-- Find orders where payment differs from invoice by more than $1
SELECT order_id, ABS(payment_amount - invoice_amount) AS discrepancy
FROM orders
WHERE ABS(payment_amount - invoice_amount) > 1.00
ORDER BY discrepancy DESC;

SELECT CEIL(4.1);    -- 5
SELECT CEIL(4.9);    -- 5
SELECT CEIL(-4.1);   -- -4  (rounds toward zero, which is UP for negatives)
SELECT FLOOR(4.9);   -- 4
SELECT FLOOR(4.1);   -- 4
SELECT FLOOR(-4.1);  -- -5  (rounds away from zero for negatives)

-- Calculate pagination: how many pages for n items at 20 per page
SELECT CEIL(COUNT(*) / 20.0) AS total_pages FROM products;

-- Calculate shipping boxes needed (capacity = 12 per box)
SELECT order_id, CEIL(item_count / 12.0) AS boxes_needed FROM orders;

SELECT ROUND(4.5);         -- 5   (half rounds up)
SELECT ROUND(4.4);         -- 4
SELECT ROUND(-4.5);        -- -5  (away from zero)
SELECT ROUND(3.14159, 2);  -- 3.14
SELECT ROUND(3.145,  2);   -- 3.15
SELECT ROUND(1234, -2);    -- 1200  (negative decimals: round to hundreds)
SELECT ROUND(1250, -2);    -- 1300

-- Apply tax and round to cents
SELECT order_id,
       subtotal,
       ROUND(subtotal * 1.08, 2) AS total_with_tax
FROM orders;

SELECT TRUNCATE(3.999, 2);   -- 3.99  (no rounding — just cuts off)
SELECT TRUNCATE(3.999, 0);   -- 3
SELECT TRUNCATE(-3.999, 2);  -- -3.99 (truncates toward zero)
SELECT TRUNCATE(1234, -2);   -- 1200  (truncate to hundreds)

-- Compare with ROUND on a negative:
SELECT ROUND(-3.5, 0);       -- -4    (rounds AWAY from zero)
SELECT TRUNCATE(-3.5, 0);    -- -3    (truncates TOWARD zero)

-- Show price floored to the dollar (no rounding)
SELECT product_name, TRUNCATE(price, 0) AS floor_price FROM products;

SELECT MOD(10, 3);    -- 1
SELECT 10 MOD 3;      -- 1 (operator alias)
SELECT 10 % 3;        -- 1 (operator alias)
SELECT MOD(10.5, 3);  -- 1.5 (works with decimals)
SELECT MOD(-10, 3);   -- -1 (sign matches the dividend)

-- Find rows with even IDs
SELECT id, name FROM users WHERE MOD(id, 2) = 0;

-- Assign rows to one of 4 processing buckets (sharding pattern)
SELECT id, MOD(id, 4) AS bucket FROM orders;

-- Find orders placed on the 15th of any month
SELECT * FROM orders WHERE DAY(created_at) = 15;

SELECT POWER(2, 10);    -- 1024
SELECT POW(3, 3);       -- 27
SELECT SQRT(144);       -- 12
SELECT SQRT(2);         -- 1.4142135623731

-- Compound interest: principal * (1 + rate)^years
SELECT
  principal,
  annual_rate,
  years,
  ROUND(principal * POWER(1 + annual_rate, years), 2) AS future_value
FROM investments;

-- Euclidean distance between two coordinate points
SELECT
  SQRT(POWER(dest_x - origin_x, 2) + POWER(dest_y - origin_y, 2)) AS distance
FROM deliveries;

SELECT FORMAT(1234567.891, 2);   -- '1,234,567.89'
SELECT FORMAT(1234567.891, 0);   -- '1,234,568'
SELECT FORMAT(0.5, 2);           -- '0.50'

-- Format revenue for a report
SELECT
  product_name,
  FORMAT(SUM(revenue), 2) AS formatted_revenue
FROM sales
GROUP BY product_name
ORDER BY SUM(revenue) DESC;

-- With locale (third argument, MySQL 5.7+)
SELECT FORMAT(1234567.89, 2, 'de_DE');  -- '1.234.567,89' (German format)

SELECT RAND();          -- 0.73829... (different every call)
SELECT RAND(42);        -- 0.63966... (reproducible with seed 42)

-- Random integer between 1 and 100 inclusive
SELECT FLOOR(RAND() * 100) + 1 AS random_int;

-- Pick 5 random rows — simple but slow on large tables
SELECT * FROM products ORDER BY RAND() LIMIT 5;

-- Faster random sampling using a random ID offset
-- Step 1: find the max ID
-- Step 2: pick a random starting point
-- Step 3: use a range query (uses the index)
SELECT * FROM products
WHERE id >= (
  SELECT FLOOR(RAND() * (SELECT MAX(id) FROM products))
)
LIMIT 5;

-- For truly uniform random sampling on large tables,
-- use a numbers table + multiple range queries and union

-- The floating-point trap
CREATE TEMPORARY TABLE float_test (val FLOAT);
INSERT INTO float_test VALUES (0.1 + 0.2);

SELECT val, val = 0.3 AS is_equal FROM float_test;
-- val shows 0.3, but is_equal returns 0 (FALSE)
-- because 0.1 + 0.2 = 0.30000000000000004 in binary floating point

-- The fix: DECIMAL is exact
CREATE TEMPORARY TABLE decimal_test (val DECIMAL(10, 2));
INSERT INTO decimal_test VALUES (0.10 + 0.20);

SELECT val, val = 0.3 AS is_equal FROM decimal_test;
-- val = 0.30, is_equal = 1 (TRUE)

-- Always use DECIMAL for monetary columns
CREATE TABLE products (
  id    INT          AUTO_INCREMENT PRIMARY KEY,
  name  VARCHAR(255) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,    -- up to 99,999,999.99
  cost  DECIMAL(12, 4) NOT NULL     -- 4 decimal places for supplier costs
);

-- DECIMAL(precision, scale):
-- precision = total digits, scale = digits after decimal point
-- DECIMAL(10,2) can hold from -99999999.99 to 99999999.99

-- Standard deviation and variance for analytics
SELECT
  AVG(order_total)             AS avg_order,
  STDDEV(order_total)          AS std_dev,
  VARIANCE(order_total)        AS variance,
  MIN(order_total)             AS min_order,
  MAX(order_total)             AS max_order
FROM orders
WHERE YEAR(created_at) = 2024;

-- STDDEV_POP vs STDDEV_SAMP:
SELECT
  STDDEV_POP(score)   AS population_std,   -- for the full population
  STDDEV_SAMP(score)  AS sample_std         -- for a sample (Bessel's correction)
FROM test_scores;

-- BIT_COUNT: count the number of set bits in an integer
SELECT BIT_COUNT(7);   -- 3  (binary 0111 has 3 bits set)
SELECT BIT_COUNT(255); -- 8  (binary 11111111)
SELECT BIT_COUNT(0);   -- 0

-- Permission bitmask pattern
-- Define permissions as powers of 2
-- READ = 1 (bit 0), WRITE = 2 (bit 1), DELETE = 4 (bit 2), ADMIN = 8 (bit 3)

-- Check if user has WRITE permission (bit 1 is set)
SELECT user_id, permissions,
  (permissions & 2) > 0 AS can_write,
  (permissions & 4) > 0 AS can_delete,
  (permissions & 8) > 0 AS is_admin
FROM user_permissions;

-- Find all users with at least READ and WRITE (permissions 1 OR 2)
SELECT * FROM user_permissions WHERE (permissions & 3) = 3;

-- Grant WRITE permission to a user
UPDATE user_permissions SET permissions = permissions | 2 WHERE user_id = 42;

-- Revoke DELETE permission from a user
UPDATE user_permissions SET permissions = permissions & ~4 WHERE user_id = 42;

-- 5% discount per complete $100 spent, max 25%
SELECT
  order_id,
  order_total,
  LEAST(FLOOR(order_total / 100) * 5, 25) AS discount_pct,
  ROUND(order_total * (1 - LEAST(FLOOR(order_total / 100) * 5, 25) / 100.0), 2) AS final_price
FROM orders;

-- Always round tax separately, never before multiplying
SELECT
  subtotal,
  ROUND(subtotal * 0.08, 2)  AS tax,
  subtotal + ROUND(subtotal * 0.08, 2) AS total
FROM orders;

-- M = P * [r(1+r)^n] / [(1+r)^n - 1]
-- P = principal, r = monthly rate, n = number of payments
SELECT
  principal,
  annual_rate,
  term_months,
  ROUND(
    principal
      * (annual_rate / 12)
      * POWER(1 + annual_rate / 12, term_months)
    / (POWER(1 + annual_rate / 12, term_months) - 1),
    2
  ) AS monthly_payment
FROM loan_applications;

SELECT
  product_id,
  current_price,
  previous_price,
  ROUND((current_price - previous_price) / NULLIF(previous_price, 0) * 100, 2) AS pct_change
FROM price_history;
-- NULLIF prevents division by zero when previous_price is 0

-- Choosing DECIMAL precision and scale
-- DECIMAL(precision, scale)
-- precision = total significant digits
-- scale = digits after the decimal point

-- Typical financial column definitions:
CREATE TABLE financials (
  price          DECIMAL(10, 2),   -- up to 99,999,999.99 (retail prices)
  exchange_rate  DECIMAL(12, 6),   -- up to 999999.999999 (forex rates need 6dp)
  percentage     DECIMAL(6,  4),   -- up to 99.9999% (tax rates, fees)
  quantity       DECIMAL(12, 3),   -- up to 999999999.999 (fractional units)
  tax_amount     DECIMAL(10, 2)    -- same as price
);

-- NEVER round intermediate values in multi-step calculations
-- Round ONLY the final output
SELECT
  subtotal,
  -- Wrong: round at each step accumulates error
  ROUND(subtotal * 0.08, 2) + ROUND(subtotal * 0.05, 2) AS wrong_total_tax,
  -- Right: compute full precision, round the final sum
  ROUND(subtotal * (0.08 + 0.05), 2) AS correct_total_tax
FROM orders;

-- CONV(number, from_base, to_base)
SELECT CONV('ff',  16, 10);   -- 255 (hex to decimal)
SELECT CONV('255', 10, 16);   -- 'FF' (decimal to hex)
SELECT CONV('11',   2, 10);   -- 3 (binary to decimal)
SELECT CONV('255', 10, 2);    -- '11111111' (decimal to binary)

-- Useful for storing hex color codes and displaying as decimal
SELECT product_id,
  CONV(color_hex, 16, 10) AS color_decimal
FROM products WHERE color_hex IS NOT NULL;

-- GREATEST: returns the largest value from the list
SELECT GREATEST(10, 20, 5, 15);    -- 20
SELECT GREATEST(NULL, 10, 5);      -- NULL (any NULL makes GREATEST return NULL)

-- LEAST: returns the smallest value
SELECT LEAST(10, 20, 5, 15);       -- 5

-- Practical: cap a discount between 0% and 25%
SELECT
  order_id,
  order_total,
  LEAST(GREATEST(discount_pct, 0), 25) AS capped_discount
FROM orders;

-- Practical: pick the latest of two dates
SELECT
  product_id,
  GREATEST(last_purchased_at, last_viewed_at) AS last_interaction
FROM product_activity;

SELECT SIGN(-42);   -- -1
SELECT SIGN(0);     -- 0
SELECT SIGN(42);    -- 1

-- Categorize account balance direction without CASE
SELECT account_id,
  CASE SIGN(balance)
    WHEN -1 THEN 'Overdrawn'
    WHEN  0 THEN 'Zero'
    WHEN  1 THEN 'Positive'
  END AS status
FROM accounts;

-- Use SIGN to flip a value: multiply by SIGN to negate negative values
SELECT amount, amount * SIGN(amount) AS abs_without_abs FROM transactions;

SELECT EXP(1);        -- 2.71828...  (Euler's number e)
SELECT EXP(0);        -- 1
SELECT LOG(10);       -- 2.30258...  (natural log, base e)
SELECT LOG(10, 100);  -- 2           (log base 10 of 100)
SELECT LOG2(8);       -- 3
SELECT LOG10(1000);   -- 3
SELECT PI();          -- 3.14159265358979

-- Exponential decay model: value * e^(-rate * time)
SELECT initial_value * EXP(-0.05 * months_elapsed) AS remaining_value
FROM subscriptions;

-- Area of a circle
SELECT radius, ROUND(PI() * POWER(radius, 2), 4) AS area FROM circles;

-- Convert degrees to radians
SELECT degrees, ROUND(degrees * PI() / 180, 6) AS radians FROM angles;