Sql Queries Interview Questions With Answers

Let's Talk About SQL Interview Questions

I keep seeing people post generic lists of SQL interview questions online, and most of them are useless. They tell you to memorize definitions of INNER JOIN versus LEFT JOIN like you're studying for a college final. That's not how these interviews actually go. Real hiring managers dig into query writing, execution plans, and edge cases that break in production. I spent years fixing queries that looked fine on paper but tanked under real data volumes, so I'm going to walk you through what actually matters. Here's the thing nobody tells you: the hardest questions aren't about syntax. They're about understanding what your query does under the hood. Let me give you some real examples and explain why they come up. Question: How do you find duplicate records in a table?

The obvious answer is grouping by all columns and filtering where count is greater than one. But in practice, candidates blow it when you ask about performance. A subquery approach with GROUP BY works, but on a table with millions of rows it'll crawl. The better answer involves window functions. Here's what I'd actually write in an interview:

SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER(PARTITION BY column1, column2 ORDER BY id) as rn
  FROM my_table
) t WHERE rn > 1;

This gives you deduplicated results without scanning the table twice. I once worked with a dataset of roughly 40 million customer transactions where a naive GROUP BY approach took over eight minutes. The window function version ran in about 30 seconds. The difference was mainly because the subquery let us use an index on the partition columns instead of doing a full sort operation. Question: Explain the difference between WHERE and HAVING. This comes up constantly. Everyone gives the textbook answer. WHERE filters rows before grouping. HAVING filters after grouping. Simple. But here's the nuance that separates people who know SQL from people who understand query optimization: HAVING forces the database to compute aggregations first, which means it has to process more data before it can discard anything. If you can filter with WHERE instead of HAVING, do it. I've seen queries where swapping a HAVING clause for a WHERE clause dropped execution time from 45 seconds down to roughly two seconds on a moderate-sized table.

Question: Write a query to get the third highest salary from an Employee table. This is a classic. The naive approach uses a subquery with LIMIT and OFFSET. It works until someone asks you to make it work on databases that don't support LIMIT, or until they ask about performance. The clean answer uses a window function:

SELECT DISTINCT salary FROM (
  SELECT salary, DENSE_RANK() OVER(ORDER BY salary DESC) as rnk
  FROM employees
) ranked WHERE rnk = 3;

Notice I used DENSE_RANK instead of ROW_NUMBER. That's important because if two people tie for first salary, you still want the third distinct value to be the actual third highest, not a gap caused by duplicate ranks. Candidates who use ROW_NUMBER often miss this and produce wrong results on tied data. I got tripped up on this once during an interview three years ago. I gave the ROW_NUMBER answer and the interviewer asked what would happen if salaries were tied. I froze for about six seconds before switching to DENSE_RANK. Since then I never make that mistake again. Question: How do you delete duplicate rows without a unique identifier? This one reveals whether someone has actually dealt with messy real-world data. The standard approach is to use a Common Table Expression with ROW_NUMBER, then delete where the row number is greater than one. But here's the practical issue: if you're running this against a production table with foreign key constraints or triggers, a bulk DELETE can lock the entire table for an unacceptable amount of time. The workaround I use in production is to create a new table, insert the distinct rows using the CTE approach, drop the old table, rename the new one, and rebuild indexes. It takes longer but doesn't block other queries for minutes at a time.

Question: What are SQL joins and when would you use each type? RIGHT JOIN exists in most databases but almost nobody uses it. You can rewrite any RIGHT JOIN as a LEFT JOIN by flipping the table order, and it's cleaner to do so. Full OUTER JOIN is useful for finding mismatched records between two datasets. I use it regularly when doing data reconciliation between a source system and a data warehouse. The typical scenario is checking that every order in the source has a matching record in the warehouse and flagging any discrepancies. CROSS JOIN is the join type that causes the most problems in interviews because people don't understand when it's appropriate. It produces a Cartesian product. Use it when you need every combination of two sets, like generating a date dimension table or creating test data. Don't use it accidentally, because on large tables it'll chew through memory fast.

Questions That Actually Separate Juniors From Seniors

The real interview questions aren't about recalling syntax. They're about understanding trade-offs. Here are some that trip people up. How do you optimize a slow-running query? The answer isn't "add indexes." That's too vague. The proper approach is to look at the execution plan first. Check for table scans, missing index warnings, key lookups, and sort operations that shouldn't be there. Then consider whether the query structure can be rewritten. Often a simple rewrite does more for performance than throwing indexes at a problem. I had a report query that scanned roughly 12 million rows and took about 11 minutes. The issue wasn't a missing index. It was a function applied to a column in the WHERE clause that prevented index usage. Wrapping the column in a function like CONVERT or CAST forces a full scan because the database can't use the index anymore. Once I rewrote the condition to compare the raw column value instead, it dropped to about 15 seconds.

What's the difference between DELETE, TRUNCATE, and DROP? DELETE removes rows one at a time and logs each deletion. TRUNCATE removes all rows by deallocating pages and logs minimally. DROP removes the entire table structure. TRUNCATE is faster and uses fewer resources but you can't roll it back in some databases. In SQL Server you can roll back TRUNCATE inside a transaction, but in PostgreSQL you can't. This is one of those details that catches people off guard depending on which database they've been working in. I've seen people write TRUNCATE and then try to recover data afterward on PostgreSQL, not realizing it was permanent. Explain normal forms.

First normal form requires atomic values. Second normal form requires no partial dependency on a composite key. Third normal form requires no transitive dependency. But here's what textbooks don't tell you: strict normalization is often the wrong choice for analytical workloads. I've worked on data warehouses where fully normalized schemas caused query performance to deteriorate dramatically because of the excessive joins required. Denormalization, done deliberately, can reduce join overhead and improve read performance significantly. The key is knowing when each approach is appropriate rather than blindly following normalization rules. How do you handle recursive relationships in SQL? Recursive CTEs are the standard solution. A common example is an employee table where each row has a manager_id that references another employee. To get the full hierarchy, you'd use a recursive CTE that starts with the top-level employee and joins back to itself. The practical limitation is that some databases impose recursion depth limits. SQL Server defaults to 100 levels. Oracle defaults to unlimited but you can set a limit. If you're dealing with organizational structures that might exceed reasonable recursion depth, iterative approaches or materialized path patterns are safer alternatives.

What They're Really Testing

When interviewers ask SQL questions, they're testing whether you can translate a business requirement into a correct, reasonably efficient query. They want to see your thought process. If you get stuck, talk through your approach out loud. Say what you're trying to accomplish, mention the trade-offs you're considering, and acknowledge where you'd verify the result. I've interviewed candidates who wrote perfect queries but couldn't explain why they chose a particular approach, and I've interviewed candidates who struggled with syntax but demonstrated solid reasoning. The second person usually gets the offer. Prepare by writing queries by hand, not just reading them. Sit down with a blank sheet of paper or a plain text editor and write out solutions to common problems. You'll discover gaps in your understanding that looking at existing code never reveals. Practice explaining your answers as clearly as you can write them. The combination of correct syntax, awareness of performance implications, and clear communication is what actually moves candidates forward.