SQL Interview Questions and Answers

SQL interviews test basic commands (SELECT, INSERT, UPDATE, DELETE), filtering and sorting, JOIN types, GROUP BY with HAVING, subqueries, window functions such as ROW_NUMBER, keys and constraints, indexes and transactions. Write clear queries, explain each step, and check the result on a small example, including NULLs and duplicates.

Key facts

• SQL was named in about 758,000 of 46.9 million US job postings tracked by O*NET in 2025. (O*NET OnLine, Employer-Based Hot Technologies, 2026)
• US database administrators and architects had a median pay of $126,760 in 2025, with about 144,500 jobs. (U.S. Bureau of Labor Statistics, Occupational Outlook Handbook, Database Administrators and Architects, Aug 2026)
• US data scientists had a median pay of $120,230 in 2025 and are projected to grow about 35 percent from 2025 to 2035. (U.S. Bureau of Labor Statistics, Fastest growing occupations, Aug 2026)
• The UK National Careers Service recommends the STAR method (Situation, Task, Action, Result) for interview answers that need an example. (National Careers Service, The STAR method, 2026)
• For video interviews, the National Careers Service advises knowing how to share your screen, because you may have to do a practical task during the interview. (National Careers Service, How to do well in video interviews, 2026)

These SQL interview questions and answers cover what analysts, data scientists, developers and database administrators are asked most. SQL is one of the most requested technical skills: O*NET found it in about 758,000 US job postings in 2025. The answers below are short and accurate, with example queries you can run and adapt. Exact syntax can differ slightly between database systems; the examples use standard SQL.

SQL commands interview questions and answers: the basics

1. What are the main types of SQL commands?

• Data query: SELECT.
• Data manipulation: INSERT, UPDATE, DELETE.
• Data definition: CREATE, ALTER, DROP, TRUNCATE.
• Data control: GRANT, REVOKE.
• Transaction control: COMMIT, ROLLBACK.

2. What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes chosen rows and can use a WHERE clause. TRUNCATE removes all rows from a table but keeps the table. DROP removes the table itself, including its structure.

3. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping. HAVING filters groups after GROUP BY, so it can use aggregate functions.

SELECT department, COUNT(*) AS staff
FROM employees
WHERE active = 1
GROUP BY department
HAVING COUNT(*) > 10;

4. What is a primary key and a foreign key?

A primary key uniquely identifies each row and cannot be NULL. A foreign key is a column that refers to the primary key of another table, which keeps related data consistent.

5. What does NULL mean, and how do you test for it?

NULL means a value is missing or unknown. You test it with IS NULL or IS NOT NULL, never with = NULL, because comparisons with NULL do not return true.

6. What is the difference between UNION and UNION ALL?

UNION combines results and removes duplicates. UNION ALL keeps all rows, including duplicates, and is usually faster.

Joins

7. Explain the main JOIN types.

• INNER JOIN: only rows with a match in both tables.
• LEFT JOIN: all rows from the left table, with matches from the right or NULLs.
• RIGHT JOIN: all rows from the right table, with matches from the left or NULLs.
• FULL OUTER JOIN: all rows from both, matched where possible.
• CROSS JOIN: every combination of rows.
• SELF JOIN: a table joined to itself, for example employees and their managers.

8. How do you find customers who have never placed an order?

SELECT c.id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Aggregation and subqueries

9. Which aggregate functions do you use most?

COUNT, SUM, AVG, MIN and MAX, usually with GROUP BY. Note that COUNT(*) counts rows, while COUNT(column) ignores NULLs in that column.

10. What is a subquery?

A query inside another query. It can return a single value, a list for an IN condition, or a table in the FROM clause.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

11. What is a correlated subquery?

A subquery that refers to the outer query and runs once for each outer row. It can be slow on large tables; a JOIN or window function is often faster.

12. How do you find duplicate values?

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Window functions

13. What is a window function?

A function that calculates across a set of related rows without collapsing them into one row, using OVER. Examples are ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD and running totals with SUM OVER.

14. How do you get the second highest salary?

SELECT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r
  FROM employees
) t
WHERE r = 2;

DENSE_RANK handles ties, so equal top salaries still leave the next value as rank 2.

15. What is the difference between RANK and DENSE_RANK?

