TheShiraverseSELECT * TheShiraverseCurriculumPracticeKahaniFAQGet StartedPlaygroundNukteTheShiraverse ↗
Practice › Topic 05

Filtering and Sorting: practice questions

400 questions in four levels, all on RetailMart, the practice database of this course. Write every query yourself, get it wrong, read the error, fix it. That is how it sticks.

Open the SQL PlaygroundPut RetailMart on your laptop
Answers are coming soon. Post your query in the WhatsApp Community or Discord and we will check it together.

Core syntax, applied directly

CONCEPTUAL QUESTIONS

  1. Q1Your junior asks "what does the WHERE clause do?" - explain in one line.
  2. Q2In an interview, you're asked: AND vs OR - which has higher precedence and why does it matter?
  3. Q3A teammate is debating whether BETWEEN 1 AND 10 includes the boundary numbers. What do you tell them?
  4. Q4When would you choose IN over chaining many OR conditions?
  5. Q5A student writes WHERE name LIKE 'samsung%' and gets zero rows. Why - and what should they have used?
  6. Q6A query WHERE phone = NULL returns nothing even though some phones look empty. Explain.
  7. Q7Why do experienced engineers always add ORDER BY even when data "looks sorted"?
  8. Q8In ORDER BY, what is the default sort direction if you don't specify ASC or DESC?
  9. Q9Explain what LIMIT 10 OFFSET 20 returns - and which page (10-per-page) the user is viewing.
  10. Q10Write out the standard clause order: SELECT, FROM, WHERE, ORDER BY, LIMIT - and then the EXECUTION order.

SELECT + BASIC WHERE

  1. Q11The Customer Service team is exporting the full customer master list. Pull every column from customers.customers.
  2. Q12Marketing wants a contact list - show first_name and email from customers.customers.
  3. Q13The catalog team needs product_id, product_name, and price from products.products for a price-list PDF.
  4. Q14The Finance Director wants order_id, cust_id, and net_total from sales.orders for a revenue audit.
  5. Q15HR is building an employee directory. Show employee_id, first_name, and role from stores.employees.
  6. Q16The premium catalog team wants every product priced ABOVE 500.
  7. Q17The 'value zone' team wants products priced BELOW 100.
  8. Q18A pricing analyst is testing - find any product whose price equals exactly 999.99.
  9. Q19Marketing is launching a satisfaction survey for completed purchases. Pull all delivered orders from sales.orders.
  10. Q20The Ops team wants to ignore cancellations - show all orders that are NOT 'Cancelled'.
  11. Q21Loyalty wants the 'Gold tier' contact list. Filter customers.customers where tier = 'Gold'.
  12. Q22Re-engagement campaign: customers whose tier is NOT 'Bronze' (everyone above entry level).
  13. Q23The CHRO wants senior employees - salary > 50000.
  14. Q24HR is reviewing entry-level pay - show employees whose salary is below 30000.
  15. Q25Store Operations wants the Mumbai locations - stores.stores where city = 'Mumbai'.
  16. Q26The expansion team wants every store outside Delhi - city != 'Delhi'.
  17. Q27Support escalation list: pull all tickets with priority = 'High'.
  18. Q28The support manager wants the open queue - tickets where status = 'Open'.
  19. Q29Loyalty wants 'big point holders' - members with points_balance above 1000.
  20. Q30CX wants all 5-star reviews for the marketing landing page - reviews with rating = 5.
  21. Q31The Quality team wants reviews flagged as unhappy - rating < 3.
  22. Q32HR is doing an attendance audit. Pull hr.attendance records for the date '2025-01-15'.
  23. Q33The CMO wants every high-budget campaign - budget above 100000.
  24. Q34The CFO wants large expenses for the audit - finance.expenses where amount > 50000.
  25. Q35DevOps is chasing production errors - application_logs where level = 'ERROR'.
  26. Q36The API team wants 500-error rows - audit.api_requests where status_code = 500.
  27. Q37The Web Analytics team wants only the mobile page views - device_type = 'Mobile'.
  28. Q38Production wants the 'Completed' work-orders list - manufacture.work_orders where status = 'Completed'.
  29. Q39The call-center supervisor wants 'long calls' - call_duration_seconds above 300 (5+ minutes).
  30. Q40Payroll is reviewing high-paid net salaries - pay_slips where net_salary > 10000.

