pencils ready ✎

SQL & PostgreSQL MCQs multiple-choice questions with answers & explanations

All 21 SQL & PostgreSQL quiz questions on one page. Pick an answer in your head, then open Show answer to check it and read why. Want a score and a timer? Take them as a quiz instead.

Written by Technical Lead, BNP Paribas

✎ Take the SQL & PostgreSQL quiz59 SQL & PostgreSQL interview questions →
  1. 1.

    What does this query return?

    mid
    -- customers.id: 1, 2, 3
-- orders.customer_id: 1, 1, NULL
SELECT count(*) FROM customers
WHERE id NOT IN (SELECT customer_id FROM orders);
    1. A2
    2. B3
    3. C0
    4. DAn error, because the subquery returns NULL
    Show answer

    Answer: C (0)

    id NOT IN (1, 1, NULL) means id <> 1 AND id <> 1 AND id <> NULL. The last comparison is always unknown, so the condition is never true and no rows qualify. NOT EXISTS would return 2, because it isn't affected by the NULL.

    try it in the quiz →

  2. 2.

    What does the final SELECT return?

    easy
    CREATE TABLE t (bonus int);
INSERT INTO t VALUES (100), (NULL), (200);

SELECT count(*), count(bonus), avg(bonus) FROM t;
    1. A3, 3, 100
    2. B3, 2, 150
    3. C2, 2, 150
    4. D3, 2, 100
    Show answer

    Answer: B (3, 2, 150)

    count(*) counts rows (3), while count(bonus) counts only non-NULL values (2). avg also ignores NULLs, so it is (100 + 200) / 2 = 150, not 300 / 3 = 100.

    try it in the quiz →

  3. 3.

    Salaries are 300, 200, 200 and 100. Ordering by salary descending, what do RANK() and DENSE_RANK() return for the row with salary 100?

    mid
    1. A3 and 3
    2. B4 and 4
    3. C3 and 4
    4. D4 and 3
    Show answer

    Answer: D (4 and 3)

    Both functions give the two 200s rank 2. RANK then skips a number, so 100 gets 4; DENSE_RANK leaves no gaps, so 100 gets 3. ROW_NUMBER would also give 4, but with no ties.

    try it in the quiz →

  4. 4.

    Which condition returns the rows where email has no value (is NULL)?

    easy
    1. AWHERE email IS NULL
    2. BWHERE email = NULL
    3. CWHERE email = ''
    4. DWHERE email IN (NULL)
    Show answer

    Answer: A (WHERE email IS NULL)

    Any comparison with NULL, including email = NULL, evaluates to unknown, and WHERE keeps only rows where the condition is true, so it matches nothing. IN (NULL) is just = NULL in disguise. An empty string is a real value, not NULL (except in Oracle).

    try it in the quiz →

  5. 5.

    How many rows does this return?

    easy
    SELECT 1 UNION SELECT 1 UNION ALL SELECT 1;
    1. A1
    2. B2
    3. C3
    4. DAn error: UNION and UNION ALL can't be mixed
    Show answer

    Answer: B (2)

    Set operations of the same precedence are evaluated left to right. SELECT 1 UNION SELECT 1 removes the duplicate, leaving one row, and UNION ALL then appends another 1 without deduplicating, giving two rows.

    try it in the quiz →

  6. 6.

    Which of these queries fails in PostgreSQL?

    easy
    1. ASELECT dept_id, count(*) FROM emp GROUP BY dept_id HAVING count(*) > 1
    2. BSELECT dept_id AS d, count(*) FROM emp GROUP BY d
    3. CSELECT dept_id, count(*) FROM emp WHERE count(*) > 1 GROUP BY dept_id
    4. DSELECT count(*) FROM emp HAVING count(*) > 1
    Show answer

    Answer: C (SELECT dept_id, count(*) FROM emp WHERE count(*) > 1 GROUP BY dept_id)

    WHERE filters rows before grouping, so aggregates aren't allowed there ("aggregate functions are not allowed in WHERE"); the condition belongs in HAVING. PostgreSQL accepts a SELECT alias in GROUP BY, and HAVING without GROUP BY treats the whole table as one group.

    try it in the quiz →

  7. 7.

    Which rows does this query return?

    mid
    -- departments: (1, 'Eng'), (2, 'HR')      HR has no employees
