Every meaningful SQL query uses comparison and logical operators. Filtering customers by status, date range, or category; combining multiple conditions for fraud detection; pattern-matching email addresses — these operators are the fundamental building blocks of all WHERE clauses.

E-Commerce Product Search with Multiple Filters

Build a product search query combining price range, category, stock status, and name pattern matching.

SELECT

p.product_id,

p.name,

p.price,

p.stock_qty,

c.category_name

FROM products p

JOIN categories c ON c.category_id = p.category_id

WHERE p.price BETWEEN 25.00 AND 250.00

AND p.stock_qty > 0

AND p.discontinued = FALSE

AND p.deleted_at IS NULL

AND c.category_name IN ('Electronics', 'Accessories', 'Gadgets')

AND (p.name LIKE 'Samsung%' OR p.name LIKE 'Apple%')

ORDER BY p.price ASC;

Combines BETWEEN, IN, IS NULL, LIKE with OR — all standard patterns in product search. Parentheses around the LIKE OR clause are critical to avoid precedence bugs.

All

User Segmentation for Marketing Campaign

Identify users eligible for a re-engagement campaign — active, verified, opted-in, not recently contacted.

SELECT

u.user_id,

u.email,

u.last_login_at,

u.signup_date

FROM users u

WHERE u.deleted_at IS NULL -- not deleted

AND u.verified_at IS NOT NULL -- email verified

AND u.marketing_opt_in = TRUE -- opted in

AND u.last_login_at < NOW() - INTERVAL '90 days' -- inactive 90+ days

AND u.last_login_at > NOW() - INTERVAL '365 days'-- but not completely gone

AND u.user_id NOT IN ( -- not contacted recently

SELECT DISTINCT user_id

FROM campaign_sends

WHERE sent_at > NOW() - INTERVAL '30 days'

AND user_id IS NOT NULL -- safe NOT IN guard

);

Real marketing segmentation: multiple IS NULL/IS NOT NULL checks, date range with AND, and a NOT IN subquery guarded with IS NOT NULL to prevent the NULL trap.