Okey dokey.
Artichoke?
Yep.
That's how I'm starting this video off.
It's your turn.
So we've covered four different things here.
Four new tools for this mysterious, magical toolbox.
We've got distinct as well as order by and then limit.
And then finally, like and you're going to put those to use along with some of the stuff you've learned
up until this point with a couple of exercises and there are some clown emojis here to show you how
excited you should be.
And they're not at all creepy.
So here's the first thing I need you to do or I'd like you to do.
Please, please do it.
Select all story collections.
So some of these books have titles that contain the word stories, which tells you that they're a collection
of stories.
What we talk about when we talk about love stories, really good read, by the way, where I'm calling
from selected stories, oblivion stories.
So using what we just saw.
And that's something I just want to point out for all of these exercises, there are ways that you could
directly select things maybe a little bit easier, or you could cheat in a way, but make sure that
you do it the way that I specify, please, using the things you just learned.
So specifically titles that contain the word stories, not exactly stories, but something that contains
stories in it.
So that's the first one.
All right.
And then the next one, find the longest book in the entire database or in the entire books table.
So this is what you should get back.
Print out the title and the pages.
The Amazing Adventures of Cavalier and Clay is 634 pages.
Of course, don't just select based off of where pages is exactly 634 or where the title is The Amazing
Adventures of Cavalier and Clay.
Please do it the right way so that your query would always work, no matter how many books are in the
table and what they're named and how many pages they have, it will always get the longest book.
Okay.
Just one.
Then the next problem.
Print a summary.
In our case, a summary contains the title of the year and print three only three of the most recent
books.
So 2017, 2016 and 2014.
So again, a summary and it says up here it says summary.
So that remember how to do that the title dash the year it was released and then only print out the
three most recently released books.
All right.
Next next up is find all the books whose authors last name contains a space character.
And it's only David Foster Wallace.
In our case, don't cheat and just say we're author.
Our name is exactly foster space.
WALLACE But how can you find any author last name that has a space in it?
And then once you do that, find the three books with the lowest number in stock, the lowest stock
quantity.
So as you can see here, we've got American Gods is 12.
Where I'm calling from has 12.
What we talk about when we talk about love has 23.
And I'd like you to print the title, the released year and the stock quantity.
So just to reiterate, the three lowest stocks.
All right, then we have this one print the title and the author last name.
So you've got title author name sorted by the author's last name.
So this is all of the books, by the way.
So sort by the author last name and then sort by title.
Because some of these authors, for example, Carver or Eggers or Gaiman, have multiple books.
So if we look at Carver, for instance, Carver has what we talk about, blah, blah, blah, where
I'm calling from, what we talk about comes before where I'm calling from alphabetically.
If that makes sense.
Same thing with Eggers a heart breaking, a hologram, the circle so sort first by the last name of
the author, and then on top of that, the title.
Then we've got this one.
Just make this happen.
It's kind of difficult to write out exactly what's happening.
The main idea is that we're going to go through every book.
You're going to print out this string here.
My favorite author is and this is yelling in all caps, I won't wake my neighbors.
It's kind of late, but my favorite author is and then the author's first name capitalized and the author's
last name capitalized and an exclamation point at the end.
And do that for every book sorted alphabetically by the author's last name.
So you can see Carver comes first Eggers, Foster, Wallace, Gammon down to Steinbeck.
And then make sure you call it yell up here.
Yes, it's a bit of a complicated one, but fortunately it's last.
Hopefully you can make this one work.
It's a little bit long.
Good luck.
See you in The Solution video.