🟡 LIKE, BETWEEN, IN

LIKE, BETWEEN, IN: Think of these three operators as an archive clerk's three different search reflexes.

Think of these three operators as an archive clerk's three different search reflexes. LIKE is the clerk who half-remembers the filename: "bring me anything with Login in it". BETWEEN is the clerk marking a range on a calendar: "all of January". IN is the clerk holding a pre-written list: "anything in one of these three states". So why did SQL define dedicated operators at all, when OR can already express every one of them? Because `status='FAIL' OR status='SKIP' OR status='ERROR'` reads fine at three values and becomes unreadable at fifteen — and if you typo one of them into `AND`, the query returns **zero rows silently** instead of raising an error. IN makes that mistake impossible. BETWEEN does the same for the classic off-by-one of `>= AND = lo && d <= hi` range check, IN ≈ `List.of(...).contains(status)`. The difference is where the work happens: in Java you filter **after** pulling the data into memory; in SQL the engine filters inside the database, using an index. Across two million rows of test history that gap is measured in minutes, not seconds. In QA work this trio is daily bread: "runs from the last sprint whose name contains Checkout and whose status is FAIL or ERROR" turns a bug report from "it breaks sometimes" into "it broke 12 times in this date range" — a claim into evidence.

Micro Lab: SQL — GROUP BY / CTE / Window

Replace the TODO line with the critical line from the expected solution. This is not a real runtime; the goal is to reinforce writing the correct structure in a controlled way.

Step by Step: SQL — GROUP BY / CTE / Window

Aggregation or Window?

Determine: aggregation (summarizing) or Window (row-by-row computing)?

For aggregation, select the summary column with GROUP BY

Apply condition on groups with HAVING — like WHERE but after aggregation

Make modular with CTE

Modularize query with WITH cte_name AS (...) SELECT ... FROM cte_name

Add OVER(PARTITION BY)

For Window, use SUM(col) OVER (PARTITION BY partition ORDER BY sort)

What is the SQL GROUP BY and Window Function query writing order?

Bug Tracking DB — Interactive Example