Intermediate SQL - Sorting and Grouping Flashcards
This final chapter teaches you how to sort and group data. These skills will take your analyses to a new level by helping you uncover critical business insights and identify trends and performance. You'll get hands-on experience to determine which films performed the best and how movie durations and budgets changed over time.
Which keyword is used to sort results of one or more fields?
ORDER BY
How does the ORDER BY keyword sort by default?
Ascending (smallest to biggest or A-Z)
How can we change the sort of the ORDER BY keyword?
We can add the DESC keyword to sort in ascending order
True/False - With ORDER BY, you have to select the field you want to sort on.
False - we don’t have to select the field we are sorting on,, but its a good idea to mention it in the select for clarity
If there are two fields in an ORDER BY separated by a comma - when will the second field be thought of as a tie-breaker?
When the first field is not decisive in telling the order
If we are using ORDER BY on two fields and are getting a tie we can break that tie by adding what?
A second sorting field
If we are using ORDER BY on two fields and are getting a tie we can break that tie by adding what?
A second sorting field
True/False - We can select a different order for each field we are sorting when using an ORDER BY on multiple fields.
True
What is the correct order of execution for this query?
FROM
WHERE
SELECT
ORDER BY
LIMIT
What keyword is used to group data in SQL?
GROUP BY
GROUP BY is often used alongside [blank] to provide summary statistics, particularly when only grouping a single field and selecting multiple fields.
aggregate functions
Why is GROUP BY often used with aggregate functions?
the aggregate functions will reduce the non-grouped field to one record only, which will need to correspond to one group
SQL will return an error if we try to SELECT a field that is not in our GROUP BY clause. We’ll need to correct this by doing what?
adding an aggregate function around the field that is not in our GROUP BY clause
We can use GROUP BY on multiple fields similar to ORDER BY, but the order we write the fields affects what about the data?
affects how the data is grouped
True/False - We can combine GROUP BY with ORDER BY to group our results, make a calculation, and then order our results.
True - Here is an example query without ORDER BY, and this is the same query with ordering added.
What is the correct order of execution for this query?
FROM
GROUP BY
SELECT / ALIAS
ORDER BY
LIMIT
True/False - In SQL, we can filter aggregate functions with WHERE clauses.
False - groups have their own special filtering word
What filtering keyword is used to filter based on the results of an aggregate function?
HAVING
What is the order of execution for this query?
FROM
WHERE
GROUP BY
HAVING
SELECT
ORDER BY
LIMIT
