Okey dokey.
Let's start working on some solutions to these challenges.
The first one, print the number of books in the database.
And again, I don't want to do the cheesy way select star from books and then just look at the bottom
where it says 22 rows, which by the way, your number will likely differ because I inserted some of
these null rows deliberately or partially null rows.
So it's okay if you don't see that answer.
Whatever answer you see though, you should see it as a result of running select count star from books.
And when we have Count star, remember that means count the rows in this case from the entire table
and we see 22.
You may see 18, 17, 19, something like that.
Next up, print out how many books were released in each year.
So we're going to use count again, but this time we're going to group by the release here.
So we'll have a select and then we want the released year and then we'll also get the Count star from
books.
But we're going to group by release year.
So we'll group them all by release here.
However many books are in each group, we count it up and we'll select that count alongside the year
from each group.
And we see 2003 had two books, 16 one book, 2001 three books, 2017 one book.
You know, there we are.
That looks good.
That's what we're supposed to do.
Or what I intended for you to do.
Next up, print out the total number of books in stock.
So we need to not count, but some together all of the stock quantities.
Let me just remember, I came up with some kind of not great names for these columns, in my opinion,
describe books.
Stock quantity.
Yeah.
I mean, I should have just called it quantity probably.
Or q t y even.
Anyway, I think I want it to be super clear, but in doing that I made it hard to remember or clunky.
Anyway, we ought to sum that all up.
So that's going to be a select some, but not star select some of stock quantity from books all rows.
And we see we have a total of 2450.
Let's say we just looked at stock quantity on its own.
That seems about right.
We've got 30, 40, 100, 150, 100, 100, 170, 195.
And then we have 1000 here.
That adds up to be 2450.
Okay.
Next up, find the average released here for each author.
So let's start by grouping together by each author.
So that's going to be select and then let's just do.
Author.
F Name.
Author.
LL name from books.
But we're going to group by.
And we'll do the same thing.
Author F name and author ll name because I want to make sure we're grouping by both so that Dan Harris
and Frieda Harris and whatever other data we have where people have the same first name and or the same
last name, we're at least distinguishing between them.
If we had authors with the same first and last name, then we'd be kind of out of luck with this approach.
But if we really had to worry about that, we likely would already have some sort of unique identifier
baked in or some way of telling the author names apart.
We would probably just use the ID of each author, but we're not doing that right now.
Okay, so this gets me.
Author first name and last name.
They're grouped together, but that's not exactly what we want.
We need to find the average released year.
So I'm going to recall this line.
And instead of doing author F name, author, L name on their own, I'm also going to do the average
released year for all of the books in each one of these groups.
And there we are.
The average release year for Jhumpa Lahiri is 19 99.5.
For a lot of the authors, you only have one book like Patti Smith.
It's just 2010, although you can see it's been turned into a decimal number.
We'll talk more about data types in just a little bit very soon.
And then for somebody like who has a bunch of them, Neil Gaiman had three books, I believe it's 2,006.666,
seven.
So that's what I wanted you to do for this challenge.
Although there are other ways you could have done it, you could have grouped differently.
Last name first, then first or last name and then first name.
You don't necessarily have to display first name and last name.
Here you could concoct first and last name and then grouped by that.
But that's good enough.
Next up, find the full name of the author who wrote the longest book.
So this is possibly going to include a subquery.
I'll show two solutions.
Let's start with the subquery solution.
So I want to find the longest book.
We can just start with that Select max pages from books that's going to give me the number of pages.
So we've already seen this before.
We happen to know 634 is correct and then we can take that and I'll do this over in a file here.
We're going to run that as a sub query like this and actually select what are we trying to find the
title or the full name of the author who wrote the longest book.
So we're going to do let's just start with author F name for first name, author L name for last name
from books, and then we'll say where.
And then we want the pages to be equal to this subquery.
So this will run first.
Here it turns into whatever the number is.
If the highest pages six for three in our case, and then we run this query, what, we run it all at
once, but then SQL will run this query using six for three there, and we're almost there.
It looks like we have two books, though, remember that have the number of pages.
Let me just display the pages here.
You won't unless you ran that line with me.
I have two books, though, with 634.
Okay, that's fine.
This answer still is valid because it's finding the book or books that have the most pages.
I just have this dumb one that says Noel Noel, four Author, First name and last name.
Don't worry about it if you didn't run that line when I ran it.
So we should be seeing Michael Chabon.
Siobhan, Siobhan I don't know.
634 But what I'm supposed to do is actually get the full name as a single value.
So instead of first name and then last name, I'll just can get them together.
So can Cat, first name and then a space and then last name.
We've seen this a bunch of times, and then I'll give it an alias as maybe just author.
And then pages secondly.
Let's select this and run that query again.
And we see author Michael Chabon, Pages 634.
And that's good enough.
That's what we're supposed to do.
But there was a second approach, which is we could order by the number of pages, so let's try that.
We'll select author, F name, author, L name and pages from books.
But we're going to order by the number of pages, and I want to go descending from highest to lowest.
So we could start with that.
And up top we see Michael Chabon and then no, no.
Six, three, four.
And then I can limit that to one result, if that's what I want.
If I only want the first, even if there's multiple that are tied, I just want the first result.
There we are.
Michael Chabon But again, I want the concatenated author name.
So author F name space author ll name in parent we can cat that whole thing.
We'll call it author.
Try that again.
And now we see Michael space Shab in one word.
Well, not one word, one column, one value we concatenated and pages.
634 OC And our final piece is to make this happen where we have all the books collected by year.
We count how many were released in each year, and then we have the average number of pages for each
or all of the books in a given year.
And I didn't call this out originally when I introduced this, but there's an order here.
They are all sorted by the year.
And also we have these aliases up top.
So let's start by selecting the released year from books and we'll do a group VI release here.
I know we want that.
And that's not going to get us very far, but that gets us the release years in their own groups.
But we're not doing anything with those groups.
What I want to do is start by getting the count of the books in each group, and I'll do this on separate
lines here, like this release here.
And then I want to select the count of the number of rows in each group.
And now we see two for 2003, one for 2016.
And then I want to give it an alias to match what I have here.
I called it number books, so I'll call it number books and do it just like that.
There we are.
Looks good.
2003 we see two 2016, we see one.
Let's verify.
2003.
We see two.
2016.
We see one.
The order is different, but we'll get there.
The next thing we'll select is the average number of pages in each one of those groups.
So we'll add a comma, average pages.
And remember, this is averaging the pages for for each of the groups that have been created based off
of the released year.
So we can start with that.
Run it again.
Looking good, but I want to change this name up top to be average pages.
So as average pages and we're almost done, it's looking good, except we're also supposed to call this
year instead of released year.
So we're also going to Alias that as year.
And I don't need quotes here because I don't have a space on this example.
I have a space and it gets unhappy with me because it thinks that this is some sort of command and then
this is referencing the books table maybe.
So if I put this there, it realizes, Oh, this is just what you're aliasing with as whereas here it's
just a single word.
Anyway, I run that almost there.
The last thing we have to do is sort all the rows based off of that year.
Ordered by released year.
And that should do it.
Let's see.
From 1945 up to 2017, 1945 up to 2017.
I'm ignoring the NOLs that I have because you likely don't have those.
But if you do, they'll be up top and that's it.
45 one 181.
Let's verify 45 one 181 2017 one 367 2017 one 367.
That's a terrible way of checking your work, but we're just comparing it to the screen shot.
It looks good.
That's sort of the culmination of a lot of what we learned in this section.
We've got count, we've got average, but we also have grouped by in there plus order by from the previous
section.
We've got aliases.
There's a lot of stuff going on, even though it's not that complex of a query compared to what we'll
see soon, especially once we get to joins.
It does combine a lot of the elements we've seen in the course so far.
All right.
This might be a good time to take a break.
If you are inclined to take a break, I recommend you do, because next, we've got a lot of other skill,
nitty gritty stuff to talk about data types and dates and times.
And it just might be a good time to take a break.
Okay.