You are currently viewing Mastering SQL Interview Questions: What Mid to Senior-Level Candidates Need to Know

Mastering SQL Interview Questions: What Mid to Senior-Level Candidates Need to Know

If you’ve worked with databases for any meaningful amount of time, you know SQL is the foundation of all data decisions. Jobs that use SQL are plentiful like data analysts, backend developers, or data engineers.

As a technical recruiter, I’ve sat through dozens of interviews where SQL knowledge was thw determining factor to if a candidate got the job or not. I have included 18 of the most common SQL interview questions that I consistently see come up during interviews for mid to senior-level roles.

A more detailed, character-driven artwork featuring a developer working with a database, charts, and an SQL file, great for blog sections about writing queries, performance tuning, or developer workflows.

1. What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?

  • INNER JOIN returns only the matching rows between two tables.
  • LEFT JOIN returns all rows from the left table and matching rows from the right.
  • RIGHT JOIN returns all rows from the right table and matching rows from the left.
  • FULL OUTER JOIN returns all rows from both tables, with NULLs where there’s no match.

2. How do you identify duplicate rows in a SQL table?

You can use GROUP BY with HAVING:

sql

CopyEdit

SELECT column1, COUNT(*)

FROM table_name

GROUP BY column1

HAVING COUNT(*) > 1;

3. What’s the difference between WHERE and HAVING clauses?

  • WHERE filters rows before aggregation.
  • HAVING filters after aggregation.

Use WHERE with raw data, and HAVING with aggregated data.

4. Explain window functions and give a use case.

Window functions perform calculations across a set of rows related to the current row, without collapsing rows like GROUP BY does.

Example: ROW_NUMBER(), RANK(), LAG(), LEAD().

Use case: Finding the top-selling product per region using RANK() over a PARTITION BY.

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

  • DELETE: Removes specific rows; can be rolled back.
  • TRUNCATE: Removes all rows; can’t target specific rows; faster but less flexible.
  • DROP: Deletes the table structure entirely.

6. How do you optimize SQL queries for performance?

  • Use indexes wisely.
  • Avoid SELECT *; specify columns.
  • Use proper join types.
  • Filter early with WHERE.
  • Avoid subqueries when possible—prefer JOINs.

These optimizations come up often in SQL interview questions targeting senior-level candidates.

7. What’s the difference between clustered and non-clustered indexes?

  • Clustered Index: Sorts the actual data rows in the table. Only one allowed.
  • Non-clustered Index: Has a separate structure from data; can be many.

8. How do you handle NULLs in SQL?

Use IS NULL or IS NOT NULL for filters.

For calculations or conditions:

sql

CopyEdit

COALESCE(column, default_value)

NULLs are a common subject in SQL interview questions because they affect logic and results in subtle ways.

9. How would you return the second highest salary in a table?

sql

CopyEdit

SELECT MAX(salary)

FROM employees

WHERE salary < (SELECT MAX(salary) FROM employees);

Or use ROW_NUMBER() or DENSE_RANK() with a CTE.

10. What is a CTE and how is it different from a subquery?

A CTE (Common Table Expression) improves readability and can be referenced multiple times.

sql

CopyEdit

WITH cte_name AS (

  SELECT …

)

SELECT * FROM cte_name;

Subqueries are nested inside another SQL statement and harder to debug.

11. Explain the EXISTS vs IN clause.

  • IN works well for small, static sets.
  • EXISTS is better for correlated subqueries and large data sets.

EXISTS stops scanning once it finds a match, improving performance.

12. How would you pivot data in SQL?

Use conditional aggregation:

sql

CopyEdit

SELECT

  department,

  SUM(CASE WHEN gender = ‘M’ THEN 1 ELSE 0 END) AS Male,

  SUM(CASE WHEN gender = ‘F’ THEN 1 ELSE 0 END) AS Female

FROM employees

GROUP BY department;

13. What are some advanced joins or techniques you’ve used?

  • Self joins for hierarchical data.
  • Cross apply/outer apply in SQL Server.
  • Recursive CTEs for tree structures.
  • Anti-joins using LEFT JOIN + WHERE column IS NULL.

These advanced use cases come up often in SQL interview questions for candidates managing data pipelines or reporting systems.

14. What’s a good use case for a recursive CTE?

Traversing hierarchical structures like organizational charts, file systems, or category trees.

15. Explain normalization and denormalization.

  • Normalization: Reduces data redundancy (3NF is most common).
  • Denormalization: Combines tables to optimize read performance, common in reporting databases.

Expect this question if you’re interviewing for database design-heavy roles.

16. How do you handle schema changes in production environments?

  • Use version control for DDL scripts.
  • Perform changes in staging first.
  • Use transactions and backup strategies.
  • Monitor rollback plans and data integrity post-deployment.

17. What is a surrogate key and when would you use one?

A surrogate key is an auto-generated key (like INT IDENTITY or UUID) used as a primary key. It is not derived from business data.

Use it when natural keys are composite, large, or likely to change.

18. How do you audit changes in a SQL database?

  • Trigger-based logging.
  • Change Data Capture (CDC).
  • Temporal tables (SQL Server).
  • Manually store change history in audit tables.

This kind of question tests your production-readiness and often shows up in SQL interview questions for enterprise roles.

Final Thoughts

It does not matter if you are preparing for a role in analytics, backend engineering, or data architecture, mastering these SQL interview questions will give you the advantage. Companies are looking for people who can optimize queries, understand data models, and solve real problems. Use this guide to sharpen your thinking, deepen your skills, and stand out from the rest.

Leave a Reply