Chapter 22: Advanced Functions and Control Flow

SQL provides a rich set of built-in functions to perform calculations, manipulate strings, format dates, and handle conditional logic directly within your queries. While we’ve used some aggregate functions (COUNT, SUM, AVG) and NOW(), there’s a much broader array available that can simplify complex data transformations and reports.

This chapter will introduce you to:

• Common String Functions
• Common Date and Time Functions
• Conditional Logic (CASE statements)

These tools allow you to do more with your data directly within the database, reducing the need for post-processing in your application code.

1. String Functions

Used for manipulating text data. Common operations include changing case, extracting parts of a string, finding/replacing substrings, and trimming whitespace.

2. Date and Time Functions

Used for working with DATE, TIME, and DATETIME values. These are crucial for handling timestamps, calculating durations, formatting dates for display, and filtering data based on time periods.

3. Conditional Logic (CASE Statements)

The CASE statement is like an IF-THEN-ELSE structure in programming. It allows you to define different outputs based on different conditions within your SELECT statement (or UPDATE and INSERT). This is incredibly powerful for creating dynamic reports and deriving new values.

SQL Syntax & Examples (MySQL/MariaDB)

Let’s continue using our scriptbuzz_db. Remember to run USE scriptbuzz_db; first.

A. String Functions

Example 1: Convert usernames to uppercase and find string length

SELECT
    user_id,
    username,
    UPPER(username) AS upper_username,
    LENGTH(username) AS username_length
FROM users;

SELECT
    user_id,
    username,
    UPPER(username) AS upper_username,
    LENGTH(username) AS username_length
FROM users;

Expected Output (example):

• UPPER(string): Converts a string to uppercase.
• LOWER(string): Converts a string to lowercase.
• LENGTH(string): Returns the length of the string in bytes (characters for ASCII).

Example 2: Extracting parts of a product name Suppose you want to get the first 5 characters of a product name.

SELECT
    product_id,
    name,
    SUBSTRING(name, 1, 5) AS short_name -- Start at pos 1, take 5 chars
FROM products
WHERE name IS NOT NULL;

SELECT
    product_id,
    name,
    SUBSTRING(name, 1, 5) AS short_name -- Start at pos 1, take 5 chars
FROM products
WHERE name IS NOT NULL;

Expected Output (example):

• SUBSTRING(string, start, length): Extracts a substring. start is 1-based.
• TRIM(string), LTRIM(string), RTRIM(string): Removes leading/trailing/both whitespace.
• REPLACE(string, old_substring, new_substring): Replaces all occurrences of a substring.

B. Date and Time Functions

Example 3: Format order dates for display and get current date

SELECT
    order_id,
    order_date,
    DATE_FORMAT(order_date, '%Y-%m-%d %H:%i:%s') AS formatted_order_date,
    DATE_FORMAT(order_date, '%M %d, %Y') AS readable_date,
    CURDATE() AS current_date, -- Current date only
    NOW() AS current_datetime  -- Current date and time
FROM orders
LIMIT 2;

SELECT
    order_id,
    order_date,
    DATE_FORMAT(order_date, '%Y-%m-%d %H:%i:%s') AS formatted_order_date,
    DATE_FORMAT(order_date, '%M %d, %Y') AS readable_date,
    CURDATE() AS current_date, -- Current date only
    NOW() AS current_datetime  -- Current date and time
FROM orders
LIMIT 2;

Expected Output (example, current date/time will vary):

• DATE_FORMAT(date, format_string): Formats a date/datetime value. (e.g., %Y for 4-digit year, %m for 2-digit month, %d for 2-digit day, %H for 24-hour, %i for minutes, %s for seconds, %M for full month name).
• CURDATE(): Returns the current date.
• NOW(): Returns the current date and time.
• DATEDIFF(date1, date2): Returns the number of days between two dates.
• DATE_ADD(date, INTERVAL value unit) / DATE_SUB(date, INTERVAL value unit): Adds/subtracts intervals (e.g., INTERVAL 1 DAY, INTERVAL 2 MONTH).

Example 4: Find orders placed in the last 7 days

SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

SELECT order_id, order_date, total_amount
FROM orders
WHERE order_date >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

What it does: Filters orders to include only those where the order_date is greater than or equal to 7 days before the current date.

C. Conditional Logic (CASE Statement)

The CASE statement allows you to display different values based on different conditions.

Syntax (Simple CASE):

CASE column_name
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

CASE column_name
    WHEN value1 THEN result1
    WHEN value2 THEN result2
    ELSE default_result
END

Syntax (Searched CASE – more flexible):

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ELSE default_result
END

Example 5: Categorize product prices into tiers

SELECT
    product_id,
    name,
    price,
    CASE
        WHEN price < 50 THEN 'Economy'
        WHEN price >= 50 AND price < 200 THEN 'Standard'
        WHEN price >= 200 AND price < 500 THEN 'Premium'
        ELSE 'Luxury'
    END AS price_tier
