6. ORDER BY
ORDER BY is used to sort records.
Ascending order
SELECT *
FROM users
ORDER BY age ASC;
Descending order
SELECT *
FROM users
ORDER BY age DESC;
ASC means ascending.
DESC means descending.
QA Scenario
Suppose the application provides:
Sort users by age — High to Low
You can verify the database results using:
SELECT *
FROM users
ORDER BY age DESC;
7. COUNT()
COUNT() is extremely useful for database validation.
It returns the number of records.
SELECT COUNT(*)
FROM users;
If the table contains five users, the result will be:
5
QA Scenario
Suppose your application displays:
Total Users: 5
You can verify it using:
SELECT COUNT(*)
FROM users;
This is particularly useful when testing dashboards and reports.
8. DISTINCT
DISTINCT returns unique values and removes duplicates from the result.
SELECT DISTINCT city
FROM users;
If the database contains:
Pune
Pune
Mumbai
Delhi
Pune
the query returns:
Pune
Mumbai
Delhi
QA Scenario
Suppose a dropdown should display each city only once.
You can use this query to check the unique values stored in the database.
9. LIKE Operator
LIKE is useful when you need to search for a pattern.
For example:
SELECT *
FROM users
WHERE name LIKE 'A%';
This finds users whose names start with A.
The % symbol represents zero or more characters.
More examples
Names ending with a:
SELECT *
FROM users
WHERE name LIKE '%a';
Names containing ne:
SELECT *
FROM users
WHERE name LIKE '%ne%';
QA Scenario
This is useful when testing:
- Search functionality
- Auto-complete
- Partial text search
- Customer lookup
10. BETWEEN
BETWEEN is used to find values within a specified range.
SELECT *
FROM users
WHERE age BETWEEN 25 AND 30;
This finds users whose age falls within the specified range.
QA Scenario
Suppose the application provides an age filter:
Age: 25–30
You can validate the backend using the BETWEEN query.
Important Note
For numeric values, BETWEEN is commonly used for range validation. For date/time testing, be careful about database-specific behavior and boundary values.