Practical Exercise: Querying a Sales Database with 15+ Function Combinations

Every function in this module is genuinely useful on its own, but real reporting queries almost always combine several of them together — formatting a date alongside a conditional label, rounding an aggregated average, handling a NULL inside a calculation. This final lesson works through 15+ realistic queries against a sales database, deliberately combining functions from every category covered in this module.

The Practice Schema: A Simple Sales Database

This exercise uses two related tables: salespeople (salesperson_id, full_name, region, hire_date) and sales (sale_id, salesperson_id, product_name, sale_amount, sale_date, discount_code). The discount_code column is intentionally nullable, and sale_amount is stored as DECIMAL, giving every challenge below a realistic, slightly messy foundation to query against.

What You'll Learn

• Combine string and date functions for clean, formatted reporting output.
• Apply numeric and aggregate functions together for rounded summary statistics.
• Use CASE WHEN alongside aggregate functions for conditional, bucketed reporting.
• Handle NULLs gracefully within calculated and displayed columns.

Key Terms to Know

• Function combination: Using two or more SQL functions together within a single expression or query.
• Realistic reporting query: A query styled after genuine business reporting needs, rather than an isolated syntax example.

Challenges 1–6: String, Numeric, and Date Function Combinations

1. Display each salesperson's full name in uppercase alongside their formatted hire date ('24 Jun 2026' style) using UPPER and DATE_FORMAT together. 2. Calculate each salesperson's tenure in completed years using TIMESTAMPDIFF. 3. Round each individual sale_amount to the nearest whole currency unit using ROUND. 4. Build a clean display invoice code combining a zero-padded sale_id with LPAD and CONCAT, like 'SALE-000042'. 5. Calculate how many days ago each sale occurred relative to today using DATEDIFF and CURDATE(). 6. Extract just the month name of each sale using DATE_FORMAT or MONTHNAME, to support month-based grouping later.

Challenges 7–11: Aggregate Functions and GROUP BY Combinations

7. Calculate total and average sale_amount per region using SUM and AVG combined with GROUP BY. 8. Find the single highest sale_amount achieved by each salesperson using MAX and GROUP BY. 9. Count how many sales each salesperson made, using COUNT(*) and GROUP BY, then filter to only salespeople with more than 20 sales using HAVING. 10. Calculate each region's total sales, rounded to 2 decimal places, combining SUM and ROUND together. 11. Count how many sales used a discount code at all (a nullable column) versus the total number of sales, using COUNT(discount_code) versus COUNT(*).

Challenges 12–17: Conditional Logic, NULL Handling, and Type Conversion

12. Label each sale as 'Discounted' or 'Full Price' using IF(), based on whether discount_code IS NOT NULL. 13. Bucket each sale_amount into 'Small', 'Medium', or 'Large' tiers using CASE WHEN. 14. Display 'No discount applied' instead of NULL wherever discount_code is missing, using IFNULL. 15. Calculate each salesperson's average sale_amount, but display 'New (no sales yet)' using COALESCE-style fallback logic for any salesperson with zero recorded sales. 16. Use conditional aggregation (SUM with CASE WHEN) to compute, in a single query, the total sales amount specifically from discounted sales versus full-price sales side by side. 17. Convert a legacy sale_amount_text column (stored as VARCHAR from an old import) into a proper DECIMAL using CAST, then correctly SUM the converted values.

Visual Summary

Picture a finished, polished sales report ready to hand to a manager — formatted dates, rounded currency figures, clean labels like 'Discounted' instead of raw NULLs, and neatly bucketed categories. Every single one of those polished details is the visible result of a specific function (or combination of functions) from this module quietly doing its job underneath, transforming raw, slightly messy stored data into something genuinely presentable without a single extra line of application code.

Challenge Set Overview

Range	Focus Area	Functions Combined
1–6	String, numeric, date formatting	CONCAT, UPPER, LPAD, ROUND, DATE_FORMAT, DATEDIFF, TIMESTAMPDIFF
7–11	Aggregation and grouping	SUM, AVG, MAX, COUNT, GROUP BY, HAVING, ROUND
12–17	Conditional logic, NULL handling, conversion	IF, CASE WHEN, IFNULL, COALESCE, CAST, conditional aggregation

SQL Example

-- Sample solutions for a few representative challenges

-- Challenge 1: formatted name + hire date
SELECT UPPER(full_name) AS salesperson, DATE_FORMAT(hire_date, '%d %b %Y') AS hired_on
FROM salespeople;

-- Challenge 7: total and average sales per region, rounded
SELECT region, ROUND(SUM(sale_amount), 2) AS total_sales, ROUND(AVG(sale_amount), 2) AS avg_sale
FROM sales
GROUP BY region;

-- Challenge 13: bucketing sale amounts into tiers
SELECT sale_id, sale_amount,
  CASE
    WHEN sale_amount < 100 THEN 'Small'
    WHEN sale_amount < 1000 THEN 'Medium'
    ELSE 'Large'
  END AS sale_tier
FROM sales;

-- Challenge 16: conditional aggregation for discounted vs full-price totals
SELECT
  SUM(CASE WHEN discount_code IS NOT NULL THEN sale_amount ELSE 0 END) AS discounted_total,
  SUM(CASE WHEN discount_code IS NULL THEN sale_amount ELSE 0 END) AS full_price_total
FROM sales;

-- Challenge 17: converting a legacy text column before summing
SELECT SUM(CAST(sale_amount_text AS DECIMAL(10,2))) AS total_legacy_sales
FROM legacy_sales_import;

