SELECT, Filtering and Aggregation

The sample table

All examples use this employees table:

id	name	department	salary	city
1	Asha	Engineering	180000	Bengaluru
2	Ravi	Engineering	150000	Pune
3	Meera	Sales	90000	Mumbai
4	John	Sales	95000	Bengaluru
5	Priya	HR	80000	NULL

SELECT

SELECT name, salary
FROM employees;

SELECT * returns every column — fine for exploring, but name columns explicitly in application code.

Filtering with WHERE

SELECT name, salary
FROM employees
WHERE department = 'Engineering'
  AND salary > 160000;

Useful operators:

WHERE city IN ('Pune', 'Mumbai')
WHERE name LIKE 'P%'            -- starts with P
WHERE salary BETWEEN 80000 AND 100000
WHERE city IS NULL              -- not "= NULL"!

Common mistake

city = NULL is never true — not even for NULL rows. Always use IS NULL / IS NOT NULL.

Sorting and limiting

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 3;

Aggregation

Aggregate functions collapse many rows into one value:

SELECT COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees;

Add GROUP BY to get one row per group:

SELECT department, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

department	headcount	avg_salary
Engineering	2	165000
Sales	2	92500
HR	1	80000

WHERE vs HAVING

WHERE filters rows before grouping. HAVING filters groups after aggregation:

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE city IS NOT NULL          -- row filter
GROUP BY department
HAVING AVG(salary) > 90000;     -- group filter

FROM→WHERE→GROUP BY→HAVING→SELECT→ORDER BY→LIMIT

The logical order SQL evaluates a query — not the order you write it.

⚡ Try it yourself

Write a query that returns each city (excluding NULL) with the number of employees and the highest salary there, showing only cities with more than one employee.

Show a solution

SELECT city, COUNT(*) AS headcount, MAX(salary) AS top_salary
FROM employees
WHERE city IS NOT NULL
GROUP BY city
HAVING COUNT(*) > 1;

Interview questions

QWhy can't you use a column alias from SELECT in the WHERE clause?+

Because WHERE is evaluated before SELECT in SQL's logical processing order, so the alias doesn't exist yet. (Many databases do allow aliases in ORDER BY, which runs after SELECT.)

QWhat's the difference between COUNT(*) and COUNT(column)?+

COUNT(*) counts rows. COUNT(column) counts rows where that column is not NULL.

Key takeaways

WHERE filters rows; HAVING filters groups.
Use IS NULL, never = NULL.
SQL runs FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY.