A tester not only checks that the software works correctly but also verifies that the underlying data is accurate. In such cases, SQL (Structured Query Language) proves to be extremely useful. Learning an essential query in SQL helps testers directly validate database records and confirm that the application is storing the correct data.
Testers can examine whether records are stored accurately and whether business rules are applied correctly by looking directly into the database using SQL. Using SQL and understanding each essential query improve the speed, accuracy, and dependability of your testing even in the absence of automation.
Here is a list of important SQL queries that every manual tester should know, shown with simple examples so you can understand each essential query easily.
SELECT
The most fundamental SQL query is the SELECT command. It is used to examine the information kept in a table.
— Fetch all records from the ‘users’ table
SELECT * FROM users;
— Fetch only specific columns from the ‘users’ table
SELECT id, name, email, state FROM users;
Example Use Case: You recently used the application to create a new user. You can verify that the user has been accurately stored in the database:
SELECT * FROM users WHERE email = ‘bhuvnesh@example.com’;
WHERE
By using conditions, the WHERE clause enables you to obtain only the records you require.
— Fetch users from the state ‘HP’
SELECT * FROM users WHERE state = ‘HP’;
— Fetch orders with amount greater than 500
SELECT * FROM orders WHERE total_amount > 500;
Example Use Case: After placing several orders, you want to see only the high-value orders:
SELECT order_id, customer_id, total_amount FROM orders WHERE total_amount > 500;
Tip: WHERE is very important for validating specific scenarios, like failed transactions, active users, or VIP customers.
JOIN
At times, data is dispersed throughout several tables. JOINs allow relevant data to be combined.
— Get orders along with customer names
SELECT o.order_id, o.order_date, c.name AS customer_name, c.email FROM orders o INNER JOIN customers c ON o.customer_id = c.id;
Example Use Case: Make sure the right customer is associated with the orders:
SELECT o.order_id, o.order_date, c.name, c.emailFROM orders oINNER JOIN customers c ON o.customer_id = c.idWHERE o.order_id = 1001;
Tip: Only matching records are returned by an INNER JOIN. To view every record from the first table, even if there is no match, you may also use LEFT JOIN.
COUNT
The COUNT() function simply tells you how many items, records, or rows exist in the table you are looking at. It’s the easiest way to get a quick total.
— Count all users
SELECT COUNT(*) AS total_users FROM users;
— Count orders for a specific customer
SELECT COUNT(*) AS order_count FROM orders WHERE customer_id = 101;
Example Use Case: Make sure all expected orders for a customer exist:
SELECT COUNT(*) AS order_count FROM orders WHERE customer_id = 101;
Tip: Always use COUNT to verify totals and missing records after data migration or batch processing.
GROUP BY
GROUP BY helps you stop looking at individual rows and start looking at summary totals for different categories.
— Count users in each state
SELECT state, COUNT(*) AS total_users FROM users GROUP BY state;
;
— Sum of total orders per customer
SELECT customer_id, SUM(total_amount) AS total_spentFROM ordersGROUP BY customer_id;
Example Use Case: Check the total money spent by every customer. (This involves adding up all the purchases for each user.)
SELECT customer_id, SUM(total_amount) AS total_spent FROM ordersGROUP BY customer_idHAVING SUM(total_amount) > 1000;
Tip: You need GROUP BY to test summary screens and reports—it helps you check that the totals, averages, and counts are exactly right for each category.
ORDER BY – Sorting Data
ORDER BY simply makes your results neat! It lets you sort the list either up or down (A-Z or Z-A).
— Most recent orders first
SELECT * FROM orders ORDER BY order_date DESC;
— List users alphabetically
SELECT * FROM usersORDER BY name ASC;
Example Use Case: Check that a report shows the latest transactions at the top:
SELECT order_id, order_date, total_amountFROM ordersORDER BY order_date DESCLIMIT 10;
<b<Tip: Sorting is helpful when you need to quickly review the latest or highest records.
LIMIT / TOP
If a table is massive, you can limit the results to just a few rows. This lets you confirm the data is correct much faster than loading the entire table.
— First 10 users
SELECT * FROM users LIMIT 10;
— Top 5 orders with highest amount (SQL Server)
SELECT TOP 5 * FROM orders ORDER BY total_amount DESC;
Example Use Case: Check the latest or most important records quickly without scanning the whole table.
— Fetch the 5 most recent orders
SELECT * FROM orders ORDER BY order_date DESC LIMIT 5;
— Fetch the top 5 highest-value orders
SELECT * FROM orders ORDER BY total_amount DESC LIMIT 5;
EXISTS / NOT EXISTS
You can use these queries to confirm the existence of particular records.
— Check if a user exists
SELECT *
FROM users
WHERE EXISTS (
SELECT 1 FROM users
WHERE email = ‘bhuvnesh@example.com’
);
— Check if a customer has no orders
SELECT *
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);
Example Use Case: Verify that all required records are present and that no required relationships are absent.
— Check if a specific user exists in the ‘users’ table
SELECT *
FROM users
WHERE EXISTS (
SELECT 1
FROM users
WHERE email = ‘bhuvnesh@example.com’
);
— Check if any customer has no orders (missing relationship)
SELECT *
FROM customers c
WHERE NOT EXISTS (
SELECT 1
FROM orders o
WHERE o.customer_id = c.id
);
UPDATE / DELETE
Testers often need to change or remove data in their test environment. This lets them quickly fix a bad test record or set up the perfect data conditions for the next test case.
— Update a test user status
UPDATE users
SET status = ‘Inactive’
WHERE email = ‘bhuvnesh@example.com’;
— Delete old test orders
DELETE FROM orderseasily.
WHERE created_at < ‘2025-01-01’;
Example Use Case: Wipe the slate clean before you start testing a new feature. You need to reset the data to a known state so your results are reliable. (No carry-over mess!)
— Reset the status of all test users to ‘Inactive’
UPDATE users
SET status = ‘Inactive’
WHERE email LIKE ‘testuser%’;
— Delete old test orders created before 2025-01-01
DELETE FROM orders
WHERE created_at < ‘2025-01-01’;
Conclusion
Manual testing goes beyond the screen. It’s about confirming the system not only functions but also handles correct and reliable data. By learning these SQL queries, we can:
- Verify the accuracy of the data without relying only on the user interface.
- Locate duplicate, inaccurate, or missing records fast.
- Check intricate business rules in several tables.