BETWEEN / IN / LIKE / IS NULL

  1. Q41The pricing team wants the mid-tier range - products priced BETWEEN 200 AND 1000.
  2. Q42The 'edge case' pricing review - products NOT priced between 100 and 500 (either cheaper or more expensive).
  3. Q43The H1 review meeting needs orders placed between January and June 2025.
  4. Q44The CHRO wants the salary 'middle band' - employees earning BETWEEN 40000 AND 80000.
  5. Q45Loyalty wants Gold + Silver tier customers - use IN.
  6. Q46Exclusion list: customers whose tier is NOT IN ('Bronze', 'Platinum').
  7. Q47The Ops dashboard wants orders that are either Delivered or Shipped - use IN.
  8. Q48Escalation queue: tickets whose priority is IN ('High', 'Critical').
  9. Q49Catalog audit - products whose name STARTS WITH 'A'.
  10. Q50Marketing wants every 'Pro' line product - product_name CONTAINS 'Pro'.
  11. Q51The premium 'Pro' tier suffix - product_name ENDS WITH 'Pro'.
  12. Q52The Gmail-first email campaign - customers whose email ends with '@gmail.com'.
  13. Q53Same for Yahoo users - email ends with '@yahoo.com'.
  14. Q54HR wants every 'Manager' across roles - role contains 'Manager'.
  15. Q55The Sales org tree - employees whose role STARTS WITH 'Sales'.
  16. Q56Customer call wants to find 'Priya' (any casing) - first_name ILIKE 'priya%'.
  17. Q57Catalog wants every Samsung product (any casing) - product_name ILIKE 'samsung%'.
  18. Q58A reporting quirk - customers whose first_name has 'a' as the SECOND letter (LIKE '_a%').
  19. Q59Logistics wants the 'in transit' list - sales.shipments where delivered_date IS NULL.
  20. Q60Same logistics dashboard - already-delivered shipments where delivered_date IS NOT NULL.
  21. Q61Privacy report: anonymous page-view sessions - web_events.page_views where customer_id IS NULL.
  22. Q62Logged-in user page views - customer_id IS NOT NULL.
  23. Q63The support queue 'still open' - tickets where resolved_date IS NULL.
  24. Q64The support team wants closed tickets - resolved_date IS NOT NULL.
  25. Q65The CFO wants the post-2025 order book - orders where order_date >= '2025-01-01'.
  26. Q66Finance wants campaigns with very specific budget amounts - budget IN (100000, 200000, 500000).
  27. Q67The merchandising shortlist - products whose brand_id is in (1, 2, 3, 4, 5).
  28. Q68Call analytics - calls where call_reason IN ('Complaint', 'Inquiry').
  29. Q69Acquisition team wants customers who joined IN 2024 - registration_date BETWEEN '2024-01-01' AND '2024-12-31'.
  30. Q70CFO wants only 2025 orders - use order_date >= '2025-01-01' AND order_date < '2026-01-01'.

ORDER BY + LIMIT + OFFSET + DISTINCT

  1. Q71The premium catalog needs the 10 most expensive products for the homepage.
  2. Q72The 'budget products' carousel - 10 cheapest products.
  3. Q73The DBA wants the 5 most recent orders for a smoke test.
  4. Q74Historical review - the 5 earliest orders ever placed.
  5. Q75CHRO wants the top 10 highest-paid employees for the compensation review.
  6. Q76HR wants the 10 lowest-paid employees for the minimum-wage compliance check.
  7. Q77Marketing wants the newest customers first - sort by registration_date DESC.
  8. Q78Customer Service wants the directory alphabetically - by first_name.
  9. Q79Store Ops wants every store ordered by city for the regional review.
  10. Q80The CFO wants the 20 highest-value orders by net_total.
  11. Q81Catalog ordering - products sorted by price DESC, then by product_name ASC (tie-break).
  12. Q82Sort employees by salary DESC, then by first_name ASC.
  13. Q83The call-center supervisor wants the 5 longest support calls.
  14. Q84Web analytics wants the 10 most recent page_views.
  15. Q85HR wants the 20 most recent attendance records.
  16. Q86The CFO wants the top 10 refund_amount returns for the loss review.
  17. Q87The CMO wants the 5 highest-budget campaigns.
  18. Q88Production wants the top 10 work_orders by quantity_produced.
  19. Q89Loyalty wants the 5 OLDEST customers - earliest registration_date.
  20. Q90Logistics wants shipments sorted by delivered_date with the in-transit ones at the bottom (NULLS LAST).
  21. Q91Support wants tickets sorted by resolved_date with unresolved ones at the TOP (NULLS FIRST).
  22. Q92The pagination test - PAGE 1 (rows 1-10) of customers ordered by customer_id.
  23. Q93PAGE 2 (rows 11-20) of customers ordered by customer_id.
  24. Q94PAGE 3 of the price-DESC product list, 20 per page (rows 41-60).
  25. Q95PAGE 5 of the high-value orders list (20 per page, rows 81-100), sorted by net_total DESC.
  26. Q96The Web Analytics team wants the distinct device types - what unique values exist?
  27. Q97Loyalty wants the list of all tier names (no duplicates).
  28. Q98The Ops team wants every distinct order_status that has appeared.
  29. Q99Store Ops wants the alphabetical list of distinct cities.
  30. Q100Support wants the alphabetical list of distinct ticket categories.

