SQL Interview Questions for Testers

These are the SQL questions testers are asked most often, each with a short answer and — where interviewers expect you to write it — a query you can run. Every query here was tested. For the complete learning path, start with The Complete SQL for Testers Guide.

Advertisement

SQL Basics (Q1–7)

Q1. What Is SQL?

SQL (Structured Query Language) is the standard language for creating, reading, updating and deleting data in relational databases such as MySQL, PostgreSQL, Oracle and SQL Server. Testers use it to verify that the application stores and changes data correctly.

Learn more: Basic SQL Commands

Q2. What Are DDL, DML, DCL and TCL?

  • DDL (definition) — CREATE, ALTER, DROP, TRUNCATE.
  • DML (manipulation) — INSERT, UPDATE, DELETE (SELECT is sometimes listed separately as DQL).
  • DCL (control) — GRANT, REVOKE.
  • TCL (transactions) — COMMIT, ROLLBACK, SAVEPOINT.

Q3. DELETE vs TRUNCATE vs DROP?

  DELETE TRUNCATE DROP
Removes Selected rows (with WHERE) or all rows All rows The whole table and its structure
Type DML DDL DDL
Rollback Yes, inside a transaction Usually not (in MySQL it commits implicitly) Usually not
Speed Slower, row by row Fast Fast
Auto-increment Keeps counting Resets Gone with the table

Q4. Primary Key vs Foreign Key?

A primary key uniquely identifies each row and can't be NULL.

A foreign key is a column that references a primary key in another table, enforcing the relationship — for example:

orders.customer_id → customers.id

Q5. WHERE vs HAVING?

WHERE filters rows before grouping; HAVING filters groups after aggregation:

SELECT assigned_to, COUNT(*) AS bug_count
FROM bugs
WHERE severity = 'High'          -- filter rows first
GROUP BY assigned_to
HAVING COUNT(*) > 5;             -- then filter the groups

Q6. CHAR vs VARCHAR?

CHAR(n) stores fixed-length text, padded to n characters — good for codes like country codes.

VARCHAR(n) stores variable-length text up to n characters — used for names, emails and most text.

Q7. IS NULL vs = NULL?

NULL means "unknown", and any comparison with NULL using = is never true — so = NULL returns no rows.

Always use IS NULL / IS NOT NULL:

SELECT * FROM bugs WHERE assigned_to = NULL;    -- returns nothing

SELECT * FROM bugs WHERE assigned_to IS NULL;   -- unassigned bugs ✅

Filtering & Operators (Q8–10)

Q8. What Does DISTINCT Do?

It removes duplicate rows from the result:

SELECT DISTINCT status FROM test_results;

Learn more: SELECT, Filtering & Sorting

Q9. IN vs BETWEEN?

IN matches any value in a list:

status IN ('Open', 'Reopened')

BETWEEN matches an inclusive range:

created_at BETWEEN '2026-01-01' AND '2026-01-31'

Q10. What Is the LIKE Operator?

Pattern matching with wildcards:

  • % — any number of characters
  • _ — exactly one character

For example:

email LIKE '%@example.com'

finds all example.com addresses.

Joins (Q11–14)

Q11. What Is a JOIN, and Why Is It Needed?

A JOIN combines rows from related tables.

It's needed because normalized databases store related data in separate tables — customers in one, orders in another.

Learn more: SQL Joins Explained

Q12. What Are the Types of JOIN?

  • INNER JOIN — only rows that match in both tables.
  • LEFT JOIN — all rows from the left table, plus matches (NULL where none).
  • RIGHT JOIN — all rows from the right table, plus matches.
  • FULL OUTER JOIN — all rows from both (not supported directly in MySQL; emulated with UNION).
  • SELF JOIN — a table joined to itself, e.g. employees and their managers.

Learn more: SQL Joins Compared

Q13. How Do You Find Records in One Table but Not Another?

For example, customers who have never placed an order:

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

NOT EXISTS works too.

Be careful with NOT IN: if the subquery returns any NULL, it returns no rows at all.

Q14. How Do You Find Common Records Between Two Tables?

With an INNER JOIN (or INTERSECT in databases that support it):

SELECT DISTINCT c.name
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id;    -- customers who have ordered

Aggregates & Grouping (Q15–17)

Q15. What Are Aggregate Functions?

COUNT, SUM, AVG, MIN and MAX calculate one value from many rows, usually per group with GROUP BY.

Learn more: Aggregate Functions & Grouping

Q16. COUNT(*) vs COUNT(column)?

COUNT(*) counts all rows.

