SQL interview questions for testers

All examples use two small tables. Create them in any database to try the queries.

CREATE TABLE departments (id INT PRIMARY KEY, name VARCHAR(50));
CREATE TABLE employees (
  id INT PRIMARY KEY, name VARCHAR(50), salary INT,
  dept_id INT, manager_id INT, email VARCHAR(80)
);

Syntax shown is standard SQL or MySQL/PostgreSQL. Where products differ (for example LIMIT versus TOP), the answer says so.

Basics

1. Why does a tester need SQL?

To check that the application stored what the UI or API said it stored, to set up and clean up test data, to verify reports and calculations against the source, and to investigate a defect ("which rows are in this broken state?"). A tester who can query the database finds data bugs the screen hides.

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

DELETE removes selected rows (with WHERE), can be rolled back inside a transaction, and fires row triggers. TRUNCATE removes all rows quickly and resets identity counters; in most databases it cannot take a WHERE, and rollback support depends on the database. DROP removes the whole table, structure included.

3. Primary key, unique key and foreign key?

A primary key identifies each row: unique and never NULL, one per table. A unique key is also unique but allows NULL (how many depends on the database), and a table can have several. A foreign key points to a primary key in another table and keeps the data consistent: you cannot insert an order for a customer that does not exist.

4. WHERE vs HAVING?

WHERE filters rows before grouping. HAVING filters groups after aggregation, so it can use COUNT, SUM and so on.

-- departments with more than 3 people earning over 50000
SELECT dept_id, COUNT(*) AS people
FROM employees
WHERE salary > 50000
GROUP BY dept_id
HAVING COUNT(*) > 3;

Joins

5. Explain the types of joins.

INNER JOIN returns only rows that match on both sides. LEFT JOIN returns all rows from the left table plus matches from the right (NULL where none). RIGHT JOIN is the mirror image. FULL OUTER JOIN returns everything from both. CROSS JOIN returns every combination.

SELECT e.name, d.name AS department
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id;

Likely follow-up: Which employees have no department? Add WHERE d.id IS NULL to the left join above. This "find the orphans" pattern is one testers use constantly.

6. How do you find records in one table that have no match in another?

SELECT d.*
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
WHERE e.id IS NULL;      -- departments with no employees

NOT EXISTS is an equivalent form and is safe when the joined column may hold NULL.

7. What is a self join? Give an example.

A table joined to itself, using aliases. To list each employee with their manager's name:

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.id = e.manager_id;

The classic questions

8. Find the second highest salary.

-- works everywhere, handles ties
SELECT MAX(salary) FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

-- with a window function (PostgreSQL, MySQL 8+, SQL Server, Oracle)
SELECT salary FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees
) t WHERE r = 2 LIMIT 1;

For the Nth highest, change the 2 to N. Use DENSE_RANK rather than ROW_NUMBER so that two people on the same salary do not push the next salary down a rank.

Likely follow-up: What if there is no second highest? The query returns no row. Wrap it as a subquery or use COALESCE if the caller needs a NULL row.

9. Find duplicate emails.

SELECT email, COUNT(*) AS times
FROM employees
GROUP BY email
HAVING COUNT(*) > 1;

10. Delete duplicates and keep one.

Keep the row with the smallest id for each email.

DELETE FROM employees
WHERE id NOT IN (
  SELECT MIN(id) FROM employees GROUP BY email
);

In MySQL you cannot select from the table you are deleting from in the same statement. Wrap the subquery in a derived table: NOT IN (SELECT id FROM (SELECT MIN(id) AS id FROM employees GROUP BY email) x). Always run it as a SELECT first to see what would go.

11. Find the highest paid employee in each department.

SELECT e.*
FROM employees e
JOIN (SELECT dept_id, MAX(salary) AS top FROM employees GROUP BY dept_id) m
  ON m.dept_id = e.dept_id AND m.top = e.salary;

12. Get the top 3 salaries.

SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 3;   -- MySQL, PostgreSQL
SELECT DISTINCT TOP 3 salary FROM employees ORDER BY salary DESC;     -- SQL Server

NULLs and details

13. Why does WHERE column = NULL return nothing?

NULL means unknown, and nothing equals unknown, not even another NULL. Use IS NULL and IS NOT NULL. Also, COUNT(column) skips NULLs while COUNT(*) counts every row, and NOT IN with a list containing a NULL returns no rows at all, which is a classic source of wrong test results.

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

UNION combines results and removes duplicates, which costs a sort. UNION ALL keeps everything and is faster. Use UNION ALL unless you want de-duplication.

15. What is an index and how can it affect testing?

An index is a lookup structure that makes searches on a column faster, at the cost of slower writes and more storage. As a tester you care because a query that is fast on 100 rows may be slow on a million, so performance tests need production-sized data, and because a unique index is what actually prevents duplicate records.

Using SQL in testing

16. After a "place order" test passes on the UI, which queries would you run?

Check that exactly one order row exists for that user with the right total and status, that the line items add up to the total, that stock decreased by the quantity, that a payment record exists, and that no order exists in a half-finished state.

