Right so hopefully you made it through those exercises or maybe you're just wanting to watch the solutions
or maybe you're curious to know how you got here.
What I'm going to do in this video is go over the solutions to the previous video and that's about noise
that happened.
All right.
So the first one.
Select All story collections that.
So these 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 as well 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 that is to 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 where and we're going to do title like.
And this is you know we just do stories.
It's not going to work.
That gives us exactly the stories we're going to want that wildcard 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 the last wildcard 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.
OK.
So now 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 a 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.
It's now the first one is the longest and then we just add our limit 1 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 that's commented out and move on.
Print out a summary of containing the title and year for the three most recent books.
So this one required you to not only use ORDER BY and limit or ordering by most recent year and then
limiting it to three but you also needed to use concatenate to concatenate the title the dash with a
year.
So let's do that now let's just start by doing this select Concat.
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 OK.
Oh the other thing we wanted to do is clean this up.
It says summary up there.
So this is also review.
Say as summary.
OK.
So now we get that now 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 at least 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.
I met three or if you want to get fancy you could do this but I don't like doing that.
So that's much easier.
Get.
So say give this link in the bardo Dasch 20:17 Norse mythology d'Este 2016.
10 percent happier dasht 2014 summary.
That's all good to go.
Next up find all the books that have an author last name containing a space.
Don't cheat by just doing Foster Wallace.
So that's come with this out first.
This one is actually pretty short and easy.
It's just a select and we're looking for a title and author name select title comma author name where
author name like and of course you can't just say space because that's only going to find authors whose
last name is exactly space and it looks like I'm forgetting from books.
Hopefully you caught that before I did.
But back to what are 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 wildcard
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 three books with the lowest stock.
So we want the title released 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.
It's like tidal.
What else was it released here in stock quantity.
OK.
Just like that.
And we have are from books.
We'll start there.
Make sure that works.
OK.
So now 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 are looking for the lowest stock quantity 12 all the way up to 1000.
Okay.
And then we want to limit it to 3.
I don't know why I picked three so many times in these exercises.
Let's try it.
Now with that limit 3 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 title and author last name sorted first by author last name and then by
title.
So let's get the title and author a last name to print first select title comma author 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 Al name and that gives just the alphabetical order we're looking
for.
We don't need to use descending.
That's all right.
But then if you look over here we take Carver's books.
Notice that where I'm calling from comes before what we talked 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 be title
because then it purely starts by title and ignores.
Author name rather we want to do this author 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 and so I'm going to change it back.
But I will also duplicate that fellow and change it to to come a one incumbent that out as well.
So we have both solutions.
Which brings us to the last problem.
Hopefully it's OK to make this happen.
So we want to yeah.
My favorite author is.
And then the uppercase first name of an author and then a space in uppercase last name of an author
and an exclamation point.
He has kind of a lot.
OK so let's start with selecting and we know already that we can start with select from books.
Sure what we're going to want to do is concatenate.
My favorite author is.
And then in upper case version of Raymond and space and then uppercase version for last name.
So an exclamation point to a lot of concatenation.
So we're going to have a concat and I'll just do this.
Well let's do this way.
Just like that.
What are we concatenating.
Well let's start with uppercase or without.
So let's just start with my favorite author is ideaspace comma and then we want Arthur F. name and then
we want another space.
This space here.
So they don't just run together.
Comma.
Author L name finally an exclamation point.
That'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 see appear the column heading here is this entire thing
so let's add the as yet.
Now if I run it there we go.
OK.
So we're close.
The next thing we need to do is capitalize author f name and our author last name which is easy just
in Upper
like that save.
Rerun it.
OK it's all uppercase.
Now we have one last thing which is sorting alphabetically by the last name.
So from books.
By Author Elman one query here.
Let's try it.
Looks like it worked.
We got Raymond Carver all the way down to my favorite authors John Steinbeck and that's about as loud
as lot Yellin 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 we use those a lot.
It is very useful to be able to determine you know the I mean highest page count maybe not but the best
selling book.
Once we get to sales and users and stuff for the most active users are like it keep coming back to the
highest commented post or the most trafficked the most clicked on ad all those sorts of bits of data
are really useful.
And we use ORDER BY 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.