Practical Exercise: 25 SQL Query Challenges on an E-commerce Product Table

Reading about SELECT, WHERE, and ORDER BY is one thing — writing real queries against real data under realistic constraints is what actually builds fluency. This final lesson presents 25 progressively challenging query exercises against a single realistic e-commerce products table, covering every concept from this entire module: filtering, sorting, pagination, updating, and deleting.

The Practice Table: products

All 25 challenges in this lesson use a single consistent products table, defined below, representing a realistic e-commerce catalog with varied prices, categories, stock levels, and ratings — deliberately including some NULL values, since handling them correctly is part of the exercise.

What You'll Learn

• Apply SELECT, WHERE, and comparison/logical operators to realistic filtering scenarios.
• Combine BETWEEN, IN, LIKE, and IS NULL correctly within varied conditions.
• Practice ORDER BY, LIMIT, and OFFSET for sorting and pagination tasks.
• Write safe, correctly scoped UPDATE, DELETE, and REPLACE INTO statements.

Key Terms to Know

• Practice table: A consistent sample schema used across all exercises for realistic, comparable practice.
• Query challenge: A specific, real-world-style question answered by writing one correct SQL statement.

Setting Up the Practice Table

CREATE TABLE products (product_id INT PRIMARY KEY AUTO_INCREMENT, product_name VARCHAR(150) NOT NULL, category VARCHAR(50) NOT NULL, brand VARCHAR(50), price DECIMAL(10,2) NOT NULL, stock_qty INT NOT NULL DEFAULT 0, rating DECIMAL(2,1), is_active BOOLEAN NOT NULL DEFAULT TRUE); — this single table, populated with a few dozen sample rows spanning electronics, clothing, books, and home goods, with some brand and rating values intentionally left NULL, is the foundation for all 25 challenges below.

Challenges 1–13: SELECT, WHERE, and Filtering Operators

1. Select all columns for every product. 2. Select only product_name and price for every product. 3. Find all products priced above 1000. 4. Find all products in the 'electronics' category. 5. Find all products that are NOT in the 'books' category. 6. Find all products priced between 500 and 2000 (inclusive) using BETWEEN. 7. Find all products in either 'electronics' or 'home' categories using IN. 8. Find all products whose brand is NULL. 9. Find all products whose name contains the word 'Pro' anywhere in it, using LIKE. 10. Find all products that start with the letter 'S'. 11. Find all active products with stock_qty less than 10. 12. Find all products priced above 500 AND in the 'electronics' category. 13. Find all products that are either priced below 100 OR rated above 4.5, using OR.

Challenges 14–19: DISTINCT, ORDER BY, and Pagination

14. Find every distinct category present in the table. 15. Find every distinct (category, brand) combination. 16. List all products ordered by price from lowest to highest. 17. List all products ordered by category alphabetically, then by price from highest to lowest within each category. 18. Find the 5 cheapest active products. 19. Implement page 2 of a product listing with 10 products per page, ordered by product_id, using LIMIT and OFFSET.

Challenges 20–25: INSERT, UPDATE, DELETE, and REPLACE INTO

20. Insert a new product with all required fields, omitting brand and rating. 21. Insert three new products in a single multi-row INSERT statement. 22. Increase the price of every product in the 'clothing' category by 8% using a relative UPDATE. 23. Deactivate (set is_active = FALSE) every product with stock_qty equal to 0, instead of deleting them. 24. Delete every product where is_active is FALSE AND stock_qty has been 0 for longer than a defined cutoff (using a deleted_at-style date column added for this exercise). 25. Use REPLACE INTO (or the safer INSERT ... ON DUPLICATE KEY UPDATE) to update product_id 1's price and stock_qty in a single statement, correctly reasoning about which approach is safer for a table like this one with no foreign key dependents in this exercise, but where the safer habit still applies.

Visual Summary

Picture this lesson as a single obstacle course built entirely around one consistent practice table, with 25 numbered stations along the way — early stations testing basic filtering and comparison skills, middle stations testing sorting and pagination, and the final stretch testing the more consequential, real-world skills of safely modifying and removing data.

Challenge Set Overview

Range	Focus Area	Concepts Exercised
1–13	SELECT & WHERE filtering	Comparison operators, AND/OR/NOT, BETWEEN, IN, LIKE, IS NULL
14–19	DISTINCT, sorting, pagination	DISTINCT, ORDER BY, LIMIT, OFFSET
20–25	Data modification	INSERT, UPDATE, DELETE, REPLACE INTO / ON DUPLICATE KEY UPDATE

SQL Example

-- Sample solutions for a few representative challenges

-- Challenge 6: BETWEEN
SELECT product_name, price FROM products WHERE price BETWEEN 500 AND 2000;

-- Challenge 9: LIKE
SELECT product_name FROM products WHERE product_name LIKE '%Pro%';

-- Challenge 13: OR
SELECT product_name, price, rating FROM products WHERE price < 100 OR rating > 4.5;

-- Challenge 17: Multi-column ORDER BY
SELECT product_name, category, price FROM products ORDER BY category ASC, price DESC;

-- Challenge 19: Pagination
SELECT product_id, product_name FROM products ORDER BY product_id LIMIT 10 OFFSET 10;

-- Challenge 22: Relative UPDATE
UPDATE products SET price = price * 1.08 WHERE category = 'clothing';

-- Challenge 23: Soft delete instead of DELETE
UPDATE products SET is_active = FALSE WHERE stock_qty = 0;

These representative solutions show the expected shape of answers across the challenge set: each one applies a single concept from this module directly and deliberately against the same consistent products table, mirroring exactly how these techniques get combined in real application queries.