Both give tied rows the same rank. RANK then skips numbers (1, 1, 3); DENSE_RANK does not (1, 1, 2).

Performance and design

16. What is an index?

A data structure that helps the database find rows quickly, like an index in a book. Indexes speed up reads on filtered or joined columns but take space and slow down writes slightly.

17. What is normalisation?

Organising tables to reduce repeated data and keep it consistent, for example storing customer details once and referring to them by key. Sometimes tables are denormalised on purpose for faster reporting.

18. What is a transaction, and what does ACID mean?

A transaction is a group of statements that succeed or fail together. ACID stands for Atomicity, Consistency, Isolation and Durability, the properties that keep data correct even when many users work at once or a failure happens.

More SQL interview questions and answers

19. What is the order in which a SELECT query is processed?

Logically: FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then SELECT, then ORDER BY, then LIMIT or its equivalent. This is why you cannot use a column alias from SELECT inside WHERE in most systems.

20. What is the difference between CHAR and VARCHAR?

CHAR stores a fixed length and pads shorter values. VARCHAR stores variable length up to a limit. VARCHAR is usually better for text that varies in length.

21. What is a view?

A saved query that you can use like a table. Views simplify complex queries and can limit which columns users see. A standard view does not store data itself.

22. What is a stored procedure?

A saved set of SQL statements that runs on the database server. It can take parameters and is useful for repeated tasks, but logic in stored procedures can be harder to test and version than application code.

23. What is a CTE?

A common table expression, written with WITH, defines a named temporary result for one query. It makes long queries easier to read and allows recursive queries, such as walking a tree of managers and staff.

24. How do you get the top three products by sales in each category?

Use a window function: rank products within each category with ROW_NUMBER() OVER (PARTITION BY category ORDER BY sales DESC) in a subquery or CTE, then keep rows where the rank is 3 or less.

25. How do you calculate a running total?

Use SUM(amount) OVER (ORDER BY order_date). Add PARTITION BY to restart the total for each customer or month.

26. How do you compare each row with the previous one?

Use LAG, for example amount - LAG(amount) OVER (ORDER BY order_date) to get the change from the previous order.

27. What is a deadlock?

Two transactions each wait for a lock the other holds, so neither can continue. Databases detect this and cancel one transaction. Keeping transactions short and accessing tables in a consistent order helps prevent it.

28. How would you speed up a slow query?

Look at the query plan, add indexes on filtered and joined columns where they help, select only the columns you need, avoid functions on indexed columns in WHERE, replace slow correlated subqueries with joins or window functions, and check that table statistics are up to date.

29. What is SQL injection, and how do you prevent it?

An attack where user input is inserted into a query and changes its meaning. Prevent it by using parameterised queries or prepared statements, never by building SQL strings from user input.

30. How do you handle NULLs in calculations?

Use COALESCE to replace NULL with a default, such as COALESCE(discount, 0). Remember that most arithmetic with NULL returns NULL, and aggregates such as AVG ignore NULL values.

How to approach a live SQL task

1. Look at the tables first. Ask about keys, relationships and whether there can be NULLs or duplicates.
2. Restate the question as a sentence: for each customer, the total of paid orders this year.
3. Build step by step. Start with FROM and JOIN, check the rows, then add filters, grouping and ordering.
4. Test edge cases: customers with no orders, ties, NULL values and dates at month boundaries.
5. Explain trade-offs such as readability versus speed, and how you would check performance on a large table.

Questions by role

Data analyst

Expect business questions turned into queries: monthly revenue, active users, conversion rates, top products, retention by month. You may be asked to explain a number that looks wrong. Show that you check joins for duplicated rows, filter dates carefully and sanity-check totals against what the business expects.

Data scientist

Expect data preparation tasks: joining several tables into one clean dataset, handling missing values, building features with window functions and sampling data. Explain how SQL fits with the other tools you use for analysis and modelling.

Backend developer

Expect questions on schema design, indexes, transactions, avoiding SQL injection and how your application talks to the database. You may be asked how you would change a table without downtime or how you would find the cause of a slow page.

Database administrator

Expect deeper questions on backups and recovery, replication, permissions, monitoring, query plans and tuning. Show how you protect data and keep the database available.

Behavioural questions with SQL

