SQL - Structured Query Language Aggregate Functions and Grouping Questions and Answers 1 — 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?
- SELECT, FROM, WHERE, GROUP BY
- SELECT, FROM, GROUP BY, HAVING (Correct answer)
- SELECT, FROM, WHERE, HAVING
- SELECT, FROM, GROUP BY, ORDER BY
Correct 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().
Question 2: Which of the following SQL queries correctly calculates the number of employees in each department?
- SELECT department, COUNT(employee_id) FROM employees;
- SELECT department, SUM(employee_id) FROM employees GROUP BY department;
- SELECT department, COUNT(employee_id) FROM employees GROUP BY department; (Correct answer)
- SELECT COUNT(department) FROM employees;
Correct 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.
Question 3: What is the primary difference between the `WHERE` and `HAVING` clauses in a SQL query?
- The `WHERE` clause filters groups after aggregation, while `HAVING` filters individual rows before aggregation.
- `HAVING` is just a newer, preferred syntax for the `WHERE` clause.
- `WHERE` can only be used on numeric data types, while `HAVING` can be used on any data type.
- The `WHERE` clause filters individual rows before aggregation, while `HAVING` filters groups after aggregation has occurred. (Correct answer)
Correct 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.
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;`
- An alias is required for the `ProductName` column.
- The `Price` column is in the SELECT list but is not part of an aggregate function or the GROUP BY clause. (Correct answer)
- The `GROUP BY` clause cannot be used with a non-numeric column like `ProductName`.
- The `SUM()` function is missing from the `GROUP BY` clause.
Correct 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.
Question 5: Which of the following is NOT a standard SQL aggregate function?
- AVG()
- SUM()
- MEDIAN() (Correct answer)
- COUNT()
Correct 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.
Question 6: You need to find the minimum, maximum, and average salary for each job title in the 'Employees' table. Which query accomplishes this?
- SELECT JobTitle, MIN(Salary), MAX(Salary), AVG(Salary) FROM Employees;
- SELECT JobTitle, MIN(Salary), MAX(Salary), AVG(Salary) FROM Employees GROUP BY JobTitle; (Correct answer)
- SELECT JobTitle, AGG(Salary) FROM Employees GROUP BY JobTitle;
- SELECT JobTitle, Salary FROM Employees WHERE Salary = MIN() OR Salary = MAX() OR Salary = AVG();
Correct 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.
SQL - Structured Query Language Aggregate Functions and Grouping Questions and Answers 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?