Real-World Examples

• Technical SQL interviews frequently use this exact style of incremental challenge set — start with simple SELECT/WHERE, build up to sorting, pagination, and finally safe data modification.
• Onboarding documentation at data-heavy companies often includes a practice schema very similar to this one for new analysts and engineers to build fluency against.
• QA and testing teams use structured challenge sets like this to validate that a junior developer has genuinely internalized safe UPDATE/DELETE habits, not just SELECT syntax.
• Bootcamps and self-paced courses commonly end a foundational SQL module with exactly this kind of consolidated, single-table practice set before moving on to JOINs and multi-table queries.

Best Practices and Pro Tips

• Work through the challenges in order — later ones intentionally build on confidence gained from earlier, simpler ones, mirroring how real query complexity typically grows in practice.
• For challenges 20 onward, apply the same safety habits covered in their respective lessons: preview UPDATE/DELETE conditions with SELECT first, and think carefully before reaching for REPLACE INTO.
• Try writing each challenge's query without looking at the sample solutions first, then compare — the act of attempting it yourself is where the actual learning happens.

Common Mistakes to Avoid

• Skipping straight to the answer key without attempting each challenge independently first, which significantly reduces how much the exercise actually builds lasting fluency.
• Forgetting NULL-specific handling (challenge 8) and instinctively reaching for = NULL instead of IS NULL.
• Treating challenge 23 and 24 identically, missing the deliberate distinction this module draws between soft-delete (UPDATE) and genuine physical removal (DELETE).
• Rushing through the multi-column ORDER BY challenge (17) without checking that both the category and price sort directions are correctly independent of each other.

Interview Questions

Q1. Why is practicing against a single consistent table valuable compared to using a different table for every exercise?

It removes the cognitive overhead of learning a new schema for every question, letting the practice focus entirely on applying the right SQL concept correctly, which mirrors how real-world query writing usually happens against a small set of familiar tables.

Q2. In challenge 23, why is UPDATE used to set is_active = FALSE instead of DELETE for out-of-stock products?

Because out-of-stock products may come back into stock or still have business value (e.g., for historical reporting or re-ordering), a soft delete via UPDATE preserves that data rather than physically and irreversibly removing it, exactly the soft-delete pattern covered in the DELETE lesson.

Q3. What concept from this module does challenge 8 (finding NULL brand values) specifically test?

It tests correct NULL handling — recognizing that WHERE brand = NULL never works as intended, and that IS NULL is the only correct operator for this kind of check.

Q4. Why does challenge 25 ask you to reason about REPLACE INTO versus ON DUPLICATE KEY UPDATE even on a table without foreign key dependents in this exercise?

Because the safer habit of defaulting to ON DUPLICATE KEY UPDATE should be a consistent practice regardless of whether a specific table currently has foreign key dependents, since schemas evolve over time and a table without dependents today might gain them later.

Practice MCQs

1. Which operator is the correct choice for challenge 8 (finding products with a NULL brand)?

1. = NULL
2. != NULL
3. IS NULL
4. LIKE NULL

Answer: C. IS NULL

Explanation: IS NULL is the only operator that correctly tests for NULL values, as covered earlier in this module — = NULL never matches, even on genuinely NULL values.

2. In challenge 17, what determines the secondary sort order within each category?

1. The product_id
2. The price, sorted descending
3. The brand name
4. The stock quantity

Answer: B. The price, sorted descending

Explanation: ORDER BY category ASC, price DESC sorts primarily by category, using price (highest first) only to break ties within each identical category group.

3. Why does challenge 23 use UPDATE rather than DELETE for handling out-of-stock products?

1. UPDATE is always faster than DELETE
2. It implements a soft delete, preserving the data instead of removing it
3. DELETE doesn't support a WHERE clause
4. UPDATE is required by MySQL for boolean columns

Answer: B. It implements a soft delete, preserving the data instead of removing it

Explanation: Marking products inactive via UPDATE rather than physically deleting them preserves the underlying data for potential future use, exactly the soft-delete pattern from the DELETE lesson.

Quick Revision Points

• This exercise consolidates every concept from the module: SELECT, WHERE, AND/OR/NOT, BETWEEN, IN, LIKE, IS NULL, ORDER BY, LIMIT/OFFSET, INSERT, UPDATE, DELETE, and REPLACE INTO.
• NULL-specific handling (IS NULL, not = NULL) is deliberately tested in this challenge set.
• The distinction between soft delete (UPDATE) and physical delete (DELETE) is deliberately tested as a real-world judgment call.
• Multi-column ORDER BY with independent ASC/DESC directions per column is deliberately exercised.

Conclusion

• Fluency with SQL comes from writing queries under realistic constraints, not just reading about syntax.
• This single practice table and its 25 challenges touch every concept covered across this entire module.
• The judgment calls embedded in the later challenges (soft delete vs delete, REPLACE INTO vs ON DUPLICATE KEY UPDATE) are just as important as getting the syntax correct.

These 25 challenges, all built against one consistent e-commerce products table, deliberately exercise every concept covered across this module — from basic SELECT and WHERE filtering through BETWEEN, IN, LIKE, and NULL handling, into ORDER BY and pagination, and finally into the more consequential territory of safe INSERT, UPDATE, DELETE, and REPLACE INTO usage. Genuine fluency with basic SQL queries comes from exactly this kind of repeated, hands-on practice rather than passive reading. With single-table querying now solidly mastered, the next module introduces JOINs, where the real power of relational databases — combining data across multiple related tables — comes fully into play.