-- employees:   ('Ada', dept 1, 200), ('Bob', dept 1, 90)
SELECT d.name, e.name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.salary > 100;
    1. AOnly Eng / Ada
    2. BEng / Ada and HR / NULL
    3. CEng / Ada, Eng / Bob and HR / NULL
    4. DEng / Ada and Eng / Bob
    Show answer

    Answer: A (Only Eng / Ada)

    The WHERE runs after the join. HR's NULL-padded row has e.salary = NULL, so NULL > 100 is unknown and the row is dropped, just like Bob's. To keep HR, move the salary condition into the ON clause.

    try it in the quiz →

  8. 8.

    In PostgreSQL, orders has 500 rows. What does the final query return?

    mid
    BEGIN;
TRUNCATE orders;
ROLLBACK;

SELECT count(*) FROM orders;
    1. A0, because TRUNCATE commits implicitly
    2. BAn error: TRUNCATE is not allowed in a transaction
    3. C0, because TRUNCATE bypasses the transaction
    4. D500, because the TRUNCATE is rolled back
    Show answer

    Answer: D (500, because the TRUNCATE is rolled back)

    In PostgreSQL, TRUNCATE is transactional, so rolling back restores the table. The "implicit commit" behavior is true of MySQL and Oracle, where TRUNCATE is treated as DDL that commits and can't be rolled back.

    try it in the quiz →

  9. 9.

    What is PostgreSQL's default transaction isolation level?

    easy
    1. ARead Uncommitted
    2. BRead Committed
    3. CRepeatable Read
    4. DSerializable
    Show answer

    Answer: B (Read Committed)

    PostgreSQL defaults to Read Committed: each statement sees a snapshot taken when that statement starts. Repeatable Read is MySQL InnoDB's default, and Serializable is the SQL standard's nominal default, but neither is PostgreSQL's.

    try it in the quiz →

  10. 10.

    In PostgreSQL, which anomaly is impossible at every isolation level, even when you request Read Uncommitted?

    hard
    1. APhantom read
    2. BNon-repeatable read
    3. CDirty read
    4. DSerialization anomaly
    Show answer

    Answer: C (Dirty read)

    PostgreSQL implements Read Uncommitted as Read Committed, so a transaction never sees another transaction's uncommitted changes. Non-repeatable reads, phantoms and serialization anomalies are all possible at Read Committed.

    try it in the quiz →

  11. 11.

    What is running on the two Jan 2 rows?

    mid
    -- sales(day, amount): (Jan 1, 10), (Jan 2, 20), (Jan 2, 5)
SELECT day, amount,
       sum(amount) OVER (ORDER BY day) AS running
