Okay, here we go.
Solution time for this exercise.
We've got quite a few pieces starting.
Simple.
Just evaluate these.
Let's start this first one.
Select ten.
Not equal to ten.
So ten is not not equal to ten.
Meaning this would be false because ten is equal to ten.
Meaning that we expect to see zero when we run it.
Zero is the MySQL version of false in this case.
All right, next step, select 15 greater than 14 and 99 minus five, less than or equal to 94.
Wow.
So first up on the left hand side of and we have 15 greater than 14.
That is true.
And 99 minus five that becomes four.
Sorry, that becomes 94 is 94 less than or equal to 94.
That is also true because they're equal.
So we have true and true.
The whole thing evaluates to true, which in my SQL is one.
And then finally select one in five and three or nine between eight and ten.
When I read it out loud, it just sounds like utter gibberish.
But we've got our ore and then the left and the right side.
So it's one in this set of five comma three.
No, it is not five and three are the only options.
One is not in there.
So the left side is false.
What about the right side?
Is nine between eight and ten?
Yes, it is.
So the right side is true, which means the whole thing is true because we're using logical or so false
or true becomes true.
And that will evaluate to true, which is one in my SQL.
Okay, moving on.
Now we need to write some queries.
Select all the books written before 1980, but not including 1980.
So let's do this in a file.
So like, start from books where and then we'll need to work with released date was less than 1980 and
that should do it.
We don't want less than or equal to what did I do.
So let's start from books where Oh, I'm an idiot.
It's released a year now.
Let's try that again.
Okay.
There we are.
We got released here, 1945.
Is that really the only book in there released before 1980?
I guess so.
We trust our query.
Then our next piece is Select all books written by Eggers or Shaban.
So select star from books where and I'll do this on the next line where and then author last name is
exactly equal to Eggers.
But then we also want or last name is equal to seven so or author last name is equal to seven.
Let's try running that one.
And we see Eggers.
Eggers shopping.
Eggers looks good to me.
Now we need to select all the books written by Lahiri, published after the year 2000 OC so select star
from books and we want to have the author.
So where author last name is exactly equal to Lahiri?
And then at the same time, a.k.a. and the release here is greater than.
2000.
Let's try it.
Where's my aunt?
I didn't type.
And somehow there we go.
We have to have that and join those two together.
And then we see The Namesake is written by Lahiri, released here.
It was 2003.
If we got rid of her last year, I think we have two books in here.
Interpreter of Maladies that was released in 96.
So that one was not included when we wrote the more complicated query.
Now we need to select all books with page counts between 102 hundred.
So there's more than one way of doing this.
But let's do select star from books where pages and the longer, more annoying way is to say where pages
is greater than or equal to 100 and pages is less than or equal to 200.
Is that the range we were supposed to do?
102 hundred and I need a from here select star from books.
And if you look at the pages here, well, it's ugly how it's wrapped around like this.
But we have 198, 176, 181.
But the cleaner way is select star from books where pages is between 102 hundred.
Not only is it shorter, but it's just more semantically meaningful.
You look at this and the word between is right there.
It tells us what we're trying to do, even if the end results are the same.
With this other option, you have to look at the code a bit longer to understand what's happening compared
to this.
But either work.
Next up, select all the books where the author last name starts with a C or an S.
So this is another one where there's at least a couple of ways to do this that I can think of.
The the one way that you may have come up with first would be to select star from books where author
last name.
And then we could do something like where author last name like and then our first pattern will be a
C at the beginning and then wild card afterwards.
So it has to start with a C and then anything after or author last name like.
And then I think it was an s let me verify last name starts with a C or an S OC.
So this is one option we use or write and we check to see two different patterns using like C at the
beginning, anything afterwards.
S at the beginning, anything after.
It's very different than this.
I hope you can see this font size where I have the sign at the beginning.
That means C doesn't have to come at the start.
This means it does.
And now we are getting four last name Shaaban.
Smith.
Carver.
Carver, Steinbeck.
Saunders.
So that's one option.
Now there's another option.
It's a little trickier, which is to just take the first letter of the name of every last name.
So we saw how to do this a long time ago.
We can do this.
Let's select author, last name, comma, substring.
If you remember this or sub str is the shorter version, and then we provide the column that we're looking
for.
So I'm looking at author last name and then the starting index is starting position and how many characters
I want to take.
So I want the first starting at character one go for one character, give me that substring from books
and let's see what we get.
Okay.
You can see we get l g for Gaiman, L for Lahiri.
E for Edgars.
C for for Shaban.
So we're getting that first letter, and now we could use that instead of selecting it, we'll just
select the author.
Last name, maybe the title author, the last name from books where that first character is in.
The set of C and S, I think is the two characters we're supposed to do.
So very different approach and we get the same result.
Siobhan Smith.
Carver.
Steinbeck.
Saunders Let me just prove it.
Both of those will work.
I'm selecting more.
Right?
I selected star here, but otherwise, if I just slim this down, we should get the same result just
so you can compare apples to apples.
So here is the longer version using like.
And then here is the version using words you go in and substring.
So.
Shaaban Smith.
Carver.
Steinbeck.
Saunders.
Same thing here.
I hope that's clear how this one works.
We use that substring function to get the first character.
We check where that first character from author LastName is in this set, whereas here we're just checking
two different patterns.
Okay, next up, this one's a little bit of a pain.
We're going to select title and author last name, and then we have a case situation going on for going
on over on the right side.
So let's select title author, Last name.
And then we're going to have a case.
I'd like to put the end in there and I'd like to do the from books before I forget about that.
And our case is going to start by saying if title contains the word stories anywhere, then we will
have short stories as the value for type so case.
And then we have when.
Author.
Nope when title like.
Percent stories percent.
So we get those wild card characters, neither side.
When that's the case, then we want short stories, I think is what is supposed to be great.
Then let's add in the else just to start with that.
So everything else will have a type of novel.
So else novel just like that.
And then we're going to call this as type, I think, or genre or something, and let's run it and see
if that part's working.
Looks good.
So we see this has stories.
It's classified as short stories, stories, short stories, stories, short stories.
Everything else is novel.
Then we can expand with a second one, one title, and then this is the annoying one we have to match
just kids and also a heartbreaking work of staggering genius.
So one title and we'll just say is directly equal to just kids or title.
Is equal to a heart, and I'm just going to copy this so I don't mess up the spelling.
Like that.
So this is one option for how we do this.
Just kids or title equals heartbreaking work.
Staggering genius.
Then we're going to say memoir is the type.
Let's try running it.
And it looks good.
We've got short stories, novel for everything that is not memoir or short stories.
And these are the two memoirs, just kids in a heartbreaking work of staggering genius.
We called it type.
I guess I capitalized it in this screenshot, but it doesn't matter.
And you probably don't have this no author down here.
My life and words.
That's fine.
But we could exclude anything that had null for author l name.
Using is null or where is not null.
But it's fine because there's a title here and that's what we're working with.
The author last name doesn't really matter and that's it.
Except for if we wanted to clean this up or put this on separate lines, we could just say one title
is equal to just kids, then memoir.
And then on the next line, say, when title was equal to a heartbreaking work of staggering genius,
then memoir.
Either one, it's both of those are essentially equivalent because the only way this would ever run
is if this was false.
So you could put it on separate when then lines or combine it using or either one.
This works.
Just prove one more time.
I run it.
And we still see memoir for Staggering Genius and memoir for Just Kids.
Almost there.
This is our last one, The bonus.
So what we're doing here is generating a table that says how many books each author has written, the
count of number of books.
Each author is written where it's a combination of their first and last name.
Right?
So Dan Harris versus Frieda Harris.
Once again, we're grouping and then counting.
So let's start with that group by.
So we'll select the author F name, author, last name from books.
And we're going to do a group by and we're going to group by both the first name and the last name.
We can't just do the last name, as we already know.
And then I'm going to select the count of the number of rows from that group by.
And I need a semicolon and we see two for the Hery, three for Neil Gaiman, one for Dan Harris.
Why don't I add on a.
Down here.
Where?
From books.
Let's do where.
Author last name is not null.
Just so that I get rid of this bottom row, which you probably don't have to worry about.
If you don't have any nulls, you're good to go.
So that is half of it.
We've got the group by portion done, but we're not just displaying the count.
What are we actually displaying?
Ha we are displaying the count plus some text that says two books, three books, one book.
So we're going to need to use a case.
So instead of displaying count right there, I'm going to do a case.
We can indent it properly if we want.
Case end.
And then I'm going to do something like when the count of the number of rows in each group is equal
to one, then that part is easy.
We just say one book, right?
And then I can do four.
Now just else multiple books.
So that's not exactly what we're supposed to do, but that gets us close.
Oh, and I should give this an as we'll call this count.
And let me put my semicolon in before I forget.
If we run this query, we see count is multiple books for the theory game and Eggers one book for Chabon.
Smith.
Harris, Harris, Saunders.
So you can trust that we're we're it's working.
Except we haven't actually done the right output when there are multiple books.
I need to take the count and then concatenate that with the word books so we know how to do that instead
of else just multiple books.
It's going to be else.
We're going to contact the Count of Star, which again is the number of rows in each group when we do
this group by.
Then we're going to do the string space books.
And that should do it for us.
Let's see.
Two books, three books, three books, one book, one book, two books.
Great.
That's looking good.
And I think we're done right.
One book, one book, one book, two books.
That's what we're supposed to have.
Okay, That was a lot of exercise.
Take a break.
If you feel like you deserve one.
I think you do deserve one.
I'll see you in the next section.