All right.
So you probably guessed it either because in the last video I told you or because of this gigantic text
here it's your turn to try putting this into practice.
So you get a couple of exercises.
They range from trivial simple logic questions all the way up to more complex case statements.
So I encourage you to go through all of them especially the case statements towards the end but hopefully
you don't lose interest before then.
Oh and one thing.
This is the last time you have to work with those darn books.
Last time that books data unless you want to keep working with it you can definitely play around that.
But I'm getting pretty sick of it.
I imagine you are.
And the good news is we're now moving on to join tables which allows us to get much more complex and
real real life data things that are more accurate and realistic.
And that's really exciting.
So get excited.
If you're not all right there's the first couple evaluate the following.
Now try doing them don't just copy them and paste them in and hit enter actually walk through and evaluate
what you think the result is.
So the first one.
Select ten not equal to 10.
Is that true or false 0 or 1.
Next select 15 greater than 14 and 99 minus five.
Less than or equal to 94.
True or false.
0 1 finally select 1 in 5 come with 3 or 9 between 8 and 10.
True or false.
So do those and then move on to these code based challenges.
The first one here is select all books written before 1980 and that does not include 1980 so everything
up to 1980 but not 1980.
Once you've done that move on.
A beautiful read text really expanding the color palette.
Select all books written by Eggers or siobhán should know how you say his name.
Honestly that guy great author Well I just googled it and apparently it's pronounced Shaban.
So it's like all books written by Eggers or shavin.
Makes sense now I think about it.
Now onto the beautiful yellow select all books written by Lahiri published after 2000.
So books written by the here he released after 2000.
Then select all books where the page count is between 100 and 200 pages so are middle length or short
books those are pretty short between 100 and 200 pages.
Next select all books where the author last name starts with a C or an S.
So this one there's a couple of ways of approaching it.
I'll let you discover what works best.
You may want to use.
You'll need to use things that we've learned in the past especially when we're working with strings
so you may want to go back or just go to the string documentation to figure out how to do this.
Select all books where the author last name starts with a C or an S.
Those are my initials so maybe I'm crazy and I just want books that start with who are written by authors
who start with my initials I don't know.
Now moving on to the main event are wonderful case statements.
So here is another example.
This is done using a case statement and let me explain it.
So we've got title and the author's last name.
But then we've got to type over here what type of book is it.
Which I'm really bad at this.
This is just because a genre doesn't matter though.
Basically the way that it works.
If the title contains the word stories recalling it short stories so as you can see where I'm calling
from selected stories is categorized as short stories.
Well what we talk about when we talk about love great collection is categorized as short stories because
it contains stories.
Same thing with oblivian stories.
But then there's another part.
If the title is just kids or it's heartbreaking work of Staggering Genius which I didn't write all the
way I appear it's giant.
But basically if it's this book or if it's this book both are memoirs.
So we have these two exceptions.
You'll need to write in Finally everything else which hopefully sounds familiar.
Otherwise everything else at the end is called a novel.
And I know some of these aren't actually novels like considered the lobster.
It's more of an essay or a collection of essays 10 percent happier is like a self-help book but I don't
want to get too complex.
So just to reiterate the title contains stories type in short stories.
If the title is exactly just kids or exactly a heartbreaking work of Staggering Genius then it's memoir.
Both great memoirs by the way.
And then finally everything else is a novel.
OK.
One more this is a bit of a bonus.
It's more complex to put a few things together.
So we have title of a book and the author's last name once again.
But then there's this count section and count which we've already seen do is a little different.
It's not just saying two or one or three but it's actually doing we can ignore the title part.
Actually you don't need to do that.
Just focus on the last name of the author in the count.
It's saying how many books each author has.
How many they've written so Carver has two books Geymann has three books.
Harris Dan Harris or Frida Harris.
They each have one book jubilee here he has two books and so on.
But not only is it printing the number but there's also the properly pluralized books or book following
it.
So one book singular two books three books one book and so on.
So you'll need to use a couple things I can give you hints if you don't want the hints then stop right
now.
But the hints are you need to use group by Nietzsche also use a case statement to generate this right
here.
But it is a bonus because you do need to combine a couple of things to a decent amount of work.
OK so that's it.
Once you finish that burn all the books are done with them.
Whatever that means to you.
I don't actually condone burning books but if you to get rid of the database go ahead.
Although you may want to refer back to it.
This is more just me saying that we're done.
Done with the books.
This is a turning point.
We're moving on to bigger and better data and two pretty important section which is joint so I'd definitely
recommend you take a break.
But I will reiterate that in the solution video which is coming up next.