Which Inspection Records Need Our Attention?

The supervisor asks Botie for inspections that found a loose component and took at least ten minutes. Sorting the longest visits is tempting, but a long routine visit does not meet that question. A short loose-component visit does not meet it either.

We need to ask both questions of the same row. Filtering is how SQL keeps the rows that satisfy the stated rule while leaving the stored log intact.

Pass inspection rows through the finding comparison and the duration comparison, then project the report fields.

[
  {
    "inspection_id": 101,
    "minutes": 30
  },
  {
    "inspection_id": 105,
    "minutes": 10
  },
  {
    "inspection_id": 106,
    "minutes": 15
  }
]

Our first predicate asks whether finding equals loose. Predicate means an expression used as a condition. The second asks whether minutes is at least ten. WHERE retains a row only when the combined predicate is true.

The filter does not delete rows. A different query can still inspect the excluded visits. This matters when building a training dataset: the inclusion rule determines which population the model will see.

Check the boundary value too. At least ten includes a recorded duration of exactly ten.

Two filters in sequence express this conjunction: keep loose findings, then keep sufficient duration. Reversing these independent tests produces the same result on our fixture. The database may choose its own evaluation plan, so do not depend on the written order of expressions to protect an unsafe calculation.

An OR rule asks a different question. It retains a row when either alternative is true, so long visits with a clear finding could enter the result. Start with the supervisor’s meaning before choosing a connective.

A shorter query is useful only if it still asks the same question.

Use a column name on one side and a literal value on the other. Text such as loose is written in single quotes. The operators =, <>, >, >=, < and <= express equality, inequality and ordered comparisons. SQLite also accepts != for inequality.

For a list of accepted values, IN can make the intended alternatives easier to read. BETWEEN includes both endpoints. Do not interpolate untrusted user text into a query string; later lessons will use bound parameters at the application boundary.

A literal names the value to compare, not another column.

CREATE TABLE assets (
  asset_id TEXT PRIMARY KEY NOT NULL,
  site TEXT NOT NULL,
  kind TEXT NOT NULL,
  active INTEGER NOT NULL CHECK (active IN (0, 1))
);
CREATE TABLE inspections (
  inspection_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  minutes INTEGER CHECK (minutes >= 0),
  finding TEXT
);
CREATE TABLE readings (
  reading_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  temperature REAL
);
INSERT INTO assets VALUES
 ('P1','north','pump',1), ('P2','north','fan',1),
 ('P3','south','pump',0), ('P4','south','pump',1);
INSERT INTO inspections VALUES
 (101,'P1',30,'loose'), (102,'P1',NULL,NULL),
 (103,'P2',0,'clear'), (104,'P3',20,'clear'),
 (105,'P2',10,'loose'), (106,'P1',15,'loose');
INSERT INTO readings VALUES
 (1,'P1',70.0), (2,'P1',80.0), (3,'P2',NULL), (4,'P3',65.0);

SELECT inspection_id, finding, minutes
FROM inspections
WHERE (finding = 'loose' OR finding = 'clear')
  AND minutes BETWEEN 10 AND 20
ORDER BY inspection_id;

AND binds more tightly than OR in these SQL expressions. A condition written as A OR B AND C therefore groups differently from (A OR B) AND C. Parentheses document which alternatives share the final requirement.

Imagine asking for north-site pumps or fans that are active. If active must apply to both equipment kinds, put those alternatives inside a shared group. Construct a small row that would distinguish the two readings before relying on the filter.

Ambiguous prose is a reason to clarify the report, not a reason to guess at parentheses.

Visit 102 has no recorded minutes. A comparison against ten cannot establish that it lasted at least ten minutes. SQL evaluates that comparison as unknown, and WHERE does not retain unknown results.

This is not the same as proving that the visit was short. If missing measurements need investigation, write a separate missingness condition. The NULL lesson makes that distinction explicit.

A filtered dataset can exclude missing values without announcing that exclusion in the final table.

Use examples that pass just one condition, examples on the threshold, and examples with missing values. These expose a query that accidentally uses OR or a strict greater-than comparison.

The workshop’s changed shift includes a long clear visit and a short loose visit. Both are plausible distractions. A correctly wired conjunction leaves both out while preserving a loose visit that meets the duration requirement.

Read the question again after the query runs. Plausible-looking rows can still answer the wrong question.