Combined ideas, multi-step thinking

FILTERING DEEPER - CONCEPTUAL

  1. Q1In WHERE A AND B OR C - which is evaluated first, AND or OR?
  2. Q2Why is WHERE NOT (col = 5) different from WHERE col != 5 when col can be NULL?
  3. Q3What does the three-valued logic of SQL mean - TRUE / FALSE / what?
  4. Q4Explain why WHERE col = NULL returns no rows but WHERE col IS NULL does.
  5. Q5What's the difference between IS DISTINCT FROM and !=?
  6. Q6In WHERE x IN (1, 2, NULL) - does the NULL match anything?
  7. Q7In WHERE x NOT IN (1, 2, NULL) - what's the surprising result and why?
  8. Q8Explain the difference between IS TRUE and = TRUE on a BOOLEAN column.
  9. Q9Compare WHERE col LIKE '%abc%' vs WHERE position('abc' IN col) > 0 - same result, but which is faster on a large table?
  10. Q10What's the cost of WHERE LOWER(col) = 'priya' - and why does it disable a regular index?
  11. Q11Why is BETWEEN '2025-01-01' AND '2025-01-31' slightly different from >= AND < when col has TIME parts?
  12. Q12Explain what ESCAPE means in LIKE '50\%' ESCAPE '\'.
  13. Q13Difference between ORDER BY 1 and ORDER BY col_name - which is safer for production?
  14. Q14In multi-column ORDER BY, does each column have its own ASC/DESC? Show example.
  15. Q15What does NULLS FIRST vs NULLS LAST control?
  16. Q16What is the default position of NULLs in ASC vs DESC ORDER BY?
  17. Q17Difference between LIMIT 10 and FETCH FIRST 10 ROWS ONLY.
  18. Q18Why is OFFSET 1000000 LIMIT 10 slow - and what's the alternative for pagination?
  19. Q19Difference between DISTINCT and DISTINCT ON (col).
  20. Q20Why might SELECT DISTINCT * be misleading on a table with TIMESTAMP columns?
  21. Q21What is "keyset pagination" and why is it faster than offset pagination?
  22. Q22Explain the precedence of AND/OR/NOT - give a query where missing parens changes the result.
  23. Q23What does <> mean - same as !=?
  24. Q24Compare WHERE col1 = col2 vs WHERE col1 IS NOT DISTINCT FROM col2 on NULLable columns.
  25. Q25Why might SELECT * FROM t LIMIT 5 give different rows each time (without ORDER BY)?

COMPLEX WHERE - MULTI-CONDITION

  1. Q26Marketing wants Gold customers in Mumbai OR Delhi who registered in 2024. Show the precedence-correct query.
  2. Q27Support wants Critical OR High priority tickets that are still 'Open'.
  3. Q28The CMO wants customers whose tier is Gold or Platinum AND whose phone starts with '+91 9'.
  4. Q29Show orders that are NOT Delivered AND NOT Cancelled AND placed in 2025.
  5. Q30Find products priced > 1000 AND cost_price > 500 AND brand_id NOT IN (1, 2).
  6. Q31Find employees with salary > 50000 AND (role LIKE '%Manager%' OR role LIKE '%Director%').
  7. Q32Find call_center.calls longer than 600 seconds AND with call_reason IN ('Complaint', 'Inquiry').
  8. Q33Find sales.shipments where delivered_date IS NULL AND shipped_date < CURRENT_DATE - 7 (stale in-transit).
  9. Q34Find tickets created in the last 7 days AND priority IN ('High','Critical') AND resolved_date IS NULL.
  10. Q35Find page_views from anonymous users (customer_id IS NULL) AND device_type = 'Mobile' AND view_timestamp >= CURRENT_DATE - 30.
  11. Q36Find expenses > 100000 in 2025 AND exp_cat_id IN (1, 2, 3, 4, 5).
  12. Q37Find loyalty members with points_balance BETWEEN 500 AND 5000 AND join_date < CURRENT_DATE - INTERVAL '1 year'.
  13. Q38Find returns where refund_amount > 5000 OR reason LIKE '%Defective%'.
  14. Q39Find audit.application_logs at level 'ERROR' or 'FATAL' AND timestamp >= CURRENT_DATE - 1.
  15. Q40Find api_requests where status_code = 500 AND endpoint LIKE '/api/v2/%' AND response_time_ms > 1000.
  16. Q41Find products with price > 5000 AND (brand_id IS NULL OR brand_id IN (1, 2)).
  17. Q42Find customers in tier Gold OR Platinum AND registered in 2024 OR 2025 - show the parenthesization needed.
  18. Q43Find employees whose salary > 80000 AND role NOT LIKE '%Intern%' AND store_id IS NOT NULL.
  19. Q44Find orders placed on weekends (Saturday/Sunday) with net_total > 10000.
  20. Q45Find tickets where status = 'Open' AND priority IN ('High','Critical') AND age (CURRENT_TIMESTAMP - created_date) > INTERVAL '24 hours'.
  21. Q46Find product_reviews where rating BETWEEN 1 AND 2 (negative) AND review_date >= CURRENT_DATE - 30.
  22. Q47Find shipments where status = 'Delivered' AND (delivered_date - shipped_date) > INTERVAL '7 days' (slow deliveries).
  23. Q48Find customers using IS DISTINCT FROM: WHERE tier IS DISTINCT FROM 'Bronze' (includes NULL tier!).
  24. Q49Find rows using IS NOT TRUE on a BOOLEAN: stores.stores WHERE (some_boolean) IS NOT TRUE - captures FALSE OR NULL.
  25. Q50Find orders where (CURRENT_DATE - order_date) BETWEEN 7 AND 30 (week-to-month-old orders).

