Okay.
So in this video, we're just going to go over the solutions to the exercises I introduced in the previous
video.
And the first one here is to print the total number of books in the database.
So we'll need to use count.
And all that will do is write a select count.
Star from books just like that.
And we get 19.
All right.
And that's it.
The next one is to print out how many books were released in each year.
So we'll need to use a group by group by release year and then use count in conjunction with that.
So let's do a select and we'll just do count.
Star from books group by released here just like that.
Now on its own.
This isn't that useful.
So I'll go ahead and add in select release year comma count so that we get the year released and then
the count and you can see most years have one book released.
2001 has three.
2000.
Three has two.
All right.
So the next problem.
Print out the total number of books in stock.
So this one you'll actually need to use some and you'll need to sum all the stock quantities, which
is something I guess we haven't really used.
Oop.
It's not how you spell from.
We haven't used stock.
Oh, my cat.
You got to chill out, blue.
It's a stock quantity is not a piece of data that we've used very often, but it is here.
We've neglected it a little bit, but it is there as you can see.
So we just want to add them all together and that's just going to be select some stock quantity.
Quantity.
From books.
There we go.
We have 2450 books in stock.
So the next problem here is to find the average release here for each author.
So let me take grouped by author and then we'll need to use average AVG to calculate the average release
year.
So let's give it a shot, average release year for each author.
So we'll need to do a select average and then release year.
From Books group by and then we're grouping by author.
So we'll do l name, comma, author f name again because of the Harris issue.
What are their names?
Dan Harris and Frieda Harris.
So if we do that.
Oh, my.
My cat again.
Oh, boy.
Well, I've taken care of that.
And I don't mean wow.
Apparently have not taken care of it.
All right.
Hang on.
So back to the problem at hand.
This works.
We're getting average years, but to make it a little bit better, we should also add in the author
f name, comma, author l name, just so we can make a bit more sense of the data.
So now we can see Raymond Carver's average released year, 1985.
Let's see.
Neil Gaiman since 2000, 6.6667.
All right.
So that's all there is to that problem.
Now we get to the one that's a little different.
Find the full name of the author who wrote the longest book.
So there's a couple of ways of doing it.
The the way that doesn't qualify as a solution.
The two step process would be first to find the longest book.
So that would be select max pages from books like this.
634.
And then at this point, we can make a second query select and we want to concatenate first name and
last name so we do can cat author f name, comma, author l name.
And we want a space in between.
Like that.
From books where pages equals 634.
And there we go.
We get Michael Chabon.
However, we want to do it in one step so we can either do this sub query route.
So we'll do select King Cat author.
However, instead of we'll do from books.
But instead of just doing web pages is 634.
We'll do where pages equals and then we use parentheses for the subquery.
Hopefully you remember this part.
And then in here all that we do is write our query.
So that's going to be select max pages from books.
And we get the same thing.
Michael Chabon And then there's the other way of going about it, which is to not use Max at all and
circumvent that which is actually faster to use order by and then limit it to one so we could do the
same thing so I can cat.
Author f name.
Author ll name from books.
And then we just do an order by pages.
Ascending limit one.
And I believe have that backwards.
We want the longest book, not the shortest one.
There we go.
So we want it in descending order so that we get the longest one at the beginning.
And that's going to be 634.
So if we if we get rid of this limit one.
And actually, if we get rid of limit one and let's do pages comma.
All right.
So you can see now we've got 634 all the way down to 176.
And I was I had a backwards.
So both are valid ways of getting the solution.
This one is a little bit well, it's a little faster because we're running one query.
This version is more complex, more advanced, I guess.
But I would definitely stick to using this when possible, using order by.
All right.
Let's move on to our final challenge here.
Make this happen.
So we've got a couple of things going on.
We've got.
A year.
So things let's see, we've got a year.
The number of books in that given year and the average pages for all those books for that given year.
So things are grouped by year and we have three aliases going on.
So let's give it a shot.
So we'll start off by selecting year or released year and giving it the alias of year.
Then the next thing is the count.
Remember, we're going to have a group bye at the end.
So we're doing all of this, writing the three things or selecting before we actually have the group
by.
But at the end we're going to have Group B released here.
So this here number books is count.
It's a count of the number of books released that year.
So I'll indent and just do count star as and I need quotes here because we have that octa Thorpe I think
is what that's called the hash symbol as well as a space.
So we need quotes for our alias.
If we want to match this, then we have average pages.
So it's going to be the same thing as far as our alias is concerned.
So we need average number of pages as a VG pages, is that correct?
There we go.
OC from books and I'll indent this one to group by and we want to group by released.
Hear it enter and pray.
And it looks like it worked out.
1945 had one book, 181 pages long.
Take a look at 2001.
There was three books and the average number of pages is 443 and a third.
All right.
So that matches what I had here, which means that we got it right.
Don't ask why you would do this.
Just don't ask.
That's it for this section.
And yeah, we'll see you in the next section.