Let's go through some of these solutions.
So the first one verse in uppercase, this following sentence Why does my cat look at me with such hatred?
So I'm just going to copy it.
And the first thing I'm actually going to do is make a new file.
So I'll come up here and do new file and I'll just call this exercises dot SQL.
Then in there, I'll paste this.
And we know that we need to do two things.
We need to reverse it in uppercase.
The order doesn't really matter.
So let's do a we need to do a select first.
Either way, select reverse and we're going to select the reverse of this.
And then we can do upper here.
Just like that.
Or we could switch them and do select upper reverse.
But let's give it a shot.
And I'm missing my quote there.
Let's get out of here, missing that closing quote.
There we go.
Try it again.
And there we go.
It's reversed and all uppercase.
So now I'm going to comment that out and we'll move on to the next one.
What does this print out?
So let's start on the inside.
Concatenate I with the space with like space and cats.
So it will be I like cats, the sentence with spaces.
But then we have a replace and replace is going to get rid of all those spaces and put dashes.
So it should be I dash like dash cats.
Let's try it.
There we go.
I like cats with dashes.
So that one is really just testing that.
You understand how these are actually executed and that you can stack multiple or nest multiple functions.
Next up, we have our first one really working with the books data.
So replace all spaces in book titles with the arrow.
So we need to do a select and we know that there's going to be a replace and we know that it's going
to be from books.
So let's start with that.
So what are we replacing?
Well, we're looking for all titles and we're replacing a space with the arrow.
Double check, replace spaces with arrows.
That should be good.
And the other thing was that up here we had title, so we need to use as title.
So let's copy it.
Make sure it works.
There we go.
Title the arrow, namesake, Norse Arrow mythology and so on.
I'll comment that one out and we'll move on to the next one.
Print this out.
So we should have forwards and backwards and this is last name printed regularly and this is last name
reversed.
So let's start.
We know we need to have a select and the first thing that we know is that we're going to have author
l name as forwards.
Just make sure I have that.
So that's just that will give us this right here and let's close off let's do from books just like that
so we can start with this if we wanted and we get that left portion now we need this right portion.
So we need a comma because we're now selecting two different things.
And what we want to do here is use reverse author l name as backwards.
Just like that.
Let's copy it.
And there we go.
Forwards, backwards.
Regular last name.
Reverse last name.
All right, comment that one out and we move on.
This one, we need to print a full name.
So first and last name of the author, all in caps.
So there's a couple of ways we could approach this.
The easiest way is just to capitalize one string.
So we combine the 2/1 and last and then capitalize.
If you wanted to, though, you could take capital first and then capital second.
Excuse me, capital first name, capital last name, then combine them.
And that works just as well.
But it's easier to just do it once and it's also faster.
So the other thing is we have full name in caps up here.
So we need to use as.
So let's start by printing full name.
So we have a select from books and what we need to do is can cat and whatever we can catting author
f name comma author l name.
And I'll leave it like this for now, but hopefully you can predict what the problem is.
When I do that, we don't have spaces, so let's add our space in and that should give us the concatenated
version.
And well, if we add the right comma
now let's try it.
There we go.
So we get Jhumpa Lahiri, Dave Eggers and so on with the spaces.
So now what we want to do is just uppercase that.
So we could do it on the same line because uppercase is so short, we can just say upper of that, copy
it, paste it in.
We also need to rename this up there.
So we need as and what did I call it, full name and caps.
So there are spaces in that.
So we need to use quotes full name in caps.
Copy that.
There we go.
Full name in caps.
And if we want to format a little bit better here, we could do this.
Just like that.
And it we'll work just the same.
OC Let's comment that one out and move on.
Make this happen.
So what we have again is the first excuse me, the title was released in and then the year.
So we're going to need to make use of concatenate.
So let's start off with our select and we'll have a cat.
But we will also have from books just like everything else we've done.
So what are we going to concatenate?
Well, first is the title.
After the title, we have space was released in space.
So we need those spaces, comma like that.
And we could just start with that if we want to take a look
and we get the namesake was released in Norse mythology was released in then we just need to add in
the year comma released year and we haven't really worked with a year in a while.
So if you forgot what the name of that column is earlier I when I was recording something, I ended
up having to redo it because I was referring to year rather than released year.
So you could just do a described books and see that it's called released year.
All right.
So then we'll copy that in paste it and we get The Namesake was released in 2003 and so on.
The only thing we need to change is this up here, it's supposed to be called blurb as blurb.
And there we go.
So if we're done with that one, comment it out.
Next up, print book titles and the length of each title.
So printing the title is easy.
Then we just need to use char length and print the character length.
So let's do that now.
Select from books.
And what do we want to select?
Well, first we want title and then after we select the title, we want char length.
And one thing I should highlight there is that because this is a SQL file, you might have noticed,
I get these autocomplete suggestions so I can either click or I can hit tab and that's much faster in
my experience is to hit tab.
So we want the length of the title.
Just like that.
And we get the namesake 12 Norse mythology.
15 And that's what we want, except we need character count, just like that.
However, this will cause an error.
Whoops.
We need to put it in quotes because there's a space there.
Now we give it a shot and it works.
Right.
Last one, I promise.
So this one is more complex.
Three different things we're selecting.
Each one is complex on its own.
So let's tackle the first bit.
The short title.
So we want the first ten characters of the title plus dot, dot, dot.
So we'll comment this out.
We'll do our same select from books and we'll start by selecting the first ten characters.
So that's substring.
Or if you're lazy, substring, whatever, let's do substring.
And we want it of title from 1 to 10.
Just like that.
So that should just give us.
At this point, you should know what it does.
It gives us the first ten characters.
But we also want to concatenate that with dot, dot, dot.
So we'll do that here.
Can cat with the string dot, dot, dot, not comicbook.com dot, dot, dot.
And now if we copy that, that works.
But it's supposed to be called short title.
So we add as short title.
And we just triple check our work.
Looks good.
Now we have our next thing we're selecting, which is the author.
But we want last name, comma, first name.
So it's going to be another king cat, actually.
And we'll start with the author l name comma a comma in quotes.
Kind of hard to talk about comma, comma, comma.
But this comma here is actually going to be concatenated in.
This is quite a string to look at there.
Author f name.
And we want it to be as author so we don't have to use quotes for that.
Let's try it.
See if we made any mistakes.
It looks good.
So we get Lahiri.
Comma, Jhumpa, Gaiman, comma, Neil.
That's what we expect.
And it's called author.
Good.
Finally, we need this last portion quantity.
And this should be the number in stock, followed by the string in stock.
So if you don't remember what the column name is, we can do describe books.
And you can see we have stock quantity.
So that's what we want to use.
So this time, we're starting off with a stock quantity comma.
And then we want space in space stock.
Just like that.
And then we need to use as quantity.
Boy, it's a long one.
So let's try it out.
Make sure we didn't do anything wrong.
And that looks good.
32 in stock, 154 in stock.
And then we have author and short title both working.
Now, one thing I want to point out here, that stock quantity is a number, it's an int, but it doesn't
break everything can cat is okay with numbers like this.
What it does is it just takes the string version, the text version of 32 and adds it to in stock.
So it basically does this 32 plus in stock.
So that's very different than trying to add a number to text.
All right.
So that was a lot.
I know.
But we're done now.
We're done with these string functions and we're moving on.