COMPLEX LIKE / ILIKE / IS NULL

  1. Q51Customers whose first_name starts with 'A' or 'B' or 'C' using ILIKE.
  2. Q52Customers whose email ends with @gmail.com OR @yahoo.com using two LIKEs joined by OR.
  3. Q53Products with 'Pro' in the name but NOT ending in 'Pro'.
  4. Q54Customers whose first_name has EXACTLY 4 letters (use LENGTH).
  5. Q55Products with name matching the LIKE pattern '_ _ _%' (at least 3 chars with two embedded).
  6. Q56Find any email that contains a literal underscore '_' character (use ESCAPE).
  7. Q57Customers with phone starting '+91 90', '+91 91', or '+91 99'.
  8. Q58Products whose name has the second character as 'a' (case-insensitive).
  9. Q59Stores whose city contains exactly one space.
  10. Q60Employees whose email is in the form '[email protected]' - pattern match.
  11. Q61Customers whose email does NOT use a Gmail or Yahoo domain.
  12. Q62Products whose name contains a digit (use ~ regex operator).
  13. Q63Customers whose first_name contains exactly two vowels (advanced - use regexp_matches or LENGTH tricks).
  14. Q64Shipments where delivered_date IS NULL AND status != 'Cancelled' (truly pending).
  15. Q65Tickets where resolved_date IS NULL AND priority = 'Critical' AND created_date < CURRENT_DATE - 2 (overdue critical).
  16. Q66Customers whose phone IS NOT NULL AND LENGTH(phone) != 14 (incorrectly formatted Indian phone with +91).
  17. Q67Products whose supplier_id IS NULL AND price > 5000 (high-value, no supplier - data quality issue).
  18. Q68Page_views where customer_id IS NULL AND device_type = 'Mobile' (anonymous mobile users for retargeting).
  19. Q69Payments where order_id IS NOT NULL AND amount IS NULL (audit anomaly).
  20. Q70Customers whose tier_updated_at IS DISTINCT FROM registration_date (those who upgraded tiers).
  21. Q71Returns whose refund_amount IS NULL (returns awaiting refund processing).
  22. Q72Employees whose store_id IS NULL OR dept_id IS NULL (incomplete records).
  23. Q73Customers whose email matches '%@%.%' (basic email shape check).
  24. Q74Products whose product_name has a '%' sign in it (LIKE with ESCAPE).
  25. Q75Tickets whose subject starts with a digit (e.g., '500-something').

