SQL - Aggregate Functions

Question 1

count the number of rows

Answer 1

COUNT()

SELECT COUNT(*)
FROM table_name;

Question 2

the sum of the values in a column

Answer 2

SUM()

SELECT SUM(downloads)
FROM fake_apps;

Question 3

the largest/smallest value

Answer 3

MAX(), MIN()

SELECT MAX(downloads)
FROM fake_apps;

Question 4

the average of the values in a column

Answer 4

AVG()

SELECT AVG(downloads)
FROM fake_apps;

Question 5

round the values in the column

Answer 5

ROUND()

SELECT ROUND(price, 0)
FROM fake_apps;

Question 6

_______ is a clause used with aggregate functions to combine data from one or more columns

Answer 6

GROUP BY

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

Question 7

_______ limit the results of a query based on an aggregate property.

Answer 7

HAVING

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

Question 8

Aggregate functions

Answer 8

Aggregate functions combine multiple rows together to form a single value of more meaningful information.

Question 9

Find the error in this code:

SELECT COUNT(*)
FROM songs
HAVING plays > 100;

Answer 9

It should be WHERE instead of HAVING.

It should be WHERE plays > 100 because WHERE filter rows and HAVING filter groups.

Question 10

Which function takes a column and returns the total sum of the numeric values in that column?

Answer 10

SUM()

Question 11

What does the following query do?

SELECT price,
COUNT(*)
FROM menu
WHERE orders > 50
GROUP BY price;

Answer 11

It calculates the total number of menu items that have been ordered more than 50 times – for each price.

Question 12

How would you calculate the minimum number of stops from the train table?

Answer 12

SELECT MIN(stops)
FROM train;

Question 13

What does the ROUND function take as argument(s)?

Answer 13

The column name, and the number of decimal places to round the values in the column to.

Question 14

The WHERE clause filters rows, whereas the HAVING clause filter groups. T/F

Answer 14

T

Question 15

What does the COUNT() function take as argument(s)?

Answer 15

The name of a column or *.

Question 16

What does the following query do?

SELECT neighborhood,
AVG(price)
FROM apartments
GROUP BY neighborhood;

Answer 16

It calculates the average price of apartments in each neighborhood.

Question 17

What does the following query do?

SELECT genre,
SUM(downloads)
FROM kindle
GROUP BY genre;

Answer 17

It returns the total amount of downloads – for each genre.