MySQL Functions String, Date, Aggregate Functions: Tutorial, Examples, FAQs & Interview Tips

MySQL Functions String, Date, Aggregate Functions

MySQL in MySQL is best learned by connecting the rule to an order-management database. Start with the smallest SQL statement, observe the output, and then add one realistic constraint so the concept becomes practical.

The key habit for this lesson is to watch table rows, constraints, and indexes as it changes. That makes the topic easier to debug, easier to explain in interviews, and easier to use in real code without memorizing isolated syntax.

String Functions

MySQL provides a rich set of built-in string functions for manipulating text data directly in SQL queries.

String 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;

Numeric Functions

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;

Date and Time Functions

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 and Control Flow Functions

Aggregate Functions and Control Flow (IF, CASE, NULLIF)

-- 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;

Window Functions (MySQL 8.0+)

-- 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;

Applied guide for MySQL

Use MySQL when the program needs a clear answer to a specific problem, not because the keyword looks familiar. In a real MySQL task, first name the input, then name the transformation, then name the output. This small discipline shows whether the topic is being used correctly or only copied from an example.

A reliable practice flow is: create the smallest working SQL statement, add one normal case, add one edge case such as missing, repeated, empty, or boundary input, and then confirm the result with result set and EXPLAIN output. If the result surprises you, reduce the code until the behavior is visible again.

The most common trap here is copying the syntax before understanding the behavior. Avoid it by writing one sentence before the code that explains why MySQL is the right choice. After the code runs, verify the lesson by doing this: change one input and explain the changed output.

Identify the exact problem solved by MySQL.
Trace table rows, constraints, and indexes before and after the main operation.
Keep one intentionally broken version and explain the fix.
Connect the example to an order-management database so the idea feels concrete.

Key Takeaways

I can explain where MySQL fits inside an order-management database.
I can point to the exact table rows, constraints, and indexes affected by this topic.
I tested a normal case and an edge case involving missing, repeated, empty, or boundary input.
I verified the result with result set and EXPLAIN output instead of assuming it worked.
I can describe the main mistake: copying the syntax before understanding the behavior.

Common Mistakes to Avoid

WRONG Copying the syntax before understanding the behavior.

RIGHT Write the expected behavior first, then make the example prove it.

A one-line expectation turns the code from copied syntax into a testable idea.

WRONG Practicing only the perfect input.

RIGHT Also test missing, repeated, empty, or boundary input before considering the lesson complete.

The edge case is where most interview follow-up questions begin.

WRONG Looking only at the final output.

RIGHT Trace table rows, constraints, and indexes through each important step.

Tracing makes debugging faster because you can see the first incorrect state.

Practice Tasks

Build one small SQL statement that demonstrates MySQL in an order-management database.
Change the example to include missing, repeated, empty, or boundary input and record the difference.
Break the example by deliberately copying the syntax before understanding the behavior, then write the corrected version.
Explain the finished example in five bullet points: input, operation, output, failure case, and verification.

Frequently Asked Questions

When should I use MySQL?

Use it when the problem matches the behavior shown in the example and when the result can be verified through result set and EXPLAIN output.

How do I avoid mistakes in MySQL?

Start with a tiny case, then test missing, repeated, empty, or boundary input. The main warning sign is copying the syntax before understanding the behavior.

How can I revise MySQL quickly?

Trace table rows, constraints, and indexes, predict the result, run the example, and compare your prediction with the actual output.

Previous Next

MySQL Functions String, Date, Aggregate Functions: Tutorial, Examples, FAQs & Interview Tips