Many interviews also ask about your experience: a time you found a data problem, a report that changed a decision, or a slow query you fixed. The UK National Careers Service recommends the STAR method for these answers: situation, task, action, result. Give numbers where you can, such as how much faster a query became or how many records you cleaned.

A one-week preparation plan

• Days 1 and 2: practise SELECT, filtering, sorting, joins and grouping on a sample database.
• Day 3: subqueries, CTEs and finding duplicates.
• Day 4: window functions such as ROW_NUMBER, RANK, LAG and running totals.
• Day 5: indexes, query plans, transactions and normalisation.
• Day 6: two timed practice tasks, explained aloud, and two project stories.
• Day 7: rest and a short review.

Common mistakes

• Using = NULL instead of IS NULL.
• Putting aggregate conditions in WHERE instead of HAVING.
• Joining tables that multiply rows and inflate totals.
• Ignoring ties when asked for the top results.
• Writing one huge query instead of building and checking step by step.
• Not asking which database system the company uses.

Example: an analyst prepares for a SQL test

This example was written for this guide and is not about a real person. Priya had used SQL for simple reports but felt unsure about window functions. She spent a week practising on a sample sales database, learning ROW_NUMBER, running totals and LAG. She also prepared a story about a report she fixed after finding duplicated rows from a bad join. In her test she built each query step by step, checked edge cases out loud and explained how she would speed up a slow query. She moved to the final round and received an offer for a data analyst role.

Writing readable SQL

Interviewers notice how your queries read. Put each main clause on its own line: SELECT, FROM, JOIN, WHERE, GROUP BY, HAVING, ORDER BY. Use clear table aliases, such as c for customers and o for orders, and name calculated columns with AS. Indent subqueries and CTEs so their structure is obvious. Avoid SELECT * in final answers; list the columns you need. Readable SQL is easier to check, easier to change and much easier for a reviewer to trust.

What interviewers really look for

A perfect query on the first try is not the goal. Interviewers want to see that you understand the data, ask the right questions, build the query in logical steps and check the result. They value candidates who notice traps, such as duplicated rows after a join or ties in a ranking, and who explain them calmly. If you get stuck, say what you are trying and try a simpler version first. Clear thinking and honest communication count for more than memorised syntax.

Checklist before your interview

• I can write joins, grouping and subqueries without notes.
• I can use ROW_NUMBER, RANK, DENSE_RANK, LAG and running totals.
• I can explain indexes, transactions and normalisation simply.
• I always test for NULLs, duplicates and ties.
• I know which database system the company uses, and its main differences.
• I have two project stories ready, using the STAR method.

Keep practising after the interview

Whatever the result, write down the questions you were asked and any query you found hard. Rebuild those queries at home on a sample database until they feel easy. If you are not offered the job, ask politely for feedback on your technical answers. SQL is a skill that grows with practice, and every interview shows you exactly what to work on next. Over a few weeks of steady practice, joins, grouping and window functions become second nature, and your confidence in the next interview will show in every answer you give.

Free ways to practise

You do not need expensive tools to practise SQL. Many database systems are free to install on your own computer, and several have sample databases with customers, orders and products ready to query. Create a small database of your own too, such as your monthly spending or a list of books, and write questions about it: which month cost the most, which category grew fastest, which items appear twice. Answering your own questions builds the habit of turning a plain-language problem into a clear query, which is exactly what interviewers test. Ten or fifteen minutes a day of this kind of practice, kept up for a few weeks, will make a clear difference to how calm and quick you feel in a real SQL interview or test.

Live on Dev Global Jobs

Right now there are 205,427 open jobs on Dev Global Jobs (live count, 23 October 2026). Browse technology and data jobs that ask for SQL, practise with the interview preparation tool, and show your projects in the CV builder.

Frequently Asked Questions

Sources

1. Employer-Based Hot Technologies, O*NET OnLine. Accessed 3 October 2026.
2. Occupational Outlook Handbook, Database Administrators and Architects, U.S. Bureau of Labor Statistics. Accessed 3 October 2026.
3. Fastest growing occupations, 2025 and projected 2035, U.S. Bureau of Labor Statistics. Accessed 3 October 2026.
4. The STAR method, National Careers Service (UK). Accessed 3 October 2026.
5. How to do well in video interviews, National Careers Service (UK). Accessed 3 October 2026.

Source: Dev Global Jobs, updated 23 October 2026.