Chapter 5 — Exercises

This is where SQL learning happens. Run every one against mercado (sample data). Write the query, run it, check the result makes sense. (answer in Appendix) = worked solution in Answers. ⭐ = stretch. Don't peek until you've tried.

Group A — SELECT and projection

5.1 Select all columns from the suppliers table. (answer in Appendix)

5.2 Select just the name and price of every product.

5.3 Select each customer's full name as a single column called full_name (combine first_name and last_name).

5.4 For each product, show name, price, and a column price_with_tax equal to the price times 1.08, rounded to 2 decimals. (answer in Appendix)

5.5 ⭐ Select each product's name and a column price_band that is just price — but first explain why you can't write WHERE price_band > 100 in the same query. (Hint: order of evaluation.)

Group B — Filtering with WHERE

5.6 All products costing more than $500. (answer in Appendix)

5.7 All orders with status 'delivered'.

5.8 All products that are not active (is_active = false).

5.9 All customers in the 'gold' or 'platinum' loyalty tier (use IN). (answer in Appendix)

5.10 All products priced between $50 and $150 inclusive (use BETWEEN).

5.11 All customers with no phone number on file.

5.12 All products whose name contains the word Book (case-insensitive).

5.13 All products whose sku starts with LAP-.

5.14 ⭐ All active products in category 3 or 5 that cost less than $300. (Mind your parentheses!) (answer in Appendix)

Group C — Sorting and limiting

5.15 All products, most expensive first. (answer in Appendix)

5.16 The 3 cheapest active products (name and price).

5.17 Customers sorted by loyalty tier, and within each tier by last name.

5.18 The 5 most recently created customers (use created_at).

5.19 ⭐ List customers ordered by phone number, with the customers who have no phone shown first. (Use NULLS FIRST.)

Group D — DISTINCT

5.20 List the distinct loyalty tiers that actually appear in the customers table. (answer in Appendix)

5.21 List the distinct (category_id, supplier_id) combinations among products.

5.22 ⭐ How many distinct statuses appear in orders? (Just list them; counting comes in Chapter 7.)

Group E — Fix the bug

Each query has a mistake. Find it, explain it, and fix it.

5.23 Trying to find customers with no phone — returns nothing:

SELECT * FROM customers WHERE phone = NULL;

(answer in Appendix)

5.24 Trying to find laptops and desktops under $600 — returns some expensive laptops too:

SELECT name, price FROM products
WHERE category_id = 3 OR category_id = 4 AND price < 600;

5.25 Trying to get "the 10 newest orders" — but the rows seem random each run:

SELECT * FROM orders LIMIT 10;

5.26 ⭐ Trying to sort by discounted price — errors with "column does not exist":

SELECT name, price * 0.9 AS sale FROM products WHERE sale < 100;

(Two things to discuss: why it errors, and how to fix it.)

Group F — Predict, then verify

For each, predict the result before running it.

5.27 SELECT 'a' < 'b'; and SELECT 'Z' < 'a'; (What governs text comparison?) (answer in Appendix)

5.28 SELECT name FROM products WHERE price NOT IN (39.00, 79.00, NULL); — how many rows, and why might it surprise you?

5.29 ⭐ SELECT DISTINCT loyalty_tier, phone IS NOT NULL AS has_phone FROM customers ORDER BY 1, 2; — describe the shape of the output.

Group G — Write the question (reverse engineering)

For each query, write the plain-English question it answers.

5.30

SELECT name, price FROM products WHERE is_active = true AND price < 50 ORDER BY price;

5.31 ⭐

SELECT first_name, last_name FROM customers
WHERE loyalty_tier <> 'standard' AND email LIKE '%@example.com'
ORDER BY last_name;

Group H — Progressive project

5.32 Against your project (or Mercado), write five SELECT/WHERE/ORDER BY/LIMIT queries that answer five of your Chapter 1 questions. For each, note the π (columns) and σ (filter).

5.33 ⭐ Pick one query and write it two ways (e.g., IN (...) vs. chained ORs, or BETWEEN vs. two comparisons). Confirm they return the same rows.

Self-check. If you can write any of A–D from a plain-English request without looking up syntax, and you can spot the Group E bugs, basic querying is becoming automatic. Next: joins — where the real power begins.