Section 8 - Refining Our Selections

Question 1

What does the DISTINCT function do?

Answer 1

It prints back only results that are unique/distinct.

Question 2

Write a function that prints only unique author_lastname s from a table called le_book_shop.

Answer 2

SELECT DISTINCT author_lastname FROM le_book_shop;

Question 3

Write a query that selects distinct author_firstname s and author_lastname s from a table called book_store.

Answer 3

SELECT DISTINCT(CONCAT(author_firstname, ‘ ‘, author_lastname)) AS ‘Distinct Author Names’ FROM book_store;

Question 4

What function do you use for returning only unique results?

Answer 4

DISTINCT

Question 5

What are some ways you could use the ORDER BY function?

Answer 5

• 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.

Question 6

Should there be a space in the ORDER BY function?

Answer 6

YES.

Don’t get tripped up by it! It’s intentional. AUTO_INCREMENT uses an underscore, but ORDER BY does not.

Question 7

What does the ORDER BY function do?

Answer 7

It puts things in order based on what we want it to order by. (Alphabetically, numerically, calendar date, sales data, etc etc.)

Question 8

Does the ORDER BY function go before or after the “FROM “ syntax?

Answer 8

It goes AFTER the FROM syntax.

For example:

SELECT mvp_winners FROM sports_stuff ORDER BY year;

Question 9

What command would you use for changing with direction the order goes in? Eg, change from ascending to descending / alphabetical to reverse alphabetical.

Answer 9

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.)

Question 10

What is the command for sorting results in ascending order?

Answer 10

ASC

In context, it would look like this:
…FROM books ORDER BY released_year ASC;

Question 11

Can ORDER BY be used with strings/letters as well as with numbers?

Answer 11

A resounding YES!

Question 12

Do you have to match what you’re selecting with what you’re ordering by?

Answer 12

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.

Question 13

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 13

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).

Question 14

Can you ORDER BY more than one column?

Answer 14

YES.

Question 15

Is the order sorting in which you use the ORDER BY function on multiple columns important? Why?

Answer 15

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.

Question 16

What does the LIMIT function do?

Answer 16

It allows us to specify a number for how many results we want to SELECT.

Question 17

Why is the LIMIT function helpful?

Answer 17

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)

Question 18

Write a function that selects released_year from a books table and limits the results to 10.

Answer 18

SELECT released_year FROM books LIMIT 10;

Question 19

What function generally makes the most sense to use alongside LIMIT?

Answer 19

ORDER BY

(Otherwise, we’d generally just be listing by whatever order the results are randomly/haphazardly listed in the database.)

Question 20

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.

Answer 20

SELECT title, released_year FROM bezos_bookstore_worldtakeover ORDER BY released_year DESC LIMIT 25;

Question 21

Look at this example of a (partial) LIMIT function:

ORDER BY LIMIT 0,5;

What indice/index do rows start at?

Answer 21

Rows start at INDEX 0, which is UNLIKE strings, which start at index 1.

Question 22

Look at this example of a (partial) LIMIT function:

ORDER BY LIMIT 0,5;

What do the 0 and 5 represent?

Answer 22

Think of this like SUBSTRING.

The 0 and 5 is instructing the data base to return results from index 0 to index 5.

Question 23

What’s an example of when you might use a limit function such as this:

ORDER BY LIMIT 10, 5;

Answer 23

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.

Question 24

Is there an easy way to write a LIMIT function that goes until the very last result?

Answer 24

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.

Question 25

What function do we use in searching for inexact matches?

Answer 25

The LIKE function

Question 26

What is the LIKE function?

Answer 26

The LIKE function allows us to search for inexact matches.

Question 27

What is the difference between the LIKE function and the WHERE function?

Answer 27

The WHERE function only allows us to search for exact matches, whereas the LIKE function allows us to search for inexact matches.

Question 28

Think of the ______ function as in “contains” or the “partial matches” function.

Answer 28

LIKE

Question 29

What’s an example of what you could use the LIKE function for, in practice?

Give an example in terms of finding someone in phone book or similar database.

Answer 29

Let’s say we met someone at a conference, and new their name was Allen, and that there last name started with an Ha.

We could use the LIKE function to find all results in our phone book for someone with the first name of Allen, and someone whose last name starts with Ha.

Question 30

What symbol is used to represent the LIKE function?

Answer 30

% (percentage symbol)

Question 31

Using the LIKE function:

A % sign before the query text means ___________.

Answer 31

A % sign before the query text means that we want to look for any data/text/strings/etc that occur BEFORE our search terms.

For example if we used searched using a function written as %da, we would find someone in our database named Merida, but we wouldn’t find someone in our database named David.

Question 32

Using the LIKE function:

A % sign after the query text means _________.

Answer 32

A % sign after the query text means we’re looking for anything where the query text is at the start.

For instance, a function with woo% would return “woof” or “woot”, but it would not return “swoo”.

Question 33

What is another term for the % and _ (underscore) symbol in MySQL? (eg LIKE symbols)

Answer 33

This is also known as a wildcard. Woooooo

Question 34

The ______ function is all about pattern matching.

Answer 34

The LIKE function is all about pattern matching .

Question 35

What does the Underscore represent in the LIKE function?

Answer 35

It’s a wildcard/LIKE symbol that represents one character. (Thus, 4 underscores in a row [ ____ } would represent 4 characters.

Question 36

What would this function be doing?

….WHERE stock_quantity LIKE ‘____’

[that’s 4 underscores in the quotation marks]

Answer 36

This would return results from the stock_quantity column for any entry in the stock_quantity column that’s exactly 4 characters long.
(1000, 1005, 9999, 5055, 4999, 3333, etc)

Question 37

What could this use of the underscore LIKE function help us do?

LIKE ‘(___)___-____’

Answer 37

It could help us find any data in our database that represents a phone number.

Question 38

How could you use LIKE when searching for something with a % sign or an _ (underscore) in it?

Answer 38

You would use an escape character, as we learned earlier with the apostrophe/quote.

LIKE ‘%\%%’
OR
LIKE ‘%_%’

Question 39

Write a function that selects title from our title column from our book_shop table where the title contains the word “stories”.

Answer 39

SELECT title FROM book_shop WHERE title LIKE ‘%stories%’;

Question 40

Write a function that prints the title of the single longest book, plus its corresponding page_number, from a table named book_shop.

Answer 40

SELECT title, page_number FROM book_shop ORDER BY page_number DESC LIMIT 1;