SQL appears in interviews for data analysts, data engineers, data scientists, backend developers and even product managers. Interviewers test both concepts and your ability to write correct queries under pressure. Here are the most common SQL interview questions, with concise answers.
Beginner SQL interview questions
What is the difference between WHERE and HAVING?
WHERE filters rows before grouping; HAVING filters groups after aggregation.
What are the types of joins?
INNER JOIN returns matching rows in both tables. LEFT JOIN returns all rows from the left table plus matches. RIGHT JOIN does the reverse. FULL OUTER JOIN returns all rows from both, matched where possible. CROSS JOIN returns every combination.
What is a primary key vs. a foreign key?
A primary key uniquely identifies each row. A foreign key references a primary key in another table, enforcing relationships.
What is the difference between DELETE, TRUNCATE and DROP?
DELETE removes selected rows and can be filtered. TRUNCATE removes all rows quickly. DROP removes the table itself.
Intermediate SQL interview questions
What is the difference between UNION and UNION ALL?
UNION removes duplicates; UNION ALL keeps them and is faster.
What is a subquery vs. a CTE?
A subquery is a query nested inside another. A common table expression (WITH clause) defines a named temporary result that makes complex queries more readable and can be referenced multiple times.
How do you find duplicate rows?
Group by the columns that define a duplicate and filter with HAVING COUNT(*) > 1.
What does COALESCE do?
It returns the first non-null value from a list — useful for replacing nulls with defaults.
What is normalization?
Organizing data to reduce redundancy — for example, storing customer details once rather than in every order row. Denormalization deliberately duplicates data to speed up reads, common in analytics.
Advanced SQL interview questions
What are window functions?
Functions that compute values across related rows without collapsing them, using OVER(). Examples: ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and running totals with SUM() OVER (ORDER BY …).
RANK vs. DENSE_RANK vs. ROW_NUMBER?
ROW_NUMBER assigns unique numbers. RANK gives ties the same rank and skips numbers. DENSE_RANK gives ties the same rank without gaps.
How do indexes improve performance?
Indexes let the database find rows without scanning the whole table, speeding reads at the cost of extra storage and slower writes.
How would you optimize a slow query?
Check the execution plan, add or adjust indexes, filter early, avoid SELECT *, reduce unnecessary joins, and consider partitioning large tables.
Common practical SQL problems
Interviewers often ask you to write queries such as:
- Find the second-highest salary
- Find employees who earn more than their managers (self-join)
- Count orders per customer and return the top 5
- Calculate month-over-month revenue growth using LAG
- Find customers who placed orders in consecutive months
- Return the latest record per user using ROW_NUMBER
- Calculate a 7-day rolling average
- Find users who signed up but never placed an order (LEFT JOIN with IS NULL)
Practice writing these from scratch, then explain your logic step by step.
Tips for SQL interviews
- Clarify table structures and expected output before writing
- Think about NULLs, duplicates and ties
- Write readable queries with CTEs and clear aliases
- Test your logic with a small example
- Mention performance considerations when relevant
Role guides: data analyst interview questions, how to become a data engineer.
Frequently asked questions
What SQL topics are asked most in interviews?
Joins, GROUP BY with aggregates, subqueries or CTEs, and window functions are the most common topics.
How can I practice SQL for interviews?
Use online SQL practice platforms, public datasets and a local database. Practice writing queries without autocomplete to simulate interview conditions.
Do I need to know a specific database for SQL interviews?
Most interviews use standard SQL. Knowing the dialect used by the company, such as PostgreSQL, MySQL or SQL Server, is a bonus.
Got the interview? Make sure your resume tells the same story.
Interviewers read your resume right before they talk to you. HiReXL aligns it with the job description’s keywords so your experience and your answers match. HiReXL is built by 4As Consulting, a cloud and DevOps consulting firm. Looking for your next role? Submit your resume for open positions with PanAmerica Inc.
