quizzMonkey
Start free

SQL Interview Mock sample questions with answers

10 questions from the SQL Interview Mock practice bank, spread across its domains. Pick your answer, then open the explanation to see why each option is right or wrong.

  1. Question 1Querying

    Adding GROUP BY status to the previous query (where no row matches the WHERE clause) still returns one row with a count of 0.

    • A

      True

    • B

      False

    Show the answer and explanation

    Answer: B

    With GROUP BY, the result has one row per group. Zero input rows form zero groups, so the query returns an empty result, not a row with 0. Dashboards that need a 0 for every status must start from a list of statuses and LEFT JOIN the counts onto it.

    Why the other options are wrong

    • A. With GROUP BY there is one row per group, and there are no groups.

  2. Question 2Engine and Production

    Which statements about PostgreSQL's REPEATABLE READ level are true? (Choose two.)

    Choose 2.

    • A

      Rows inserted and committed by others after its snapshot never appear in its queries

    • B

      Two transactions can each read data and write disjoint rows, producing write skew

    • C

      It can read another transaction's uncommitted rows under heavy load

    • D

      A concurrent update to the same row is silently overwritten

    • E

      It behaves identically to SERIALIZABLE in PostgreSQL

    Show the answer and explanation

    Answer: A and B

    PostgreSQL implements REPEATABLE READ as snapshot isolation: a stable snapshot (no non-repeatable reads, no phantoms) and first-updater-wins conflicts on the same row. It is stronger than the standard requires for phantoms, but it still allows write skew, which only SERIALIZABLE prevents.

    Why the other options are wrong

    • C. Dirty reads are impossible at every PostgreSQL level.

    • D. PostgreSQL raises error 40001 instead of losing the other update.

    • E. SERIALIZABLE adds SSI checks that catch write skew; RR does not.

  3. Question 3SQL Foundations

    Which statements about identity columns and serial in PostgreSQL are true? (Choose two.)

    Choose 2.

    • A

      Identity columns are SQL-standard syntax, while serial is a PostgreSQL shorthand

    • B

      With GENERATED ALWAYS, an explicit id needs OVERRIDING SYSTEM VALUE

    • C

      Identity columns guarantee gap-free numbers

    • D

      serial automatically adds a UNIQUE constraint to the column

    • E

      An identity value is given back to the sequence if the transaction rolls back

    • F

      GENERATED BY DEFAULT rejects explicit values just like ALWAYS

    Show the answer and explanation

    Answer: A and B

    Prefer identity columns: they are standard, the sequence is tied tightly to the column, and ALWAYS prevents accidental manual values. Neither form guarantees consecutive numbers, and neither enforces uniqueness on its own; add PRIMARY KEY or UNIQUE.

    Why the other options are wrong

    • C. Both use sequences, and sequence values are not returned on rollback, so gaps are normal.

    • D. serial only sets a default; you must add PRIMARY KEY or UNIQUE yourself.

    • E. nextval is never rolled back, which is how gaps appear.

    • F. BY DEFAULT accepts explicit values, like serial does.

  4. Question 4Advanced SQL

    In PostgreSQL, what does SELECT ceil(-1.5), floor(-1.5); return?

    • A

      -1 and -2

    • B

      -2 and -1 (both rounded away from zero)

    • C

      -1 and -1

    • D

      -2 and -2

    Show the answer and explanation

    Answer: A

    ceil returns the smallest integer not less than the argument, and floor returns the largest integer not greater than it. For negatives that means ceil(-1.5) = -1 and floor(-1.5) = -2. trunc always moves toward zero (trunc(-1.7) = -1).

    Why the other options are wrong

    • B. This swaps them; ceil of a negative number moves toward zero.

    • C. floor rounds down to the next lower integer, which is -2.

    • D. ceil rounds up, and -1 is greater than -1.5.

  5. Question 5Querying

    Which three statements about aggregates and NULL are true in PostgreSQL? (Choose three.)

    Choose 3.

    • A

      COUNT(DISTINCT x) ignores NULL values

    • B

      MAX(x) over a column with only NULLs returns NULL

    • C

      SUM(x) returns NULL if any input value is NULL

    • D

      GROUP BY puts all NULL values of the key in a single group

    • E

      COUNT(x) returns NULL when every x is NULL

    Show the answer and explanation

    Answer: A and B and D

    NULL inputs are skipped by aggregates. When nothing non-NULL remains, aggregates return NULL except COUNT, which returns 0. GROUP BY and DISTINCT treat NULLs as equal, so they form one group. SUM does not become NULL because of a single NULL input.

    Why the other options are wrong

    • C. NULL inputs are skipped; the sum of the rest is returned.

    • E. COUNT returns 0, never NULL.

  6. Question 6Engine and Production

    An int column customer_id has a B-tree index. An ORM sends the filter as a numeric literal: WHERE customer_id = 42.0. The plan is a Seq Scan with Filter: ((customer_id)::numeric = 42.0). Why?

    • A

      Numeric literals are not allowed to use any index in PostgreSQL

    • B

      The column is cast to numeric, so the integer index no longer matches

    • C

      The planner rounds 42.0 to 42 and finds no matching rows

    • D

      Statistics on customer_id are missing, so the planner gives up on the index

    Show the answer and explanation

    Answer: B

    When the operand types differ, PostgreSQL resolves the operator by casting one side. Here it casts customer_id to numeric (the wider type), wrapping the indexed column in an expression. Passing parameters with the column type, as in = 42 or $1::int, keeps the predicate sargable. The same thing happens with customer_id::text = '42'. Verified on PostgreSQL 18.

    Why the other options are wrong

    • A. Numeric comparisons can use indexes on numeric columns; the issue is the type mismatch.

    • C. No rounding happens; the comparison is done in numeric and returns correct rows slowly.

    • D. This is a type-resolution problem, not a statistics problem.

  7. Question 7SQL Foundations

    What does this return in PostgreSQL?

    SELECT 0.1::float8 + 0.2::float8 = 0.3 AS f,
       0.1::numeric + 0.2::numeric = 0.3 AS n;
    • A

      f = true, n = true

    • B

      f = false, n = true

    • C

      f = true, n = false

    • D

      f = false, n = false

    Show the answer and explanation

    Answer: B

    real and double precision are IEEE 754 binary types, so values such as 0.1 are approximations. numeric/decimal stores exact decimal digits, which is why money and quantities that must add up exactly belong in numeric (or in integer cents), never in float.

    Why the other options are wrong

    • A. Binary floating point cannot represent 0.1 and 0.2 exactly, so their float sum misses 0.3.

    • C. numeric stores decimal digits exactly, so 0.1 + 0.2 is exactly 0.3.

    • D. Only the binary floating-point comparison is inexact here.

  8. Question 8Advanced SQL

    A query defines WINDOW w AS (PARTITION BY dept) and WINDOW f AS (ORDER BY salary ROWS BETWEEN 1 PRECEDING AND CURRENT ROW). Which uses are valid in PostgreSQL? (Choose two.)

    Choose 2.

    • A

      ROW_NUMBER() OVER (w ORDER BY salary DESC)

    • B

      SUM(salary) OVER f

    • C

      SUM(salary) OVER (f)

    • D

      ROW_NUMBER() OVER (w PARTITION BY name)

    • E

      ROW_NUMBER() OVER (f ORDER BY name)

    Show the answer and explanation

    Answer: A and B

    A named window can be referenced bare (OVER w) or copied and extended (OVER (w …)). When copying, you may add an ORDER BY or frame only if the base lacks them; you can never change its PARTITION BY. A base that already has a frame can only be used bare. PostgreSQL reports "cannot copy window \"f\" because it has a frame clause".

    Why the other options are wrong

    • C. Copying a window that has a frame clause is an error.

    • D. A referenced window's PARTITION BY cannot be overridden.

    • E. f already has an ORDER BY, and it cannot be overridden.

  9. Question 9Querying

    In PostgreSQL, a data-modifying statement in WITH, such as WITH d AS (DELETE FROM u RETURNING *) SELECT 1, still runs to completion even if the main query never reads d.

    • A

      True

    • B

      False

    Show the answer and explanation

    Answer: A

    The documentation guarantees that data-modifying statements in WITH are executed exactly once and always to completion, independently of how much of their output the main query reads. Running the example deletes every row of u while the main query returns just 1.

    Why the other options are wrong

    • B. Unlike SELECT CTEs, which may be skipped if unused, DELETE/INSERT/UPDATE in WITH always runs fully.

  10. Question 10Engine and Production

    With the session set to tenant a, the app role runs an INSERT. What happens?

    CREATE POLICY p ON docs
  USING (tenant = current_setting('app.tenant', true));
-- no WITH CHECK clause; app has INSERT on docs

SET app.tenant = 'a';
INSERT INTO docs (id, tenant, body) VALUES (3, 'b', 'z');
    • A

      It succeeds, because USING only filters which rows a query can read

    • B

      It fails with "new row violates row-level security policy"

    • C

      It succeeds, but the row is invisible to tenant a afterwards

    • D

      It is silently skipped, and the INSERT reports zero rows

    Show the answer and explanation

    Answer: B

    A policy created without FOR applies to all commands. When it has no WITH CHECK, PostgreSQL uses the USING expression to check rows being inserted or updated. A tenant therefore cannot write rows for another tenant. Verified in PostgreSQL 18: the INSERT, and an UPDATE moving a row to tenant b, both fail with "new row violates row-level security policy".

    Why the other options are wrong

    • A. For an ALL policy without WITH CHECK, the USING expression also checks new rows.

    • C. The row is rejected before it is written, so nothing is stored.

    • D. Inserts that fail a policy check raise an error rather than being skipped.

Practise all 450 SQL questions

Start with the free 15-question diagnostic. It shows where to focus, and your results carry over if you sign up.

Go to SQL