COUNT(column) counts only rows where that column is not NULL.

COUNT(DISTINCT column) counts unique non-NULL values.

Q17. What Does GROUP BY Do? Find Duplicate Records.

GROUP BY groups rows with the same values so aggregates are calculated per group — which is also how you find duplicates:

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

Subqueries (Q18–20)

Q18. What Is a Subquery?

A subquery is a query inside another query — used in WHERE, FROM or SELECT to filter or calculate based on another result.

Learn more: Subqueries & Nested Queries

Q19. How Do You Find the Second or Nth Highest Salary? ⭐

Second highest:

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

Nth highest — here N = 3, handling ties:

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

Without window functions (MySQL / MariaDB):

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

Q20. Correlated vs Non-Correlated Subqueries?

A non-correlated subquery runs once, independently (like the one in Q19).

A correlated subquery refers to the outer query and runs once per outer row:

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

Transactions & Design (Q21–23)

Q21. What Is a Transaction? COMMIT vs ROLLBACK?

A transaction is a group of statements that succeed or fail together.

  • COMMIT saves the changes permanently.
  • ROLLBACK undoes everything since the transaction started.

This is useful for cleaning up test data.

Learn more: Insert, Update, Delete & Transactions

Q22. What Are the ACID Properties?

Atomicity — all or nothing.

Consistency — rules and constraints always hold.

Isolation — concurrent transactions don't interfere.

Durability — committed data survives failures.

Example: a money transfer must debit one account and credit the other, or do neither.

Q23. What Is Normalization?

Normalization means organizing tables to reduce duplicate data and update anomalies.

  • 1NF: atomic values, no repeating groups.
  • 2NF: no partial dependency on part of a composite key.
  • 3NF: no non-key column depends on another non-key column.

Learn more: Advanced SQL & Normalization

Database Testing (Q24–25)

Q24. How Do You Validate Backend Data Against the UI?

Perform the action in the UI (or API), query the database for the same record by its key, and compare every field — including values the UI doesn't show, such as status flags and timestamps.

Automation frameworks do this with JDBC.

See JDBC & Database Connectivity.

Q25. How Do You Verify a Record Was Inserted Correctly?

Query it by its unique key right after the action and check:

  • The row count — exactly one.
  • Every column value.
  • Related tables.

For an order, this could include its order items and payment.

Learn more: Scenario-Based SQL for Testers

Bonus: Practical Queries Testers Get Asked

Bugs Raised in the Last 7 Days

SELECT COUNT(*) AS bugs_last_7_days
FROM bugs
WHERE created_at >= NOW() - INTERVAL 7 DAY;

Users Who Did Not Log In Today

SELECT u.name
FROM users u
WHERE NOT EXISTS (
    SELECT 1
    FROM logins l
    WHERE l.user_id = u.id
      AND DATE(l.login_time) = CURDATE()
);

Bugs Assigned to Each Developer, Most First

SELECT assigned_to, COUNT(*) AS bug_count
FROM bugs
WHERE assigned_to IS NOT NULL
GROUP BY assigned_to
ORDER BY bug_count DESC;

Passed and Failed Test Cases in One Row

SELECT
    SUM(CASE WHEN status = 'PASS' THEN 1 ELSE 0 END) AS passed,
    SUM(CASE WHEN status = 'FAIL' THEN 1 ELSE 0 END) AS failed
FROM test_results;

Date functions differ slightly between databases (these use MySQL/MariaDB syntax) — mention that in the interview.

More: Practical SQL Queries.

From Real Projects

Much of my testing on Apkope and Canolog was about data: prospect and customer data captured from many channels on Apkope, and records flowing between sales, inventory, finance and service on Canolog. Checking what the application shows is only half the job — the data behind it has to be right too, which is where SQL helps testers most. Practice joins and GROUP BY until you can write them without hesitation — they come up in almost every round.

📖 Related tutorials

📚 Official documentation: MySQL Reference Manual

👉 See also: Project Interview Questions for Testers: Process & Team

FAQs

How Much SQL Does a Tester Need?

Enough to write SELECT queries with filters, joins, GROUP BY/HAVING and subqueries, and to validate inserts, updates and deletes — the questions on this page.

Which SQL Question Is Asked Most Often?

The second or Nth highest salary, followed by joins, duplicates and WHERE vs HAVING.

Why Doesn't "= NULL" Work?

NULL means unknown, so comparing with = is never true. Use IS NULL.

How Do Testers Use SQL in Automation?

Through JDBC in Java frameworks: set up and clean test data, and verify that UI or API actions stored the right values.

Next Steps