Aggregate Functions and Grouping Flashcards

A data analyst is asked to provide a report showing only the product categories whose average list price is greater than $150. Which combination of clauses is required to produce this result?

Answer: SELECT, FROM, GROUP BY, HAVING

To filter the results of an aggregate function like AVG(), the HAVING clause is required. The GROUP BY clause is needed to group the products by category so the average price can be calculated for each one. The WHERE clause filters rows before aggregation, so it cannot be used to filter on the result of AVG().

Which of the following SQL queries correctly calculates the number of employees in each department?

Answer: SELECT department, COUNT(employee_id) FROM employees GROUP BY department;

This query correctly uses the COUNT() aggregate function to count employees and the GROUP BY clause to create a separate count for each distinct 'department'. The other options are incorrect because they either lack a GROUP BY clause, use the wrong aggregate function (SUM), or count the wrong thing.

What is the primary difference between the `WHERE` and `HAVING` clauses in a SQL query?

Answer: The `WHERE` clause filters individual rows before aggregation, while `HAVING` filters groups after aggregation has occurred.

The key distinction lies in the SQL order of operations. The `WHERE` clause is processed before `GROUP BY`, so it filters individual rows. The `HAVING` clause is processed after `GROUP BY`, allowing it to filter the summarized or aggregated results.

A query is written to find the total sales for each product. Why is the following query syntactically incorrect? `SELECT ProductName, Price FROM Sales GROUP BY ProductName;`

Answer: The `Price` column is in the SELECT list but is not part of an aggregate function or the GROUP BY clause.

When using `GROUP BY`, any column in the `SELECT` list must either be part of the `GROUP BY` clause or be used within an aggregate function (like SUM(), AVG(), COUNT()). In this case, `Price` is a non-aggregated column and is not in the `GROUP BY` list, which will cause an error in most SQL dialects.

Which of the following is NOT a standard SQL aggregate function?

Answer: MEDIAN()

While MEDIAN() is a common statistical calculation, it is not a standard aggregate function in ANSI SQL, unlike COUNT(), SUM(), AVG(), MIN(), and MAX(). Some database systems have implemented their own version of a median function, but it is not universally available.

You need to find the minimum, maximum, and average salary for each job title in the 'Employees' table. Which query accomplishes this?

Answer: SELECT JobTitle, MIN(Salary), MAX(Salary), AVG(Salary) FROM Employees GROUP BY JobTitle;

This query correctly groups the rows by `JobTitle` and then applies the `MIN()`, `MAX()`, and `AVG()` aggregate functions to the `Salary` column for each of those groups, providing the required statistics per job title.