Okay.
So let's go over.
Hopefully you're still alive.
It was maybe a bit of a challenging problem set.
But if you're still here, let's go with these first three.
So evaluate the following.
Select ten not equal to ten.
Well, ten is equal to ten.
So this should return false.
We get zero next one, a little more complex.
We've got an end.
So remember, that means both sides have to be true.
Start with the left.
Is 15 greater than 14?
Yes, that's true.
But how about this side?
99 minus five.
It gives us 94.
Is 94 less than or equal to 94?
Yes, it's equal to.
So that means both sides are true, meaning the whole thing is true, which we get one finally select
one in five, comma three or nine between eight and ten.
So we've got four means either side can be true.
Four to make the whole thing true.
So let's start with this select one in five comma three.
Remember, this is I didn't show you this explicitly, but we saw how to use n, so you should know
how it works.
Is one in that set of five comma three?
No.
So this side is false.
But that doesn't nullify the whole thing.
That doesn't falsify it because what's on the right side is nine between eight and ten.
Yes, it is.
So this is true.
Making the whole thing true.
Let's try it.
There we go.
We get one.
So false, true, true.
Next, select all the books written before 1980.
So I didn't specify what part of the book that's totally up to you.
Let's just do title and release here so we can see that released here from books.
And then the key thing is we want to do where release here is less than 1980 and not less than or equal
to because it says non inclusive so less then where released here is less than 1980.
Let's make sure it works.
And there we go.
All books are written before 1980.
Apparently, we only have one.
Let's just actually verify that.
It's kind of catching me off guard there.
And it looks like that's the case.
All we have is Cannery Row.
Well, I apologize that we don't have more exciting data.
Next up, select all books written by Eggers or Shavon, which I know now know how to pronounce.
So there's a couple of ways of doing this.
Let's start with the most straightforward maybe, which would be, and also let's do title and author
last name.
So again, I didn't specify what parts he had to select.
It's up to you, but this makes the most sense.
So we can see the author last names.
So what we can do is select them where and we can use an or so we can do a double equality where author
last name is equal to Eggers or author last name is equal to Chabon, where author L name is equal to
Eggers or author LL name is equal to Chabon just like that.
That's one way of doing it.
And you can see we get those for books.
However, we can also use in because all that we're doing is trying to see if author ll name is in the
set of Eggers and Chabon so we can combine them basically say where author l name in.
That set.
Copy that.
And there we go.
Same results.
So whichever you prefer, this one is a little bit easier to see.
I guess it's shorter.
Easier to edit.
And if we wanted to add another author in, it's pretty simple, but this is also perfectly valid.
Next, select all the books written by Lahiri published after 2000.
So the first thing we'll do title and this time we'll do author l name.
Oops.
And released here from books.
And they'll start by all books written by Lahiri.
So where author ll name equals Lahiri.
So we can start there.
We know that this should work, but we get two books, and now we want only the ones published after
2000.
So what we need is a logical and so where author l name is equal to Lahiri and released here is greater
than 2000.
And I didn't specify if it was inclusive or not.
So based off of the language published after 2000 will keep it as a greater than if you put an equal
sign there.
It's not.
I mean, you got it right, essentially.
I didn't do a great job of clarifying that before.
So either one works.
Now we only get the namesake.
Also really great read.
Next up, select all books with page count between 102 hundred.
So just like the problem we did back here, there is a couple of solutions.
The first one is to use logical.
And so we could do select title and page count or what is it just pages from books where pages is greater
than 100 and less than 200.
So where pages and we'll do greater than or equal to pages is greater than or equal to 100 and pages
is less than or equal to 200.
That's one way of selecting based off of a range there.
We get these shorter books but we could also use between which maybe you gathered because of the language
here it screams out between and that might simplify it a bit.
So all we have to do is change it to where pages between 102 hundred.
Nice easy to read version we get the same results.
Okay.
One thing.
Remember that this is inclusive.
So if we didn't want to include 102 hundred, we could either do this or we would have to change this
to be 101 and 199 or something.
So I'll leave it as 200.
Moving on.
Select all books where the author last name starts with a C or an SE.
So there's also a couple of ways of doing this.
One.
We'll start with the simplest one.
So we want to select and this time we'll do title and author L name from books.
And basically we want to let's just type it out with pseudocode or English code.
So where author l name starts with C or author.
Our name starts with S.
So those are not you know, that's not valid code.
So how do we do this?
There's a couple of options.
The first one is to just use like if you're if you're comfortable with that instead of starts with C,
we can just say wear author L name like and then we use the percent sign to force the C to be at the
beginning of the string.
So remember that this says string starts with C and then anything afterwards.
So that's one way to say it starts with a C and then we just do the same thing down here, like S with
the percent sign after that.
And that should do it for us.
So we get Chabon, Smith, Carver, Steinbeck, Saunders, good collection of authors.
However, there's another way, which is to use substring.
So if you remember substring, or maybe you remember me complaining about how there is also an abbreviated
subset str version.
So we could use that.
So we could say we're author l name sub strip of author l name and we want one comma one which will
give us the first character we want to check where that's equal to C or.
And I'll just duplicate this whole thing where it's equal to sx.
So we could do this.
And we get the same thing.
But there's yet another way to clean this up, which is to then I'll just keep them all here so that
you can see them all.
We could go.
And because we're checking these two values, we could also use in C comma se just like that and get
rid of the OR.
So now we're saying select title and author l name from books where the first character of author l
name is in the set of C or S and we get the same result.
So three different ways.
Next up, moving on to K statements.
So in this one, I tried to throw you for a little bit of a curveball because unlike the other case
statements we saw, we were just using greater than and less than.
There's a couple of things you're doing here.
So we're just working with Title, but you're seeing if title is like stories and then you're seeing
if it exactly equals just kids or a heartbreaking work of staggering genius.
So let's let's start off with the basics select title and author l name.
And we'll just do from books just like that.
And then we need our case statement syntax.
So we're going to have case and I like to just put my end right in there as well as my as and I called
it type, which I don't actually think is a great name genre would be better, but inside of here we're
going to have our statements.
So the first thing we're going to do is figure out, okay, if title contains the word stories anywhere,
type is short stories.
So when title like and how do we write it?
Express the fact that it can contain stories anywhere, not just at the beginning, but anywhere in
there.
Well, we use parentheses.
Excuse me, percent sign.
So this means that it contains a string stories with anything afterwards, which technically we could
have histories or something.
Whoops.
Which maybe is not what we want, but we can leave it at that.
You could put a space here or something to make sure that it's just stories.
But I'll keep it simple with stories like this, then that's going to be short stories as our type.
Then we have another statement when title and when title is exactly just kids or a heartbreaking work
of staggering genius.
We should print out memoir, so let's leave that part blank and just add in then.
Memoir.
So then how do we fill this part in?
There's it's pretty straightforward.
It's going to be a bit annoying.
So how do we check if it's equal to just kids when title equals just kids or when title equals?
And this is going to be very long, so I'm going to copy it.
Where are you?
Just like that.
And I might shorten that.
I definitely would shorten it.
So maybe it would be better to use like or something and search for a particular part rather than the
entire name.
But that was a problem here, followed by our else, which is otherwise everything is a novel.
So that's where this comes in else.
Novel.
Just like that.
Now let's double check.
We don't have any extra commas, which I said I sometimes do.
Looks good.
Let's try it out.
And we get all novels except for just kids.
And a heartbreaking work is staggering genius.
And then those are memoirs.
And then we have two Raymond Carver collections of short stories and a David Foster Wallace collection.
Okay, so that's it.
And of course, you could clean this up so you could have used in here title equals.
So you can say where title in Just Kids Comma a heartbreaking work.
Staggering genius.
You could use like something like this or a title like Heartbreaking.
Although maybe we have multiple books with heartbreaking the title, so I'm going to leave that and
move on to our final one.
How do we make this happen?
So again, what's happening?
We're grouping things by author and we're counting how many books each author has written.
But we're not just printing that out.
We're printing it along with a space and then the correctly plural sized version of books.
So we'll start by just printing out.
We can ignore title, just do author last name and account without the books part.
So that should be review of group by and count.
So we'll select author.
L name comma count star from books group by and we need to group by author.
So if we do this.
Oops.
You'll see we end up with Carver has two books, Eggers has three books and so on.
We do have that problem with Harris having two books, so we need to make this more specific where we
also group by author first name.
And now we have two different heresies.
Perfect.
So now how do we, instead of just printing count like this, how do we add in that variable the changeable
suffix book or books that uses the case statement?
So I'm going to make some space here.
And rather than just printing the count, we're going to add in our case, case end.
And if you look at how I printed it, I just called it count.
So end as count.
Then what is the logic?
We're actually trying to do what?
We're trying to figure out when the number of books Count Star.
Is equal to one, let's say.
Then we want to do something.
Let's just say singular book.
This isn't the right answer.
Otherwise.
We'll do books.
So the logic is going down the right page.
Regrouping Things by author.
Then we're having a case statement that says When the total count of the number of books that authors
written is equal to one, then count is book.
And before I forget, we are missing our thens.
So when count equals one, then book, that's very important.
Now if we paste it so we get.
Carver has books.
Eggers has books, but each Harris has book.
So we're close.
But how do we get the number in there?
And we need to use Cat.
Hopefully you guessed that.
So we're going to King Cat.
Basically, we want to take the number which is going to be count.
And we want to concatenate it with space book.
Now if we want to shorten this, we could just hard code this as one book, right.
Because when count is equal to one, then it's always going to be one book.
This is the one where we have multiple books.
We need to add in the number because it could be two books, it could be ten books.
So we'll can cat the two pieces, which are going to be count star comma.
And I know this looks like a lot here, so let's step through it.
Concatenate the count with space books.
So two books.
Three books.
And that should do it.
Let's double check.
Here we go.
Carver has two books.
Chabon has one book.
Harris has one book.
Lahiri has two books.
The only other improvement, I guess, to suggest would be to also put author first name, just to make
it a little clearer.
In the case of Harris, we now can see Dan Harris has one book in free to.
Harris has one book.
Jhumpa Lahiri has two books.
Oh, right.
Quite a bit there.
Hopefully you made it through.
Time to move on.
We can burn that part of us that's attached to the books.
We are done.
We're moving on to bigger, more exciting things.
I highly recommend that you, if you're at your computer, get up and take a break because joins are
a big section.
Very important stuff coming up next.
But a bit dense.
So go for a walk if you'd like or do whatever you need to do to get prepared to focus on joins.