3.3 Aggregate functions

Question 1

What is an aggregate function?

Answer 1

An aggregate function processes values from a set of rows (group) 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

1. The GROUP BY clause organizes data into groups
2. It allows aggregate functions to calculate & summarize values for each group.

provides insightful analysis of the data; magnifying it

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 include value 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.

It’s the only way to filter results based on aggregate functions in SQL

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

What was the likely code used in the GROUP clause?

Answer 17

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

Question 18

What was the likely code used in the GROUP clause?

Answer 18

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

Question 19

What was the likely code used in the HAVING clause? With 2300000.

Answer 19

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

Question 20

What was the likely code used in the HAVING clause?

With >= 2

Answer 20

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

Question 21

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 21

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 22

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 22

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

Question 23

Refer to the image

What common functions are these?

Answer 23

Numeric functions operates on, and evaluates to, specific data type

Question 24

Refer to the image

What common functions are these?

Answer 24

String functions manipulate string values.

Question 25

Refer to the image

What common functions are these?

Answer 25

Aggregate functions

processes values from a set of rows and returns a summary value

Question 26

1. Create A SELECT statement to compute the absolute value of (column X), to the power of 3.
2. The Book table has the following columns:

• ID INT,
• X INT,
• Y INT

Answer 26

POW(ABS (X), 3)

1. ABS(X): Returns the absolute value of X.
2. POW(ABS (X), 3): Returns ABS(0) to the power of 3.