FROM sales;
    1. A30 and 35
    2. B20 and 5
    3. C35 and 35
    4. D25 and 25
    Show answer

    Answer: C (35 and 35)

    With ORDER BY and no explicit frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which includes all peers with the same day. Both Jan 2 rows therefore see 10 + 20 + 5. A ROWS frame with a unique order would give 30 and 35.

    try it in the quiz →

  12. 12.

    A B-tree index exists on orders (customer_id, created_at). Which query can it help the least?

    mid
    1. AWHERE customer_id = 7
    2. BWHERE customer_id = 7 AND created_at > '2024-01-01'
    3. CWHERE customer_id = 7 ORDER BY created_at
    4. DWHERE created_at > '2024-01-01'
    Show answer

    Answer: D (WHERE created_at > '2024-01-01')

    The index is sorted by customer_id first, so without a condition on the leading column, matching created_at values are scattered across the whole index. The other three use the leftmost prefix, and the ORDER BY query can read rows already sorted.

    try it in the quiz →

  13. 13.

    What does this return?

    easy
    SELECT avg(x) FROM (VALUES (10), (NULL), (20)) AS v(x);
    1. A10
    2. B15
    3. CNULL
    4. DAn error
    Show answer

    Answer: B (15)

    Aggregate functions skip NULLs, so the average is (10 + 20) / 2 = 15. You would get 10 only if NULL were treated as 0, which would require avg(COALESCE(x, 0)).

    try it in the quiz →

  14. 14.

    In INSERT ... ON CONFLICT (sku) DO UPDATE SET qty = EXCLUDED.qty, what does EXCLUDED refer to?

    mid
    1. AThe row that was proposed for insertion
    2. BThe existing row that caused the conflict
    3. CA system table that logs rejected rows
    4. DRows skipped by an earlier DO NOTHING
    Show answer

    Answer: A (The row that was proposed for insertion)

    EXCLUDED is the row you tried to insert, which was excluded because of the conflict. The existing row is referenced by the table name or its alias, as in SET qty = inventory.qty + EXCLUDED.qty.

    try it in the quiz →

  15. 15.

    For a jsonb column payload, what are the types of payload -> 'user' and payload ->> 'user'?

    mid
    1. Ajsonb and jsonb
    2. Btext and jsonb
    3. Cjsonb and text
    4. Djson and text
    Show answer

    Answer: C (jsonb and text)

    -> returns the field as jsonb (so you can keep navigating), and ->> returns it as text. That's why comparisons against string literals usually use ->>, and chains look like payload -> 'user' ->> 'plan'.

    try it in the quiz →

  16. 16.

    Which PostgreSQL index type can speed up WHERE payload @> '{"type": "buy"}' on a jsonb column?

    mid
    1. AGIN
    2. BB-tree
    3. CHash
    4. DBRIN
    Show answer

    Answer: A (GIN)

    GIN is an inverted index over the keys and values inside each document, and its jsonb operator classes support containment (@>). B-tree and hash indexes compare whole values, and BRIN only stores block-range summaries.

    try it in the quiz →

  17. 17.

    A multi-terabyte, append-only log table is queried by created_at ranges, and rows arrive in time order. Which index is by far the smallest while still useful?

    hard
    1. AHash
    2. BGIN
    3. CGiST
    4. DBRIN
    Show answer

    Answer: D (BRIN)

    BRIN stores only a min/max summary per range of table blocks, so it's tiny, and it works well when values correlate with physical order, as timestamps do in an append-only table. Hash can't do ranges, and GIN and GiST index every row.

    try it in the quiz →

  18. 18.

    Jobs 1 and 2 are queued. Worker A runs this and keeps its transaction open. What does worker B get when it runs the same statements?

    hard
    BEGIN;
SELECT id FROM jobs
WHERE status = 'queued'
ORDER BY id
LIMIT 1
FOR UPDATE SKIP LOCKED;
    1. AJob 1, the same row as A
    2. BJob 2
    3. CIt waits until A commits
    4. DAn error: the row is locked
    Show answer

    Answer: B (Job 2)

    SKIP LOCKED silently skips rows locked by other transactions, so B gets the next unlocked job. Plain FOR UPDATE would make B wait, and FOR UPDATE NOWAIT would raise an error instead.

    try it in the quiz →

  19. 19.

    In PostgreSQL, which ids does the final query return?

    mid
    CREATE TABLE t (id serial PRIMARY KEY, v text);
INSERT INTO t (v) VALUES ('a');
BEGIN;
INSERT INTO t (v) VALUES ('b');
ROLLBACK;
INSERT INTO t (v) VALUES ('c');
SELECT id FROM t;
    1. A1, 2
    2. B1, 2, 3
    3. C1, 3
    4. D2, 3
    Show answer

    Answer: C (1, 3)

    nextval is never rolled back, so the aborted insert still consumed 2 and 'c' gets 3. Gaps in sequence-generated ids are normal and shouldn't be relied on either way.

    try it in the quiz →

  20. 20.

    What does REFRESH MATERIALIZED VIEW CONCURRENTLY require in PostgreSQL?

    mid
    1. AA unique index on the materialized view
    2. BA primary key on every base table
    3. CAutovacuum enabled on the materialized view
    4. DThe view to be defined WITH CHECK OPTION
    Show answer

    Answer: A (A unique index on the materialized view)

    A concurrent refresh computes the new result and applies only the differences, so readers aren't blocked. To match old and new rows it needs a unique index on the materialized view, using plain columns and no WHERE clause; without one the command fails.

    try it in the quiz →

  21. 21.

    In PostgreSQL, what happens physically when you UPDATE a row?

    hard
    1. AIt is overwritten in place, and the old value goes to an undo log
    2. BThe whole page is locked and rewritten in place
    3. CThe change is queued and applied later by VACUUM
    4. DA new row version is written, and the old one is expired
    Show answer

    Answer: D (A new row version is written, and the old one is expired)

    PostgreSQL's MVCC writes a new tuple and sets xmax on the old one, which stays for snapshots that still need it and later becomes a dead tuple for VACUUM to reclaim. In-place updates with an undo log are how Oracle and MySQL InnoDB do it.

    try it in the quiz →

esc