interviewDeck

Your one-stop platform to prepare, practice and ace your interviews.

Loading your questions…

interviewDeck

All Questions

Filters & tools

SQL for Testers Interview Questions and Answers

20 hand-picked SQL for Testers interview questions with detailed answers. Open the interactive version above to search, filter by difficulty, run code, bookmark questions and track your progress.

Why do testers need SQL? Give practical examples.

Testers use SQL to validate data behind the UI/API — confirm records were created, updated, or deleted correctly, and investigate defects with evidence.

Examples: after placing an order in UI, check orders table status; after API create customer, verify row values; compare counts before/after a batch job.

-- After creating order ORD-1001 via API/UI
SELECT order_id, status, total_amount
FROM orders
WHERE order_id = 'ORD-1001';

How do you write a basic SELECT query to validate test data?

Use SELECT columns FROM table WHERE filters match your test entity. Start specific (by id/email) so you don’t scan unrelated rows.

SELECT customer_id, email, status
FROM customers
WHERE email = 'qa.user+123@test.com';

How do JOINs help in testing? Example for order + customer validation.

JOINs combine related tables so you can validate relationships: an order belongs to the right customer, line items belong to the order, payments link to the order.

INNER JOIN shows matched pairs; LEFT JOIN helps find missing children (order without payment row).

SELECT o.order_id, c.email, o.status, o.total_amount
FROM orders o
INNER JOIN customers c ON c.customer_id = o.customer_id
WHERE o.order_id = 'ORD-1001';

-- Orders missing payment rows
SELECT o.order_id
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.payment_id IS NULL;

How do COUNT/SUM help in test validation?

Aggregates verify volume and totals. COUNT(*) confirms how many rows were created/updated. SUM checks money/quantity totals match UI/API values.

-- How many items in an order?
SELECT COUNT(*) AS item_count
FROM order_items
WHERE order_id = 'ORD-1001';

-- Does item sum match order total?
SELECT SUM(line_total) AS items_total
FROM order_items
WHERE order_id = 'ORD-1001';

How do NULLs affect SQL checks in testing?

NULL means "unknown/missing". You cannot compare with =; use IS NULL / IS NOT NULL.

For testers, unexpected NULLs often mean a field was not persisted (address, payment reference, status).

SELECT order_id, shipping_city
FROM orders
WHERE order_id = 'ORD-1001'
  AND shipping_city IS NULL;

How do you combine UI/API testing with DB validation?

Treat layers as one story: perform action via UI or API, then verify persistence with SQL, then optionally re-fetch via API/UI.

This finds bugs where the UI says success but data is wrong/missing, or API returns OK while DB transaction rolled back.

1) POST /orders  → 201 orderId=ORD-1001
2) SQL: SELECT status, total_amount FROM orders WHERE order_id='ORD-1001'
3) GET /orders/ORD-1001 → body matches DB

How do testers use INSERT/UPDATE/DELETE safely?

Use DML to prepare or cleanup test data in QA — create a known user/order, update a status to a precondition, delete temporary rows after. Prefer QA databases only; never run destructive scripts on production. Prefer transactions/rollback or isolated test schemas when available.

INSERT INTO customers(email, status) VALUES ('qa+1@test.com', 'ACTIVE');
UPDATE orders SET status='CREATED' WHERE order_id='ORD-QA-1';
DELETE FROM order_items WHERE order_id='ORD-QA-1';

When do you choose INNER JOIN vs LEFT JOIN in test validation?

Use INNER JOIN when both sides must exist (order must have a customer). Use LEFT JOIN when you need all left rows and want to detect missing right-side children (orders missing payments).

-- Orders with customer (both must exist)
SELECT o.order_id, c.email
FROM orders o
INNER JOIN customers c ON c.customer_id = o.customer_id;

-- Orders missing payments
SELECT o.order_id
FROM orders o
LEFT JOIN payments p ON p.order_id = o.order_id
WHERE p.payment_id IS NULL;

How do GROUP BY and HAVING help in testing?

