SQL Interview Questions for Testers: 50 Must-Know Q&As

SQL is one of the most underrated skills in a tester’s toolkit. Almost every application stores data in a database, and checking that data directly is often the fastest way to confirm whether a feature really works. That is why SQL interview questions for testers appear in manual, automation and API testing interviews alike — even for freshers.

This guide covers 50 questions: 10 concept questions, 20 practical queries and 20 advanced and test-data questions on a simple sample schema. Queries use MySQL 8 syntax; most work unchanged on PostgreSQL, SQL Server and Oracle, and differences are noted where they matter.

Key takeaways

• Joins, GROUP BY with HAVING, and subqueries are asked in nearly every round.
• Classic puzzles — second highest salary, finding duplicates — test your logic, so practise them by hand.
• Explain how you use SQL in testing: validating UI or API actions, preparing test data and checking data integrity.

Why testers need SQL

When you place an order in the UI or call a “create user” API, the response only tells you part of the story. A quick query confirms that the right rows were inserted, amounts were calculated correctly, statuses changed and nothing was duplicated. Testers also use SQL to find or create test data, verify data migrations and investigate the root cause of defects.

Sample tables used in this guide

CREATE TABLE departments (
    dept_id   INT PRIMARY KEY,
    dept_name VARCHAR(50) NOT NULL
);

CREATE TABLE employees (
    emp_id     INT PRIMARY KEY,
    name       VARCHAR(100) NOT NULL,
    email      VARCHAR(100),
    salary     DECIMAL(10,2),
    dept_id    INT,
    manager_id INT,
    join_date  DATE,
    FOREIGN KEY (dept_id) REFERENCES departments(dept_id)
);

SQL concept questions for testers (1–10)

1. What are DDL, DML, DCL and TCL?
DDL defines structure (CREATE, ALTER, DROP, TRUNCATE). DML changes data (INSERT, UPDATE, DELETE); SELECT is often grouped here or called DQL. DCL controls access (GRANT, REVOKE). TCL manages transactions (COMMIT, ROLLBACK, SAVEPOINT).

2. Primary key vs foreign key?
A primary key uniquely identifies each row and cannot be NULL. A foreign key references a primary key in another table to enforce relationships.

3. Primary key vs unique key?
A table has one primary key, which cannot be NULL. It can have several unique keys, which usually allow NULL values.

4. WHERE vs HAVING?
WHERE filters rows before grouping; HAVING filters groups after GROUP BY and can use aggregate functions.

5. DELETE vs TRUNCATE vs DROP?
DELETE removes selected rows and can use WHERE. TRUNCATE removes all rows quickly while keeping the structure. DROP removes the table entirely. Rollback behaviour differs by database.

6. What types of joins exist?
INNER, LEFT, RIGHT, FULL OUTER, CROSS and self joins. MySQL does not support FULL OUTER JOIN directly; you combine LEFT and RIGHT joins with UNION.

7. UNION vs UNION ALL?
UNION removes duplicate rows; UNION ALL keeps them and is faster.

8. How do you check for NULL?
Use IS NULL or IS NOT NULL. A comparison like salary = NULL never returns true.

9. What is an index?
A structure that speeds up lookups on columns, at the cost of extra storage and slower writes.

10. What is a transaction and ACID?
A transaction is a unit of work that fully succeeds or fully fails. ACID stands for atomicity, consistency, isolation and durability — important when testing payments or transfers.

Practical SQL queries (11–30)

11. Employees earning more than 50,000, highest first.

SELECT name, salary FROM employees
WHERE salary > 50000
ORDER BY salary DESC;

12. Names starting with “A”.

SELECT name FROM employees WHERE name LIKE 'A%';

13. Number of employees in each department.

SELECT dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id;

14. Departments with more than five employees.

SELECT dept_id, COUNT(*) AS emp_count
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 5;

15. Employee names with department names.

SELECT e.name, d.dept_name
FROM employees e
INNER JOIN departments d ON e.dept_id = d.dept_id;

16. Employees without a valid department (orphan records).

