Blog / Skills

SQL for testers: the few queries you will use most

Many testing jobs ask for basic SQL. You usually do not need to design databases. You need to look at data and check that the application stored what it should.

Why testers use SQL

You fill a form, then want to confirm the record exists and the values are right. A query lets you check that without relying only on the screen.

SELECT and WHERE

Imagine a table called users with columns id, name, email and status.

SELECT * FROM users;
SELECT name, email FROM users WHERE status = 'active';

The first shows every row. The second shows only active users and only two columns.

ORDER BY and LIMIT

SELECT * FROM users ORDER BY id DESC LIMIT 5;

This shows the five newest rows, handy right after you create a test record.

COUNT

SELECT COUNT(*) FROM users WHERE status = 'active';

Compare the number with what the application reports.

Finding duplicates

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

If a signup should not allow the same email twice, this tells you whether it did.

JOIN

Data is often split over tables. An orders table may hold a user_id, while names live in users.

SELECT users.name, orders.id FROM orders JOIN users ON orders.user_id = users.id;

Be careful

• Only run SELECT queries unless you are told otherwise. Never run UPDATE or DELETE without a WHERE clause on shared data.
• Use a test environment, not live customer data.

How to practise

Install a free database such as SQLite, create a small table, and try each query above. Ten minutes a day for a couple of weeks is enough to explain these in an interview.