All right, solution time for the exercises.
The first exercise is to take this sentence, and we want to reverse it in uppercase it at the same
time.
So that is as simple as I'll do it in the workbench.
I'll take this sentence and select the reverse of the uppercase, which I also could do use case of
this string.
The order doesn't really matter.
Do we uppercase and then reverse, or do we reverse and then uppercase?
It doesn't matter.
But let's run this.
And that's what we get back.
Right there, this lovely piece of text.
I can also, of course, just paste it over here, and that's what we get.
Why does my cat look at me with such hatred?
Question mark, but in reverse.
Next up, what does this printout?
So we evaluate this from the inside out, right?
This cat is nested in the replace, so we start with the cat.
What does this give us?
I space like space cats.
It gives us gives us the sentence.
I like cats, but immediately afterwards we have our replace which takes that sentence.
I like cats and replaces all spaces with dashes so our output will look something like this.
I like cats.
Let's see.
Am I correct?
And there it is.
I like cats so we can cat with the spaces first and then replace that string.
Replace all spaces with dashes.
Next up, we're going to write a query from our select from our books table to work with the titles,
replace the spaces in the titles with a arrow.
So let's do this again over in an editor.
Let's do it here.
Select.
And then we want to replace from the title.
We want to replace space characters with an arrow from books.
And if we run that.
Look at the output here.
That looks correct, except for one thing, which remember all of these tables or all of these outputs
I've shown you have customized or alias column names there.
So let's change that.
Let's take this and do an as title from books.
Paste this over here.
Make sure it works.
There we are.
It looks good.
If I didn't have that as title, then we just end up with this sort of ugly heading that just says the
operation, the function name.
All right, so that's how we can replace in every title all spaces with arrows.
Next up, print this out.
So we have the last name of an author and then the last name in reverse.
And it's called Forwards and Backwards.
So let's start over here.
Shrink you down.
Let's select the easy one is just author.
I think it's author ll name from author or from books.
Let's just start with that.
Make sure that works and that gives us the last name.
It's great.
But then I want to also, with a comma, select the reverse of author L name.
From.
And I messed that up.
This needs to go right after our first column, then our second one from books.
Let's run it.
And just as a reminder, when I click that second lightning bolt, it's just running where my cursor
is.
So none of these other queries are running.
And that looks good, right?
Lahiri And then Eerie Hall or whatever.
It's supposed to be pronounced backwards.
The only thing we have remaining is to rename our columns.
So I'm going to call this one as forwards, and this one will be as backwards.
Or did I call it reverse now, forwards and backwards.
All right.
And then we could format this as well if we really wanted to.
Does that make it much better?
Maybe a little bit.
Let's run it in my console this time.
And there we are.
Forwards and backwards.
Next up, print out the full name of each author in caps.
So full name means their first name and last name smushed together with a space.
So that's going to be using canned cat or we're going to do that using con cat.
Let's do it again over here in a file.
So let's start by worrying about the con cat and we'll wait to worry about the upper casing.
So select we're going to concatenate author f name with a space with author l name from books.
And that should hopefully just give us first and last name with a space.
Looks good to me.
Jhumpa Lahiri.
Neil Gaiman.
The space is there.
So now we need to uppercase.
And there's a couple of ways of doing this, of course, but the simplest one is to uppercase after
we can cat.
But we could have uppercase this and uppercase this and then concatenated them.
I think it makes more sense to just upper the result of that concatenation.
So if we try that, there we are.
We're getting the full name with the space in uppercase, the last thing we need to do is rename the
column there.
Our output is supposed to say full name and caps, so let's do that as full name in caps just like that.
And let's run this one in my shell again.
Full name and caps looks good.
Next up, we've got to make this blurb happen.
So the blurb is the title of a book and then a space and was well, it's this whole piece of text here
was released in and then the year and then of course we call it blurb.
So we're going to do some concatenation.
We know that.
What are we Concatenating Well we're going to concatenate the book's title with was with a space at
the beginning was released in and then another space because we need those spaces because the title
doesn't have a trailing space.
So we want that space was released in space and then the year and I believe what we're looking for is
called released year.
Let's just verify.
Yes, released here.
So we'll put that in here, released, underscore year from books.
And if we run that.
And expand this.
It's really annoying that this keeps shrinking down.
The Namesake was released in 2003.
Norse mythology was released in 2016.
That's what we're supposed to do.
All we have left to do now is rename this use as.
Blurb.
Try it again.
And it's probably impossible for you to see that it says blurb there, but it does.
Let's just copy and paste it over to our shell.
There we are.
Blurb.
OC Next up, print the book titles and the length of each title.
So the title part is easy, and then the length of each title is in characters.
So don't forget that we're going to do a select title.
Comma.
And then we want char length.
Of the title from books just like that.
And we should see.
Yeah.
The Namesake 12.
Is that correct?
Namesake 12.
Norse mythology.
15.
And then we just need to rename the columns again.
Character count is what that one should say.
Because right now, if we run this, it says char length of title.
So let's rename that as character count.
And copy that pasted here.
And just as a reminder, I'm only pasting it over here because it's impossible for you to see the results
and the name of each one of these columns.
And if we take a look, it looks good character count and title.
And I think we have one more, the most complicated one.
We have three pieces of information.
Let's start with the short title.
It's the first ten characters, so it's a substring.
And then we concatenate, but let's just get the substring of the title working first.
We actually saw how to do this already.
It's substring or sub str of the title.
And then we have to say what character we start at.
So position one and how many characters we move on for.
Ten, I think is what we wanted.
Let's verify.
The name say essay.
That's what we were supposed to have.
American Gee.
Looks good to me.
OC Then we want to concatenate that with a dot, dot, dot.
Right.
So I'm going to take my cat, wrap it around this substrate and then add in dot, dot, dot.
So this should then concatenate the shortened version, the substring with the dot, dot, dot.
And we want to call that short title.
So we'll start with that as short.
Title.
All right, let's format this.
And now we have one third of what we need, at least in terms of the number of columns, maybe not in
terms of the amount of work.
Now, let's move on to the next piece, which is the author.
But our author field here is going to have the author's last name, a comma, then their first name.
So we're going to concatenate once again the author's last name.
So author, LL name and then a comma.
And you could technically use that W.
S for the separator and put the comma first.
But this is easy enough.
And then author last name and I first name rather, because it's last.
Then first and we're calling this author, I believe.
Make sure I have that correct as author.
Let's run this one.
Author Lahiri comma, Jhumpa Gaiman, comma, Neil Eggers, comma, Dave.
It's what it's supposed to be.
And then our quantity is supposed to be the number in stock.
So that's going to be another.
Venkat And this time we're contacting can catting the number that we have.
I think it's called stock Quantity can Verify.
Again, Select Star.
From books and it is indeed called stock quantity.
So stock quantity.
And then we're concatenating that with in stock or technically space in stock as.
Quantity, I think, is what it's supposed to be called.
Moment of truth here.
Oof!
There's a lot to this exercise.
Let's see.
32 in stock.
43 in stock.
12 in stock.
97 in stock.
That looks good to me.
104 in stock for a heart breaking work of staggering genius.
That's it for this exercise.
Woof.
There's a lot a lot of practice with string functions.
Next up, we're going to continue leveling up our skills and learn how to refine our selections even
further.