Section 8 - Refining Our Selections Flashcards
What does the DISTINCT function do?
It prints back only results that are unique/distinct.
Write a function that prints only unique author_lastname s from a table called le_book_shop.
SELECT DISTINCT author_lastname FROM le_book_shop;
Write a query that selects distinct author_firstname s and author_lastname s from a table called book_store.
SELECT DISTINCT(CONCAT(author_firstname, ‘ ‘, author_lastname)) AS ‘Distinct Author Names’ FROM book_store;
What function do you use for returning only unique results?
DISTINCT
What are some ways you could use the ORDER BY function?
- Find the bestselling product in order store
- Order movies by released date
- Order albums by best reviewed
- Order yoyos by how many yoyos we have in stock
- Order our milk by most likely to be expired to least likely to be expired.
Should there be a space in the ORDER BY function?
YES.
Don’t get tripped up by it! It’s intentional. AUTO_INCREMENT uses an underscore, but ORDER BY does not.
What does the ORDER BY function do?
It puts things in order based on what we want it to order by. (Alphabetically, numerically, calendar date, sales data, etc etc.)
Does the ORDER BY function go before or after the “FROM “ syntax?
It goes AFTER the FROM syntax.
For example:
SELECT mvp_winners FROM sports_stuff ORDER BY year;
What command would you use for changing with direction the order goes in? Eg, change from ascending to descending / alphabetical to reverse alphabetical.
You would use the DESC command.
The DESC syntax comes after(!) ORDER BY.
For Example:
SELECT bachelor_winners FROM reality_show_contestants ORDER BY year DESC;
The above would list the results from newest to oldest.
(Ascending is the default ordering, which is lowest to highest. 1935 is a “lower” year, at least numerically, than 2019 would be, so this is why Ascending order would have older years first, newer years last.)
What is the command for sorting results in ascending order?
ASC
In context, it would look like this:
…FROM books ORDER BY released_year ASC;
Can ORDER BY be used with strings/letters as well as with numbers?
A resounding YES!
Do you have to match what you’re selecting with what you’re ordering by?
NO, these can be different!
For example:
SELECT released_year FROM books ORDER BY copies_sold_since_released;
Provided those are all valid column names, this would be a perfectly valid expression/function/query/whatever you want to call it.
If we are selecting multiple columns, what’s a shortcut we can use in our ORDER BY statement so that we don’t have to write out the column name?
Give an example.
Answer:
You use a number in place of the column name. The number corresponds to the order you write the column names.
For example:
SELECT rockstar, drugs_taken, lawsuits_won FROM music_penal_records ORDER BY 2;
In the above example, 2 refers to the drugs_taken column, because it’s second in our list of 3 total columns (rockstar, drugs_taken, lawsuits_won).
Can you ORDER BY more than one column?
YES.
Is the order sorting in which you use the ORDER BY function on multiple columns important? Why?
YES, it is important.
It’s important because your results may change based on what you ORDER BY first. For example, if you sorted first by last_name, and then by first_name, the majority of your results would be sorted by first_name.
In the above example, the sort would first sort by last_name, then sort again by first_name, which would reshuffle the results to be shuffled by first_name.
What does the LIMIT function do?
It allows us to specify a number for how many results we want to SELECT.
Why is the LIMIT function helpful?
With a lot of databases, you have thousands or even millions of results. The limit function gives us parameters, so we’re not sorting through ALL the data ALL the time.
For instance, we could limit our query only by orders within the last year, or we limit our presidential candidate lists to only current senators or we could limit Mexican restaurants only by those in CA with a 4.5 or greater star rating. The options are… LIMITLESS? (har har har)
Write a function that selects released_year from a books table and limits the results to 10.
SELECT released_year FROM books LIMIT 10;
What function generally makes the most sense to use alongside LIMIT?
ORDER BY
(Otherwise, we’d generally just be listing by whatever order the results are randomly/haphazardly listed in the database.)
Write a function that selects title and released_year from a table called bezos_bookstore_worldtakeover ordered by the year of release in descending order and limited to 25 results.
SELECT title, released_year FROM bezos_bookstore_worldtakeover ORDER BY released_year DESC LIMIT 25;
Look at this example of a (partial) LIMIT function:
ORDER BY LIMIT 0,5;
What indice/index do rows start at?
Rows start at INDEX 0, which is UNLIKE strings, which start at index 1.
Look at this example of a (partial) LIMIT function:
ORDER BY LIMIT 0,5;
What do the 0 and 5 represent?
Think of this like SUBSTRING.
The 0 and 5 is instructing the data base to return results from index 0 to index 5.
What’s an example of when you might use a limit function such as this:
ORDER BY LIMIT 10, 5;
This would be useful for showing results.
This function would start at index 10, and retrieve 5 results. Note that this would retrieve the 11th, 12th, 13th, 14th, and 15th results.
This would be great for showing the next 5 blog posts, the next 5 products in a store, etc.
Is there an easy way to write a LIMIT function that goes until the very last result?
Unfortunately, there is NOT!
You (seriously) need to simply make up a real big number that you know is bigger than the total number of rows/results your query will return.
