Advanced SQL quiz

Question 1

What is the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()?

Accepted Answer

RANK() skips numbers for ties; DENSE_RANK() does not

Answer

ROW_NUMBER() skips numbers for ties; RANK() does not

Answer

DENSE_RANK() skips numbers for ties; RANK() does not

Answer

All three produce identical results when there are no ties

Question 2

Which isolation level prevents phantom reads?

Accepted Answer

SERIALIZABLE

Answer

READ COMMITTED

Answer

REPEATABLE READ

Answer

READ UNCOMMITTED

Question 3

What does EXPLAIN ANALYZE reveal that EXPLAIN alone does not?

Accepted Answer

EXPLAIN ANALYZE executes the query and shows actual vs estimated stats

Answer

EXPLAIN ANALYZE shows the logical plan; EXPLAIN shows the physical plan

Answer

EXPLAIN ANALYZE only works on INSERT/UPDATE statements

Answer

EXPLAIN ANALYZE disables query caching automatically

Question 4

What is a SARGable predicate?

Accepted Answer

WHERE name = 'Alice'

Answer

WHERE UPPER(name) = 'ALICE'

Answer

WHERE LEFT(name, 3) = 'Ali'

Answer

WHERE SUBSTRING(name, 1, 3) = 'Ali'

Question 5

How does NOT EXISTS typically outperform NOT IN when nulls are present?

Accepted Answer

NOT IN returns no rows if the subquery contains NULLs

Answer

NOT EXISTS returns no rows if the subquery contains NULLs

Answer

NOT IN and NOT EXISTS are always equivalent

Answer

NOT IN is always faster than NOT EXISTS

Question 6

What is the difference between optimistic and pessimistic locking?

Accepted Answer

Optimistic locking checks for conflicts at commit time

Answer

Optimistic locking acquires locks before reading data

Answer

Pessimistic locking checks version columns at commit time

Answer

Both strategies prevent all forms of data races

Question 7

What is the difference between DELETE, TRUNCATE, and DROP?

Accepted Answer

DELETE fires triggers; TRUNCATE does not in most databases

Answer

TRUNCATE can be filtered with a WHERE clause

Answer

DROP only removes the data, not the table structure

Answer

DELETE is faster than TRUNCATE for large tables

Question 8

What is a composite index and why does column order matter?

Accepted Answer

The leftmost prefix principle governs which queries can use a composite index

Answer

The order of columns in a composite index does not matter

Answer

A composite index on (a,b) supports queries filtering only on column b

Answer

Composite indexes are always slower than single-column indexes

Question 9

Explain NULLIF and COALESCE with practical examples.

Accepted Answer

NULLIF returns NULL when both arguments are equal

Answer

COALESCE returns the last non-NULL value in its argument list

Answer

NULLIF returns the second argument when arguments differ

Answer

COALESCE is not ANSI standard SQL

Question 10

What are CTEs as optimization barriers in PostgreSQL?

Accepted Answer

Before PostgreSQL 12, CTEs were optimization fences that prevented predicate pushdown

Answer

CTEs are always faster than subqueries in all PostgreSQL versions

Answer

CTEs are never materialized in any version of PostgreSQL

Answer

The MATERIALIZED keyword was removed in PostgreSQL 14

Question 11

What is the difference between UNION and UNION ALL?

Accepted Answer

UNION ALL is faster because it skips deduplication

Answer

UNION ALL removes duplicate rows

Answer

UNION keeps all duplicates from both result sets

Answer

UNION and UNION ALL produce identical output when both tables have no duplicates

Question 12

Explain the difference between correlated and non-correlated subqueries.

Accepted Answer

A correlated subquery is re-executed for each row of the outer query

Answer

A correlated subquery runs once and its result is reused

Answer

A non-correlated subquery references columns from the outer query

Answer

Correlated subqueries are always faster than joins

Question 13

What are database transactions and what does ACID mean?

Accepted Answer

Atomicity means all operations in a transaction succeed or all fail

Answer

Durability means that transactions can be rolled back after a crash

Answer

Isolation means a transaction must satisfy all database constraints

Answer

Consistency means each transaction runs in a separate database connection

Test yourself