pbPassingBI
/
sql/exams/sql-exam-full

SQL full mock exam

21 questions sampled across all 7 modules. Timed like the real thing: answer everything, then review the explanations.

  1. What makes a column a primary key?

    1. It is the first column
    2. It uniquely identifies each row and is never null
    3. It is numeric
    4. It is indexed

    Answer: It uniquely identifies each row and is never null — Uniqueness and not-null are the defining properties.

  2. BETWEEN 100 AND 500 includes:

    1. 101 to 499
    2. 100 to 500
    3. 100 to 499
    4. 101 to 500

    Answer: 100 to 500 — BETWEEN is inclusive on both endpoints.

  3. SQL Server's equivalent of LIMIT 10 is:

    1. LIMIT 10
    2. TOP 10
    3. ROWNUM <= 10
    4. FIRST 10

    Answer: TOP 10 — SQL Server uses TOP; Oracle uses FETCH FIRST or ROWNUM.

  4. COUNT(*) is 500 and COUNT(email) is 480. Why?

    1. Duplicate rows
    2. 20 rows have a null email
    3. A join issue
    4. An index issue

    Answer: 20 rows have a null email — COUNT on a column ignores nulls.

  5. Which query is valid?

    1. SELECT region, city, SUM(t) FROM o GROUP BY region
    2. SELECT region, SUM(t) FROM o GROUP BY region
    3. SELECT region, city FROM o GROUP BY SUM(t)
    4. SELECT SUM(region) FROM o GROUP BY t

    Answer: SELECT region, SUM(t) FROM o GROUP BY region — Every non-aggregated column must appear in GROUP BY.

  6. Putting a row-level filter in HAVING instead of WHERE:

    1. Errors
    2. Usually works but is slower
    3. Is always faster
    4. Changes the result

    Answer: Usually works but is slower — More rows reach the grouping step and are then thrown away.

  7. Joining orders to line_items inflates SUM(total). Why?

    1. The join is wrong type
    2. Each order repeats once per line item
    3. Nulls are counted
    4. Missing index

    Answer: Each order repeats once per line item — The one-to-many relationship duplicates the order row.

  8. LEFT JOIN with WHERE right.status = 'x' behaves like:

    1. LEFT JOIN
    2. INNER JOIN
    3. CROSS JOIN
    4. FULL OUTER JOIN

    Answer: INNER JOIN — The NULL-extended rows fail the WHERE and are discarded.

  9. An INNER JOIN placed after a LEFT JOIN can:

    1. Speed up the query
    2. Eliminate the rows the LEFT JOIN preserved
    3. Change column order
    4. Have no effect

    Answer: Eliminate the rows the LEFT JOIN preserved — The inner join requires a match, discarding null-extended rows.

  10. Why does a join fail on values that look identical?

    1. Wrong data type
    2. Trailing whitespace or case differences
    3. Missing index
    4. Too many rows

    Answer: Trailing whitespace or case differences — TRIM and case normalisation on import prevent both.

  11. SELECT 7 / 2 in PostgreSQL returns:

    1. 3.5
    2. 3
    3. 4
    4. An error

    Answer: 3 — Integer division discards the remainder. Cast to get 3.5.

  12. Grouping monthly by a formatted string causes:

    1. An error
    2. Alphabetical sorting instead of chronological
    3. Duplicate rows
    4. Slower joins

    Answer: Alphabetical sorting instead of chronological — April sorts before January as text. Truncate to a date instead.

  13. A subquery referencing the outer query is:

    1. Scalar
    2. Correlated
    3. Derived
    4. Recursive

    Answer: Correlated — Correlated subqueries are evaluated per outer row.

  14. Derived tables in FROM usually require:

    1. An index
    2. An alias
    3. A GROUP BY
    4. A WHERE clause

    Answer: An alias — Most dialects reject an unaliased derived table.

  15. The main advantage of a CTE over nested subqueries is:

    1. Always faster
    2. Readable top-to-bottom structure and reuse
    3. Avoids indexes
    4. Allows aggregates

    Answer: Readable top-to-bottom structure and reuse — Performance depends on whether the engine inlines it.

  16. The main difference between a window function and GROUP BY:

    1. Speed
    2. Window functions retain every row
    3. Windows only work on numbers
    4. GROUP BY is deprecated

    Answer: Window functions retain every row — That retention is the whole point.

  17. With duplicate dates, RANGE differs from ROWS because:

    1. RANGE is faster
    2. RANGE includes all rows sharing the current ORDER BY value
    3. ROWS ignores nulls
    4. They are identical

    Answer: RANGE includes all rows sharing the current ORDER BY value — That is why moving averages should specify ROWS.

  18. LAG(revenue) on the first row returns:

    1. 0
    2. NULL unless a default is supplied
    3. The last row
    4. An error

    Answer: NULL unless a default is supplied — The third argument supplies a default.

  19. Which type should store a currency amount?

    1. FLOAT
    2. DECIMAL(10,2)
    3. REAL
    4. DOUBLE

    Answer: DECIMAL(10,2) — DECIMAL is exact; floating point accumulates rounding error.

  20. The difference between PRIMARY KEY and UNIQUE:

    1. None
    2. UNIQUE allows nulls, PRIMARY KEY does not
    3. PRIMARY KEY allows duplicates
    4. UNIQUE cannot be indexed

    Answer: UNIQUE allows nulls, PRIMARY KEY does not — A table also has only one primary key.

  21. DELETE differs from TRUNCATE because DELETE:

    1. Is faster
    2. Can be filtered with WHERE and rolled back
    3. Removes the table
    4. Resets identity columns

    Answer: Can be filtered with WHERE and rolled back — TRUNCATE empties everything and usually cannot be rolled back.