GROUP BY buckets rows (e.g. orders per status). HAVING filters those buckets (statuses with count > 0, customers with more than 3 open orders). Useful for batch-job and reconciliation checks.

SELECT status, COUNT(*) AS cnt
FROM orders
WHERE created_at >= CURRENT_DATE
GROUP BY status
HAVING COUNT(*) > 0;

How can subqueries help find data issues?

Subqueries help express “rows that match/don’t match another set” — e.g. orders whose customer is inactive, or payments without a matching order. EXISTS/NOT EXISTS are often clearer for presence checks.

SELECT o.order_id
FROM orders o
WHERE NOT EXISTS (
  SELECT 1 FROM payments p WHERE p.order_id = o.order_id
);

How do you detect duplicate records with SQL?

Group by the business key that should be unique and use HAVING COUNT(*) > 1. Duplicates often appear after retry bugs or missing unique constraints.

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

How do you validate date/time fields in testing with SQL?

Filter with clear ranges, beware timezones, and validate business rules like “cancel allowed within 60 minutes”. Compare timestamps from API/UI with DB values and check formatting/precision issues.

SELECT order_id, created_at, cancelled_at
FROM orders
WHERE order_id = 'ORD-1001'
  AND cancelled_at <= created_at + INTERVAL '60' MINUTE;

How do you use LIKE for test data discovery?

LIKE helps find seeded test rows by prefix/suffix patterns (e.g. all QA emails). Use it for investigation, not as a substitute for exact key lookups in assertions.

SELECT email FROM customers WHERE email LIKE 'qa.order+%@test.com';

Why do testers care about database transactions?

Transactions keep multi-step writes atomic — all succeed or all roll back. Testers should verify failure paths don’t leave partial data (order header without items, payment captured without order).

-- After failed payment attempt, order should not be PAID
SELECT status FROM orders WHERE order_id='ORD-1001';

Do testers need to know indexes? What should you understand?

You don’t design DBs daily, but you should know indexes speed lookups and that missing indexes can make QA checks/jobs slow. If a validation query on large tables is extremely slow, raise it — it may affect app performance too.

-- Conceptual: email lookup benefits from index on customers(email)

How do you validate UI/API field mapping to database columns?

Build a mapping checklist: each important UI/API field → DB column/table. After create/update, compare values field-by-field (types, enums, rounding for money, trimming spaces).

API totalAmount=99.90  <=>  orders.total_amount=99.90
API status=CREATED     <=>  orders.status='CREATED'

How do you test soft delete vs hard delete in DB?

Hard delete removes the row. Soft delete keeps the row but marks it inactive (is_deleted/deleted_at/status). Tests should verify the chosen behaviour: row gone vs flagged, and that APIs no longer return deleted entities.

SELECT customer_id, is_deleted, deleted_at
FROM customers
WHERE email='qa+del@test.com';

How do IN and BETWEEN help write validation queries?

IN checks membership in a set of values (statuses, ids). BETWEEN checks inclusive ranges (dates/amounts). Both make validation queries readable for multi-value assertions.

SELECT *
FROM orders
WHERE status IN ('CREATED','PAID')
  AND total_amount BETWEEN 100 AND 500;

COUNT(*) vs COUNT(column) — why does it matter in testing?

COUNT(*) counts rows. COUNT(column) counts non-NULL values in that column. If you count a nullable column, you may under-count rows and misjudge data quality.

SELECT COUNT(*) AS rows_total,
       COUNT(shipping_city) AS city_populated
FROM orders
WHERE created_at >= CURRENT_DATE;

How do you do data reconciliation testing with SQL?

Reconciliation compares two sources of truth — e.g. source file vs DB, order service vs billing table, API totals vs SUM of lines. You check counts, totals, and mismatched keys between sets.

-- Orders missing in billing
SELECT o.order_id
FROM orders o
LEFT JOIN bills b ON b.order_id = o.order_id
WHERE o.status='DELIVERED' AND b.bill_id IS NULL;