Mini Project: Search & Filter System
Apply everything from this module by building a complete product search and order filter system on ecommerce_db. This mini project combines comparison operators, logical operators, BETWEEN, IN, LIKE, ORDER BY, NULL handling, and CASE statements into realistic queries.
Project Goals
- Build a product catalog search with price range filter
- Filter orders by status with multiple conditions
- Create an order summary report using CASE
- Apply NULL-safe payment status filtering
- Rank products by price tier
Mini Project Queries
-- 1. Product search + price range filter
SELECT
name,
price,
stock,
CASE
WHEN price < 100 THEN 'Budget'
WHEN price < 600 THEN 'Mid-Range'
ELSE 'Premium'
END AS tier
FROM products
WHERE name LIKE '%' -- replace with search term
AND price BETWEEN 0 AND 1000
AND stock > 0
ORDER BY price ASC;
-- 2. Order dashboard filter (multiple statuses)
SELECT
o.id AS order_id,
u.name AS customer,
o.total_amount,
o.status,
o.created_at
FROM orders o
JOIN users u ON o.user_id = u.id
WHERE o.status IN ('pending', 'shipped')
AND o.total_amount > 0
ORDER BY o.created_at DESC;
-- 3. Payment status report (NULL-safe)
SELECT
p.order_id,
p.amount,
COALESCE(p.payment_method, 'Unknown') AS method,
CASE
WHEN p.paid_at IS NOT NULL THEN 'Paid'
ELSE 'Unpaid'
END AS payment_status,
p.paid_at
FROM payments p
ORDER BY p.paid_at DESC NULLS LAST;
-- 4. Low stock alert (combine conditions)
SELECT name, price, stock
FROM products
WHERE stock BETWEEN 0 AND 60
AND price > 50
ORDER BY stock ASC;Quick Quiz
Tip
Tip
Practice Mini Project Search Filter System in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
Relationships define how tables connect through foreign keys
Common Mistake
Warning
A common mistake with Mini Project Search Filter System is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Mini Project Search Filter System from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- Apply everything from this module by building a complete product search and order filter system on ecommerce_db.
- Build a product catalog search with price range filter
- Filter orders by status with multiple conditions
- Create an order summary report using CASE