JobsDart LogoJobsDart
InterviewsVerified & Peer Reviewed

SQL interview questions and answers

Almost every data and backend interview includes SQL. These are the concepts and query patterns that come up most, with short answers and examples you can practise on any PostgreSQL or MySQL database.

J
JobsDart Editorial
2 min read

Key Takeaways

  • Master joins, GROUP BY with HAVING, and window functions — they cover most practical questions.
  • Know the difference between WHERE and HAVING, and between INNER, LEFT and FULL joins.
  • Be able to find duplicates, the Nth highest value, and running totals from memory.
  • For senior roles, expect questions on indexes, query plans and transactions.

Core concept questions

Frequently asked SQL concept questions with short answers
QuestionShort answer
WHERE vs HAVING?WHERE filters rows before grouping; HAVING filters groups after aggregation.
INNER vs LEFT JOIN?INNER returns only matching rows; LEFT returns all rows from the left table with NULLs where there is no match.
DELETE vs TRUNCATE vs DROP?DELETE removes selected rows and can be filtered; TRUNCATE removes all rows quickly; DROP removes the table itself.
UNION vs UNION ALL?UNION removes duplicates; UNION ALL keeps them and is faster.
Primary key vs unique key?Both enforce uniqueness; a table has one primary key, which cannot be NULL, but can have many unique constraints.
What is normalisation?Organising tables to reduce redundancy, typically to third normal form, so each fact is stored once.
What is an index?A data structure (often a B-tree) that speeds up lookups on columns at the cost of extra storage and slower writes.
What is a transaction?A group of statements that succeed or fail together, following ACID properties.

Query patterns you should write from memory

These examples use a simple employees(id, name, department, salary, hired_at) table.

Window functions explained

Window functions calculate a value across a set of related rows without collapsing them into one row like GROUP BY does. ROW_NUMBER gives a unique sequence, RANK leaves gaps after ties, and DENSE_RANK does not. LAG and LEAD read the previous or next row, which makes month-over-month comparisons easy.

Advanced and performance questions

  • How would you find why a query is slow? (Read the execution plan with EXPLAIN or EXPLAIN ANALYZE, look for sequential scans and large sorts.)
  • When would an index not be used? (Low selectivity, functions on the column, leading wildcard LIKE, or small tables.)
  • What is a composite index and why does column order matter?
  • Explain isolation levels and problems like dirty reads and phantom reads.
  • What is the difference between a CTE and a subquery, and when is a recursive CTE useful?
Actionable Implementation

Practical Steps & Action Plan

1

Set up a practice database

Install PostgreSQL locally or use a free online SQL sandbox with sample data.

2

Drill the patterns

Write each query in this guide without notes until it takes under two minutes.

3

Explain while you type

Practise narrating your approach, as interviewers score reasoning as well as correctness.

Frequently Asked Questions

Which SQL dialect should I learn for interviews?

Standard SQL with PostgreSQL or MySQL is enough for most interviews. Syntax differences are minor and interviewers rarely penalise them.

Are window functions asked for analyst roles?

Yes. ROW_NUMBER, RANK and running totals are among the most common analyst interview topics.

Further Reading & Primary Sources

Referenced in These Guides

JobsDart First-Party Tools

Put This Into Practice with JobsDart Tools

Test and apply the principles from this guide directly using our dedicated career tools. Designed to eliminate guesswork and surface verified opportunities.

JobsDart Verified Job Discovery

Direct Recruiter Review

Explore verified global, remote, and corporate tech openings with direct hiring team review and real-time application tracking.

Browse Active Roles

JobsDart Free ATS Resume Checker

Instant Analysis

Validate that your resume formatting and keyword taxonomy match enterprise hiring standards before submitting.

Check Resume Match
J

JobsDart Editorial

Platform Research & Technical Editorial Board

Researched and audited by the JobsDart Technical Career Advisory Team. All guides, ATS benchmarks, and career roadmaps are tested against empirical hiring data, enterprise ATS parsers, and verified recruiter workflows.

Reviewed under JobsDart Technical Editorial StandardsLearn more about our methodology →
All Career Guides