You want every department that has more than 5 employees.… (Databases & SQL interview question)

SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id HAVING COUNT(*) > 5

Use HAVING COUNT(*) > 5 after GROUP BY dept_id. HAVING filters groups after they are counted, so it is the place for conditions on aggregates. An aggregate is a function like COUNT(*) that turns many rows into one value.

Why the other options are wrong:
• "... WHERE COUNT(*) > 5 GROUP BY dept_id": WHERE filters single rows before grouping. The counts don't exist yet, so WHERE cannot use COUNT(*).
• "... GROUP BY dept_id WHERE n > 5": WHERE must come before GROUP BY, and WHERE cannot use an aggregate or a SELECT alias like n.
• "SELECT dept_id FROM employees HAVING COUNT(*) > 5": there is no GROUP BY. Selecting dept_id next to an aggregate without grouping by it is an error in PostgreSQL and in standard SQL. (MySQL allows it only when ONLY_FULL_GROUP_BY is turned off.)

Aggregate functions (https://www.postgresql.org/docs/current/tutorial-agg.html)postgresql.org