SQL Interview Questions and Answers
A complete list of 416+ SQL interview questions and answers, organized by topic. Click any question to jump straight to its detailed answer with code examples.
9 topics 416 questions
SQL Query Basics
Joins
- What is a JOIN?
- What is the difference between INNER JOIN and OUTER JOIN?
- What is the difference between LEFT and RIGHT JOIN?
- What is a self join?
- What does a CROSS JOIN produce?
- How do you find rows with no match using a join?
- What is the difference between the ON and WHERE clauses in a join?
- Why does filtering the right table in WHERE turn a LEFT JOIN into an INNER JOIN?
- How do you join three or more tables?
- What does the USING clause do?
- What is a NATURAL JOIN and why is it risky?
- How do you combine a join with aggregation?
- Why do joins sometimes produce duplicate rows?
- What is the COUNT trap with LEFT JOIN?
- When should you use a join vs a subquery?
- What is the difference between EXISTS and IN?
- How do you write an anti-join?
- What is a semi-join?
- What does a FULL OUTER JOIN do?
- How do you emulate a FULL OUTER JOIN in MySQL?
- What is the difference between an equi-join and a non-equi join?
- How do you join on multiple columns?
- How do you use a self join to compare consecutive rows?
- What is the fan trap (row multiplication) in joins?
- Why and how do you pre-aggregate before joining?
- How do indexes affect join performance?
- What join algorithms do databases use?
- How do you handle NULLs from outer joins in the output?
- How do you remove duplicate rows from a join result?
- What is a LATERAL join (or CROSS APPLY)?
- What happens when you chain outer joins across three tables?
- What is the difference between a JOIN and a UNION?
- How do you join rows within a date or value range?
SELECT & WHERE
- What does a SELECT statement do?
- What does the WHERE clause do?
- What comparison operators can you use in WHERE?
- How do AND and OR precedence interact?
- What does SELECT DISTINCT do?
- How does the LIKE operator work?
- What does the IN operator do?
- How does BETWEEN work, and is it inclusive?
- How do you test for NULL values?
- What are column and table aliases?
- In what logical order are the clauses of a SELECT evaluated?
- Can you compute expressions in a SELECT list?
- How does the NOT operator behave with NULLs?
- How do you return a conditional value in SELECT?
- How do you do case-insensitive string filtering?
- How do you return just a few rows to preview a table?
- What is the difference between WHERE and HAVING?
Sorting & Limiting
- What does ORDER BY do?
- How do you control sort direction?
- How do you sort by multiple columns?
- Can you sort by an expression or alias?
- Where do NULLs sort in ORDER BY?
- How do you limit the number of rows returned?
- How do you paginate results with OFFSET?
- What is keyset (cursor) pagination and why is it faster?
- Why does OFFSET get slow for deep pages?
- How do you get the top N rows per group?
- How do you sort by a custom order?
- Why should you add a unique tie-breaker to ORDER BY?
- How do you include rows tied with the Nth row?
- How do you get one row per group (Postgres DISTINCT ON)?
- What is collation and how does it affect sorting?
- How do you return rows in random order?
Aggregation & GROUP BY
- What is an aggregate function?
- What does GROUP BY do?
- Why must non-aggregated SELECT columns appear in GROUP BY?
- What is the difference between COUNT(*) and COUNT(column)?
- How do aggregate functions handle NULLs?
- What does the HAVING clause do?
- Why can't you use an aggregate in WHERE?
- How do you aggregate conditionally (pivot)?
- Why might AVG return a truncated value?
- How do you group by more than one column?
- How do you count distinct values?
- What do MIN and MAX return on different types?
- Why does SUM sometimes return NULL instead of 0?
- Can you group by a computed expression?
- What do GROUPING SETS, ROLLUP and CUBE do?
- What is the FILTER clause on an aggregate?
- How do you avoid double-counting when aggregating over joins?
Set Operations
- What are SQL set operations?
- What is the difference between UNION and UNION ALL?
- What rules must the queries in a UNION satisfy?
- What does INTERSECT do?
- What do EXCEPT and MINUS do?
- How do set operations treat NULLs in deduplication?
- How do you sort or limit the result of a UNION?
- When do you use UNION instead of a JOIN?
- Why is UNION ALL faster than UNION?
- How do you emulate INTERSECT or EXCEPT without native support?
- What is the precedence of set operators when you chain them?
- Is there a difference between UNION and UNION DISTINCT?
- How do you tag which source each UNION row came from?
- Is SELECT DISTINCT the same as UNION for one query?
- What happens when column types differ across a UNION?
SQL Subqueries & CTEs
Subqueries
- What is a subquery in SQL?
- What are the main types of subqueries?
- What is a scalar subquery and where can you use it?
- What is a correlated subquery?
- What is the difference between a correlated and a non-correlated subquery?
- What is the difference between IN and EXISTS?
- Why can NOT IN behave unexpectedly with NULL values?
- What do the ANY and ALL operators do with subqueries?
- How are subqueries used in the WHERE clause?
- How do you use a subquery in the SELECT list?
- What is a derived table (subquery in the FROM clause)?
- When should you use a subquery versus a join?
- What are nested subqueries?
- Why does "subquery returns more than one row" error occur?
- In what order are subqueries executed?
- How can you improve the performance of a correlated subquery?
- Can you use a subquery in the HAVING clause?
- Can subqueries be used in INSERT, UPDATE, and DELETE statements?
- What is the difference between a subquery and a CTE?
Common Table Expressions (CTEs)
- What is a Common Table Expression (CTE)?
- What is the syntax of a CTE?
- What is the difference between a CTE and a subquery?
- What is the difference between a CTE and a view?
- Can you define multiple CTEs in one query?
- How do you chain CTEs to build a multi-step pipeline?
- What is a recursive CTE?
- What are the two parts of a recursive CTE?
- How do you prevent an infinite loop in a recursive CTE?
- Can a CTE be referenced multiple times in the same query?
- Are CTEs materialized or inlined?
- Do CTEs improve query performance?
- Can CTEs be used with INSERT, UPDATE, and DELETE?
- Why are CTEs often paired with window functions?
- What is the scope of a CTE?
- How do you rename a CTE's output columns?
- What is the difference between a CTE and a temporary table?
- What are common use cases for recursive CTEs?
- When should you avoid using a CTE?
SQL Window Functions
Window Function Basics
- What is a window function in SQL?
- What does the OVER clause do?
- What is the difference between a window function and GROUP BY?
- What does PARTITION BY do in a window function?
- How does PARTITION BY differ from GROUP BY?
- What is the role of ORDER BY inside the OVER clause?
- How do you compute a running total with a window function?
- Can regular aggregate functions be used as window functions?
- What are the main categories of window functions?
- In which clauses can window functions be used?
- At what point are window functions evaluated in query processing?
- What is a named window (the WINDOW clause)?
- Can you use DISTINCT inside a window function?
- What are the performance considerations of window functions?
- Why are window functions preferred over self-joins for analytics?
- How can you conditionally aggregate within a window?
- What does an empty OVER() clause mean?
- How do you filter rows based on a window function's result?
Ranking Functions
- What are ranking window functions?
- What does ROW_NUMBER() do?
- What is the difference between RANK() and DENSE_RANK()?
- How does ROW_NUMBER() differ from RANK() and DENSE_RANK()?
- What does NTILE() do?
- What does PERCENT_RANK() compute?
- What does CUME_DIST() compute and how does it differ from PERCENT_RANK()?
- Why do ranking functions require an ORDER BY in the OVER clause?
- How do you select the top N rows per group?
- How do you find the Nth highest value using ranking functions?
- How do you remove duplicate rows using ROW_NUMBER()?
- How can ROW_NUMBER() be used for pagination?
- How do you make ROW_NUMBER() deterministic when ordering values tie?
- How does PARTITION BY affect ranking functions?
- Why can't you filter directly on a ranking function in WHERE?
- What happens with NTILE() when rows don't divide evenly?
- How do you choose between ROW_NUMBER, RANK, and DENSE_RANK?
- How can ranking/distribution functions help compute a median?
Frames & Offset Functions
- What is a window frame?
- What is the difference between ROWS and RANGE in a frame?
- What is the default window frame when you specify ORDER BY but no frame?
- What is the window frame when there is no ORDER BY?
- What are the possible frame boundary keywords?
- How do you compute a moving average?
- What does the LAG() function do?
- What does the LEAD() function do?
- What are the optional arguments of LAG() and LEAD()?
- What do FIRST_VALUE() and LAST_VALUE() return?
- Why does LAST_VALUE() often return the current row instead of the last?
- What does NTH_VALUE() do?
- How does the frame turn an aggregate into a running vs windowed total?
- Why use LAG/LEAD instead of a self-join for row comparisons?
- How do offset/ranking functions solve gaps-and-islands problems?
- Can RANGE frames use intervals (e.g. time-based windows)?
- How do LAG/LEAD and FIRST_VALUE handle NULLs and partition edges?
- How do frames interact with PARTITION BY?
SQL Schema & Data Types
Data Types
- Why does picking the right data type matter?
- What are the main integer types and when do you choose each?
- What is the difference between NUMERIC/DECIMAL and FLOAT/REAL?
- What is the difference between CHAR, VARCHAR, and TEXT?
- What date and time types does SQL offer and how do they differ?
- How do databases handle boolean values?
- What is NULL and how does three-valued logic work in SQL?
- What are UUIDs and when should you use them as primary keys?
- When should you store data as JSON in a relational column?
- What are ENUM types and what are their trade-offs?
- How do you choose precision and scale for NUMERIC(p, s)?
- What is the difference between SERIAL and GENERATED AS IDENTITY?
- What is the best way to store monetary values in SQL?
- When would you store binary data in a SQL column?
- What is implicit type casting and why can it be dangerous?
- When should you use array columns (Postgres) instead of a child table?
DDL — Creating & Altering Tables
- What is DDL and how does it differ from DML?
- What are the essential parts of a CREATE TABLE statement?
- What can you do with ALTER TABLE and what are the risks?
- What is the difference between DROP TABLE and TRUNCATE?
- What is a SQL schema (namespace) and why use one?
- What is a sequence and how does it relate to auto-increment columns?
- What is the DDL to create and drop an index, and when does CREATE INDEX CONCURRENTLY matter?
- What are temporary tables and when should you use them?
- Can DDL statements be run inside a transaction and rolled back?
- What does CREATE TABLE IF NOT EXISTS do and why is it useful in migrations?
- How do you rename a table in SQL?
- What are generated (computed) columns?
- How do you create a partitioned table in Postgres?
- How do you create and replace a view?
- What is a materialized view and how does it differ from a regular view?
Constraints & Integrity
- What is a constraint and why use one?
- What is a PRIMARY KEY constraint?
- What is a FOREIGN KEY constraint and what does it enforce?
- What are ON DELETE / ON UPDATE referential actions on a foreign key?
- What is a UNIQUE constraint and how does it differ from a PRIMARY KEY?
- What is a CHECK constraint and what can it express?
- What does NOT NULL enforce and when should you omit it?
- What are deferrable constraints and when do you need them?
- What is an exclusion constraint in Postgres?
- Why should you name your constraints explicitly?
- What is a partial unique index and when does it replace a constraint?
- Can you disable a constraint temporarily and should you?
- How does a UNIQUE constraint relate to a unique index?
- What error do you get when a constraint is violated, and how do you handle it?
- What is the difference between a column-level and a table-level constraint?
Normalization
- What is database normalization and why does it matter?
- What are the three update anomalies normalization prevents?
- What is First Normal Form (1NF)?
- What is a functional dependency?
- What is Second Normal Form (2NF) and what does it fix?
- What is Third Normal Form (3NF) and what does it eliminate?
- What is Boyce-Codd Normal Form (BCNF) and how does it differ from 3NF?
- What is denormalization and when is it justified?
- What is a star schema and how does it differ from a normalized OLTP schema?
- How do you balance normalization and query performance in practice?
- What is the difference between a surrogate key and a natural key?
- What is Fourth Normal Form (4NF) and what does it address?
- How do you quickly check if a table is in 3NF?
- What is a junction (bridge) table and when do you use one?
- When should you deliberately stop normalizing?
SQL Modifying Data
INSERT, UPDATE & DELETE
- What is the syntax for inserting a row into a table?
- How do you insert rows from another table?
- What is an UPSERT and how do you write one in SQL?
- How do you silently ignore a duplicate row on INSERT?
- What is the syntax for updating rows in SQL?
- How do you update rows using values from another table?
- What is the DELETE statement and what happens if you omit WHERE?
- How do you delete rows based on a condition in a related table?
- What does the RETURNING clause do in Postgres?
- What is soft delete and how is it implemented in SQL?
- How do you safely delete millions of rows from a large table?
- When should you use TRUNCATE instead of DELETE?
- What is the difference between UPDATE and REPLACE in MySQL?
- How do you update a column to different values based on a condition?
- How do you use a CTE with INSERT, UPDATE, or DELETE?
Views
- What is a view in SQL?
- How do you create, replace, and drop a view?
- What are the main use cases for views?
- Can you INSERT, UPDATE, or DELETE through a view?
- What does WITH CHECK OPTION do on a view?
- When should you use a view vs a CTE?
- What is the difference between a view and a materialized view?
- Do views have a performance cost compared to writing the query inline?
- How do views implement row-level security?
- What happens to a view when the underlying table schema changes?
- Can a view be recursive?
- What is an indexed view in SQL Server?
- When would you use a table-valued function instead of a view?
- How do you find which tables and columns a view depends on?
- What does SCHEMABINDING do on a view in SQL Server?
SQL Transactions
Transactions & ACID
- What is a database transaction?
- What are the ACID properties?
- What do BEGIN, COMMIT, and ROLLBACK do?
- What is autocommit and how does it affect transactions?
- What is a SAVEPOINT and when would you use one?
- What does RELEASE SAVEPOINT do?
- What is an implicit transaction in SQL Server?
- What is the transaction log (WAL) and why does it matter?
- Why are long-running transactions harmful?
- What is a deadlock and how does the database resolve it?
- What is the difference between optimistic and pessimistic locking?
- What does SELECT FOR UPDATE do?
- How do you implement a concurrent job queue with SKIP LOCKED?
- What is two-phase commit (2PC) and when is it used?
- What are the most important best practices for writing transactions?
Isolation Levels & Concurrency
- What is transaction isolation?
- What are the three read phenomena isolation levels protect against?
- What is READ UNCOMMITTED and when (if ever) is it useful?
- What does READ COMMITTED guarantee and what can still go wrong?
- What does REPEATABLE READ guarantee?
- What does SERIALIZABLE isolation guarantee?
- What is MVCC and how does it enable concurrency without locking reads?
- What is write skew and how does SERIALIZABLE prevent it?
- What is a lost update and how do you prevent it?
- What is a phantom read and what isolation level prevents it?
- What are gap locks and next-key locks in MySQL InnoDB?
- What is the default isolation level in Postgres, MySQL, and SQL Server?
- How should application code handle a serialization failure?
- What is snapshot isolation and how does it differ from SERIALIZABLE?
- What lock modes do databases use and how do they interact?
SQL Indexes & Performance
Indexes
- What is a database index and how does it speed up queries?
- What is a B-tree index and what kinds of queries does it support?
- What is a composite index and what is the left-prefix rule?
- What is a covering index and how does it eliminate table lookups?
- What is a partial index and when does it help?
- When should you use a hash index instead of a B-tree?
- What is a GIN index and when do you use it in Postgres?
- What is index bloat and how do you fix it?
- When should you NOT add an index?
- When does the optimizer choose a sequential scan over an index scan?
- What is a functional (expression) index?
- How do you find and remove duplicate or unused indexes?
- What is the difference between a clustered and a non-clustered index?
- Should you always index a foreign key column?
- What index types are available in Postgres and when do you use each?
Query Optimization
- What does EXPLAIN do and how do you read its output?
- You see a Seq Scan on a large table — what do you check first?
- What is the N+1 query problem and how do you fix it in SQL?
- What are the three join strategies and when does the optimizer choose each?
- What are table statistics and how do you update them?
- Why is OFFSET-based pagination slow and what is the alternative?
- When does a subquery perform worse than a JOIN and how do you fix it?
- How can OR in a WHERE clause hurt performance and how do you fix it?
- Why should you avoid SELECT * in production queries?
- Can CTEs hurt query performance and how?
- What is table bloat and how does VACUUM address it?
- How do you set and enforce query timeouts in SQL?
- How do you proactively find slow queries and missing indexes in production?
- What is partition pruning and how does it improve query performance?
- What is an Index Only Scan and how do you enable it?
SQL Built-in Functions
String & Numeric Functions
- How do you concatenate strings in SQL?
- How do you extract a part of a string?
- How do you change string case in SQL?
- How do you remove whitespace or specific characters from a string?
- How do you replace occurrences of a substring?
- How do you get the length of a string in SQL?
- How do you search for a substring within a string?
- How do you round numeric values in SQL?
- What are MOD, ABS, and POWER and how do you use them?
- How do you convert a value from one data type to another?
- How do you pad a string to a fixed length?
- How do you format numbers and dates as strings?
- How do you split a delimited string in SQL?
- How do you aggregate multiple rows into a single string?
- How do you use regular expressions in SQL?
Date & Time Functions
- How do you get the current date and time in SQL?
- How do you add or subtract time from a date?
- What does DATE_TRUNC do and how is it used for grouping?
- How do you extract a specific part of a date (year, month, day)?
- How do you calculate the difference between two dates?
- How do you handle timezones when storing and querying timestamps?
- How do you format a date as a string?
- How do you generate a date series to fill gaps in time-series data?
- What is the INTERVAL type and how do you use it?
- What is the difference between now() and clock_timestamp() in Postgres?
- How do you efficiently query by time period without slowing down writes?
- How do you calculate time between consecutive events per user?
- How do you construct a date from year, month, and day components?
- How do you calculate a person's age or the time elapsed between two dates?
- How do you find rows where two date ranges overlap?
Conditional & NULL Functions
- What is the CASE expression and what are its two forms?
- What does COALESCE do?
- What does NULLIF do and when is it useful?
- What are IFNULL, NVL, and ISNULL — and how do they compare to COALESCE?
- What is IIF and when can you use it?
- Why do comparisons with NULL often return unexpected results?
- How does NULL affect aggregate functions?
- What is conditional aggregation and how do you use it?
- What are GREATEST and LEAST?
- How do you compare two values that may both be NULL?
- How does three-valued logic affect AND and OR with NULLs?
- How do you handle multiple conditional mappings cleanly?
- How do you safely cast a string to a number or date without raising an error?
- How can you use CASE in ORDER BY to create custom sort orders?
- How do you pivot rows into columns using CASE?
SQL Security & Integrity
Permissions & Roles
- What do GRANT and REVOKE do?
- What is the principle of least privilege and how does it apply to SQL?
- What are roles and how do they differ from users?
- What does WITH GRANT OPTION do?
- What is Row-Level Security (RLS) in Postgres?
- How do schema-level permissions work?
- Can you grant permissions on specific columns rather than whole tables?
- What are superuser privileges and why should you avoid using them for applications?
- How do you audit who changed what in a database?
- Why is the PUBLIC schema dangerous in Postgres and how do you secure it?
- How do you manage database user passwords securely?
- How do you restrict which hosts can connect to the database?
- How can views be used to implement access control?
- What are default privileges and why do they matter?
- How do you inspect what permissions a user or role currently has?
SQL Injection
- What is SQL injection?
- What are parameterised queries (prepared statements) and why do they prevent injection?
- Are ORM queries safe from SQL injection by default?
- What are the main types of SQL injection attacks?
- Can stored procedures be vulnerable to SQL injection?
- What is second-order (stored) SQL injection?
- Is input validation sufficient to prevent SQL injection?
- How do database error messages contribute to SQL injection risk?
- What role does a Web Application Firewall (WAF) play in preventing SQL injection?
- Does SQL injection also apply to NoSQL databases?
- What is mass assignment and how does it relate to database security?
- How do you detect SQL injection vulnerabilities in an existing codebase?
- How do you safely handle a dynamic ORDER BY clause?
- What is the difference between escaping and parameterising?
- How does least-privilege database access reduce SQL injection impact?
More ways to practice
The self-quiz is live. Join our channel for updates, new content & tech tips.