Each sample solution combines at least two distinct function categories from this module: string and date formatting together, aggregate functions with rounding, CASE WHEN for tiered bucketing, conditional aggregation for side-by-side categorized totals, and explicit type conversion before aggregation on legacy data — exactly the kind of layered, combined usage that real sales reporting requires.

Real-World Examples

• Sales operations teams build monthly performance dashboards combining exactly this mix of formatting, aggregation, and conditional bucketing functions.
• Finance teams use conditional aggregation patterns like challenge 16 to separate discounted versus full-price revenue without running multiple separate queries.
• Data migration projects regularly need challenge 17's CAST-before-SUM pattern when consolidating sales data imported from older, inconsistently-typed systems.
• Sales leadership reviews regularly rely on HAVING-filtered, GROUP BY summaries (challenge 9) to focus attention on top or under-performing salespeople.

Best Practices and Pro Tips

• Notice how almost every realistic challenge in this exercise combines two or more functions together — this mirrors real reporting work far more closely than any single-function example could.
• When a challenge feels hard, break it into smaller pieces: get the raw aggregate or extraction working first without formatting, then layer the formatting or conditional logic on top once the core calculation is correct.
• Revisit specific lessons in this module whenever a challenge calls for a function you're unsure about — this exercise is intentionally comprehensive and is meant to send you back to earlier material as needed.

Common Mistakes to Avoid

• Attempting to format or bucket a value before the underlying calculation is correct, making it hard to tell whether a wrong result comes from the calculation itself or the formatting layered on top.
• Forgetting NULL-aware functions (challenge 14, 15) and accidentally displaying raw NULLs in what's meant to be a polished report.
• Mixing up which aggregate function suits each specific business question (e.g., using SUM where AVG was actually needed, or vice versa).
• Skipping the CAST step in challenge 17 and attempting to SUM a text column directly, leading to either an error or a nonsensical numeric result depending on MySQL's implicit conversion behavior.

Interview Questions

Q1. In challenge 16, why is conditional aggregation preferred over running two separate queries?

Conditional aggregation computes both the discounted and full-price totals in a single pass over the sales table within one query, which is both more efficient and easier to keep consistent than running two separate queries and combining their results afterward in application code.

Q2. Why does challenge 17 require CAST before SUM can be used correctly?

Because sale_amount_text is stored as VARCHAR (text), SUM would either fail or behave unpredictably without explicit conversion; CAST(sale_amount_text AS DECIMAL(10,2)) explicitly converts each value to a proper numeric type first, ensuring SUM calculates a correct, meaningful total.

Q3. What's the difference between challenge 13's tiered bucketing and challenge 12's simple two-outcome labeling?

Challenge 12 only needs two possible outcomes ('Discounted' or 'Full Price'), making IF() a clean, sufficient choice. Challenge 13 needs three distinct outcomes ('Small', 'Medium', 'Large'), which is exactly the kind of multi-branch scenario CASE WHEN is specifically designed to handle cleanly.

Q4. Why is COUNT(discount_code) different from COUNT(*) in challenge 11, and what does that difference tell you?

COUNT(*) counts every sale row regardless of discount_code, while COUNT(discount_code) only counts rows where discount_code is not NULL. The difference between the two values directly tells you how many sales did not use any discount code at all.

Practice MCQs

1. Which function combination correctly solves challenge 1 (uppercase name plus formatted hire date)?

1. LOWER and DATEDIFF
2. UPPER and DATE_FORMAT
3. TRIM and DATE_ADD
4. CONCAT and ROUND

Answer: B. UPPER and DATE_FORMAT

Explanation: UPPER converts the name to uppercase, and DATE_FORMAT converts the raw hire_date into the requested friendly display format.

2. Why must CAST be used before SUM in challenge 17?

1. SUM never works on any text column under any circumstance
2. The column is stored as text (VARCHAR), so it must be explicitly converted to a numeric type first for a correct sum
3. CAST makes the query run faster
4. SUM requires exactly one argument

Answer: B. The column is stored as text (VARCHAR), so it must be explicitly converted to a numeric type first for a correct sum

Explanation: Since sale_amount_text is VARCHAR, explicit conversion with CAST ensures SUM operates on actual numeric values rather than relying on unpredictable implicit conversion.

3. Which function is best suited for challenge 13's three-tier sale amount bucketing?

1. IF()
2. CASE WHEN
3. IFNULL
4. COALESCE

Answer: B. CASE WHEN

Explanation: CASE WHEN supports any number of ordered conditions, making it the right tool for bucketing into three or more tiers, unlike IF() which only supports two outcomes.

Quick Revision Points

• This exercise deliberately combines functions across every category covered in Module 4.
• Conditional aggregation (SUM/COUNT + CASE WHEN) is a recurring, high-value pattern across multiple challenges.
• Explicit type conversion (CAST) is essential before aggregating any legacy or poorly-typed text column.
• NULL-aware display functions (IFNULL/COALESCE) are essential for producing genuinely polished, presentable reports.

Conclusion

• Real-world SQL reporting almost always combines multiple function categories together, not just one at a time.
• This single sales database and its 15+ challenges touch every function category covered across this entire module.
• Breaking a complex combined query into smaller verified pieces is a reliable strategy for tackling harder challenges.

These 15+ challenges, built against a realistic two-table sales database, deliberately combine string, numeric, date, aggregate, NULL-handling, conditional, and type-conversion functions exactly the way genuine business reporting requires — formatted names and dates, rounded aggregated totals, tiered bucketing, conditional aggregation, and safe handling of both NULLs and legacy text data. Genuine fluency with SQL functions comes from exactly this kind of combined, realistic practice rather than isolated single-function examples. With this module's complete function toolkit now exercised together, the course is ready to move into JOINs, where data from multiple related tables gets combined directly within a single query.