Aggregate Functions

Question 1

What are Aggregates?

Answer 1

Calculations performed on multiple rows of a Table.

Question 2

Which function is the fastest way to calculate how many rows are in a table?

Answer 2

COUNT( )

Question 3

What does this statement do?

SELECT COUNT(*)
FROM _______;

Answer 3

Counts every row

Question 4

Which function makes it easy to add all values in a particular column?

Answer 4

SUM( )

Question 5

What does this statement do?

SELECT SUM(downloads)
FROM ______;

Answer 5

This adds all values in the downloads column

Question 6

What does the SUM( ) function do?

Answer 6

It takes the name of a column as an argument and returns the sum of all the values in that column.

Question 7

Where are arguments passed in the functions?

Answer 7

Inside the parentheses of the function.

Question 8

Which function returns the highest values in a column?

Answer 8

MAX( )

Question 9

Which function returns the lowest values in a column?

Answer 9

MIN( )

Question 10

What do all functions have in common?

Answer 10

They take the name of a column as an argument and return the value for the specified information in that column.

Question 11

Which function quickly calculates the average value of a particular column?

Answer 11

AVG( )

Question 12

What does this statement do?

SELECT AVG(downloads)
FROM fake_apps;

Answer 12

This statement returns the average number of downloads for an app in the database.

Question 13

Which function makes the result Table easier to read?

Answer 13

ROUND( )

Question 14

How many arguments does the ROUND( ) function take?

Answer 14

Two

Question 15

What are the two arguments that the ROUND( ) function takes inside the parentheses?

Answer 15

A column name
An integer

Question 16

What does the ROUND( ) function do?

Answer 16

It rounds the values in the column to the number of decimal places specified by the integer.

Question 17

What does this statement do?

SELECT ROUND(price, 0)
FROM fake_apps;

Answer 17

This passes the column price and integer 0 as arguments. SQL rounds the values in the column to 0 decimal places in the output.

Question 18

What clause is used with aggregate functions and with the SELECT statement to arrange identical data into groups?

Answer 18

GROUP BY

Question 19

Where does the GROUP BY statemenet go in a query?

Answer 19

It goes after any WHERE statements, but before ORDER BY or LIMIT.

Question 20

What does this statement do?

SELECT year,
AVG(imdb_rating)
FROM movies
GROUP BY year
ORDER BY year;

Answer 20

SELECT year column to get the average imdb_rating of particular years FROM the movies Table

Question 21

Unlike GROUP BY I, GROUP BY II uses ?

Answer 21

Column references

Question 22

What are the column references in GROUP BY?

Answer 22

1 is the first column selected
2 is the second column selected
3 is the third column selected

Question 23

What does this GROUP BY II do?

SELECT ROUND(imdb_rating)
COUNT(name)
FROM movies
GROUP BY 1
ORDER BY 1;

Answer 23

The 1 refers to the first column in our SELECT statement, ROUND(imdb_rating)

Question 24

Why would GROUP BY II be used?

Answer 24

To GROUP BY a calculation done on a column.

Question 25

What clause allows the filtering of groups?

Answer 25

HAVING

Question 26

True or False: HAVING allows you to filter which groups to include and which to exclude?

Answer 26

True

Question 27

What is the difference between WHERE and HAVING?

Answer 27

WHERE filters the rows while HAVING filters the groups.

Question 28

What types of WHERE clauses can be used with HAVING?

Answer 28

ALL of them

Question 29

What does this statement do?

SELECT year,
genre,
COUNT(name)
FROM movies
GROUP BY 1, 2
HAVING COUNT(name) > 10;

Answer 29

This selects the year and genre column while counting the name column from the movies Table and grouping them by column 1 and column 2. The groups will then be filtered by the HAVING that counts the name column and sets a condition to see if its greater than 10.

Question 30

What clause is used to limit the results of a query based on values of the individual rows?

Answer 30

WHERE

Question 31

What clause is used to limit the results of a query based on an aggregate property?

Answer 31

HAVING

Question 32

Where does a HAVING statement go in a query?

Answer 32

Always after GROUP BY but before ORDER BY and LIMIT.

Question 33

What are aggregate functions?

Answer 33

Combine multiple rows together to form a single value of more meaningful information.