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

SELECT, FROM, WHERE, GROUP BY

Answer

SELECT, FROM, WHERE, HAVING

Answer

SELECT, FROM, GROUP BY, ORDER BY

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

SELECT department, COUNT(employee_id) FROM employees;

Answer

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

Answer

SELECT COUNT(department) FROM employees;

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 `WHERE` clause filters groups after aggregation, while `HAVING` filters individual rows before aggregation.

Answer

`HAVING` is just a newer, preferred syntax for the `WHERE` clause.

Answer

`WHERE` can only be used on numeric data types, while `HAVING` can be used on any data type.

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

An alias is required for the `ProductName` column.

Answer

The `GROUP BY` clause cannot be used with a non-numeric column like `ProductName`.

Answer

The `SUM()` function is missing from the `GROUP BY` clause.

Question 5

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

Accepted Answer

MEDIAN()

Answer

AVG()

Answer

SUM()

Answer

COUNT()

Question 6

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

Accepted Answer

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

Answer

SELECT JobTitle, MIN(Salary), MAX(Salary), AVG(Salary) FROM Employees;

Answer

SELECT JobTitle, AGG(Salary) FROM Employees GROUP BY JobTitle;

Answer

SELECT JobTitle, Salary FROM Employees WHERE Salary = MIN() OR Salary = MAX() OR Salary = AVG();

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?

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?