Top SQL Interview Questions & Answers (2026)

I'd use a window function in a subquery: SELECT department_id, employee_id, salary, DENSE_RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS rnk FROM employees, then wrap it and filter WHERE rnk = 2. You can't filter on a window function in the same query's WHERE because it's calculated after WHERE. The ties question decides the function. DENSE_RANK gives the second distinct salary, so two people tied at the top don't push the next salary to third. RANK would leave a gap, so there might be no rank two at all. ROW_NUMBER picks exactly one row per position, arbitrarily among ties unless I add a tiebreaker.

SUM(amount) OVER (ORDER BY sale_date) gives a running total, and adding PARTITION BY store_id restarts it per store. The gotcha: when you specify ORDER BY without a frame, the default frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. RANGE treats rows with the same ORDER BY value as peers, so if there are several rows for one date, they all get the same total, including all of that day's sales. If I want it to grow row by row I write ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW and add a unique tiebreaker to the ORDER BY. Often the cleaner answer is to aggregate to one row per day first.

I choose by what the question means. If I need columns from both tables, it's a join. If I just need to check whether related rows exist, EXISTS or IN is clearer and avoids a common bug: joining customers to orders to find customers with orders duplicates customers who ordered several times, while EXISTS returns each customer once. A correlated subquery references the outer query, like WHERE EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id), so logically it's evaluated per outer row. Modern optimizers, Oracle's included, often rewrite these into joins anyway, so I focus on correctness first, then check the plan. For not-exists checks I use NOT EXISTS because of NOT IN's NULL problem.

UNION combines results and removes duplicate rows, which means the database has to sort or hash the combined set. UNION ALL keeps every row and skips that work. If the two queries can't overlap, say this year's and last year's partitions, UNION ALL is faster and I'd use it. If duplicates are possible and must be removed, UNION is correct. Both need the same number of columns with compatible types, and column names come from the first query. ORDER BY goes once at the end. Oracle also has INTERSECT and MINUS; other databases call MINUS EXCEPT, and recent Oracle releases accept EXCEPT too, but MINUS is what you'll see in most Oracle code.

A B-tree index stores the key values in sorted order with pointers to the rows, so the database can find a few matching rows without scanning the whole table. It helps most for selective filters and join columns. It often won't be used when the column is wrapped in a function, like UPPER(last_name), unless there's a function-based index; when there's an implicit type conversion, like comparing a VARCHAR2 column to a number; with a leading wildcard like '%son'; or when the filter matches so many rows a full scan is cheaper. In Oracle, a regular B-tree index doesn't store entries where all key columns are NULL, so IS NULL searches can't use a single-column index.