SORTING + PAGINATION

  1. Q76Top 10 products by price DESC, tie-broken by product_name ASC.
  2. Q77Bottom 10 employees by salary, tie-broken by joining_date DESC (most recent hire first within a tie).
  3. Q78Orders sorted by order_date DESC NULLS LAST, then net_total DESC.
  4. Q79Customers ordered by registration_date DESC - paginated to page 4 (20 per page).
  5. Q80Products ordered by price DESC - get page 7 (rows 121-140) using LIMIT/OFFSET.
  6. Q81Page 1 of high-value orders (net_total > 5000), sorted by order_date DESC, 25 per page.
  7. Q82Top 5 cities by store count (need GROUP BY - single line).
  8. Q83Most recent 10 support tickets across all priorities.
  9. Q84Most recent 5 page_views per device_type - use DISTINCT ON.
  10. Q85Most recent 1 order per customer using DISTINCT ON (cust_id) ... ORDER BY cust_id, order_date DESC.
  11. Q86Get the 50th most expensive product (ORDER BY price DESC LIMIT 1 OFFSET 49).
  12. Q87Get the 100th oldest customer registration.
  13. Q88Top 3 customer tiers by member count.
  14. Q89Sorted list of distinct courier_names from sales.shipments.
  15. Q90Top 10 longest-running calls in the last 30 days.
  16. Q91Orders sorted with NULL net_total at the bottom even in DESC sort.
  17. Q92List products with the SAME price (handles ties): order by price DESC then product_id ASC for a stable order.
  18. Q93Top 10 stores by opening_date (oldest first).
  19. Q94Most recent 20 ad_spend rows per platform.
  20. Q95Show 5 page_views - but PAGE 1 of MOBILE users only, ordered by view_timestamp DESC.
  21. Q96Pagination using KEYSET (not OFFSET): orders WHERE order_date < last_seen_date ORDER BY order_date DESC LIMIT 20.
  22. Q97Top 5 brand_ids by product count.
  23. Q98Top 10 customers by registration_date DESC, excluding any with NULL first_name.
  24. Q99Top 10 finance.expenses by amount DESC, only from 2025.
  25. Q100Show the 25 most recently shipped shipments (where shipped_date IS NOT NULL).

Interview grade, edge cases

FILTERING & SORTING - CONCEPTUAL

  1. Q1What does "sargable" mean - and why does WHERE LOWER(email) = ... defeat an index?
  2. Q2Compare WHERE col = NULL vs WHERE col IS NULL - why the first never matches.
  3. Q3Explain IS DISTINCT FROM - and why it's safer than = for nullable columns.
  4. Q4Walk through how WHERE col BETWEEN x AND y treats inclusive bounds.
  5. Q5Why is WHERE col1 = a AND col2 = b often faster with a composite index (col1, col2)?
  6. Q6Explain index column-order matters: (a, b) vs (b, a).
  7. Q7Why is WHERE date_col >= '2025-01-01' AND date_col < '2026-01-01' faster than EXTRACT(year FROM date_col) = 2025?
  8. Q8Compare ILIKE 'abc%' vs ILIKE '%abc' - only one is index-usable.
  9. Q9Walk through LIMIT + OFFSET pagination - and why deep OFFSETs are slow.
  10. Q10Explain keyset pagination - show ORDER BY id > last_seen_id LIMIT 50.
  11. Q11What is ORDER BY ... NULLS LAST - and what's the default for ASC vs DESC?
  12. Q12Compare DISTINCT vs DISTINCT ON - and when each preserves which row.
  13. Q13Explain WHERE x = ANY(array) vs WHERE x IN (...) - semantically equivalent.
  14. Q14What is WHERE x = ALL(subquery) - and why is it rarely used?
  15. Q15Explain EXISTS vs IN - when does each scale better?
  16. Q16Why does NOT IN break when the subquery returns NULLs?
  17. Q17Compare NOT EXISTS vs LEFT JOIN ... IS NULL for anti-join.
  18. Q18What does WHERE col @@ tsquery do - full-text search predicate.
  19. Q19Explain how a covering index can answer a query without touching the heap.
  20. Q20What is "row constructor compare": (a, b) > (1, 2) - and how does it help keyset paging.
  21. Q21Why is ORDER BY RANDOM() LIMIT 1 catastrophic on huge tables?
  22. Q22Compare WHERE col IN (subq) vs WHERE col = (subq scalar).
  23. Q23Explain WHERE col SIMILAR TO regex - and why most use ~ instead.
  24. Q24What is a "false predicate" (WHERE 1=0) - used as table-shape filter.
  25. Q25Walk through the planner's decision: SeqScan vs IndexScan vs BitmapIndexScan.

