More Grouping

Question 1

What aggregate function counts all rows?

Answer 1

COUNT (*)

Last Revised: 4/2/21, 2.

Question 2

What aggregate function counts all non-NULL values?

Answer 2

COUNT (column_name)

Last Revised: 4/2/21, 2.

Question 3

What aggregate function averages all non-NULL values?

Answer 3

AVG (column_name)

Last Revised: 4/2/21, 2.

Question 4

What aggregate function returns the maximum (highest) value?

Answer 4

MAX (column_name)

Last Revised: 4/2/21, 2.

Question 5

What aggregate function returns the minimum (lowest) value?

Answer 5

MIN (column_name)

Last Revised: 4/2/21, 2.

Question 6

What aggregate function returns the sum (total) of all non-NULL values?

Answer 6

SUM (column_name)

Last Revised: 4/2/21, 2.

Question 7

What do aggregate functions do with NULL values?

Answer 7

Aggregate functions ignore NULL values.

Last Revised: 4/2/21, 2.5

Question 8

What keyword can be used immediately after the SELECT keyword to indicate that you only want rows returned that have unique combinations of values in the column identified in the SELECT clause?

Answer 8

Use the keyword DISTINCT when you want unique values rather than all values.

(Last Revised: 4/2/21, 2.8)

Question 9

The training companion lists two ways the use DISTINCT. What are they?

Answer 9

1. Using DISTINCT in the SELECT clause to return rows that have unique combinations of values in the column identified in the SELECT clause
2. Using DISTINCT with COUNT to return the number of unique values in the given column

(Last Revised: 4/2/21, 2. )

Question 10

Can aggregate data be filtered on in the WHERE clause?

Answer 10

No
• Filter on aggregate data in the HAVING clause.

(Last Revised: 4/2/21, 2.10)

Question 11

Can the HAVING clause reference column aliases?

Answer 11

No
• The HAVING clause is processed before the SELECT clause.

(Last Revised: 4/2/21, 2.10)

Question 12

Is the HAVING clause processed before or after the WHERE clause?
Why is that relevant?

Answer 12

After
• The HAVING clause will work off of the data set the WHERE clause defined.

(Last Revised: 4/2/21, 2.10)

Question 13

Which clause can be used to conditionally include/exclude groups from a query result?

Answer 13

The HAVING clause.

Last Revised: 4/2/21, 2.11

Question 14

How would you achieve the necessary granularity before the GROUP BY clause is processed?

Answer 14

By using joins in the FROM clause and the WHERE clause to filter out undesired data.

(Last Revised: 4/2/21)

Question 15

How would you achieve the necessary granularity after the GROUP BY clause is processed?

Answer 15

By using the HAVING clause.

Last Revised: 4/2/21, 2.

Question 16

True or False:
If a query is using the GROUP BY clause then the ONLY columns you can display (via the SELECT clause) will be the column(s) that are in the GROUP BY clause or are part on an aggregation.

Answer 16

True

Last Revised: 4/2/21, 2.