AnnotationPrep

SQL & Algorithmic Complexity practice questions

10 questions from the Coding assessment: debugging & verification practice test.

Start practising

SQL & Algorithmic Complexity practice drill

10 scenarios, graded as you go, with the exact grading principle for every answer. Start free, no sign-up.

Sample scenarios

An LLM writes a SQL query to find all users who have NEVER placed an order:

```sql
SELECT u.id, u.email
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE o.status = 'paid' AND o.id IS NULL;
```

Why does this return 0 rows?

  1. `LEFT JOIN` does not allow `IS NULL`.
  2. Putting `o.status = 'paid'` in the `WHERE` clause turns the `LEFT JOIN` into an inner join—any unmatched row has `o.status = NULL`, which fails `o.status = 'paid'`.
  3. `WHERE` must come before `FROM`.
  4. `o.id` can never be `NULL`.
Show the answer

B. Putting `o.status = 'paid'` in the `WHERE` clause turns the `LEFT JOIN` into an inner join—any unmatched row has `o.status = NULL`, which fails `o.status = 'paid'`.

Filter conditions on the right-hand table of a `LEFT JOIN` must go in the `ON` clause (`AND o.status = 'paid'`), not in `WHERE`, otherwise unmatched rows (where `o.id IS NULL`) are filtered out.

Principle: SQL Joins: LEFT JOIN Filtering in WHERE vs ON

An LLM writes:

```sql
SELECT * FROM users WHERE id NOT IN (SELECT user_id FROM banned_users);
```

If `banned_users.user_id` contains even a single `NULL` value, what does this query return?

  1. All non-banned users.
  2. Zero rows (empty set), because `id NOT IN (1, 2, NULL)` evaluates to `UNKNOWN` for every row.
  3. Only the rows where `id` is `NULL`.
  4. Raises a division-by-zero error.
Show the answer

B. Zero rows (empty set), because `id NOT IN (1, 2, NULL)` evaluates to `UNKNOWN` for every row.

In SQL three-valued logic, `x NOT IN (1, NULL)` means `x != 1 AND x != NULL`, which is always `UNKNOWN` (never `TRUE`). Prefer `NOT EXISTS` or filter `WHERE user_id IS NOT NULL` inside the subquery.

Principle: SQL Three-Valued Logic: NOT IN with NULL

An LLM writes a Python deduplication function and claims it is O(n) time:

```python
def dedup(items):
seen = []
for x in items:
if x not in seen:
seen.append(x)
return seen
```

What is the actual worst-case time complexity?

  1. O(n)
  2. O(n log n)
  3. O(n²), because `x not in seen` performs a linear O(n) scan on a list on every iteration.
  4. O(1)
Show the answer

C. O(n²), because `x not in seen` performs a linear O(n) scan on a list on every iteration.

Checking membership (`x not in seen`) on a `list` is O(n), making the loop O(n²). To preserve order in O(n), track seen items in a `set` alongside the output list (or `list(dict.fromkeys(items))`).

Principle: Big-O Analysis: List Membership vs. Set Lookup

Other topics