Intermediate SQL - Aggregate Functions Flashcards
SQL allows you to zoom in and out to better understand an entire dataset, its subsets, and its individual records. You'll learn to summarize data using aggregate functions and perform basic arithmetic calculations inside queries to gain insights into what makes a successful film.
What does an aggregate function do?
performs calculation on several values and returns a single value
What are the five most common aggregate funtions?
1) COUNT()
2) AVG()
3) SUM()
4) MIN()
5) MAX()
What aggregate functions can only be used with numerical fields?
AVG and SUM
What aggregate functions can be used with various types of data?
COUNT, MIN, and MAX
Why are we able to combine aggregate functions with the WHERE clause?
because the WHERE clause executes before the SELECT statement
What are the two parameters for the ROUND() operator?
ROUND(number_to_round, decimal_places)
True/False - The decimal_places parameter in the ROUND() operator is optional if you are rounding to a whole number.
True - ROUND() will return a whole number by default
True/False - A negative number can be placed in the decimal_places parameter in the ROUND() operator.
True - negative numbers will round to the left of the decimal point instead of the right
ROUND() can only be used with what type of fields?
numerical fields
What are the four basic arithmetic symbols you can use in SQL? What do parenthesis determine?
+, -, *, and / (parenthesis indicates the order of execution)
What gets returned when we try dividing integers? Why?
an integer because SQL assumes we want an integer back if we divide two integers. We can add decimals to our numbers to get more precision.
What is the difference between aggregate functions vs. arithmetic?
Aggregate functions (like SUM) perform their operations on the fields vertically while arithmetic adds up the records horizontally
When summarizing data with aggregate functions and arithmetic we will always need to use what?
An alias
Where are aliases defined in the query?
SELECT statement
Aliases defined in the SELECT clause cannot be used in the WHERE clause due to what?
order of execution
