MCQ-2A table `employees` has 100 rows. 20 of them have `salary IS NULL`. A developer runs `SELECT * FROM employees WHERE salary = NULL;`. What does the query return?⌄
MCQ-21A nightly reconciliation job compares each row's `discount` column between two snapshots and must flag a row whenever the value changed: including a change from NULL to 0, or from 0 to NULL. Which comparison flags exactly those rows?⌄
MCQ-3A `users` table has 1,000 rows. The `email` column is nullable and 150 rows store NULL. Of the remaining rows, 50 hold an address that also appears on exactly one other row. What does `SELECT COUNT(*), COUNT(email), COUNT(DISTINCT email) FROM users;` return?⌄
MCQ-12A table is created and four rows are inserted, one statement each, against PostgreSQL 16:
```sql
CREATE TABLE shifts (
id bigint PRIMARY KEY,
staff int NOT NULL,
room int,
UNIQUE (staff, room)
);
INSERT INTO shifts VALUES (1, 7, NULL);
INSERT INTO shifts VALUES (2, 7, NULL);
INSERT INTO shifts VALUES (3, 7, 12);
INSERT INTO shifts VALUES (4, 7, 12);
```
Which statement raises a unique violation, and how many rows does `shifts` hold afterwards?⌄
MCQ-1On PostgreSQL 16, this query fails with `ERROR: column "line_total" does not exist`:
```sql
SELECT price * qty AS line_total
FROM order_items
WHERE line_total > 100
ORDER BY line_total DESC;
```
Which change makes the query return the rows the engineer intended?⌄
MCQ-8A report groups `orders(order_id, customer_id, region, status, amount, created_at)` by `customer_id` and returns each customer's order count and total amount. The team wants to count only paid orders, include only 2024 orders, restrict to the EU region, and drop customers whose aggregate order value falls below the reporting threshold. Which of the following conditions must be evaluated in `HAVING` rather than `WHERE`?⌄
MCQ-24A `deploys` table holds one row per deploy:
| service | status |
| --- | --- |
| api | ok |
| api | ok |
| web | failed |
| web | ok |
A dashboard is meant to list services with no failed deploys, using this query:
```sql
SELECT service, failed
FROM (
SELECT service, SUM(CASE WHEN status = 'failed' THEN 1 END) AS failed
FROM deploys
GROUP BY service
) t
WHERE failed = 0;
```
Which services does it list?⌄
MCQ-20A reporting query joins customers to their orders:
```sql
SELECT c.id, SUM(o.amount) AS total
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.status = 'shipped'
GROUP BY c.id;
```
Customer 7 has no rows in `orders`. What does the query return for customer 7?⌄
MCQ-4`orders` holds 4,120 rows and `shipments` 9,300. `SELECT o.id, s.carrier FROM orders o LEFT JOIN shipments s ON o.id = s.order_id AND s.status = 'delivered'` returns 4,120 rows; moving `s.status = 'delivered'` into a WHERE clause returns 986. The report must list every order, with a NULL carrier where no delivered shipment exists. Where does the predicate belong?⌄
MCQ-7orders holds 4 rows, one per order, totalling 1,000. SELECT SUM(o.total) FROM orders o LEFT JOIN order_items i ON i.order_id = o.id returns 2,000, and order_items holds 6 rows. What is the most likely cause?⌄
MCQ-23A nightly reconciliation job runs `SELECT count(*) FROM events e JOIN sessions s ON e.session_id = s.session_id`. `events` holds 4,000 rows, and a backfill wrote `session_id = 'unknown'` on every one of them. `sessions` holds 3,000 rows; the same backfill wrote `session_id = 'unknown'` on 500 of them, while the other 2,500 kept real ids. The job used to return 4,000 rows in 40 seconds; now it runs for hours and spills to disk. How many rows does the join emit?⌄
MCQ-5`SELECT id FROM accounts WHERE id NOT IN (SELECT owner_id FROM transfers)` returns zero rows. `accounts.id` is NOT NULL and unique; `transfers.owner_id` is nullable and exactly one of its rows is NULL. What makes the anti-join reject every account?⌄
MCQ-17`customers` holds 2M rows and `orders` 40M, roughly 20 orders per customer. A report needs one row per customer who placed at least one order since 2024-06-01, and no order columns. Which construct should the query use?⌄
MCQ-15Tables: `customers(id, name)` = (1,'Ana'), (2,'Bo'), (3,'Cy'), (4,'Di'); `orders(order_id, customer_id, total)` = (101,1,40), (102,1,250), (103,2,90), (104,3,300), (105,3,20).
A developer writes:
```sql
SELECT c.name FROM customers c
WHERE (SELECT MAX(o.total) FROM orders o WHERE o.customer_id = c.customer_id) > 200;
```
then deletes `WHERE o.customer_id = c.customer_id` from the subquery. What does the query return after the deletion?⌄
MCQ-6A nightly job builds `active_users` by unioning `SELECT user_id FROM app_events` and `SELECT user_id FROM web_events`. The two sources are disjoint by construction: an event is written to exactly one of them. The query is written with `UNION`, and `EXPLAIN ANALYZE` (Postgres) shows a blocking deduplication step above the append (`Sort` + `Unique`) that accounts for 1.4 s of the 1.6 s runtime. Which change removes that deduplication step without changing the result?⌄
MCQ-22PostgreSQL 16. A `parts` table holds three rows (`('L','M')`, `('M','N')`, `('N','M')`) where the second column is `parent_id`. This query walks the parent chain from L:
```sql
WITH RECURSIVE chain AS (
SELECT part_id, parent_id, 1 AS lvl FROM parts WHERE part_id = 'L'
UNION ALL
SELECT p.part_id, p.parent_id, c.lvl + 1
FROM parts p JOIN chain c ON p.part_id = c.parent_id
)
SELECT * FROM chain;
```
A colleague changes `UNION ALL` to `UNION`. What happens when the query runs?⌄
MCQ-13An `orders` table has a nullable `sales_rep_id` column referencing `sales_reps(id)`. When a rep leaves, their `sales_reps` row is deleted, but the order rows must survive for audit and must no longer point at a rep that no longer exists. Which `ON DELETE` action on the foreign key does this?⌄
MCQ-19On PostgreSQL 16, a worker retries `INSERT INTO jobs (tenant_id, idempotency_key, payload) VALUES ($1, $2, $3)` after a network timeout, and a second worker may run the same job concurrently. `jobs` has a primary key on `id` and `UNIQUE (tenant_id, idempotency_key)`, and the first worker's payload must survive. Which change makes the retry safe?⌄
MCQ-10A nightly job runs `SELECT id, status FROM orders WHERE status = 'open'` with no ORDER BY. On the 2,000-row staging table it returns rows in ascending `id` order every night; on the 40-million-row production table the same statement returns a different order on some nights. Which statement is correct?⌄
MCQ-11A Postgres 16 `events` table holds 50M rows with a primary key on `id`. `SELECT id, payload FROM events ORDER BY id LIMIT 20 OFFSET 4000000` runs at 1.4 s p99; the same query at `OFFSET 0` runs at 2 ms. `EXPLAIN (ANALYZE)` shows an Index Scan on the primary key feeding a Limit node: the scan returns 4,000,020 rows, the Limit emits 20, and there is no Sort node. Each offset is requested once per session. Which change brings the deep-page query down to roughly the 2 ms of the first page?⌄
MCQ-14A ledger column is declared `double precision`. The nightly reconciliation sums the column and compares the result to the partner's exact total with `=`, and the check fails even though every line item matches. Analysts also query the warehouse directly, comparing the column against literal amounts from the feed. Which column type should replace it?⌄
MCQ-25A PostgreSQL 16 appointments table stores `start_time timestamp` (no time zone). The app writes local wall-clock strings in `America/New_York`. On 2024-11-03, two rows are inserted: id 1 at 01:30 EDT and id 2 at 01:30 EST. Both store as `2024-11-03 01:30:00`. A query `ORDER BY start_time` returns them in arbitrary order, and a report that groups by `start_time` counts them as one appointment. Which change makes the two rows order correctly and count as two distinct instants?⌄
MCQ-16A dashboard endpoint runs `SELECT * FROM events WHERE tenant_id = $1 ORDER BY created_at DESC LIMIT 200` on PostgreSQL 16. The table has 22 columns; the UI renders 6 of them. A migration added a `raw_payload jsonb` column averaging 40 KB per row. Since then the endpoint's p99 has gone from 30 ms to 1.9 s and its response size from 90 KB to 8.1 MB. The plan is unchanged, still an Index Scan on `(tenant_id, created_at)`, pool wait time is flat at 0.3 ms, and the row count is the same. What caused the regression?⌄
MCQ-18A service runs MySQL 8.0 with the default `ONLY_FULL_GROUP_BY` sql_mode. The table is `orders(order_id BIGINT PRIMARY KEY, customer_id BIGINT NOT NULL, total DECIMAL(10,2))`. The team runs `SELECT order_id, customer_id, total FROM orders GROUP BY order_id;`. What happens?⌄
MCQ-9A service builds its query by concatenation and executes it directly:
```python
sql = "SELECT id, total FROM orders WHERE user_id = " + user_id
cursor.execute(sql)
```
A reviewer's fix strips every `'`, `--` and `;` from `user_id` before concatenation. A tester submits `user_id = "1 UNION SELECT id, balance FROM accounts"`. What does the endpoint return?⌄