Chapter 2, Level 1: Aggregate Functions

Question 1

The … function calculates the smallest value for the selected column.

Answer 1

min

Question 2

Which ‘aggregate function’ statement will return the numerical sum of all values in one column or more?

Answer 2

SELECT sum(column_name)
FROM table_name;

Question 3

Which ‘aggregate function’ statement will return the total number of rows in a table, except for those with a null value?

Answer 3

SELECT count(column_name)
FROM table_name;

Question 4

Which functions (also known as aggregate functions) only work if the columns contain numbers?

Answer 4

min
max
sum
avg

Question 5

Which ‘aggregate function’ statement will return the numerical average of all values in one column or more?

Answer 5

SELECT avg(column_name)
FROM table_name;

Question 6

Which ‘aggregate function’ statement will return the total number of rows in a table?

Answer 6

SELECT count(*)
FROM table_name;

Question 7

True or false:

When using the ‘count(*)’ function statement, rows with ‘null’ values will be returned as well.

Answer 7

True.

Question 8

If you wanted to know the lowest and highest numerical values of a column, which statement would return these values?

Answer 8

SELECT min(column_name), max(column_name)
FROM table_name;

Question 9

The … function calculates the largest value for the selected column.

Answer 9

max

Question 10

Which ‘aggregate function’ statement will return the smallest numerical value in one column or more?

Answer 10

SELECT min(column_name)
FROM table_name;

Question 11

Which ‘aggregate function’ statement will return the largest numerical value in one column or more?

Answer 11

SELECT max(column_name)
FROM table_name;

Question 12

You have one column called Cost and another column which contains several different categories (or groups) called Products.
Give an example of a statement which will filter data in a table, to find the total cost of specific groups within the Products column.

Answer 12

SELECT column_name, sum(column_name)
FROM table_name
GROUP BY column_name

Question 13

How can we filter data in a table to find the ‘total cost, lowest value, highest value or average’ of a specific group?

Answer 13

SELECT column_name1, aggregate_function(column_name2)
FROM table_name
GROUP BY column_name1

Question 14

What does the HAVING clause do?

Answer 14

The HAVING clause can be used to only list groups that have more than one row associated with them.

Question 15

Give an example of a SQL statement with a HAVING clause.

Answer 15

SELECT column_name, sum(column_name)
FROM table_name
WHERE column_name operator value (optional)
GROUP BY column_name
HAVING aggregate_function (column_name) operator value;