45 SQL Interview Questions and Answers to Know
Hiring for a data analyst, backend developer, or BI role means one thing eventually: you have to check if the candidate actually knows SQL. Skimming a resume tells you nothing about whether someone can write a working JOIN or explain the difference between a subquery and a CTE under pressure. That's why every recruiter and hiring manager needs a reliable set of sql questions for an interview ready to go, whether you're screening candidates yourself or briefing a technical panel.
This guide gives you exactly that. Below you'll find 45 SQL interview questions and answers, ranging from basic syntax checks to scenario-based questions that separate candidates who memorized definitions from those who've actually built queries against messy production data. Each answer is written so you can evaluate a response quickly, even if you're not a SQL expert yourself.
If you're managing a high volume of technical roles, this list also pairs well with tools built for scale. Olibr's AI hiring suite for technical screening can run structured technical questions like these across hundreds of candidates and hand you scored transcripts instead of raw video, cutting the time you spend deciding who deserves a second-round interview.
1. Basic SQL and database fundamentals questions
Every technical screen should start here. Basic sql questions for an interview filter out candidates who can't tell a primary key from a foreign key, and they set the baseline before you move into harder territory. If someone stumbles on these, don't waste panel time on window functions.
Sample questions and answers
- What is SQL, and what's the difference between SQL and MySQL? SQL is the language used to query relational databases; MySQL is one specific database engine that implements it, alongside PostgreSQL, SQL Server, and Oracle.
- What's the difference between DELETE, TRUNCATE, and DROP? DELETE removes rows and can be rolled back, TRUNCATE removes all rows quickly and resets identity counters, DROP removes the entire table structure permanently.
- What is a primary key vs a foreign key? A primary key uniquely identifies each row in a table; a foreign key references a primary key in another table to enforce a relationship.
- What's the difference between WHERE and HAVING? WHERE filters rows before grouping, HAVING filters groups after aggregation.
- Write a query to find the second-highest salary in an employee table.
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
A candidate who can explain why that subquery works, not just recite it, understands SQL rather than memorized it.
Why interviewers ask these
Recruiters ask these fundamental questions on SQL for interview rounds because they expose candidates who padded a resume with buzzwords but never wrote a real query against a live database. A confident, fast answer signals genuine hands-on experience; a hesitant one usually means bootcamp-level exposure only. These questions also give non-technical recruiters a fair shot at screening, since the answers are short enough to check against a reference sheet without needing a developer on the call. Finally, they establish vocabulary. If a candidate can't define a primary key clearly, later questions about joins and normalization will only get harder to evaluate fairly, so it's worth spending the first five minutes of any interview here, backed by a wider bank of basic MySQL questions for beginners, before moving to more advanced material.
2. SQL joins interview questions
Joins trip up more candidates than any other topic on this list, mostly because people memorize the four types without ever tracing what happens to unmatched rows. SQL joins interview questions are where you find out if a candidate can actually combine tables correctly or if they'll ship a query that silently drops half your data in production.
Sample questions and answers
- What's the difference between INNER JOIN and LEFT JOIN? INNER JOIN returns only rows with matches in both tables; LEFT JOIN returns all rows from the left table plus matches from the right, with NULLs where there's no match.
- When would you use a RIGHT JOIN instead of rewriting it as a LEFT JOIN? Rarely in practice, most teams standardize on LEFT JOIN for readability and swap table order instead.
- What does a FULL OUTER JOIN return? All rows from both tables, matched where possible and NULL-filled where not.
- Write a query to find employees with no assigned department.
SELECT e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id
WHERE d.id IS NULL;
If a candidate can't explain why that WHERE clause has to check the right-side table, they don't understand LEFT JOIN, they've just memorized the syntax.
Why interviewers ask these
Joins show up in nearly every real query a data analyst or backend developer writes, so weak join logic means bugs in dashboards, reports, and APIs down the line. These interview questions and answers of SQL also reveal whether a candidate thinks about data relationships or just pattern-matches syntax from memory.
3. Aggregation, grouping, and filtering questions
Grouping and aggregation questions catch candidates who can write a SELECT statement but freeze the moment you ask them to summarize data across categories. Aggregation, grouping, and filtering questions matter because almost every business dashboard, from headcount reports to revenue breakdowns, depends on GROUP BY logic working correctly the first time.
Sample questions and answers
- What's the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT column)? COUNT(*) counts all rows, COUNT(column) skips NULLs in that column, COUNT(DISTINCT column) counts only unique non-null values.
- Write a query to find departments with more than 10 employees.
SELECT dept_id, COUNT(*) AS headcount
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 10;
- How does GROUP BY interact with ORDER BY? GROUP BY collapses rows into groups first; ORDER BY then sorts the resulting summary rows, not the original data.
- What happens if you SELECT a column that isn't in the GROUP BY clause or an aggregate function? Most databases throw an error, though MySQL historically allowed it and returned unpredictable values.
A candidate who confuses WHERE and HAVING in a grouped query will produce reports with silently wrong totals.
Why interviewers ask these
Businesses run on aggregated numbers, so SQL questions asked in interview rounds lean heavily on GROUP BY and HAVING to confirm a candidate won't hand a stakeholder a broken summary metric. Getting these wrong in production usually means a dashboard quietly reports the wrong revenue or headcount for weeks before anyone notices, which makes this one of the highest-stakes basics to verify early.
4. Window functions interview questions
Window functions separate candidates who only know basic SQL from those who can write analyst-grade queries. Unlike GROUP BY, a window function lets you calculate a running total, rank, or average without collapsing individual rows, which is exactly what modern reporting tools expect. Window functions interview questions are increasingly common in data analyst and BI screens because so much real work now involves ranking, cohort analysis, or year-over-year comparisons that plain aggregation can't handle.
Sample questions and answers
- What's the difference between RANK(), DENSE_RANK(), and ROW_NUMBER()? RANK() leaves gaps after ties, DENSE_RANK() doesn't, ROW_NUMBER() assigns a unique sequential number regardless of ties.
- What does PARTITION BY do inside a window function? It resets the calculation for each group, similar to GROUP BY, but without collapsing rows.
- Write a query to find the top 2 highest-paid employees per department.
SELECT name, dept_id, salary
FROM (
SELECT name, dept_id, salary,
RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
FROM employees
) ranked
WHERE rnk <= 2;
A candidate who reaches for a window function instead of a messy self-join usually has real production experience.
Why interviewers ask these
Teams ask these sql interview questions and answers because window functions show up constantly in reporting work, and a candidate who avoids them will write slower, clunkier queries using self-joins or correlated subqueries instead, which is why analyst screens lean on a dedicated set of SQL questions for analyst roles.
5. Subqueries, CTEs, and set operations questions
Subqueries and CTEs test whether a candidate can break a complicated problem into smaller, readable steps instead of writing one unreadable nested query. Subqueries, CTEs, and set operations questions also reveal whether someone understands correlated versus non-correlated subqueries, a distinction that matters a lot once query performance is on the line.
Sample questions and answers
- What's the difference between a subquery and a CTE (Common Table Expression)? A subquery is nested inline and evaluated once per outer row if correlated; a CTE is defined once with WITH and can be referenced multiple times, improving readability.
- What's a correlated subquery? A subquery that references a column from the outer query, so it re-executes for every row processed.
- What's the difference between UNION and UNION ALL? UNION removes duplicate rows across result sets, UNION ALL keeps every row including duplicates and runs faster.
- Write a CTE to find employees earning above their department's average salary.
WITH dept_avg AS (
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
)
SELECT e.name, e.salary
FROM employees e
JOIN dept_avg d ON e.dept_id = d.dept_id
WHERE e.salary > d.avg_salary;
Candidates who default to UNION ALL without thinking about duplicates usually haven't debugged a reporting mismatch before.
Why interviewers ask these
Queries built entirely from nested subqueries are hard to maintain, so these interview questions and answers on SQL check whether a candidate writes code someone else can read six months later. They also expose whether a candidate understands performance tradeoffs between rewriting a query as a join, a CTE, or a correlated subquery.
6. Indexing and query performance questions
Performance questions catch candidates who can write a query that returns correct results but takes ten minutes to run against a real table with millions of rows. Indexing and query performance questions matter for any role touching production databases, because a slow query in a batch job is annoying, but a slow query behind a live API is a customer-facing outage.
Sample questions and answers
- What is an index, and why does it speed up queries? An index is a separate data structure that lets the database find rows without scanning the whole table, similar to a book's index pointing you to a page instead of reading cover to cover.
- Can too many indexes hurt performance? Yes, every INSERT, UPDATE, or DELETE has to update each index too, so write-heavy tables slow down as index count grows.
- What's the difference between a clustered and a non-clustered index? A clustered index determines the physical order of rows on disk, and a table can only have one; a non-clustered index is a separate lookup structure, and a table can have several.
- How would you find out why a query is slow? Run EXPLAIN or EXPLAIN ANALYZE to see the execution plan, check for full table scans, and confirm the right columns are indexed.
A candidate who reaches for EXPLAIN before guessing at a fix is thinking like an engineer, not a script kiddie.
Why interviewers ask these
These sql interview questions with answer sets separate candidates who write correct-but-slow queries from those who write queries that scale, which matters most once a database moves past a few thousand rows, and they pair well with intermediate MySQL questions on indexing and tuning.
7. Database design, keys, and normalization questions
Database design questions check whether a candidate can build a schema that won't collapse under real-world data, not just query one someone else already built. Database design, keys, and normalization questions matter most for backend developers and data engineers, since a poorly normalized schema creates duplicate data and update anomalies that no amount of clever querying can fully fix later.
Sample questions and answers
- What is normalization, and why does it matter? Normalization organizes tables to reduce data duplication and prevent update anomalies, typically following 1NF, 2NF, and 3NF rules.
- What's the difference between a composite key and a candidate key? A composite key uses multiple columns together to uniquely identify a row; a candidate key is any column or combination that could serve as the primary key.
- When would you intentionally denormalize a database? For read-heavy reporting systems where join performance matters more than storage efficiency or write consistency.
- What's the difference between a natural key and a surrogate key? A natural key comes from real business data, like an email address; a surrogate key is an artificial identifier, usually an auto-incrementing integer, generated purely to identify rows.
A candidate who can explain when to break normalization rules on purpose understands database design, not just the textbook definitions.
Why interviewers ask these
Schemas outlive the person who wrote them, so these sql questions and answers for interview rounds reveal whether a candidate thinks ahead about maintainability, or builds something that only works until the data doubles in size.
8. Transactions, constraints, and data integrity questions
Transactions decide whether your data stays consistent when something fails halfway through an operation, like a payment that debits one account but never credits another. Transactions, constraints, and data integrity questions matter for any role touching financial data, inventory systems, or anything where a half-completed write could cost real money.
Sample questions and answers
- What are the ACID properties? Atomicity, Consistency, Isolation, and Durability, the four guarantees that keep a transaction reliable even if the system crashes mid-operation.
- What's the difference between COMMIT and ROLLBACK? COMMIT saves all changes in a transaction permanently; ROLLBACK undoes them if something goes wrong before the commit happens.
- What's a deadlock, and how would you prevent one? A deadlock happens when two transactions each hold a lock the other needs; prevention usually means accessing tables in a consistent order across all transactions.
- What's the difference between UNIQUE and PRIMARY KEY constraints? Both enforce uniqueness, but a table allows only one primary key while it can have multiple unique constraints, and unique columns can accept a single NULL value.
A candidate who can't explain isolation levels probably hasn't debugged a race condition in production.
Why interviewers ask these
Recruiters include these sql interview questions answers because backend systems fail in ways that basic SELECT queries never expose. A candidate who understands transactions won't ship code that leaves a database in a half-updated, inconsistent state during an outage, and for Oracle-heavy stacks you can extend this round with PL/SQL interview questions on procedures and error handling.
9. Advanced and scenario-based SQL questions
This is where you separate candidates who know syntax from candidates who can actually solve a messy, real-world data problem under time pressure. Advanced and scenario-based SQL questions ask candidates to combine everything from earlier rounds, joins, aggregation, window functions, into one query that mirrors what they'd actually be asked to build on the job.
Sample questions and answers
- Write a query to find duplicate rows in a table.
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
- How would you find the third-highest value without using LIMIT or TOP? Use DENSE_RANK() in a subquery, then filter where the rank equals 3.
- A report is returning more rows than expected after a join. What's your first suspicion? A one-to-many relationship is fanning out rows, usually from a missing or incorrect join condition.
- How would you migrate a table's data without downtime? Create the new table, backfill in batches, dual-write during the transition, then cut over reads once verified.
Candidates who ask clarifying questions before writing a scenario query are showing you exactly how they'll behave with an ambiguous business request.
Why interviewers ask these
Hiring managers rely on interview questions and answers for SQL at this level because real tickets rarely arrive as clean textbook problems. These questions confirm a candidate can diagnose messy data, reason about tradeoffs, and communicate a plan, not just produce a correct answer in isolation, and you can push senior candidates further with advanced MySQL scenario questions.
Putting these answers into practice
Running through 45 questions manually works fine for one or two hires, but it breaks down fast when you're screening dozens of candidates for a SQL-heavy role every month. Consistency matters more than clever questions: the same core set, scored the same way, gives you a fair comparison across every candidate instead of a gut feeling shaped by whoever interviewed best that week. Treat this list as a rubric, not a script, and pair it with an actual query on a messy sample table whenever you can.
If you'd rather skip the manual screening entirely, Olibr's shared candidate network already includes verified SQL developers with documented skill data, so you're not starting from a blank resume pile. Head over to browse pre-vetted SQL developer profiles in India and start shortlisting candidates who've already proven they can do more than recite definitions.
For engineers
Find work worth your time.
Live engineering roles across India and the US, matched to your stack. Build a profile recruiters actually discover.