SQL Interview Questions and Answers for Testers

• INNER JOIN: returns only the rows that match in both tables.
• LEFT JOIN: returns all rows from the left table. It adds matching rows from the right table. Where nothing matches, the right side is NULL.
• RIGHT JOIN: the same as LEFT, but from the other side.
• FULL OUTER JOIN: returns all rows from both tables. Unmatched sides are NULL. MySQL does not have it. You can copy it with a UNION of a LEFT and a RIGHT join.

SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON d.id = e.dept_id;   -- employees without a department still appear

A tester can use LEFT JOIN with WHERE d.id IS NULL to find orphan records (rows with no parent).

What they are checking: How joins work

WHERE filters single rows before grouping. It cannot use aggregate functions like COUNT. HAVING filters groups after GROUP BY. It can use aggregate functions.

SELECT dept_id, COUNT(*) AS cnt, AVG(salary) AS avg_sal
FROM employees
WHERE salary > 20000          -- row filter
GROUP BY dept_id
HAVING COUNT(*) > 5;          -- group filter

The order the database runs the clauses: FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY, LIMIT.

What they are checking: Order in which a query runs

The simple case uses GROUP BY:

SELECT product_id, MAX(amount) AS highest_bid
FROM bids
GROUP BY product_id;

If you also need the bidder (the whole row), use a window function:

SELECT product_id, bidder_id, amount
FROM (
  SELECT b.*, ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY amount DESC) AS rn
  FROM bids b
) t
WHERE rn = 1;

Or join back to the max value with this query. JOIN (SELECT product_id, MAX(amount) m FROM bids GROUP BY product_id) x ON x.product_id = b.product_id AND x.m = b.amount. This returns ties. To keep ties, use RANK() instead of ROW_NUMBER().

What they are checking: Max per group, and getting the whole row

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

To see the full duplicate rows:

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

To delete duplicates and keep the lowest id (PostgreSQL / MySQL 8):

DELETE FROM users WHERE id IN (
  SELECT id FROM (
    SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn FROM users
  ) t WHERE rn > 1
);

Always run the SELECT first. Take a backup before you delete.

What they are checking: A common data quality query

The second highest salary, using a subquery:

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

The nth highest, using DENSE_RANK (it handles ties; N = 3 here):

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

Using an offset (MySQL/PostgreSQL):

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

Note: if there are fewer than N different salaries, the result is empty. It is not an error.

What they are checking: Ranking and equal values

DELETE with no WHERE removes all rows. It keeps the table structure and the identity counter.

What they are checking: Basic SQL commands

• Primary key: identifies each row. It cannot be NULL. A table has only one.
• Unique key: values must be different. It may allow NULL (one or more, depending on the database). A table can have many.
• Foreign key: a column that points to a primary or unique key in another table. It keeps data linked correctly (referential integrity), so there are no orphan rows. On delete it can do actions like CASCADE, RESTRICT or SET NULL.

Testing: try to insert a duplicate PK (you get an error). Try an order for a customer that does not exist (FK error). Try to delete a parent that has children. It should be blocked or cascade, as designed.

What they are checking: Knowledge of constraints

SELECT d.dept_name,
       COUNT(e.id)            AS emp_count,
       ROUND(AVG(e.salary),2) AS avg_salary
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.id
GROUP BY d.id, d.dept_name
ORDER BY avg_salary DESC;

Points to say: LEFT JOIN keeps departments with zero employees. COUNT(e.id) gives 0 because it ignores NULLs, but COUNT(*) would give 1. Every column in SELECT that is not an aggregate must also be in GROUP BY. Give the tables short alias names so the query is easy to read.

What they are checking: Writing a join with an aggregate live

NULL means unknown or missing. It is not zero or an empty string. Comparing with NULL using = or <> gives UNKNOWN, so WHERE col = NULL returns nothing. Use IS NULL or IS NOT NULL instead.

SELECT * FROM customers WHERE phone IS NULL;
SELECT name, COALESCE(phone, 'N/A') FROM customers;   -- replace NULL

Aggregates ignore NULL (except COUNT(*)). NULL + 1 is NULL. NOT IN returns no rows if the list has a NULL, so prefer NOT EXISTS.

What they are checking: Common NULL mistakes

Three correct ways:

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

-- NOT EXISTS (safe with NULLs)
SELECT c.id, c.name FROM customers c
WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.customer_id = c.id);

-- NOT IN (avoid if the subquery can return NULL)
SELECT id, name FROM customers WHERE id NOT IN (SELECT customer_id FROM orders WHERE customer_id IS NOT NULL);

Testers also use this to find orphan rows or missing related data.

What they are checking: Anti-join pattern (find rows with no match)

• Atomicity: a transaction is all or nothing.
• Consistency: a transaction moves the database from one valid state to another and follows all the rules.
• Isolation: transactions that run at the same time do not see each other's unfinished work.
• Durability: once a transaction is committed, the data is kept even after a crash.

GROUP BY puts rows with the same values into groups. Then aggregates (COUNT, SUM, AVG, MAX, MIN) work on each group. ORDER BY only sorts the final rows, from low to high or high to low. You can use both together: group first, then sort the groups.

• COUNT(*): counts all rows, including rows with NULLs.
• COUNT(column): counts the rows where the column is not NULL.
• COUNT(DISTINCT column): counts the different values that are not NULL.

Both join the results of two SELECTs. The two SELECTs need the same number of columns, with matching types. UNION removes duplicates, which takes extra work. UNION ALL keeps all rows and is faster. Use UNION ALL when duplicates cannot happen or you want them. Use UNION when you need only different rows.

