3.3 Aggregate functions

Question 1

What is an aggregate function?

Answer 1

An aggregate function processes values from a set of rows and returns a summary value, such as COUNT(), MIN(), MAX(), SUM(), and AVG().

Aggregate functions: SUM(Salary) | Arithmetic: SUM(Salary + Bonus)

Question 2

How does a SELECT statement use aggregate functions?

Answer 2

Aggregate functions are included in the SELECT clause to summarize data from rows that meet the WHERE clause criteria. If no WHERE clause exists, they summarize all rows.

Question 3

What does the COUNT() function do?

Answer 3

The COUNT() function counts the number of rows in a specified set.

Question 4

When is the GROUP BY clause used in SQL?

Answer 4

The GROUP BY clause is used to group rows that have the same values in specified columns, allowing SQL to perform aggregate functions on each group separately.

Question 5

Can you explain the importance of having the GROUP BY clause in a query?

Answer 5

The GROUP BY clause organizes data into groups, allowing aggregate functions to calculate summary values for each group, providing insightful analysis of the data.

Question 6

How does the HAVING clause work with GROUP BY?

Answer 6

The HAVING clause filters the results of a GROUP BY operation based on a specified condition, allowing for more refined data analysis.

Question 7

What happens if NULL values are present in aggregate functions?

Answer 7

Aggregate functions ignore NULL values, which can lead to unexpected results when arithmetic operations are applied to aggregate values that include NULLs.

Aggregate functions: SUM(Salary) | Arithmetic: SUM(Salary + Bonus)

Question 8

What is the difference between SUM(Salary) + SUM(Bonus) and SUM(Salary + Bonus)?

Aggregate functions: SUM(Salary) | Arithmetic: SUM(Salary + Bonus)

Answer 8

1. Arithmetic operators return NULL when either operand is NULL.
2. Aggregate functions ignore NULL values & may generate surprising results when NULL is present.

Aggregate functions: SUM(Salary) | Arithmetic: SUM(Salary + Bonus)

Question 9

Write a SQL query using the aggregate function AVG() to find the average salary from a table called Compensation.

Answer 9

SELECT AVG(Salary) FROM Compensation;

Question 10

How can you filter results based on aggregate functions?

Answer 10

You can use the HAVING clause to filter groups formed by the GROUP BY clause based on the results of aggregate functions.

Question 11

Refer to the Employee Table

What would the aggregate functions in the SELECT statement result in?
~~~
SELECT AVG(Salary)
FROM Employee;
~~~

Answer 11

Result:

AVG(Salary)
57333.333333

Question 12

Refer to the Employee Table

What would the aggregate functions in the SELECT statement result in?
~~~
SELECT MIN(Salary)
FROM Employee;
~~~

Answer 12

Result:

MIN(SALARY)
32000

Question 13

Refer to the Employee Table

What would the aggregate functions in the SELECT statement result in?
~~~
SELECT COUNT(*)
FROM Employee
WHERE Bonus > 500;
~~~

Answer 13

Result:

COUNT(*)
2

Question 14

Refer to the Auto Table

What value returns from the SELECT statement?

SELECT MIN(Year)
FROM Auto;

Answer 14

Result:

Min(Year)
2014

MIN(Year) finds the minimum (lowest) year.

Question 15

Refer to the Auto Table

What value returns from the SELECT statement?

SELECT SUM(Price) 
FROM Auto;

Answer 15

Result:

SUM(Price)
92300

The sum of the price is selected.

Question 16

Refer to the Auto Table

What value returns from the SELECT statement?

SELECT COUNT(*) 
FROM Auto 
WHERE Price < 10000;

Answer 16

Result:

COUNT(*)
2

Two rows have Price < 10000

Question 17

Refer to the City Table

What value returns from the SELECT statement?

Answer 17

Result:

Question 18

What was the likely code used in the GROUP clause?

Answer 18

SELECT CountryCode, SUM(Population)
FROM City
GROUP BY CountryCode;

Question 19

What was the likely code used in the GROUP clause?

Answer 19

SELECT CountryCode, District, COUNT(*)
FROM City
GROUP BY CountryCode, District;

Question 20

What was the likely code used in the HAVING clause?

Answer 20

SELECT CountryCode, SUM(Population)
FROM City
GROUP BY CountryCode
HAVING SUM(Population) > 2300000;

Question 21

What was the likely code used in the HAVING clause?

Answer 21

SELECT CountryCode, District, COUNT(*)
FROM City
GROUP BY CountryCode, District
HAVING COUNT(*) >= 2;

Question 22

Refer to the Auto table | HAVING Clause

What would be the result of the statement below?

SELECT Make, COUNT(Make) 
FROM Auto 
GROUP BY Make
HAVING COUNT(Make) > 1;

Answer 22

1. GROUP BY Make ensures each auto make is counted separately.
2. HAVING COUNT(Make) > 1 eliminates Ford and Volkswagen, which appear in only one row each.

Question 23

Refer to the Auto table | HAVING Clause

What would be the result of the statement below?

SELECT Year, Type, MAX(Price) 
FROM Auto 
GROUP BY Year, Type 
HAVING MAX(Price) > 15000
ORDER BY Year, MAX(Price);

Answer 23

1. The results show the max price in each year and type group, but only for groups having a max price > 15,000.
2. The HAVING clause excludes the groups (2014, compact, 8800) and (2016, sedan, 10200).