Part 30: Practical Hands-On Project: Product Catalog Query

Let's synthesize everything learned in Modules 1–3 into a production-grade E-Commerce Catalog query.

Schema Definition

CREATE TABLE products (
  id BIGINT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  category VARCHAR(100) NOT NULL,
  price DECIMAL(10, 2) NOT NULL,
  stock_quantity INT NOT NULL DEFAULT 0,
  is_active BOOLEAN NOT NULL DEFAULT TRUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

The Production Search Query

Find active Electronics or Accessories priced between $20 and $200 with inventory in stock, ordered by price descending, returning Page 1:

SELECT 
  id,
  name,
  category,
  price,
  stock_quantity,
  CASE 
    WHEN stock_quantity < 5 THEN 'Low Stock'
    ELSE 'In Stock'
  END AS inventory_status
FROM products
WHERE is_active = TRUE
  AND category IN ('Electronics', 'Accessories')
  AND price BETWEEN 20.00 AND 200.00
  AND stock_quantity > 0
ORDER BY price DESC, id ASC
LIMIT 12 OFFSET 0;

Key Takeaways

• Combines filtering (WHERE, IN, BETWEEN), projection, computed case columns, and deterministic pagination.

In Part 31, we enter Module 4 to master Data Definition Language (DDL)!