OK.
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 10 not equal to 10.
Well 10 is equal to 10.
So this should return false and we get zero.
Next One little more complex we've got.
And remember that means both sides have to be true.
Start the left is 15 grade than 14.
Yes that's true.
But how about this side.
Ninety nine minus five 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 1 finally select 1 in
5 come a three or nine between eight and 10.
So we've got more.
I mean it's either side can be true for me to make the whole thing true.
So let's start with this select one in five come three.
Remember he didn't show you this explicitly.
But we saw how to use in.
So you should know how it works is one in that set of five comma three.
No.
So this is false but that doesn't nullify the whole thing and falsify it because what's on the right
side is nine between eight and 10.
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 year from books and then the key thing
is we want to do where released here is less than 1980 and not less than or equal to because it says
non-inclusive so less then we're released here is less than 1980 to make sure it works.
And there we go.
All books 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 if we don't have more exciting data.
Next up select all books written by Eggers or shavin which I 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 most sense if we can see the authors last names.
So what we can do is select them where and we can use and or so we can do a double equality where author
last name is equal to Aggers or author last name is equal to Shaban where author L name is equal to
Aggers or author name is equal to shavin 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 L name is in the
set of Eggers and shavin so we can combine them.
Basically say where author name in that set copy that never go.
Same result.
So whichever you prefer this one is a little bit easier to see.
I guess it's shorter easier to edit and if you want to add another author and 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 will do.
Title and this time will do.
Author L name.
And released here from books and will start by all books written by the.
So where author L name equals Harry so we can start there.
We know 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 name is here to live here and released here is greater
than 2000 and I didn't specify if it was inclusive or not.
So based off of 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 100 and 200.
So just like the problem we did back here there's a couple of solutions.
The first one is to use logical and so we can do select title and page count or is it just pages from
books where pages is greater than 100 and less than 200.
So where pages will do greater than or equal to pages is greater than or equal to 100 and pages less
than or equal to 200.
That's one way of selecting based off of a range.
As 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 100 and 200.
Nice easy to read version.
We get the same results.
OK.
One thing remember that this is inclusive.
So if we didn't want to include 100 and 200 we could either do this or we would have to change just
to be 100 one and when ninety nine or something.
OK.
So I'll leave it as 200.
Moving on select all books where the author last name starts with a C or an S.
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 will do.
Title and author L. name from books and basically we want to let's just type it out with the pseudo
code or English code.
So where author Al name starts with C or author
Al name starts with s.
So those are not you know it'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 comfortable with that sort of starts with C..
You can just say where author I'll 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 the C and then we just do the same thing done here like s but
the percent sign after that and that should do it for us.
So we get shavin Smith Carver Steinback Saunders good collection of authors.
However there's another way which is to use substring.
So if you remember substring Maybe you remember me complaining about how there is also an abbreviated
subset S T R version so we could use that so we can say were author L name sub stra of author L name
and we want one common one which will give us the first character we want to check where that's equal
to C or not just duplicate this whole thing where it's equal to S..
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 you can
see them all.
We could go and because we're checking these two values we could also use in C comma s just like that
and get rid of the OR.
So now we're saying it's like title author realname from books where the first character of author all
name is in the set of C or S And We get the same result.
So three different ways.
Next up.
Moving on to case statements.
So in this one I tried to throw you for a little bit of a curve ball because unlike the other case statements
we saw where we were just using greater than the must.
Then there's a couple of things we'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 name and we'll just do from books just like that.
And then we need our case statements syntax so we're going to have case and I like to just put my and
right in there as well as my as I call 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 OK if Title contains the word stories anywhere type
as short stories so when title like.
And how do we write or express the fact that it can contain stories anywhere not just at the beginning
but anywhere in there.
Well we use parentheses accesory for sentence.
So this means that it contains the string stories with anything afterwards which technically we could
have histories or something 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 is just stories but I'll keep it simple with
stories like this.
Then that's going to be short stories as our type.
OK then we have another statement.
When title and when title is exactly just kids or Heartbreaking Work is 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.
It's pretty straightforward it's going to be a bit annoying.
So how do we check 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 it like or something and search for
a particular part rather than the entire name.
But that was the 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 to Raymond Carver collections of short stories and a David
Foster Wallace Collection.
OK.
So that's it.
And of course he could clean this up so you could have used in here.
Title equals so you could say where title.
In just kids comma Heartbreaking Work Staggering Genius you could use the 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 right.
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 to correct the pluralize version of books.
So we'll start by just printing out.
We can ignore title just to author last name and account without the book's part.
So that should be review of group by and count so we'll select author I'll name Khama count star from
Books Group II and we need to group by author.
So if we do this
you'll see we end up with Carver has two books Aggers has three books and so on.
We do have that problem with Harris having two books.
So we need to make this more specific.
Are we also grouped by author first name and now we have two different Harris's perfect son now how
do we instead of just printing out like this how do we add in that variable.
The changeable suffix book or books that use 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.
And and if you look at how I printed it I just call that count.
So and as counts 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 1.
Let's say then we want to do something.
Let's just say singular book.
This isn't the right answer.
Otherwise will you.
Books.
OK 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 1 then count is book.
And before I forget we are missing our then's.
So one count equals 1 then book that's very important.
Now if we paste it OK so we get Carver has books.
Eggers has books but huge Harris has book.
So we're close but how do we get the number in there.
And we need to use Concat.
Hopefully you guessed that.
So we're going to concat 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 wanted to shorten this we could just hard code this as one book.
Right.
Because when Count is equal to 1 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
could be 10 books.
So of concat 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.
Shaban has one book.
Harris has one book here he 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 and Frida Harris has one book to play here
he has two books.
Oh right.
Quite a bit there.
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 joined our 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 joints.