Database Queries: Translate AND, OR and Sorting into Results

Database queries: turn conditions into exact results

A retrieval request has three separate decisions: which records qualify, which fields appear and how the result is ordered. Mixing them causes common errors. Sorting by quantity does not filter out small quantities, and hiding a field does not stop it being used as a condition.

Use the complete fictional equipment catalogue below. It contains no people or private records. ItemID, Category and ServiceStatus are text fields; Quantity is a whole-number field. There are no missing values, and category and status spellings match exactly. Those assumptions keep this lesson focused on two-valued AND/OR logic rather than database-specific handling of missing values or text matching.

Read the supplied table

Quantity zero is a supplied value, not an empty cell. The values 4, 5 and 8 make the boundary at five visible. Ready and Service are catalogue labels; do not infer any repair decision from them.

A condition such as Quantity ≥ 5 includes five. Quantity > 5 excludes five. Evaluate conditions for each row, retain qualifying rows, and only then apply the requested ordering. All worked results display the same four fields so you can check their selection.

AND requires both; OR includes either or both

OR is inclusive here. A row satisfying both conditions is included once in this single-table result; it is not copied into the answer twice. Parentheses make grouping explicit when AND and OR appear together.

The grids below use LibreOffice Base’s documented convention: conditions on one criteria row combine with AND; alternative criteria rows combine with OR. Empty grid cells impose no condition. They do not mean that the source field must be empty. These are teaching grids, not screenshots. The optional read-only SELECT statements use SQLite-compatible syntax; do not assume that every application accepts identical notation.

Worked request 1: ready equipment with at least five items

Request: Show Ready records whose quantity is at least five. Put the greatest quantity first, breaking quantity ties by ItemID ascending.

Logical form: (ServiceStatus = Ready) AND (Quantity ≥ 5).

SELECT ItemID, Category, Quantity, ServiceStatus
FROM Equipment
WHERE ServiceStatus = 'Ready' AND Quantity >= 5
ORDER BY Quantity DESC, ItemID ASC;

E06 and E08 both have eight items and are Ready, so E06 precedes E08 by the tie-break rule. E03 qualifies at the inclusive boundary of five. E01 is Ready but has only four; E02 has five but is in Service. Each fails one required condition. E05 has zero, while E04 and E07 fail the status test. Exactly three rows remain.

Worked request 2: audio equipment or fewer than five items

Request: Show every Audio record or record with fewer than five items. Sort by ItemID ascending; status is unrestricted.

Logical form: (Category = Audio) OR (Quantity < 5).

SELECT ItemID, Category, Quantity, ServiceStatus
FROM Equipment
WHERE Category = 'Audio' OR Quantity < 5
ORDER BY ItemID ASC;

E01 satisfies both conditions and appears once. E02 and E06 qualify because they are Audio even though neither has fewer than five items. E05 qualifies because zero is less than five even though its category is Storage. E03, E04, E07 and E08 satisfy neither condition. Replacing OR with AND would return only E01, answering a narrower request.

Worked request 3: ready audio or display equipment

Request: Show Ready equipment in either Audio or Display. Put the smallest quantity first, breaking ties by ItemID ascending.

Logical form: ((Category = Audio) OR (Category = Display)) AND (ServiceStatus = Ready).

SELECT ItemID, Category, Quantity, ServiceStatus
FROM Equipment
WHERE (Category = 'Audio' OR Category = 'Display')
  AND ServiceStatus = 'Ready'
ORDER BY Quantity ASC, ItemID ASC;

The diagram’s two branches mean Audio AND Ready, or Display AND Ready. Repeat the Ready condition on both grid rows. E01, E03, E06 and E08 qualify. E02 and E04 are excluded by status; E05 and E07 are excluded by category. E06 and E08 tie at eight and remain in ItemID order.

Diagnose a plausible wrong result

A learner writes Category = ‘Audio’ OR Category = ‘Display’ AND ServiceStatus = ‘Ready’, with no parentheses. In SQLite, AND has higher precedence than OR. The condition therefore means Audio OR (Display AND Ready). The Ready requirement does not apply to the Audio branch.

The wrong result, sorted by quantity then ItemID, is E01, E02, E03, E06, E08. E02 exposes the mistake: it is Audio but in Service. Restore the parentheses around the category alternatives. In the grid, the equivalent repair is to require Ready on both rows. Changing the sort direction would not remove E02 and would not fix the selection.

Check every row, including excluded ones

This row audit checks membership separately from order. Next verify the displayed values against the source table, then check adjacent results against the sort rules. A result can have the right row count but the wrong records. It can also have all the right records in an order the request did not specify.

Three solved practice tasks

Task 1: Select records in Service with quantity at least five, ordering quantity descending then ItemID ascending. Solution: Use (ServiceStatus = Service) AND (Quantity ≥ 5). The ordered rows are E04 (Display, 8, Service), E02 (Audio, 5, Service), E07 (Storage, 5, Service). The tie-break places E02 before E07.

Task 2: Select quantities from zero through five inclusive, sorting by quantity ascending then ItemID ascending. Solution: Use Quantity ≥ 0 AND Quantity ≤ 5. The complete order is E05 (0), E01 (4), E02 (5), E03 (5), E07 (5). Quantity five is included, and the three tied rows follow ItemID order.

Task 3: Request 1 should display only ItemID and Quantity. Must ServiceStatus be removed from the condition? Solution: No. Keep the Ready condition and display just the requested fields. The result is E06, 8; E08, 8; E03, 5. Field visibility and row selection serve different purposes.

Read the request back from the result

For each returned row, explain why it qualifies. For a row exactly on a boundary, explain why the inequality includes or excludes it. Finally, identify which condition rejects a near miss. This is more reliable than judging a query by whether its output “looks sensible”.

The LibreOffice Base Query Design reference documents criteria rows, alternative rows, visibility and sorting. The SQLite expression reference states operator precedence. Use explicit grouping even when an application’s default happens to give the result you intended.