FROM products;

SELECT
    product_id,
    name,
    price,
    CASE
        WHEN price < 50 THEN 'Economy'
        WHEN price >= 50 AND price < 200 THEN 'Standard'
        WHEN price >= 200 AND price < 500 THEN 'Premium'
        ELSE 'Luxury'
    END AS price_tier
FROM products;

Expected Output (example):

What it does: For each product, it evaluates the price and assigns a text label (Economy, Standard, etc.) based on the defined ranges.

Example 6: Display custom status for orders

SELECT
    order_id,
    status,
    CASE status
        WHEN 'pending' THEN 'Waiting for Confirmation'
        WHEN 'processing' THEN 'In Progress'
        WHEN 'shipped' THEN 'On Its Way'
        WHEN 'completed' THEN 'Delivered'
        ELSE 'Unknown Status'
    END AS display_status
FROM orders;

SELECT
    order_id,
    status,
    CASE status
        WHEN 'pending' THEN 'Waiting for Confirmation'
        WHEN 'processing' THEN 'In Progress'
        WHEN 'shipped' THEN 'On Its Way'
        WHEN 'completed' THEN 'Delivered'
        ELSE 'Unknown Status'
    END AS display_status
FROM orders;

What it does: Maps the internal status values to more user-friendly display strings.

Example 7: Using CASE with SUM for conditional aggregation We briefly saw this in the ALTER VIEW example in the previous chapter. This is very common.

SELECT
    u.username,
    SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) AS total_spent_on_completed,
    SUM(CASE WHEN o.status = 'pending' THEN o.total_amount ELSE 0 END) AS total_spent_on_pending
FROM users AS u
LEFT JOIN orders AS o ON u.user_id = o.user_id
GROUP BY u.username;

SELECT
    u.username,
    SUM(CASE WHEN o.status = 'completed' THEN o.total_amount ELSE 0 END) AS total_spent_on_completed,
    SUM(CASE WHEN o.status = 'pending' THEN o.total_amount ELSE 0 END) AS total_spent_on_pending
FROM users AS u
LEFT JOIN orders AS o ON u.user_id = o.user_id
GROUP BY u.username;

What it does: Calculates the sum of total_amount for ‘completed’ orders and ‘pending’ orders separately for each user in a single query. Users without orders will show 0 for both sums due to COALESCE (or just 0 if LEFT JOIN and SUM on NULL which is 0).

Common Pitfalls and Best Practices

• Function Compatibility: While many functions are standard, exact names and behaviors can vary slightly between different SQL databases (MySQL, PostgreSQL, SQL Server, Oracle). Always check the documentation for your specific database.
• Performance: Applying functions to columns in WHERE clauses can sometimes prevent the use of indexes. For example, WHERE DATE_FORMAT(order_date, '%Y-%m-%d') = '2025-01-01' is less efficient than WHERE order_date >= '2025-01-01' AND order_date < '2025-01-02'. Try to keep filtering conditions “sargable” (search argument-able) to allow index usage.
• Readability: While powerful, don’t over-complicate queries with too many nested functions. Sometimes, it’s better to do some data formatting or logic in your application layer.
• NULL handling: Be mindful of how functions treat NULL values. SUM() ignores NULLs, but COUNT(*) counts rows with NULLs. COALESCE() and IFNULL() are useful for explicitly handling NULLs.

Notes:

• String Functions (UPPER, LOWER, LENGTH, SUBSTRING, TRIM, REPLACE) manipulate text data.
• Date and Time Functions (NOW, CURDATE, DATE_FORMAT, DATEDIFF, DATE_ADD, DATE_SUB) handle date and time values, formatting, and calculations.
• CASE Statements provide conditional logic within your queries, allowing you to create dynamic results or perform conditional aggregations.
• These functions enable more powerful data transformation and reporting directly within SQL.
• Be aware of function compatibility across databases and their potential impact on index usage.

Small Exercise

Using your scriptbuzz_db:

1. List all usernames, but display them with only the first letter capitalized and the rest lowercase (e.g., ‘John_doe’). (Hint: You’ll need UPPER for the first character and LOWER with SUBSTRING for the rest, and CONCAT to join them).
2. For each order, display its order_id, order_date, and a new column delivery_estimate which is 5 days after the order_date.
3. Calculate the total number of products for each category, but display a category name of ‘Miscellaneous’ for any products where category is NULL.
4. Count the number of users who registered in ‘2025’. (Hint: Use YEAR() or DATE_FORMAT()).

You’ve now got a robust set of tools for querying and manipulating data with great flexibility! This concludes the core practical aspects of SQL you’ll use daily. In the next (and final) chapter, we’ll wrap up with a discussion on more advanced topics and next steps.

🚀 Explore Popular Learning Tracks

Learn Python

Learn AWS

Learn Git & GitHub