Get.
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 need to use count and all that we'll 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 need to use a group by group by released here and then use count in conjunction with that.
So let's do a select and we'll just do count Starr 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 like you released here comma count
so that we get the released and then the count you can see most years have one book released 2001 has
three.
2003 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.
You'll need to sum all the stock quantities which is something I guess we haven't really used.
But it's nice fell from.
We haven't used stock.
Why can't you got a chill out.
It's a start.
Quantity is not a piece of data that we use very often but it is here.
We've tried to do 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 as we go.
We have 2000 450 books in stock.
So the next problem here is to find the average release year for each author or so many to group by
author and then we need to use average Viji to calculate the average for this year.
So let's give it a shot average release year for each author.
So we need to do a select average and then release here.
From books group by and then we're grouping by author.
So we'll do l name Khama author f name again because of the Harris issue.
What are their names.
Dan Harrison free Harris.
OK so if we do that my cat again.
Oh boy.
Well we've taken care of that and I don't mean.
Wow.
Apparently they have not taken care of it.
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 name author Alvin I'm just so we
can make a bit more sense of the data.
So now we can see Raymond Carver's average release years 1985.
See Neil Gaiman's his 2006 point six six six seven.
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 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 6:34 and then you know at this point we can make
a second query select and we want to concatenate first name and last name so we do concat author name
comma author L name and we want a space in between
that
from books where pages equals 6:34 and there we go we get Michael Chaplin.
However we want to do it in one step so we can either do the sub query route so do so like concat author.
However instead of we'll do from books but instead of just doing web pages 6:34 will do where pages
equals and then we use parentheses for the sub query.
Hopefully you remember this part and then in here all that we do is write our queries.
That's going to be select max pages from books and we get the same thing.
I go Siobhan and then there's the other way of going about it which is to not use Macs 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 L name from books and then we just
do an order by pages ascending limit one and they believe have that backwards.
We got the longest book not the shortest one area so we want it in descending order so that we get the
longest one at the beginning.
And that's going to be 6:34.
So if we if we get rid of this limit one and actually we get rid of limit 1 and let's do pages comma.
All right.
So you can see now you've got six thirty four all the way down to 176.
And I was I had it backwards.
OK.
So both are valid ways of getting a 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 right.
All right.
Let's move on to our final challenge here.
Make this happen.
So I've got a couple of things going on.
We've got a year so things let's see if we get Year 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 alias's going on.
OK 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
and the next thing is the count.
Remember we're going to have a group by at the end so we're doing all of this you know writing the three
things we're selecting before we actually have the group by.
But at the end we're going to have group five at least here.
So this here number books is count to count the number of books released that year.
So all in debt and just do count star paths.
And I need quotes here because we have that Octa Thorpey I think is what that's called the hash symbol
as well as a space.
So we need quotes for Aliase 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 Viji pages.
That's correct.
There we go OK from books.
And you know all and done this one to group by.
And we want to group by released here hit enter and pray.
And it looks like it worked out.
19:45 had one book 181 pages long.
Take a look at.
Like 2001 there were three books and the average number of pages is 443 and the 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 help us in the next section.