Week 3 Flashcards
(20 cards)
What are the five basic aggregate operations in SQL?
COUNT, SUM, MIN, MAX, and AVG
What does COUNT do?
counts how many rows are in a particular column
What does SUM do?
adds together all the values in a particular column
What does MIN do?
returns the lowest value in a particular column
What does MAX do?
returns the highest value in a particular column
What does AVG do?
calculates the average of a group of selected values
Except for count, what do all these aggregations apply to?
a single attribute
What happens to NULL in aggregations?
it is ignored
What do we do if we need multiple aggregates?
we do one aggregate per column in a list after SELECT
What do we do if we want to subtract/add aggregates?
we can do an aggregate for each column then subtract or add in the SELECT statement.
ex: sum(revenue) - sum(budget) AS TotalProft
What is another way to write sum(revenue) - sum(budget) AS TotalProft?
Sum(revenue-budget) AS TotalProfit
What does everything in SELECT have to be?
GROUP-BY or an aggregate
In SQL, how does ORDER BY work?
it sorts in ascending order by default unless you explicitly specify DESC
How is the WHERE condition applied?
to individual rows
What is not allowed in the WHERE condition?
no aggregates are allowed here
What can happen when we apply the WHERE condition occasionally?
some groups become empty and are removed
How is the HAVING condition applied?
to the entire group
What happens to the entire group when we use HAVING?
either the entire group is returned or it is removed
What can we use on the group when we use HAVING?
may use aggregate functions
If a query has WHERE, GROUP BY, and HAVING, in what order does it the query?
begins with FROM and WHERE, then GROUP BY, then HAVING, and ends with SELECT
