SQL for Testers: 20 Queries Every QA Engineer Should Know

Whether you are a manual tester, an automation engineer or a fresher in India preparing for your first QA job, SQL for testers is the single most useful technical skill you can add. Interviewers ask for it, test data lives in databases, and many defects are only visible in tables, not on screens. This tutorial covers the 20 queries testers actually use, with examples you can run today.

Why Testers Need SQL

• Verify what the screen saved. After you submit a form, check the row in the database, not just the success message.
• Create and find test data without waiting for a developer.
• Test back-end rules such as status changes, totals and audit columns.
• Get hired. Most QA job descriptions in India list SQL, and ETL and data testing roles are built on it. See ETL testing jobs in India.

Free SQL Practice Setup

You do not need a company database. Use any of these free options:

Create two tables, customers and orders, with 20–30 rows each. All queries below use them.

Queries 1–8: The Basics

-- 1. See everything (add a limit on big tables)
SELECT * FROM customers;

-- 2. Pick columns and filter
SELECT customer_id, name, city FROM customers WHERE city = 'Pune';

-- 3. Multiple conditions
SELECT * FROM orders WHERE status = 'PAID' AND amount > 1000;

-- 4. Pattern search
SELECT * FROM customers WHERE email LIKE '%@gmail.com';

-- 5. Sort
SELECT * FROM orders ORDER BY order_date DESC;

-- 6. Unique values (great for checking allowed values)
SELECT DISTINCT status FROM orders;

-- 7. Count and totals
SELECT COUNT(*), SUM(amount), MIN(amount), MAX(amount) FROM orders;

-- 8. Group and filter groups
SELECT city, COUNT(*) FROM customers GROUP BY city HAVING COUNT(*) > 5;

Queries 9–13: Joins

-- 9. Orders with customer names
SELECT o.order_id, c.name, o.amount
FROM orders o
INNER JOIN customers c ON c.customer_id = o.customer_id;

-- 10. Customers with no orders
SELECT c.customer_id, c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

-- 11. Orphan orders (customer missing): a classic defect
SELECT o.order_id
FROM orders o
LEFT JOIN customers c ON c.customer_id = o.customer_id
WHERE c.customer_id IS NULL;

-- 12. Total spend per customer
SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.name;

-- 13. Subquery: customers whose total spend beats the average order value
SELECT customer_id FROM orders
GROUP BY customer_id
HAVING SUM(amount) > (SELECT AVG(amount) FROM orders);

Queries 14–20: The Checks Testers Run

-- 14. Duplicates on a key
SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1;

-- 15. Missing mandatory values
SELECT COUNT(*) FROM customers WHERE email IS NULL OR TRIM(email) = '';

-- 16. Invalid values against a rule
SELECT * FROM orders WHERE amount < 0 OR status NOT IN ('NEW','PAID','CANCELLED');

-- 17. Rule check with CASE: expected vs actual
SELECT order_id, amount, discount,
       CASE WHEN amount >= 5000 THEN 'Y' ELSE 'N' END AS expected_discount
FROM orders
WHERE discount <> CASE WHEN amount >= 5000 THEN 'Y' ELSE 'N' END;

-- 18. Rows in one table but not another (MINUS in Oracle)
SELECT customer_id FROM customers_source
EXCEPT
SELECT customer_id FROM customers;

-- 19. Latest record per customer (window function)
SELECT * FROM (
  SELECT o.*, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders o
) t WHERE rn = 1;

-- 20. Data created today (check audit columns after a test)
SELECT * FROM orders WHERE created_at >= CURRENT_DATE;

Queries 14–18 are exactly what ETL testers run every day on millions of rows. See ETL testing SQL queries for the data warehouse versions.

4-Week SQL Practice Plan for Testers

SQL Interview Questions for Testers

1. What is the difference between WHERE and HAVING?

WHERE filters rows before grouping; HAVING filters groups after GROUP BY and can use aggregates.

2. How do you find duplicate records?

GROUP BY the key column(s) and use HAVING COUNT(*) > 1.

3. INNER JOIN vs LEFT JOIN?

INNER JOIN returns only matches; LEFT JOIN returns all left rows with NULLs where there is no match.

4. How do you find the second-highest salary?

SELECT MAX(salary) FROM emp WHERE salary < (SELECT MAX(salary) FROM emp), or use DENSE_RANK().

5. DELETE vs TRUNCATE vs DROP?

DELETE removes chosen rows; TRUNCATE removes all rows quickly; DROP removes the table.

More: database testing interview questions and SQL queries for ETL testing interviews.

Data testing skills: data migration testing · Power BI testing · big data testing · data quality checks · ETL tools list · ADF & Databricks testing · database testing interview questions

More guides for Indian students: ETL full form · ETL testing with example · ETL testing jobs in India · ETL testing course in India · ETL training in Hyderabad, Bangalore & Pune · best ETL testing course on Udemy · ETL testing roadmap · ETL tester vs ETL developer · interview questions for freshers · free ETL testing mock test · QA to data engineer · ETL testing kya hai (Hindi)

Frequently Asked Questions

How much SQL does a tester need?

Most testers need SELECT with filters, joins, GROUP BY with HAVING, subqueries and a few functions. ETL and data testers also need EXCEPT/MINUS, CASE and window functions.

Is SQL required for manual testing jobs?

Many manual testing jobs in India list basic SQL, and it is almost always asked in interviews. It also makes you faster at verifying data and creating test data.

How long does it take a tester to learn SQL?

About 3 to 4 weeks of daily practice for the basics testers use. Fluency with joins and rule checks takes a few more weeks of real use.

Which database should testers practise SQL on?

Any free one: MySQL, PostgreSQL or SQLite, or an online playground. The core SQL is the same across them.

What job can a tester get with strong SQL?

Strong SQL opens database testing, ETL testing and data warehouse testing roles, which are more specialised than general manual testing.

AN

Written by

Asim Noaman Lodhi

Certified Google Partner · QA Consultant · 12+ Years IT

QA consultant specializing in ETL testing and data quality. Trained 913+ students to transition into data testing roles through hands-on, real-world instruction.

4.5 Rating 913+ Students Google Partner 79 Lectures