SELECT o.id, o.total, SUM(i.qty * i.price) AS items_total
FROM orders o JOIN order_items i ON i.order_id = o.id
WHERE o.user_id = 42
GROUP BY o.id, o.total
HAVING o.total <> SUM(i.qty * i.price);   -- should return no rows

17. What is a transaction and what are the ACID properties?

A transaction is a group of statements that succeed or fail together. ACID: Atomic (all or nothing), Consistent (rules and constraints still hold afterwards), Isolated (concurrent transactions do not see each other's half-done work), Durable (once committed it survives a crash). For testing: try to break atomicity by failing a payment half-way and check that no partial order remains.

More joins, subqueries and window functions

18. When do you use a subquery and when a join?

A join combines columns from two tables in one result. A subquery answers a question inside another query: "which employees earn more than the average?" Joins are often faster and easier to read when you need columns from both tables; a subquery is natural for a single value or a list to compare against. Both can usually be rewritten as the other.

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

19. What is a correlated subquery?

A subquery that refers to the outer query's current row, so it runs once per outer row. Example: employees who earn more than the average of their own department.

SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);

It reads well but can be slow on big tables; a join to a grouped query is the usual faster alternative.

20. What are window functions? Show ROW_NUMBER, RANK and DENSE_RANK.

They calculate over a set of rows related to the current row without collapsing the result into groups. ROW_NUMBER gives a unique number; RANK gives ties the same number and leaves gaps after them; DENSE_RANK gives ties the same number with no gaps.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees;

For salaries 90, 90, 80: rn is 1, 2, 3; rnk is 1, 1, 3; dense is 1, 1, 2.

21. How do you get the top earner in each department using a window function?

SELECT * FROM (
  SELECT e.*, RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS r
  FROM employees e
) t
WHERE r = 1;

PARTITION BY restarts the ranking for each department. Use RANK to keep ties, ROW_NUMBER for exactly one per department.

22. How do you calculate a running total?

SELECT order_date, amount,
       SUM(amount) OVER (ORDER BY order_date) AS running_total
FROM orders;

23. How does CASE work, and where do testers use it?

It is SQL's if-else inside a query. Testers use it to label or count categories in one pass.

SELECT
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END)   AS paid,
  SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) AS failed
FROM orders;

24. Find employees who earn more than their manager.

A self join with a comparison:

SELECT e.name AS employee, e.salary, m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON m.id = e.manager_id
WHERE e.salary > m.salary;

25. How do you find records created today, or in the last 7 days?

The syntax differs by database; know the idea and name the dialect.

-- PostgreSQL
SELECT * FROM orders WHERE created_at >= CURRENT_DATE;
SELECT * FROM orders WHERE created_at >= now() - interval '7 days';

-- MySQL
SELECT * FROM orders WHERE created_at >= CURDATE();
SELECT * FROM orders WHERE created_at >= NOW() - INTERVAL 7 DAY;

Mention time zones: a "today" report can be off by hours if the server stores UTC and the business works in IST.

26. How do you do pagination in SQL?

LIMIT and OFFSET (MySQL, PostgreSQL) or OFFSET ... FETCH (SQL Server, Oracle). Always add an ORDER BY with a unique tie-breaker, or pages can overlap or skip rows, which is the same bug as the page boundary bug in APIs (see API testing questions).

SELECT * FROM products ORDER BY id LIMIT 10 OFFSET 20;   -- page 3 of size 10

Design and safety

27. What are the types of index, and why can too many hurt?

A primary key usually has a clustered or unique index that defines the stored order. Other indexes (B-tree is the common type) speed up lookups, joins and sorting on their columns. Every index makes writes slower and takes space, so index the columns you filter and join on, not everything. A composite index helps queries that use its leading columns.

28. What is the difference between a view and a materialised view?

A view is a saved query; it runs every time you read it and always shows current data. A materialised view stores the result and must be refreshed, so reads are fast but can be stale. A tester checking a report built on a materialised view should check when it was last refreshed.

29. What is normalisation? Explain 1NF, 2NF and 3NF briefly.

Organising tables to avoid repeated data and update problems. 1NF: each cell holds one value and rows are unique. 2NF: every non-key column depends on the whole key (matters for composite keys). 3NF: non-key columns depend only on the key, not on other non-key columns. Example: keep a customer's address in a customers table, not repeated on every order. Reporting tables are sometimes denormalised on purpose for speed.

30. How do you change or delete test data safely?

Run the matching SELECT first and look at the rows. Use a transaction so you can roll back: BEGIN; UPDATE ...; SELECT ...; ROLLBACK; until you are sure. Always include a WHERE, and in a shared environment filter by a test marker (a test email domain) so you never touch other people's data. Never run write statements against production.

31. What is the difference between a stored procedure and a function?

A stored procedure is a saved block of SQL logic you call to do work; it can change data and return several results. A function returns a value and can be used inside queries. As a tester you check them by calling with normal, boundary and invalid inputs, and verify the data changes and the error handling.