Not boring movies

Problem

Table: Cinema

+----------------+----------+
| Column Name    | Type     |
+----------------+----------+
| id             | int      |
| movie          | varchar  |
| description    | varchar  |
| rating         | float    |
+----------------+----------+
id is the primary key (column with unique values) for this table.
Each row contains information about the name of a movie, its genre, and its rating.
rating is a 2 decimal places float in the range [0, 10]

Write a solution to report the movies with an odd-numbered ID and a description that is not "boring".

Return the result table ordered by rating in descending order.

Example

Expected Output

Try it YourSelf

Solution

The goal is to retrieve records that adhere to specific criteria selectively. Initially, the query focuses on films with odd IDs, utilizing the modulo operation '%'. Simultaneously, records labeled as 'boring' in the description are excluded to enhance the overall quality of the selection.

Then, arrange the results by rating them in descending order, ensuring that the highest-rated films meeting the defined criteria are prioritized.

SELECT *
FROM   Cinema
WHERE  id % 2 = 1
       AND description != 'boring'
ORDER  BY rating DESC 

Let's break down the query step by step:

Step 1: SELECT * FROM cinema

This step selects all columns (*) from the "cinema" table.

Output After Step 1:

+----+------------+-------------+--------+
| id | movie      | description | rating |
+----+------------+-------------+--------+
| 1  | War        | great 3D    | 8.9    |
| 2  | Science    | fiction     | 8.5    |
| 3  | Irish      | boring      | 6.2    |
| 4  | Ice song   | Fantasy     | 8.6    |
| 5  | House card | Interesting | 9.1    |
+----+------------+-------------+--------+

Step 2: Filtering

WHERE  id % 2 = 1 AND description != 'boring'

This step filters the rows based on two conditions:

• id % 2 = 1 (Select only rows where the id is odd).
• description != 'boring' (Exclude rows where the description is 'boring').

Output After Step 2:

+----+------------+-------------+--------+
| id | movie      | description | rating |
+----+------------+-------------+--------+
| 1  | War        | great 3D    | 8.9    |
| 5  | House card | Interesting | 9.1    |
+----+------------+-------------+--------+

Step 3: ORDER BY rating DESC

ORDER  BY rating DESC 

This step orders the result set based on the "rating" column in descending order.

Final Output:

+----+------------+-------------+--------+
| id | movie      | description | rating |
+----+------------+-------------+--------+
| 5  | House card | Interesting | 9.1    |
| 1  | War        | great 3D    | 8.9    |
+----+------------+-------------+--------+

Pattern: composite filter (parity + inequality + sort)

Name: multi-predicate row filter with post-filter ordering — keep rows that satisfy all limbs (AND), then ORDER BY.

Limbs: (1) parity predicate id % 2 = 1 (odd ids), (2) inequality description <> 'boring', (3) sort by rating DESC.

Alternatives for parity: MOD(id,2)=1 (portable), id & 1 = 1 (bitwise, MySQL/Postgres integers). Avoid wrapping id in a non-sargable expression if you later need range seeks on id — modulo generally prevents a plain btree range on id anyway.

When-NOT for modulo: if "odd" is a business attribute you filter constantly, store/materialize a flag or use a partial index rather than computing % on every scan of a huge table.

NULL / collation notes: description != 'boring' drops rows where description IS NULL (UNKNOWN). Case: under case-insensitive collation, 'Boring' matches; under binary, it does not. Prefer description IS DISTINCT FROM 'boring' if NULL descriptions should be kept as "not boring."

Sargability systems note: id % 2 = 1 is typically non-sargable as a sole access path; on small cinema tables this is fine. The equality on description can use an index; the composite AND may bitmap-combine.

Drill: Change to even ids that are boring, ordered by rating ASC. What is the expected single row on the sample? (id 3 is odd+boring — none match even+boring on the sample; answer empty. Add a hypothetical even boring row to test.)

🎯 STANDOUT elevation: Why / example / when-not / failure / panel / drills — Not boring movies

Why this exists / the decision it encodes

This drill trains multi-predicate AND filters plus ORDER BY — the everyday SELECT shape. The standout layer is naming the pattern, tracing each limb, knowing NULL/collation edges, and knowing when id%2 is an acceptable scan vs a production anti-pattern.

Worked example with numbers or traced SQL/FD

Cinema sample:
1 War great3D 8.9 — odd, not boring → keep
2 Science fiction 8.5 — even → drop
3 Irish boring 6.2 — odd but boring → drop
4 Ice song Fantasy 8.6 — even → drop
5 House card Interesting 9.1 — odd, not boring → keep
ORDER BY rating DESC → (5, 9.1), (1, 8.9)
NULL description: description != 'boring' → UNKNOWN → dropped
Prefer IS DISTINCT FROM 'boring' if NULL means 'not labeled boring'

When NOT / named alternative

When NOT modulo: constant parity filter on huge tables — use generated flag + index or partial index. When NOT: if "boring" is a controlled enum, a check constraint + status column beats free text. Alternative parity: MOD(id,2)=1; avoid leading-function wraps if you also need range seeks on id.

Failure mode / ops fingerprint / interview trap

Trap: case-insensitive collation makes 'Boring' match; binary does not. Interview: only solution with no sargability note is junior. Ops: full scan from % on primary key of multi-million table.

Domain judgment (K11 theory-bridge / K12 concurrency / K13 query-judgment)

K13: even easy filters have access-path consequences; always state sargability when a function touches a key column.

Hostile-panel drills (with model answers)

Q1. Name the pattern and its three limbs.
Model answer: Composite multi-predicate filter + sort: (1) parity id%2=1, (2) description <> 'boring', (3) ORDER BY rating DESC.

Q2. Why is id % 2 = 1 typically non-sargable?
Model answer: It applies arithmetic to the indexed column so the planner cannot map the predicate to a contiguous B-tree range; expect index/table scan unless a functional/generated index exists.

Q3. Even + boring on the sample — expected rows?
Model answer: None on the given sample (id 3 is odd+boring). Empty result; invent an even boring row to test.