SELECT e.emp_id, e.name
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.dept_id
WHERE d.dept_id IS NULL;

17. Departments with no employees.

SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON d.dept_id = e.dept_id
WHERE e.emp_id IS NULL;

18. Each employee with their manager’s name (self join).

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

19. Second highest salary.

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

20. Nth highest salary (here N = 3) using a window function.

SELECT DISTINCT salary
FROM (
    SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) ranked
WHERE rnk = 3;

DENSE_RANK handles ties correctly. An alternative in MySQL is SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 2.

21. Find duplicate emails.

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

22. Delete duplicates, keeping the lowest emp_id (MySQL).

DELETE e1 FROM employees e1
JOIN employees e2
  ON e1.email = e2.email AND e1.emp_id > e2.emp_id;

Always run the matching SELECT first and never run deletes on shared environments without permission.

23. Highest salary in each department.

SELECT d.dept_name, MAX(e.salary) AS max_salary
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

24. Employees earning more than their department average (correlated subquery).

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

25. Top three earners.

SELECT name, salary FROM employees
ORDER BY salary DESC
LIMIT 3;   -- SQL Server: SELECT TOP 3 ...

26. Employees who joined in the last 30 days.

SELECT name, join_date FROM employees
WHERE join_date >= CURDATE() - INTERVAL 30 DAY;

27. Count employees with no email.

SELECT COUNT(*) AS missing_email
FROM employees
WHERE email IS NULL OR email = '';

28. Give a 10% raise to one department.

UPDATE employees
SET salary = salary * 1.10
WHERE dept_id = 3;

29. Validate a record created through the UI or API.

SELECT emp_id, name, email, dept_id, salary
FROM employees
WHERE email = 'new.user@example.com';

Check that exactly one row exists and every column matches what was entered. This is a favourite way for interviewers to connect SQL with your testing work.

30. Total salary cost per department, only above 5 lakh, sorted.

SELECT d.dept_name, SUM(e.salary) AS total_cost
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
GROUP BY d.dept_name
HAVING SUM(e.salary) > 500000
ORDER BY total_cost DESC;

Advanced SQL and test-data questions (31–50)

31. What is a view?
A saved SELECT query that behaves like a virtual table. Testers use views to simplify repeated validation queries; when testing, check that the view returns the same data as the underlying query and respects permissions.

32. Stored procedure vs function?
A stored procedure performs a set of operations (it can modify data, return multiple result sets and is called with CALL/EXEC). A function returns a single value or table and is used inside queries. To test a procedure, call it with valid, boundary and invalid inputs and verify the affected tables.

33. What is a trigger and how would you test it?
A trigger runs automatically on INSERT, UPDATE or DELETE — for example writing to an audit table. Test it by performing the action and verifying the trigger’s effect (audit row created, values correct), including bulk operations and rollbacks.

34. What is normalization? Explain 1NF, 2NF and 3NF.
Organising tables to reduce duplication. 1NF: atomic values, no repeating groups. 2NF: 1NF plus every non-key column depends on the whole primary key. 3NF: 2NF plus no non-key column depends on another non-key column. Testers mainly need to understand it to read schemas and write correct joins.

35. Which constraints should testers verify?
NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK and DEFAULT. Try inserting data that violates each one (through the UI/API or directly) and confirm it is rejected with a sensible error, not silently accepted.

36. ROW_NUMBER vs RANK vs DENSE_RANK — show the difference.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,   -- 1,2,3,4
       RANK()       OVER (ORDER BY salary DESC) AS rnk,       -- 1,2,2,4 (gap)
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_rnk  -- 1,2,2,3 (no gap)
FROM employees;

37. Classify employees into salary bands using CASE.

SELECT name, salary,
       CASE
         WHEN salary >= 100000 THEN 'High'
         WHEN salary >= 50000  THEN 'Medium'
         ELSE 'Low'
       END AS band
FROM employees;

38. Names containing “an”, case-insensitive.

SELECT name FROM employees WHERE LOWER(name) LIKE '%an%';

39. Employees who share the same salary.