INDEX-AWARE FILTERING

  1. Q26Find orders in date range '2025-03-01' to '2025-04-01' (sargable).
  2. Q27Find customers whose email LIKE 'a%' (prefix-search index-friendly).
  3. Q28Find products WHERE price BETWEEN 1000 AND 5000 (range index).
  4. Q29Find orders WHERE order_status IN ('Pending','Processing') using IN-list.
  5. Q30Find products WHERE supplier_id = 10 AND brand_id = 3 (composite-index candidate).
  6. Q31Find tickets WHERE priority = 'Critical' AND status = 'Open' (composite).
  7. Q32Find shipments WHERE courier_name = 'Bluedart' AND shipped_date >= now() - 7.
  8. Q33Find page_views WHERE device_type = 'Mobile' AND os = 'iOS' (composite-index potential).
  9. Q34Find employees WHERE role = 'Manager' AND store_id IN (1,2,3).
  10. Q35Find orders WHERE net_total > 10000 AND order_date >= '2025-01-01' (compound).
  11. Q36Sargable date filter: orders in current month (DATE_TRUNC vs range bounds).
  12. Q37Sargable: WHERE created_at >= now() - INTERVAL '7 days'.
  13. Q38Non-sargable rewrite: WHERE year(order_date) = 2025 -> WHERE order_date >= '2025-01-01' AND order_date < '2026-01-01'.
  14. Q39Index-only scan: SELECT email FROM customers WHERE email LIKE 'a%' (with appropriate index).
  15. Q40Find tickets WHERE resolved_date IS NULL (partial-index candidate).
  16. Q41Find orders WHERE order_status = 'Cancelled' (partial-index for rare value).
  17. Q42Find customers WHERE deleted_at IS NULL AND email = '[email protected]'.
  18. Q43Find pay_slips WHERE salary_year = 2025 AND salary_month IN (1,2,3).
  19. Q44Find reviews WHERE rating >= 4 AND created_at >= '2025-01-01'.
  20. Q45Find inventory_snapshots WHERE warehouse_id = 5 AND snapshot_date = '2025-04-15'.
  21. Q46Find calls WHERE call_reason = 'Refund' AND duration > 300.
  22. Q47Find ad_spend WHERE platform = 'Facebook' AND spend_date BETWEEN ... AND ....
  23. Q48Find page_views with referrer_url LIKE 'https://google.%' (prefix).
  24. Q49Find customers with tier_id IN (3, 4) - top tiers.
  25. Q50Find supply_chain.shipments WHERE supplier_id = 7 AND quantity > 100.

PAGINATION + ORDER BY EDGE CASES

  1. Q51OFFSET-LIMIT pagination: orders page 100, page size 50 (OFFSET 4950).
  2. Q52Keyset pagination: orders WHERE (order_date, order_id) < (last_date, last_id) ORDER BY order_date DESC, order_id DESC LIMIT 50.
  3. Q53ORDER BY net_total DESC NULLS LAST - keep NULLs at end.
  4. Q54ORDER BY tier_id NULLS FIRST - surface "no tier" first.
  5. Q55ORDER BY priority CASE WHEN priority='Critical' THEN 1 WHEN 'High' THEN 2 ... (custom sort).
  6. Q56Tie-breaker: ORDER BY revenue DESC, customer_id ASC (deterministic order).
  7. Q57ORDER BY DATE_TRUNC('month', order_date), net_total DESC - bucket-then-sort.
  8. Q58SELECT DISTINCT ON (cust_id) * FROM orders ORDER BY cust_id, order_date DESC - latest order per customer.
  9. Q59SELECT DISTINCT ON (product_id) ... ORDER BY product_id, review_date DESC - most-recent review per product.
  10. Q60Top-N per group via window or DISTINCT ON.
  11. Q61Stable pagination across DELETEs - why id-based keyset is robust.
  12. Q62Sort with LOCALE: ORDER BY full_name COLLATE "C" vs default.
  13. Q63Sort numbers stored as TEXT: ORDER BY col::int (cast).
  14. Q64Sort emails by domain: ORDER BY split_part(email, '@', 2).
  15. Q65Sort tickets by priority weight: CASE-driven.
  16. Q66Pagination cursor with composite key (created_at, id).
  17. Q67Backward pagination (previous page) using keyset.
  18. Q68LIMIT + OFFSET 0 - degenerate case (just LIMIT).
  19. Q69LIMIT 0 - return no rows but force query plan & metadata.
  20. Q70ORDER BY column_position (1, 2, 3) - quick demo.
  21. Q71Find duplicates: ORDER BY email, customer_id then row_number window pattern.
  22. Q72Avoid OFFSET 100000: use keyset with composite cursor.
  23. Q73ORDER BY 1 vs ORDER BY full_name - when each is appropriate.
  24. Q74ORDER BY RANDOM() LIMIT N - sample N random rows (small tables only).
  25. Q75TABLESAMPLE BERNOULLI(1) - proper random sampling on huge tables.

