All right.
So hopefully you made it through those exercises, or maybe you're just wanting to watch the solutions,
or maybe you're here accidentally.
However you got here, what I'm going to do in this video is go over the solutions to the previous video,
and that's a bad noise.
Did that happen?
All right.
So the first one, select all story collections that.
So those are titles that contain the word stories in them somewhere.
And we just want to print the title.
So I'm going to go over here and make some space.
And the first thing that I'll do is just make a new file and I'll just call it exercises dot SQL, and
then I'll open that up and we'll start off with this first one select title from books and we'll start
there.
Just make sure we're doing everything correctly, source refining selections, slash exercises, SQL,
make sure that it's working, selecting the titles.
So now we only want the ones that contain stories.
So that's going to be aware and we're going to do title like and this is if we just do stories, it's
not going to work.
That gives us exactly stories.
We're going to want that wild card character just like that.
And now we get what we're looking for.
Technically with our specific data, we could leave that last percentage, sign off that last wild card,
because all of the books that contain the word stories, it comes at the end.
But that may not always be the case.
There might be a book out there called My Favorite Stories for Kids or something like that.
And if we wanted this truly to just find all books that have stories in the title, we need both of
those wild card characters.
All right.
So that's the first one.
Next up, find the longest book.
So print out the title and the page.
Count for the one longest book.
So we'll go ahead and comment this line out and let's just do a select title and pages from books just
like that to start.
So now what we want to do is sort them, we'll sort them by the pages.
So that's an order by pages.
And if we just do the default sort.
Remember that we get ascending rather than descending, which is what we want.
What we're going to do is use limit to give us the first result.
But right now, if we do limit one, it's going to give us the shortest book rather than the longest
one.
So we want to reverse that sort with the descending order.
So now the first one is the longest.
And then we just add our limit one and we run it again and again and again.
And it always works.
And so whatever book has the longest page count will always be what is printed out here.
All right, so that's that one.
Let's comment it out and move on.
Print out a summary containing the title and year for the three most recent books.
So this one required you to not only use order by and limit we're ordering by most recent year and then
limiting to three.
But you also needed to use Concatenate to concatenate the title with a dash with a year.
So let's do that now.
Let's just start by doing the select Kann cat and it was title comma.
Then we have a space and a dash and a space comma released here.
And we'll start with just that from Books Save.
Okay.
Oh, the other thing we wanted to do is clean this up.
So it says summery up there.
So this is also review, we'll say as summery.
Okay.
So now we get that.
Now we want to order them by most recent.
And let's break this up on to some different lines here.
From Books Order by and we'll order by released here.
And if we try that now, we also see that it does work, but it's in the opposite order that we want
we want most recent.
So we need to reverse that again with descending.
And then finally, we want to limit it to the first three, limit three or if you want to get fancy,
we could do this, but I don't like doing that, so that's much easier.
So that gives us Lincoln in the Bardo Dash 2017 Norse Mythology Desk 2016 10% happier dash 2014 summary.
That's all good to go.
Next up, find all the books that have an author last name containing a space and don't cheat by just
doing Foster Wallace.
So let's comment this out first.
And this one is actually pretty short and easy.
It's just a select and we're looking for title and author l name select title comma author l name where
author l name like.
And of course, we can't just say space because that's only going to find authors whose last name is
exactly space.
And it looks like, Oh, I'm forgetting from books.
Hopefully you caught that before I did.
But back to what I was saying, this isn't going to work because we don't have any authors whose last
name is space.
So what we want is that wild card.
On either side, anything with a space and then anything afterwards.
Now we get Foster Wallace, and that's it for that one.
Next up, find the three books with the lowest stock.
So we want the title, the release here and the stock quantity.
Three books with the lowest stock.
So let's start with commenting this out just like we ve done with all the others select title.
But also is it released here in stock quantity.
Just like that.
And we'll have our from books.
We'll start there, make sure that works.
Okay.
So now what we want to do is sort them by stock quantity.
So that's just an order by stock quantity.
And you can see that gives us the order we want.
We want them to be in ascending order this time rather than descending, because we're looking for the
lowest stock quantity 12 all the way up to 1000.
And then we want to limit it to three.
I don't know why I picked three so many times in these exercises.
Let's try it now with that limit three.
And there we go, American gods, where I'm calling from, what we talk about when we talk about love,
that's what we're looking for.
Then we've got this one print the title and author last name sorted first by author last name and then
by title.
So let's get the title and author.
Last name to print first.
Select.
Title comma author l name from books.
Just like that.
And at this point, we all know what that does.
So now let's sort by the author last name.
So that's just a simple order by author LL name.
And that gives us the alphabetical order we're looking for so we don't need to use descending.
That's all right.
But then if you look over here, if we take Carver's books, notice that where I'm calling from comes
before what we talk about when we talk about love, even though w h a comes before w h e.
So we want to now sort by title there.
And the way we do that is not by just changing that to B title, because then it purely starts by title
and ignores author l name rather.
We want to do this.
Author L name comma title.
And there we go.
Now, you could also replace this whole thing with two comma one, which I really don't like, just
because it's not very semantic.
It's not meaningful to look at this, but it is shorter.
And again, that too is referring to author L name, and one is referring to title.
It's just the order that we're selecting them in.
So I'm going to change it back, but I will also duplicate that below and change it to two.
Come on one and comment that out as well.
Just so we have both solutions.
Which brings us to the last problem.
Hopefully it's the last.
It is.
Make this happen.
So we want to yell.
My favorite author is.
And then the uppercase first name of an author.
And then a space in the uppercase last name of an author.
And then exclamation point.
Yeah, it's kind of a lot.
Okay, so let's start with selecting.
And we know already that well, we can start with select from books.
Sure.
What we're going to want to do is concatenate.
My favorite author is and then an uppercase version of Raymond and a space and then uppercase version
of the last name.
So an exclamation point.
It's a lot of concatenation.
So we're going to have a King Cat and I'll just do this.
Well, let's do it this way.
Just like that.
What are we, Concatenating?
Well, let's start without the uppercase or without.
So let's just start with my favorite author is and we'll add a space comma and then we want author f
name and then we want another space, this space here so they don't just run together.
Comma.
Author.
LL Name.
Finally an exclamation point.
It's a lot.
So let me drag this down a bit so we can see the whole query.
Select the concatenation of my favorite author is Space.
The first name of an author space the last name of an author.
Exclamation point.
Let's save it and run it.
It's pretty long.
Oh, and this is partially messed up because you can see up here the column heading here is this entire
thing.
So let's add the as yellow.
And now if I run it.
There we go.
Okay, so we're close.
The next thing we need to do is capitalize author f name and author last name, which is easy.
Just an upper.
Like that.
Save re run it.
It's all uppercase.
Now we have one last thing which is sorting alphabetically by the last name.
So from books order by author.
Any long query here.
Let's try it.
It looks like it worked.
We got Raymond Carver all the way down to my favorite author is John Steinbeck.
And that's about as loud as I'll yell in here.
Don't want to wake my neighbors.
All right.
So congratulations.
Hopefully, you made it through this relatively unharmed.
It's a lot of exercises, a lot of ordering by and limiting.
But the reality is you'll use those a lot.
It is very useful to be able to determine the the I mean, highest page count, maybe not, but the
best selling book once we get to to sales and users and stuff for the most active users or like I keep
coming back to the highest commented post or the most trafficked the most clicked on add.
All those sorts of bits of data are really useful and you'll use order, buy and limit all the time
as well as other tools that we saw distinct and like they're all very useful.
All right.
Well, I recommend you take a quick little break and then we'll move on.