Lesson 1 of 4

Querying basics: SELECT, WHERE, ORDER BY and aggregation

2 min read

Throughout this course we use a small hiring dataset:

sql

students(id, name, city, college, grad_year)
companies(id, name, city)
jobs(id, company_id, title, area, salary, posted_on)
applications(id, job_id, student_id, status, applied_on, score)

The shape of a query

sql

SELECT city, COUNT(*) AS students
FROM students
WHERE grad_year = 2026
GROUP BY city
HAVING COUNT(*) >= 10
ORDER BY students DESC
LIMIT 5;

Logical order of execution (asked constantly): FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT → ORDER BY → LIMIT. This explains why you can't use a SELECT alias inside WHERE (it doesn't exist yet), but you can in ORDER BY.

Filtering

sql

SELECT * FROM jobs
WHERE area IN ('Data Analysis', 'Web Development')
  AND salary BETWEEN 15000 AND 40000
  AND title LIKE '%Intern%'          -- % any characters, _ exactly one
  AND posted_on >= CURRENT_DATE - INTERVAL '30 days';

NULL is special

NULL means unknown. Any comparison with it is unknown, so WHERE score = NULL returns nothing.

sql

WHERE score IS NULL
WHERE score IS NOT NULL
SELECT COALESCE(score, 0) FROM applications;  -- replace NULL with a default

COUNT(*) counts rows; COUNT(score) counts non-NULL scores; AVG(score) ignores NULLs.

Aggregation

sql

SELECT area,
       COUNT(*)            AS jobs,
       AVG(salary)         AS avg_salary,
       MAX(salary)         AS top_salary,
       COUNT(DISTINCT company_id) AS companies
FROM jobs
GROUP BY area;

Every column in SELECT must be either in GROUP BY or inside an aggregate.

WHERE vs HAVING

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

sql

-- Companies that posted more than 3 jobs paying over 20k
SELECT company_id, COUNT(*) AS n
FROM jobs
WHERE salary > 20000      -- row filter
GROUP BY company_id
HAVING COUNT(*) > 3;      -- group filter

CASE: if/else in SQL

sql

SELECT name,
       CASE WHEN score >= 8 THEN 'Strong'
            WHEN score >= 5 THEN 'Average'
            ELSE 'Weak' END AS band
FROM applications a JOIN students s ON s.id = a.student_id;

-- Conditional counts: a pivot in one pass
SELECT job_id,
       COUNT(*) FILTER (WHERE status = 'shortlisted') AS shortlisted,   -- PostgreSQL
       SUM(CASE WHEN status = 'hired' THEN 1 ELSE 0 END) AS hired       -- works everywhere
FROM applications GROUP BY job_id;

Practice

Number of applications per status.
Cities with more than 50 students graduating in 2026.
Average score per job, only for jobs with at least 5 scored applications.