SUBQUERY & SET FILTERS

  1. Q76EXISTS: customers who placed at least one order.
  2. Q77EXISTS: products with at least one 5-star review.
  3. Q78NOT EXISTS: customers who never wrote a review.
  4. Q79NOT EXISTS: products that never sold.
  5. Q80IN (subquery): orders by Gold-tier customers.
  6. Q81NOT IN (subquery): customers NOT in loyalty.members - show NULL-safety variant with NOT EXISTS.
  7. Q82= ANY: orders matching list of statuses pulled from a config table.
  8. Q83> ALL: products priced higher than all products of brand_id=5.
  9. Q84< ANY: customers whose tier_id < ANY(SELECT tier_id FROM ...).
  10. Q85Find orders by customers in the top 10 highest-spend cust_ids (subquery + IN).
  11. Q86Find products in categories with > 100 products.
  12. Q87Find tickets opened on dates when there was a system outage (subquery joining audit logs).
  13. Q88Find customers in cities where there is at least one store.
  14. Q89Find shipments in months where average delivery time was < 2 days.
  15. Q90Find pay_slips in months when total payroll > 100,000 (HAVING + subquery).
  16. Q91Find orders whose product mix overlaps with the top-selling brand.
  17. Q92Find ad_campaigns active during a customer signup spike (subquery joining).
  18. Q93Find customers whose tier matches a calculated cohort.
  19. Q94Find products in brands whose AVG(price) > 5000 (correlated subquery).
  20. Q95Find employees whose salary > AVG(salary) for their department (correlated subquery).
  21. Q96Find customers who placed > 3 orders in any single week (subquery + grouping).
  22. Q97Find products whose total units sold > median product units.
  23. Q98Find orders that placed in the same hour as the spike (audit-linked).
  24. Q99Find calls handled by agents who also resolved tickets (intersection-style subquery).
  25. Q100Find customers who have ALL of {order, review, ticket, call} (relational division pattern).

Production scenarios, optimisation

CONCEPTUAL

  1. Q1Explain SQL three-valued logic (TRUE/FALSE/UNKNOWN) and how WHERE treats UNKNOWN.
  2. Q2Why does x = NULL never match, and what should you use instead?
  3. Q3Explain IS DISTINCT FROM and why it is "NULL-safe equality".
  4. Q4Why can NOT IN (subquery) return zero rows when the subquery has a NULL?
  5. Q5Compare EXISTS vs IN - semantics, NULL-safety, and how planners treat them.
  6. Q6State De Morgan's laws and why they matter when negating compound predicates.
  7. Q7Explain AND/OR precedence: how is "a OR b AND c" parsed?
  8. Q8Why prefer half-open ranges (>= start AND < end) over BETWEEN for dates?
  9. Q9Why are deep OFFSETs slow, and what does the engine actually do?
  10. Q10Describe keyset pagination and why it beats OFFSET for large pages.
  11. Q11What are the default NULL orderings for ASC and DESC, and how do you override?
  12. Q12Compare DISTINCT vs DISTINCT ON - which row does DISTINCT ON keep?
  13. Q13Show that x IN (a,b,c) equals x = ANY(ARRAY[a,b,c]).
  14. Q14Explain x = ALL(subquery) and > ALL(subquery) - when is each true?
  15. Q15Explain < ANY(subquery) and > ANY(subquery) in terms of MIN/MAX.
  16. Q16Two ways to express relational division ("matches ALL of a set").
  17. Q17Compare NOT EXISTS vs LEFT JOIN ... IS NULL for anti-joins.
  18. Q18What makes a predicate "sargable", and how does it shape how you write WHERE?
  19. Q19Expand the row-constructor comparison (a,b) > (1,2).
  20. Q20What does COLLATE "C" do to ORDER BY, vs a locale collation?
  21. Q21Why is ORDER BY RANDOM() expensive, and what is the cheaper sampling option?
  22. Q22Compare col = (scalar subquery) vs col IN (subquery) - cardinality rules.
  23. Q23Compare SIMILAR TO, POSIX ~, and LIKE prefix vs suffix matching.
  24. Q24Why does a stable/paginated sort need a unique tie-breaker column?
  25. Q25Does the written order of WHERE predicates change the result or the plan?

COMPLEX PREDICATES & NULL LOGIC

  1. Q26Find customers whose phone is missing (NULL or empty string).
  2. Q27Find shipments where delivered_date differs from shipped_date, NULL-safely.
  3. Q28Find Returned-or-Cancelled orders over 5000 (mind OR/AND precedence).
  4. Q29Find orders that are NOT (Delivered AND under 1000) - apply De Morgan.
  5. Q30Find March-2025 orders using a half-open date range.
  6. Q31Find promotions overlapping the window 2025-06-01..2025-06-30.
  7. Q32Find orders whose net_total is zero, treating NULL as zero via COALESCE.
  8. Q33Find orders whose status is none of Delivered/Shipped/Out for Delivery.
  9. Q34Find customers whose email begins with 'a' (case-insensitive).
  10. Q35Find customers whose email local-part contains a digit (regex).
  11. Q36Find customers whose name contains a non-ASCII character.
  12. Q37Find customers who have a tier but no tier_updated_at timestamp.
  13. Q38Find products priced 1000-5000 but excluding exactly 1999.
  14. Q39Find orders where gross_total - discount_amount exceeds 10000.
  15. Q40Find customers whose tier is anything other than Platinum (include NULLs).
  16. Q41Find addresses with a valid 6-digit numeric pincode.
  17. Q42Find reviews that are unrated (NULL) or rated below 2.
  18. Q43Find customers whose email does NOT end in .com/.in/.net/.org.
  19. Q44Find 2025+ orders with net_total between 2000 and 8000.
  20. Q45Find order_items with positive quantity but non-positive net_amount (data smell).
  21. Q46Find tickets unresolved for more than 30 days (created_date is a timestamp).
  22. Q47Find Critical/High tickets using = ANY over an array.
  23. Q48Find customers whose first_name has leading/trailing whitespace.
  24. Q49Find orders where "Cancelled" and "net_total = 0" disagree (XOR-style).
  25. Q50Rank products by margin %, guarding against price = 0 division.

