Chapter 8 — Exercises

Run against mercado. These build your working knowledge of the function toolkit. (answer in Appendix) = worked solution in Answers. ⭐ = stretch.

Group A — Strings

8.1 Show each customer's name as Last, First (e.g., Nguyen, Alice). (answer in Appendix)

8.2 Show each customer's email and the domain part only (everything after @). Use split_part.

8.3 Show product names in upper case, and their length.

8.4 Produce a zero-padded order code like ORD-00001 from order_id (use lpad). (answer in Appendix)

8.5 ⭐ Some customers might lack a middle name column; demonstrate why a || NULL || b is NULL and rewrite it with concat so missing parts are skipped.

Group B — Numbers

8.6 Show each product's price rounded to the nearest 10 dollars. (answer in Appendix)

8.7 Predict and then verify: SELECT 7 / 2, 7 / 2.0, 7 % 2;. Explain the first result.

8.8 Compute, per product, price divided by 3 as a true decimal (not integer division).

8.9 ⭐ Compute a "discount percent off" for order items where discount > 0, formatted as a whole-number percent (e.g., 15). Guard against any divide-by-zero with NULLIF.

Group C — Dates and times

8.10 Show each order's order_id and just the date part (no time). (answer in Appendix)

8.11 Count orders per month using date_trunc. (answer in Appendix)

8.12 Find all orders placed in March 2024 using a half-open range (>= ... AND < ...). Explain why this is safer than BETWEEN '2024-03-01' AND '2024-03-31'.

8.13 For each employee, show their name and how long they've worked here (use age(now(), hired_at)).

8.14 ⭐ List orders placed within the last 1000 days of the latest order in the table. (Hint: subtract an interval from (SELECT max(order_date) FROM orders).)

Group D — Type conversion

8.15 Convert the string '250' to an integer and add 50. (answer in Appendix)

8.16 Format a price as text with a thousands separator and two decimals using to_char.

8.17 ⭐ Convert '2024-12-25' (text) to a date, then extract its day-of-week name with to_char(..., 'Day').

Group E — Conditional expressions

8.18 Add a price_band column to products: 'premium' (≥1000), 'mid-range' (≥200), else 'budget'. (answer in Appendix)

8.19 Show each customer's phone, or 'no phone' if null (use COALESCE).

8.20 In one query over orders, count delivered and cancelled orders using COUNT(*) FILTER (WHERE ...). (answer in Appendix)

8.21 ⭐ Compute average rating per product but return NULL (not an error) for products with zero reviews — show how NULLIF/COALESCE and a LEFT JOIN interact.

Group F — Combine & fix

8.22 Find and fix the bug — "success rate" is always 0: (answer in Appendix)

SELECT order_id, delivered_items / total_items AS success_rate FROM order_stats;

(Assume both columns are integers.)

8.23 ⭐ Write a single query producing a clean order summary: customer name title-cased, order date formatted Mon DD, YYYY, a status label via CASE, and a money-formatted total with COALESCE for orders with no items.

Group G — Progressive project

8.24 Format a display name and a date-by-month grouping for your domain.

8.25 Use CASE to bucket a numeric value, and COALESCE/NULLIF for a default and a safe division.

Self-check. If you can format names/dates, avoid integer-division and NULL-concatenation traps, and write a CASE/COALESCE expression from memory, you have the everyday toolkit. Next: subqueries.