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 we 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
with it.
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.
So here'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 ten.
Is that true or false?
Zero or one next select 15 greater than 14 and 99 minus five less than or equal to 94.
True or false?
Zero one.
Finally select one in five, comma, three or nine between eight and ten.
True or false?
So do those and then move on to these code based challenges.
So 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.
I've got some beautiful red text.
Really expanding the colour palette.
Select all books written by Eggers or Shaban.
I don't know how you say his name, honestly.
That guy.
Great author.
Well, I just googled it and apparently it's pronounced Shaban.
So select all books written by Eggers or Shaban.
Make sense now?
I think about it.
Now onto the beautiful yellow select all books written by Lahiri, published after 2000.
So books written by Lahiri released after 2000.
Then select all books where the page count is between 102 hundred pages.
So our middle length or short books, those are pretty short between 102 hundred pages.
Next, select all books where the author last name starts with a C or an S.
So this one does a couple of ways of approaching it.
I'll let you discover what works best.
You may want to use well, 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 to start with who are written by authors
who start with my initials?
I don't know.
Now moving on to the main event, our wonderful case statements.
So here is another example.
This is done using a case statement and let me explain it.
So you've got title and the author last name, but then we've got a type over here.
What type of book is it?
Which I'm really bad at this.
This is just be called genre.
It doesn't matter though.
Basically the way that it works, if the title contains the word stories, we're calling it short stories.
So as you can see where I'm calling from, selected stories is categorized as short stories.
What we talk about when we talk about love, great collection is categorized as short stories because
it contains stories.
Same thing with Oblivion stories.
But then there's another part.
If the title is just kids or its heartbreaking work of Staggering Genius, which I didn't write all
the way up here because 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 that 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 Consider the Lobster.
It's more of an essay or collection of essays.
10% Happier is like a self-help book, but I didn't want to get too complex.
So just to reiterate, the title contains stories.
Type is 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.
Okay, one more.
This is a bit of a bonus.
So it's more complex.
You have to put a few things together.
So we have title of the book and the author last name once again.
But then there's this count section and count, which we've already seen under do is a little different.
It's not just saying two or one or three what 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 and the count.
It's saying How many books each author has, how many they've written.
So Carver has two books.
Gaiman has three books.
Harris, Dan Harris or Frieda Harris.
They each have one book.
Jhumpa Lahiri has two books and so on.
But not only is it printing the number, but there's also the properly plural sized 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 of things.
I can give you a hints.
If you don't want the hints, then stop right now.
But the hints are you need to use group by.
You need to 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.
Okay, so that's it.
Once you finish that, burn all the books, we're done with them, whatever that means to you.
I don't actually condone burning books, but if you want 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 some bigger and better data and to pretty important section, which is joint.
So I definitely recommend you take a break, but I will reiterate that in the Solution video, which
is coming up next.