SET-BASED FILTERS - EXISTS / IN / ANY / ALL / DIVISION

  1. Q51Customers who placed at least one order (EXISTS).
  2. Q52Products that never sold (NOT EXISTS).
  3. Q53Customers not in loyalty.members - NULL-safe with NOT EXISTS.
  4. Q54Orders placed by Platinum-tier customers (IN subquery).
  5. Q55Products priced above every product of brand 5 (> ALL).
  6. Q56Members whose tier rank is below at least one Mumbai member (< ANY).
  7. Q57Orders whose status is in a VALUES list (= ANY, read-only - no config table).
  8. Q58Products that have at least one 5-star review (correlated EXISTS).
  9. Q59Customers who reviewed but never ordered (EXISTS + NOT EXISTS).
  10. Q60Orders containing products from brands that have more than 100 products.
  11. Q61Customers who bought from ALL of categories {1,2,3} (HAVING COUNT DISTINCT).
  12. Q62Customers present in orders AND reviews AND tickets (chained EXISTS).
  13. Q63Orders above the global average net_total (scalar subquery).
  14. Q64Products priced above their own brand's average (correlated scalar).
  15. Q65Orders by the top-10 highest-spending customers (IN aggregated subquery).
  16. Q66Stores that have never had a Returned order (NOT EXISTS).
  17. Q67Tickets opened on a day with >100 ERROR/FATAL log entries (audit subquery).
  18. Q68Customers who live in a city that has a store (city via addresses).
  19. Q69Products priced above at least one brand's average price (> ANY).
  20. Q70Customers with no orders - NOT IN made safe by filtering NULLs.
  21. Q71Customers with at least one order over 50,000 (correlated EXISTS, inequality).
  22. Q72Customers who bought every product of brand 5 (division via count subquery).
  23. Q73Customers with 10+ orders using a derived table in FROM.
  24. Q74Customers who ordered but never returned anything (EXISTS + NOT EXISTS).
  25. Q75Brands whose every product is priced under 20000 (> ALL guarantee).

PAGINATION, ORDERING & TOP-N WITHOUT WINDOW FUNCTIONS

  1. Q76Page 100 (size 50) of orders by id using OFFSET - the slow way.
  2. Q77The same page using keyset pagination (order_id > last_seen).
  3. Q78Forward page using a composite (order_date, order_id) cursor.
  4. Q79Backward (previous) page using keyset - flip comparison and ORDER BY.
  5. Q80Top orders by net_total, keeping NULLs last.
  6. Q81Sort tickets by custom priority weight (CASE), then newest first.
  7. Q82Top-20 customers by revenue with a deterministic tie-break.
  8. Q83Latest order per customer using DISTINCT ON.
  9. Q84Most-recent review per product using DISTINCT ON.
  10. Q85Sort customers by name using COLLATE "C" (byte order).
  11. Q86Sort customers by email domain, then full email.
  12. Q87Sort phone numbers (TEXT) numerically, not lexically.
  13. Q88Top-3 orders per customer WITHOUT a window function (correlated count).
  14. Q89The single highest-value order per customer via correlated NOT EXISTS.
  15. Q90Products selling above the median units sold (PERCENTILE_CONT, no window).
  16. Q91The second-highest-priced product per brand WITHOUT a window function.
  17. Q92Paginate customers on a stable (registration_date, customer_id) cursor.
  18. Q93Order reviews by review-text length, longest first.
  19. Q94Top-10 stores by revenue with a deterministic tie-break.
  20. Q95Bottom-10 products by margin (ORDER BY ASC + LIMIT).
  21. Q96Earliest order per store using DISTINCT ON ascending.
  22. Q97Paginate customers with >50k revenue by customer_id cursor (HAVING + ORDER BY).
  23. Q98The 50 most recent still-open Critical tickets.
  24. Q99Take a ~1% random sample of orders efficiently (TABLESAMPLE).
  25. Q100Latest DELIVERED order per customer over 5000, newest first - no window functions.