SQL - Structured Query Language Aggregate Functions and Grouping Questions and Answers

Question 1

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?

Accepted Answer

SELECT, FROM, GROUP BY, HAVING

Answer

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().

Question 2

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

Accepted Answer

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

Answer

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.

Question 3

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

Accepted Answer

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

Answer

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.

Question 4

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;`

Accepted Answer

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

Answer

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.

Question 5

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

Accepted Answer

MEDIAN()

Answer

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.

SQL Practice Test

SQL Practice Test

SQL - Structured Query Language Aggregate Functions and Grouping Questions and Answers