PrepPilotPractise free

SQL Essentials interview questions

Joins, grouping, indexes, transactions and query tuning

18 questions · SELECT · updated November 2026

Practise these as flashcards. One or two lines each; the ones you miss come back first.

Start free

Core questions

What does the LIMIT clause do in a SQL query?

LIMIT restricts the number of rows returned by a query. For example, LIMIT 10 will only return the first 10 rows from the result set.

How do you check for NULL values in SQL?

You use IS NULL or IS NOT NULL to check for NULL values. Comparisons like '=' or '!=' don't work with NULL because NULL means unknown.

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows with matching keys in both tables, while LEFT JOIN returns all rows from the left table and fills in NULLs for missing matches on the right.

Why would you use GROUP BY in a query?

GROUP BY is used to aggregate data by grouping rows based on one or more columns, allowing you to apply aggregate functions like COUNT or SUM to each group.

Intermediate questions

Explain a common mistake when writing joins.

A common mistake is forgetting to specify the join condition, which results in a Cartesian product and can return millions of rows unexpectedly. Another mistake is misunderstanding how NULLs behave in outer joins.

When should you use HAVING instead of WHERE?

HAVING is used to filter groups after aggregation, while WHERE filters rows before grouping. For example, to filter departments with more than 10 employees, you use HAVING COUNT(*) > 10.

What is a covering index and why is it useful?

A covering index includes all columns needed for a query, so the database can satisfy the query using only the index without accessing the table. This improves performance, especially for read-heavy workloads.

How does the order of columns in a composite index affect query performance?

The order matters because the index can only efficiently filter or sort based on the leading columns. Queries must match the leftmost columns to benefit from the index.

What is the purpose of an execution plan?

An execution plan shows how the database will execute a query, including which indexes are used, join methods, and whether any full table scans occur. It helps diagnose performance issues.

What is the difference between a subquery and a CTE?

A subquery is a nested query inside another query, while a CTE (Common Table Expression) uses WITH to define a temporary result set. CTEs make complex queries more readable and maintainable.

How would you find the second highest salary in a table?

You can use a subquery: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); or use window functions for more advanced cases.

What is a window function and how is it different from aggregate functions?

Window functions perform calculations across a set of rows related to the current row, like ranking or running totals, without collapsing groups. Aggregate functions summarize groups into single values.

How would you find duplicate rows in a table?

See the answer in the app, free

How do indexes hurt performance?

See the answer in the app, free

Senior questions

Describe how you would debug a slow query using EXPLAIN.

See the answer in the app, free

What are the trade-offs between normalization and denormalization?

See the answer in the app, free

Explain the different isolation levels and their impact on concurrency.

See the answer in the app, free

How do deadlocks occur and how can you prevent them?

See the answer in the app, free

More Developer fundamentals topics