Okeydokey, let's go through a solution for this exercise.
The first challenge is to select all the collection, or rather, all the titles that contain stories.
So I'm going to do this from within DB Gate this time just to mix it up.
Obviously, hopefully by now it's clear you can run these queries however you want.
I've tried to show a bunch of different options.
Workbench doing it in line here in the show and using a tool like DB Gate.
I'm going to do it this way because I can write these longer queries and have them on multiple lines.
It will just look a little nicer for you.
But again, the code is exactly the same.
So I'm going to select title from Books Where and All I Want is title where title like.
Remember the like.
Operator.
We just learned.
Like.
And then I can't just do stories, of course, because I'm not going to get any matches when I run it.
We get nothing back because no title is exactly stories.
But if I use my wild card characters at the beginning and end, this means the word stories needs to
come at the very beginning, at the very end, or anywhere in the middle of the string.
We don't care.
Just somewhere in there.
Optionally zero or more characters before or after.
And if I execute that, we see we get three results.
What we talk about when we talk about love stories, where I'm calling from selected stories and Oblivion
stories.
I'll just run it over here in my SQL in the Shell just to show you.
Same thing.
It just looks a little different the way it's presented over here.
So that's our first piece.
The next challenge find the longest book and print out the title and page count for each book.
Well, sorry for that one longest book.
So what we're going to do is let's start by just selecting title and pages from books.
Just get all of them and I'll execute that line.
So you can see here we get title and pages.
But what I want to do is find the longest one.
So I'm going to order by pages and then limit so that we only get one result.
And again, we're going to learn a way of doing this.
It's a little easier very soon, but this will work if I select them all.
Title and pages from books Order by.
Pages.
Limit to one.
But let me just start by ordering by pages.
Make sure that that works and you can see my results here.
We start with 176 all the way down to 634.
So I don't quite want to limit it to one just yet because then I would only get the least number of
pages.
What I'm going to do is change it to sort in descending order, limit one.
Now, when I run this, we see The Amazing Adventures of Kavalier and Clay.
634 and that's what we see over here.
Okay, Next up, print a summary containing the title and the year for the three most recent books.
So I'm going to need to use Kit-Kat to create this new piece of data, which is the concatenation of
the title, and then a space dash space and then the year.
So why don't I start there?
I'll do Select Canned Cat and then title.
And then SpaceX dash SpaceX.
And then released here.
I don't know why I accidentally capitalized that from books.
We'll start with that.
I'll just run this one current query.
And there we are.
We see the namesake Dash 22,003 Lincoln and the Bardo Dash 2017.
Now, I do want to rename that I want to call it summary just to match.
So I'll do as some Oh, I can't do it there.
Of course it has to come right after the piece I want to rename as summary.
Rerun this.
And it's called summary up here.
Or if I run it in my SQL in the shell, there we are.
Summary.
So now we need to narrow it down.
I want the three most recent books, meaning the three Largest Years.
So I'm going to do an order by based off of the year.
So let me get rid of the semicolon.
Order by released here.
We can start with that.
But I first want to reorder.
I want to go in descending order so that then when I take the first three, it's easier.
I'll get them in in the right order.
I want the three most recent, so I'll do release here descending.
Run that again.
I hate that.
I don't want to see this go away.
This right there.
It always shows up every time I rerun this query.
I wish it didn't anyway.
Now I'm getting them in the right order and I simply need to limit it to three results, and that should
do it for us.
There we are.
Lincoln In the Bardo Norse mythology, 10% happier.
Next up, what's our fourth exercise?
Find all the books with an author.
Last name that contains a space and don't cheat.
This one is easy enough.
We actually just need to use like again.
So what are we selecting?
The title and the author?
Last name.
So title comma author L Name from books.
I guess we'll start there.
Nice and simple.
But we want to narrow it down and only get the author last names that have a space.
And the way that we tell if they have a space is to use where.
Right.
So where?
Author ll name.
I'll do this on the next line here where author ll name like.
And then once again it doesn't work just to do a space.
We have to say percent sign space, percent sign because that space could come anywhere.
I mean, I wasn't specific.
It doesn't have to be in the middle.
We only have one author last name with the space anyway, but I don't think anybody has a last name
that starts to the space or enter the space.
Maybe I'm totally underestimating and I'm completely wrong, but it doesn't matter because we're going
to get any space.
It's we're going to match them regardless of where they are in the author last name.
And we just get foster space.
WALLACE And that's it.
Next up, find the three books that have the lowest stock.
So we're going to get the title here in stock quantity.
Let's just start with that.
Select title released year and stock quantity from books.
I'll just do it on separate lines to clean it up a bit.
And this, of course, will be for all the books I accidentally just killed.
I meant execute current.
And there we are.
We get title released your stock quantity.
But what I'm supposed to do is sort them by the stock quantity and then get the bottom three the least
number or the lowest stock quantity rows.
So we'll do an order.
Buy stock quantity.
Stock.
Come on.
And we'll start with that.
It's looking good, except we have this lull that's in the way, which is really obnoxious.
I guess we probably should just drop that book if you made that book when I did.
Then we can get rid of it.
Or you should get rid of it.
But I don't think I asked you to do this.
I was just demonstrating what happened when we inserted a row that only had a title and a last name,
I believe.
But anyway, if you do have any NOLs in there like I do, let's go ahead and just drop that or delete
it.
I'll do it the old fashioned way.
I'll just run a query to do it.
I'll do it over here.
So delete from books where and I'll just go where title equals my life, I think is what I called it.
That should have gotten rid of it.
Let's try rerunning this query now.
Looking good.
All right.
So we see the 12, 12, 23 American gods where I'm calling from.
What we talk about when we talk about love, I just need to limit it because we want the bottom three,
while technically the first three, which is the lowest stock quantity.
So let's run that.
Looks good.
OC And I think it's just our last one.
No, almost last one.
Print the title and author last name sorted first by author, last name, and then by title.
So this one's not too bad.
We're going to select title author L Name from books.
And then we want to order by.
Let's just run it first, though.
We want to order by the author's last name.
So we should see Carver up top and then Eggers all the way down to Steinbeck at the bottom.
Order by author l name.
Run it again.
And we see Carver down to Steinbeck, but we're going to sort with a secondary sort meaning after every
row has been sorted by author last name.
Any time that we have the same author, last name, let's also sort by the title.
So we just add an order by author L name, comma title, and we should see that.
Whereas an example where it would change, consider the lobster will come before oblivion there by the
same author.
But this alphabetically will come before let's check and run it.
Scroll down.
There we are.
Consider the lobster comes before oblivion.
Or with Eggers.
A hologram.
Hologram for the king comes before the circle.
And that's pretty much it for this one.
We don't need to rename any of our columns.
It looks good and we don't need to limit it.
Make this happen.
We want to yell for every row we're yelling.
My favorite author is Raymond Carver, and it is not only concatenated right where we have a piece of
text plus the author phone first and last name at the space in the middle.
It is also capitalized.
So we have different ways of doing this.
A lot of ways of doing this because we could capitalize the author first and last name first and then
concatenate that.
Or we could just concatenate everything and then uppercase at the end, which might be the simplest
way.
So why don't we start by doing the concatenation?
We'll do a select and then can cat we want to contact.
My favorite author is.
And then author first names.
I'm going to put a space there.
Author F name.
And then, comma, we want another space and then comma.
Author l name not in quotes.
That is an accident.
Should not be in quotes there.
And this is getting kind of long for a single line, at least in this window.
So I'm going to then let me just close my friends here and run that and see what we end up with.
Whoops.
Let's see.
And I'm an idiot once again because I never said what we're selecting from.
So from books, that's our table.
Let's try that again.
And we now see, my favorite author is Jhumpa Lahiri, Neil Gaiman, and so on, but not capitalized.
So that's the first thing we need to do.
And we're also supposed to sort at the end.
So when I want to uppercase all of this, I could do upper author f name and upper.
Author l name.
Run that.
And our results.
That looks good.
To make this a bit more legible, though, this is frustrating on this super long line, let's just
space things out with Con Cat.
So we'll select our con cat.
But we're going to concatenate.
My favorite author is and then upper of author first name and then an empty string or a space rather
than upper of the author.
Last name, something like that.
Or we might even indent all of these.
Like that.
So that's one option.
And select that from books.
The other option I'll comment that out is to do a single call to upper.
And just do author F name author L name as is and then upper.
That entire King cat thing.
All of this is going to be uppercase, so I'll indent that and dent that.
Indentation, of course, doesn't matter.
It's just about making it slightly more legible, possibly.
Like that.
Okay, so we're going to run this first, concatenate everything together, then uppercase the entire
thing.
Remember, we can also do, I think, UCS instead of upper.
Let's just run this, make sure it works, execute current, and it still does in fact work.
All right, then what we're supposed to do is rename that to be called Yell.
So let's do that.
As know.
Simple enough.
It's called yell here.
And then we need to sort alphabetically by last name.
So we'll do that here.
Order by author LL name.
And it looks like it's in a no by last name.
Si, si, si.
Yeah.
It's in the ascending order that it comes in, so we don't need to change that.
And there we are.
We should see the correct answer.
My favorite author is Raymond Carver.
Raymond Carver, Michael Chabon, Don DeLillo, all the way down to John Steinbeck.
And that looks good.
We did it.
We answered that one.
It was a bit of a mess, but that looks good to me.
We had two options.
I mean, technically, with this second option, we don't have to uppercase this text.
It could be lowercase, it will be uppercase by upper, but that works just great.
Let's see if we format this differently.
Does it look nicer?
Yeah, I guess this looks slightly nicer.
There we go.
That's a little prettier.
Okay, so that was it for the challenge.
Moving on.
We've got a really important topic.
Aggregate functions.