11. IN Operator
The IN operator is useful when you want to match a value against multiple possibilities.
Instead of writing:
SELECT *
FROM users
WHERE city = 'Pune'
OR city = 'Mumbai'
OR city = 'Delhi';
you can write:
SELECT *
FROM users
WHERE city IN ('Pune', 'Mumbai', 'Delhi');
This is shorter and easier to read.
QA Scenario
You can use IN when validating:
- Multiple category filters
- Multiple statuses
- Multiple locations
- Multiple product types
12. UPDATE Query
UPDATE is used to modify existing records.
For example:
UPDATE users
SET city = 'Nashik'
WHERE id = 1;
This changes the city of the user whose ID is 1.
QA Scenario
Suppose a user changes their address from Pune to Nashik.
After performing the update through the UI, you can verify:
SELECT *
FROM users
WHERE id = 1;
Important QA Warning
Always be careful with UPDATE.
Never run:
UPDATE users
SET city = 'Nashik';
unless you intentionally want to update every record.
Always use an appropriate WHERE condition when testing specific data.
13. DELETE Query
DELETE removes records from a table.
DELETE FROM users
WHERE id = 5;
This deletes the user with ID 5.
QA Scenario
Suppose the application has a Delete User feature.
After deleting the user from the UI, you can verify:
SELECT *
FROM users
WHERE id = 5;
If no record is returned, the record was successfully removed.
Important Warning
Be very careful with:
DELETE FROM users;
Without a WHERE clause, it can delete all rows from the table.
14. IS NULL
Sometimes a database column does not contain a value.
SQL represents a missing value using NULL.
To find records where a column is NULL:
SELECT *
FROM users
WHERE email IS NULL;
Do not write:
WHERE email = NULL;
That is not the correct way to check for SQL NULL.
Use:
IS NULL
or:
IS NOT NULL
QA Scenario
This is useful for testing optional fields.
For example:
Is the phone number stored as NULL when the user leaves it blank?
15. Finding Duplicate Records
Duplicate data is a common database testing problem.
Suppose you want to check whether multiple users have the same email address.
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;
This groups users by email and returns emails appearing more than once.
QA Scenario
Imagine your requirement says:
Email address must be unique.
You can use this query to verify whether duplicate emails exist.
This is a very useful query for data validation testing.