SELECT name, salary
FROM employees
WHERE salary IN (SELECT salary FROM employees GROUP BY salary HAVING COUNT(*) > 1)
ORDER BY salary;

40. Name of the highest-paid employee in each department.

SELECT dept_id, name, salary
FROM (
  SELECT dept_id, name, salary,
         DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS r
  FROM employees
) t
WHERE r = 1;

41. Running total of salary cost by joining date.

SELECT name, join_date, salary,
       SUM(salary) OVER (ORDER BY join_date) AS running_total
FROM employees;

42. Number of employees who joined each month in 2026 (MySQL).

SELECT DATE_FORMAT(join_date, '%Y-%m') AS month, COUNT(*) AS joined
FROM employees
WHERE join_date >= '2026-01-01' AND join_date < '2027-01-01'
GROUP BY DATE_FORMAT(join_date, '%Y-%m')
ORDER BY month;

43. Employees whose manager works in a different department.

SELECT e.name AS employee, m.name AS manager
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
WHERE e.dept_id <> m.dept_id;

44. Fetch only even-numbered emp_id rows.

SELECT * FROM employees WHERE MOD(emp_id, 2) = 0;

45. Back up a table before a destructive test.
Always take a backup (or use a transaction) before running UPDATE/DELETE on a shared test database.

CREATE TABLE employees_backup AS SELECT * FROM employees;
-- restore later if needed
-- INSERT INTO employees SELECT * FROM employees_backup;

46. Data migration test: find rows present in the source but missing in the target.

SELECT s.emp_id
FROM employees s
LEFT JOIN employees_new t ON t.emp_id = s.emp_id
WHERE t.emp_id IS NULL;

-- also compare counts
SELECT (SELECT COUNT(*) FROM employees) AS source_count,
       (SELECT COUNT(*) FROM employees_new) AS target_count;

47. Find records with an invalid email format.
For stricter checks MySQL 8 supports REGEXP.

SELECT emp_id, email
FROM employees
WHERE email IS NOT NULL
  AND email NOT LIKE '%_@_%._%';

48. Show “Not assigned” when an employee has no department.

SELECT e.name, COALESCE(d.dept_name, 'Not assigned') AS department
FROM employees e
LEFT JOIN departments d ON d.dept_id = e.dept_id;

49. Test an update safely with a transaction and rollback.

START TRANSACTION;
UPDATE employees SET salary = salary * 1.10 WHERE dept_id = 3;
SELECT name, salary FROM employees WHERE dept_id = 3;  -- verify
ROLLBACK;  -- or COMMIT when you are sure

50. EXISTS vs IN — when do you use each?
IN compares a value with a list or subquery result; it is simple for small lists. EXISTS checks whether a correlated subquery returns any row and stops at the first match, so it is often faster for large tables. Beware of NOT IN with NULLs in the subquery — it returns no rows; use NOT EXISTS instead.

-- departments that have at least one employee
SELECT d.dept_name
FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

How to answer SQL interview questions for testers

Think aloud: state which tables you need, how they join, and what to filter or group. Write the query step by step, then mention edge cases such as NULLs, ties and duplicates. If you are unsure about dialect-specific syntax, say so and give the standard version. For reference, the official MySQL documentation covers every function used here. Many interviews combine SQL with testing theory and APIs, so also revise our manual testing interview questions and API testing interview questions.

FAQs

How much SQL does a tester need?

Confident SELECT queries with joins, grouping, subqueries and basic INSERT, UPDATE and DELETE cover most testing roles.

Are SQL interview questions for testers asked to freshers?

Yes. Freshers are commonly asked basic queries, joins and the second highest salary problem.

Which database should I practise on?

MySQL or PostgreSQL. Both are free, widely used, and the concepts transfer to other databases.

Do automation testers use SQL?

Yes — for test data setup, cleanup and backend validation inside automated tests, often via JDBC or a Python driver.

Conclusion

These SQL interview questions for testers cover the concepts and queries interviewers ask most often. Create the sample tables, insert some data, and run every query yourself until you can write them without notes. Test your recall with the free QA interview question bank, then browse the daily QA jobs list and start applying.