A subquery is a query inside another query. A non-correlated subquery runs on its own, only once. A correlated subquery uses columns from the outer query. It runs once for each outer row.

Normalization organizes tables to reduce repeated data and update errors.

• 1NF: each column holds one simple value. There are no repeating groups, so no comma-separated lists in one cell.
• 2NF: it follows 1NF, and every non-key column depends on the whole primary key. No column depends on only part of a composite key.
• 3NF: it follows 2NF, and no non-key column depends on another non-key column. For example, city should not be set by zip.

Constraints make the database follow rules: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.

CHAR(n) has a fixed length and is padded with spaces. VARCHAR(n) stores any length up to n. DATE holds only a date. DATETIME holds a date and time without a time zone. TIMESTAMP is stored in UTC and converted to the session time zone. In MySQL it has a limited range of years. Test the edges: maximum length plus one, trailing spaces, Unicode (emoji needs a charset like utf8mb4), and time zone changes around midnight and daylight saving.

• LIKE 'A%': matches a text pattern (% is any length, _ is one character).
• IN (1,2,3): matches any value in a list or a subquery.
• BETWEEN 10 AND 20: a range that includes both ends (10 and 20 are included).
• EXISTS (subquery): true if the subquery returns at least one row. It stops at the first match and is safe with NULLs.

An index is a data structure (usually a B-tree) that makes lookups faster. The cost is more storage and slower writes. A clustered index sets the physical order of the table rows. So a table has only one. It is often the primary key (in InnoDB and SQL Server). A non-clustered (secondary) index is a separate structure that points to the rows. A table can have many.

Window function version:

SELECT name, dept_id, salary
FROM (
  SELECT name, dept_id, salary,
         AVG(salary) OVER (PARTITION BY dept_id) AS dept_avg
  FROM employees
) t
WHERE salary > dept_avg;

Join version:

SELECT e.name, e.dept_id, e.salary
FROM employees e
JOIN (SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id) d
  ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_sal;

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 <= 3
ORDER BY dept_id, salary DESC;

Use ROW_NUMBER() to get exactly 3 rows per department. Use DENSE_RANK() to include ties. Window...

Take the salaries 90, 80, 80, 70, ordered from high to low:

A transaction puts several statements into one unit of work.

START TRANSACTION;
UPDATE accounts SET balance = balance - 500 WHERE id = 1;
UPDATE accounts SET balance = balance + 500 WHERE id = 2;
COMMIT;      -- or ROLLBACK to undo everything since START

Tests to run:

• Force an error after the first update...

Keep the smallest id for each email.

A table stores data. A view is a saved query that works like a virtual table. It has no data of its own, unless it is materialized (the result is stored). It makes queries simpler and can limit access. A stored procedure is a named block of SQL logic. It can do actions (INSERT, UPDATE, transactions) and...

A self join joins a table to itself, using aliases (short names). It helps with hierarchies and with comparing rows. Example: list employees with their manager.

SQL (relational) databases use tables, a fixed schema, joins and ACID transactions (MySQL, PostgreSQL). NoSQL covers document stores (MongoDB), key-value stores (Redis), column stores and graph stores. They have a flexible schema and can grow by adding more servers. They often use eventual consistency (data becomes the same everywhere after a short delay).

SELECT order_date,
       SUM(total) AS daily_total,
       SUM(SUM(total)) OVER (ORDER BY order_date) AS running_total
FROM orders
GROUP BY order_date
ORDER BY order_date;

The inner SUM(total) is the total for each day. The outer SUM(...) OVER (ORDER BY ...) adds the days up as it goes. Check that the...

Use conditional aggregation (a pivot):

Database testing checks that the data layer is correct and reliable:

• Data integrity: values are stored correctly, there are no orphan rows, and the FK, PK, unique, check and NOT NULL rules work
• Data mapping: actions in the UI or API change the right tables and columns. Check insert, update and delete with...

1. Do the action or open the page. Note the values you see and the record ID.
2. Query the database for the same record. Join the tables the page reads from.
3. Compare field by field. Allow for formatting: dates, currency, rounding, trimmed spaces, and status codes like 1 = Active.
4. Check counts and totals....

Compare the source and the target by key.

• Before: study the source data (row counts, nulls, duplicates, min and max, different values). Read the mapping document. Take a backup. Test on a copy.
• After: row counts match. Checksums or sums of number columns match. Sample records match field by field. Keys and relationships (FK) are intact, with no...

1. Reproduce the problem and measure it. Then run EXPLAIN (or EXPLAIN ANALYZE). It shows full table scans, row estimates and join order.
2. Add or fix indexes on columns used in WHERE, JOIN and ORDER BY. Put the columns of a composite index in the right order.
3. Avoid SELECT *, functions on indexed columns,...

SQL injection happens when user input is joined into a query as plain text. An attacker can then change what the query means. An example is ' OR '1'='1 in a login field.

Export or fetch the API data. Load it into a staging table, or compare IDs and key fields with a query on the database side. Check counts first. Then check totals (SUM(amount)). Then find row differences with LEFT JOIN ... IS NULL in both directions. Group the differences by type: missing, extra, or changed values. Put the checks in a script that runs after each deployment and reports mismatches. Look for timing causes (replication lag, caching) before you log a bug.

Take a snapshot before the fix. Save row counts, checksums or sums of key columns, and a copy of some rows. Apply the fix, run the scenario, then compare. Check these points:

• Only the rows you meant to change were changed. Compare by primary key and by timestamp columns like updated_at.
• Related tables and FK relations are still correct.
• Totals still add up.
• No new NULLs or duplicates appeared.
• Audit or history tables recorded the change.