16. GROUP BY
GROUP BY groups records based on a column.
For example:
SELECT city, COUNT(*)
FROM users
GROUP BY city;
The result may look like:
| city | count |
|---|---|
| Pune | 3 |
| Mumbai | 1 |
| Delhi | 1 |
QA Scenario
Suppose an admin dashboard displays:
Pune — 3 users
Mumbai — 1 user
Delhi — 1 user
You can compare those numbers against the database.
17. HAVING
HAVING is commonly used with GROUP BY to filter grouped results.
For example:
SELECT city, COUNT(*)
FROM users
GROUP BY city
HAVING COUNT(*) > 1;
This returns only cities that have more than one user.
Difference Between WHERE and HAVING
A common interview question is:
What is the difference between WHERE and HAVING?
WHERE
Filters individual rows before grouping.
WHERE city = 'Pune'
HAVING
Filters groups after grouping.
HAVING COUNT(*) > 1
Remember this distinction for SQL interviews.
18. INNER JOIN
Real-world applications usually have multiple tables.
For example, suppose we have:
users
| id | name |
|---|---|
| 1 | Mrunal |
| 2 | Priya |
orders
| order_id | user_id | amount |
|---|---|---|
| 101 | 1 | 500 |
| 102 | 2 | 800 |
The user_id connects the two tables.
We can use INNER JOIN:
SELECT users.name, orders.order_id, orders.amount
FROM users
INNER JOIN orders
ON users.id = orders.user_id;
This returns users who have matching orders.
QA Scenario
Suppose the application displays:
Mrunal — Order #101 — ₹500
You can use a JOIN query to verify whether the user and order information are correctly connected in the database.
19. LEFT JOIN
LEFT JOIN returns all records from the left table, even when there is no matching record in the right table.
SELECT users.name, orders.order_id
FROM users
LEFT JOIN orders
ON users.id = orders.user_id;
This can show users even if they don’t have an order.
QA Scenario
Suppose you need to identify:
Users who have never placed an order.
You can use:
SELECT users.name
FROM users
LEFT JOIN orders
ON users.id = orders.user_id
WHERE orders.order_id IS NULL;
This is a very useful real-world testing query.
20. Subquery
A subquery is a query inside another query.
Suppose you want to find users whose age is greater than the average age.
You can write:
SELECT *
FROM users
WHERE age > (
SELECT AVG(age)
FROM users
);
The inner query calculates the average age.
The outer query finds users whose age is greater than that average.
QA Scenario
Subqueries can be useful when validating:
- Business rules
- Reports
- Calculations
- Aggregated data
- Complex search conditions