Get Your CertDatabases & SQLPlayCompete
‹ All Databases & SQL questions

Databases & SQL Practice Question 2: Where vs Having

Databases & SQL · Test 1 · question 2 of 10

Aggregation & Groupingeasy

The table employees(name, dept, emp_type, salary) holds both full-time and part-time staff. HR wants the departments whose average full-time salary is above 60000. Part-time employees must not affect the average, and there should be one row per department. Which query is correct?

Select one answer
Show answer and explanation

Answer:

  • A. SELECT dept, AVG(salary) FROM employees WHERE emp_type = 'full_time' GROUP BY dept HAVING AVG(salary) > 60000;

WHERE filters individual input rows before they are grouped and aggregated. HAVING filters the groups after the aggregates have been computed. The part-time exclusion is a per-row condition, so it belongs in WHERE, which keeps part-time salaries out of the average. The "average above 60000" condition can only be checked once each group's average exists, so it belongs in HAVING. Option A does exactly this.

Option B puts an aggregate in WHERE. When WHERE runs, no groups exist yet, so there is no average to compare. PostgreSQL (and standard SQL) rejects this with "aggregate functions are not allowed in WHERE".

Option C puts the row-level condition in HAVING. emp_type is not in the GROUP BY and is not inside an aggregate, so it has no single value per department and the query is an error. Even if it ran, part-time salaries would already be in the average.

Option D adds emp_type to the grouping, so it returns separate full-time and part-time rows for each department, including part-time groups above 60000. That breaks both requirements.

Reference: PostgreSQL: Aggregate Functions (tutorial, WHERE vs HAVING)

Practice Test 1 ›