MySQL Functions

SELECT
    CONCAT('Hello', ' ', 'World')           AS concat_result,      -- 'Hello World'
    LENGTH('Hello World')                   AS length_bytes,        -- 11
    CHAR_LENGTH('Hello World')              AS length_chars,        -- 11
    UPPER('hello world')                    AS upper_result,        -- 'HELLO WORLD'
    LOWER('HELLO WORLD')                    AS lower_result,        -- 'hello world'
    SUBSTRING('Hello World', 7, 5)          AS substr_result,       -- 'World'
    TRIM('  Hello World  ')                 AS trim_result,         -- 'Hello World'
    LTRIM('  Hello')                        AS ltrim_result,        -- 'Hello'
    RTRIM('Hello  ')                        AS rtrim_result,        -- 'Hello'
    REPLACE('Hello World', 'World', 'MySQL') AS replace_result,     -- 'Hello MySQL'
    INSTR('Hello World', 'World')           AS instr_result,        -- 7
    LPAD('42', 5, '0')                      AS lpad_result,         -- '00042'
    RPAD('Hi', 5, '!')                      AS rpad_result,         -- 'Hi!!!'
    REVERSE('MySQL')                        AS reverse_result,      -- 'LQSyM'
    REPEAT('ab', 3)                         AS repeat_result;       -- 'ababab'

-- Practical string queries on the shop database
SELECT
    customer_id,
    CONCAT(first_name, ' ', last_name)      AS full_name,
    UPPER(email)                            AS email_upper,
    SUBSTRING(email, 1, INSTR(email, '@') - 1) AS username
FROM customers;

SELECT
    ROUND(3.14159, 2)       AS round_result,    -- 3.14
    ROUND(3.145, 2)         AS round_result2,   -- 3.15
    CEIL(3.2)               AS ceil_result,     -- 4
    FLOOR(3.9)              AS floor_result,    -- 3
    ABS(-42)                AS abs_result,      -- 42
    MOD(17, 5)              AS mod_result,      -- 2
    POWER(2, 10)            AS power_result,    -- 1024
    SQRT(144)               AS sqrt_result,     -- 12
    RAND()                  AS random_0_to_1,   -- e.g. 0.7234
    TRUNCATE(3.14159, 3)    AS truncate_result; -- 3.141

-- Practical: round prices to 2 decimal places
SELECT name, ROUND(price * 1.1, 2) AS price_with_tax FROM products;

-- Random sample of 3 products
SELECT name, price FROM products ORDER BY RAND() LIMIT 3;

SELECT
    NOW()                                   AS now,           -- 2024-06-15 14:30:00
    CURDATE()                               AS today,         -- 2024-06-15
    CURTIME()                               AS current_time,  -- 14:30:00
    YEAR(NOW())                             AS year,          -- 2024
    MONTH(NOW())                            AS month,         -- 6
    DAY(NOW())                              AS day,           -- 15
    DAYNAME(NOW())                          AS day_name,      -- Saturday
    MONTHNAME(NOW())                        AS month_name,    -- June
    DATE_FORMAT(NOW(), '%d/%m/%Y %H:%i')    AS formatted,     -- 15/06/2024 14:30
    DATE_FORMAT(NOW(), '%W, %M %e, %Y')     AS long_format;   -- Saturday, June 15, 2024

-- Date arithmetic
SELECT
    ordered_at,
    DATE_ADD(ordered_at, INTERVAL 7 DAY)    AS delivery_estimate,
    DATE_SUB(ordered_at, INTERVAL 1 MONTH)  AS one_month_ago,
    DATEDIFF(NOW(), ordered_at)             AS days_since_order,
    TIMESTAMPDIFF(HOUR, ordered_at, NOW())  AS hours_since_order
FROM orders
WHERE order_id = 1;

-- Orders placed in the last 30 days
SELECT order_id, customer_id, total, ordered_at
FROM orders
WHERE ordered_at >= DATE_SUB(NOW(), INTERVAL 30 DAY)
ORDER BY ordered_at DESC;

-- Aggregate functions
SELECT
    COUNT(*)                AS total_products,
    COUNT(description)      AS products_with_desc,  -- NULLs not counted
    SUM(stock)              AS total_stock,
    AVG(price)              AS avg_price,
    MIN(price)              AS cheapest,
    MAX(price)              AS most_expensive
FROM products;

-- IF(condition, true_value, false_value)
SELECT
    name,
    price,
    IF(stock > 0, 'In Stock', 'Out of Stock') AS availability,
    IF(price < 50, 'Budget', IF(price < 200, 'Mid-range', 'Premium')) AS tier
FROM products;

-- CASE expression: multi-branch conditional
SELECT
    order_id,
    total,
    CASE
        WHEN total < 50   THEN 'Small'
        WHEN total < 200  THEN 'Medium'
        WHEN total < 500  THEN 'Large'
        ELSE 'Enterprise'
    END AS order_size
FROM orders;

-- IFNULL(expr, default): return default if expr is NULL
SELECT customer_id, IFNULL(phone, 'N/A') AS phone FROM customers;

-- NULLIF(a, b): return NULL if a = b, otherwise return a
-- Useful to avoid division by zero
SELECT order_id, total / NULLIF(item_count, 0) AS avg_item_price
FROM (
    SELECT o.order_id, o.total, COUNT(oi.item_id) AS item_count
    FROM orders o
    LEFT JOIN order_items oi ON o.order_id = oi.order_id
    GROUP BY o.order_id, o.total
) AS order_summary;

-- ROW_NUMBER(): assign a sequential number within a partition
SELECT
    customer_id,
    order_id,
    total,
    ordered_at,
    ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY ordered_at DESC) AS order_rank
FROM orders;

-- RANK() and DENSE_RANK(): rank products by price within category
SELECT
    name,
    category,
    price,
    RANK()       OVER (PARTITION BY category ORDER BY price DESC) AS price_rank,
    DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

-- Running total of revenue
SELECT
    order_id,
    ordered_at,
    total,
    SUM(total) OVER (ORDER BY ordered_at) AS running_total
FROM orders
WHERE status = 'delivered'
ORDER BY ordered_at;