SQL Developer Interview Questions and Answers

Questions and model answers

9 questions you are likely to hear.

1. 1

   What is the difference between WHERE and HAVING?

   Why they ask: A fundamentals check on query processing order.

   Sample answer: WHERE filters rows before grouping and aggregation, while HAVING filters groups after aggregation. So WHERE cannot use aggregate functions, but HAVING can. For example, to find customers with more than five orders in 2026, I filter the order date in WHERE and then use HAVING COUNT(*) greater than 5 after GROUP BY customer_id. Putting row-level filters in WHERE is also more efficient, because fewer rows are grouped. In one report I moved a date filter from HAVING to WHERE and the query ran several times faster on a large orders table.

2. 2

   Explain clustered and non-clustered indexes.

   Why they ask: Index knowledge is central to SQL performance.

   Sample answer: A clustered index defines the physical order of rows in the table, so there can be only one, and the leaf level is the data itself. In SQL Server it is often the primary key. A non-clustered index is a separate structure with the key columns and a pointer to the row, and a table can have many. If a non-clustered index does not contain all needed columns, the engine does key lookups, which become expensive at volume. I add included columns to create covering indexes for frequent queries, but avoid too many indexes on write-heavy tables because each insert must update them.

3. 3

   Write a query to get a running total of sales by date.

   Why they ask: Window functions are asked in almost every SQL interview.

   Sample answer: I would aggregate daily sales first, then apply a window function: SELECT sale_date, daily_total, SUM(daily_total) OVER (ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total FROM a CTE that groups sales by sale_date. Specifying ROWS instead of the default RANGE avoids unexpected results with duplicate dates and performs better in SQL Server. If the running total should reset each month or per region, I would add PARTITION BY. I used this pattern for a finance dashboard showing month-to-date revenue against target.

4. 4

   How do you approach tuning a slow query?

   Why they ask: This is the core practical skill for SQL developers.

   Sample answer: I first capture the actual execution plan and IO and time statistics. I look for table scans on large tables, key lookups, big differences between estimated and actual rows, which point to stale statistics or parameter sniffing, and expensive sorts or hash spills. I check that predicates are sargable, for example no functions wrapped around indexed columns in WHERE. Fixes include adding or adjusting indexes, rewriting the query, updating statistics or breaking it into steps with temp tables. In one stored procedure, rewriting a YEAR() filter as a date range let it use an index and cut run time from 45 seconds to under 2.

5. 5

   What are transaction isolation levels, and which problems do they prevent?

   Why they ask: Concurrency correctness is essential in transactional systems.

   Sample answer: Isolation levels control how transactions see each other's changes. Read Uncommitted allows dirty reads. Read Committed, the default in SQL Server, prevents dirty reads but allows non-repeatable reads. Repeatable Read prevents those, but phantom rows can still appear. Serializable prevents phantoms too but increases blocking. Snapshot isolation and Read Committed Snapshot use row versioning so readers do not block writers. For a reporting workload that was blocking order inserts, enabling Read Committed Snapshot removed most blocking without changing application code, after we checked tempdb capacity.

6. 6

   How do you handle deadlocks?

   Why they ask: Deadlocks are a common production issue in busy databases.

   Sample answer: A deadlock happens when two transactions each hold a lock the other needs, and the database kills one as the victim. I capture deadlock graphs with Extended Events, using the system_health session in SQL Server, to see which statements and resources are involved. Common fixes are accessing tables in the same order in all procedures, keeping transactions short, adding indexes so queries lock fewer rows and using row versioning isolation. I also add retry logic for deadlock errors in the application. In one system, reordering updates in two procedures eliminated daily deadlocks during batch processing.

7. 7

   Write a query to find employees who earn more than their managers.

   Why they ask: Self joins are a classic interview test.

   Sample answer: I would self join the employees table: SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary FROM employees e INNER JOIN employees m ON e.manager_id = m.employee_id WHERE e.salary > m.salary. The inner join excludes employees without a manager, such as the CEO, which is correct here. I would confirm whether salary can be null, since comparisons with null return unknown and those rows would drop out. For a full reporting chain rather than direct managers only, I would use a recursive CTE.

8. 8

   When would you use a temp table versus a table variable or CTE?

   Why they ask: This tests practical understanding of query optimisation tools.

   Sample answer: A CTE is just a named query that improves readability and enables recursion; it is not stored, so if referenced several times it may run several times. Temp tables are stored in tempdb, support indexes and have statistics, so they are good for larger intermediate results used multiple times. Table variables have limited statistics, which historically led to poor estimates, so I use them for small sets. In a complex month-end procedure, materialising an intermediate result of a few million rows into an indexed temp table cut run time significantly compared to a chain of nested CTEs.

9. 9

   Tell me about a database problem you solved that had business impact.

   Why they ask: This shows ownership and real-world value.

   Sample answer: At a retail client, the nightly inventory sync took around five hours and often overran into store opening times. I traced it to a cursor-based stored procedure that processed products row by row with multiple lookups. I rewrote it as set-based logic with MERGE statements, added covering indexes for the lookup tables and batched updates in chunks of 50,000 rows to limit log growth and locking. The job dropped to under 40 minutes. Store managers stopped seeing stale stock levels in the morning, which reduced manual stock checks.

Keep going.

• Data Analyst Interview Questions and Answers
• ETL Developer Interview Questions and Answers
• Data Engineer Interview Questions and Answers
• SQL Job Support Online for Developers and Analysts
• Technical Interview Tips That Actually Help
• Coding interview help