1
00:00:00,090 --> 00:00:03,990
Next up, an exercise using most of the string functions we've learned.

2
00:00:03,990 --> 00:00:04,920
Not all of them.

3
00:00:05,580 --> 00:00:08,090
Treat this as an open book thing.

4
00:00:08,100 --> 00:00:10,230
If you need to go open the docs.

5
00:00:10,260 --> 00:00:10,810
Do it.

6
00:00:10,830 --> 00:00:13,680
I have to look at the docs all the time before I teach any of these videos.

7
00:00:13,680 --> 00:00:14,870
So here we go.

8
00:00:14,880 --> 00:00:16,260
String functions.

9
00:00:16,560 --> 00:00:18,810
There's a couple of pieces to this.

10
00:00:19,050 --> 00:00:23,040
I'm basically going to start with the easiest ones going up to more complicated ones.

11
00:00:23,070 --> 00:00:29,790
The first thing is just to simply reverse and uppercase the following sentence using a single query.

12
00:00:30,610 --> 00:00:33,280
So no tables, no data, just this string.

13
00:00:33,910 --> 00:00:37,060
Then I'd like you to pause for all of these.

14
00:00:37,060 --> 00:00:39,970
But for this one pause and think out loud.

15
00:00:39,980 --> 00:00:44,260
Well, it doesn't have to be out loud, but think to yourself what this evaluates to what is printed

16
00:00:44,260 --> 00:00:44,680
out.

17
00:00:46,080 --> 00:00:49,020
Then I'd like you to write a query using our books.

18
00:00:49,020 --> 00:00:53,550
So if you deleted books, if you got rid of them, just make sure there's some of the books data in

19
00:00:53,580 --> 00:00:54,210
that table.

20
00:00:54,210 --> 00:00:55,270
You don't need all of them.

21
00:00:55,290 --> 00:01:03,270
And I'd like for you to select the titles and replace all spaces with an arrow, which is just a dash

22
00:01:03,270 --> 00:01:04,530
and a greater than sign.

23
00:01:04,650 --> 00:01:09,630
Make sure as well that you name the column that is generated title.

24
00:01:09,780 --> 00:01:11,970
So multiple pieces there.

25
00:01:12,540 --> 00:01:18,780
Next up, I'd like you to print this table out, which is the last name of every author on the left

26
00:01:18,780 --> 00:01:24,380
side, printed forwards and then the same last name in reverse on the right side.

27
00:01:24,390 --> 00:01:26,970
And notice again that we've alias them.

28
00:01:26,970 --> 00:01:29,580
So this one is called forwards and this one is backwards.

29
00:01:29,610 --> 00:01:31,110
Similar story here.

30
00:01:31,110 --> 00:01:33,280
Printouts or generate this table.

31
00:01:33,300 --> 00:01:37,650
This column here is called full name in caps, so make sure you name it that.

32
00:01:37,650 --> 00:01:39,210
And that's exactly what it is.

33
00:01:39,210 --> 00:01:43,740
It is the first name of an author and the last name of an author with a space in the middle.

34
00:01:43,890 --> 00:01:45,540
All capitalized.

35
00:01:46,020 --> 00:01:46,710
All righty.

36
00:01:47,410 --> 00:01:48,370
After that.

37
00:01:48,370 --> 00:01:50,620
This one is called Blurb.

38
00:01:50,650 --> 00:01:52,510
It's the novel blurb.

39
00:01:52,510 --> 00:01:53,770
Or the book blurb.

40
00:01:54,340 --> 00:02:00,600
You'll take the title of each book, the release year of each book, and combine them into a sentence.

41
00:02:00,610 --> 00:02:04,350
So you'll have to put some text before The Namesake.

42
00:02:04,360 --> 00:02:05,210
That's the title.

43
00:02:05,230 --> 00:02:07,380
So actually, you don't put in text before.

44
00:02:07,390 --> 00:02:09,100
Norse mythology is the title.

45
00:02:09,130 --> 00:02:14,590
American Gods and then was released in and then the year it was released.

46
00:02:16,100 --> 00:02:22,190
And then I think we just have to more print the book titles and the length of each book title.

47
00:02:22,190 --> 00:02:29,670
So title and then call this column character count, and we'll have things like American Gods with the

48
00:02:29,810 --> 00:02:34,160
Count of Characters, which is 13, and this is the length and characters, not bytes.

49
00:02:35,280 --> 00:02:39,470
And then the final piece, the most complicated one, is to generate this output.

50
00:02:39,480 --> 00:02:44,190
We have three different fields, three different columns I want you to print out.

51
00:02:44,310 --> 00:02:46,620
The first one is called Short title.

52
00:02:46,620 --> 00:02:48,840
It's the first ten characters, I believe.

53
00:02:48,840 --> 00:02:54,240
One, two, three, four, five, six, seven, eight, nine, ten of a book title of all book titles

54
00:02:54,240 --> 00:02:56,730
plus dot, dot, dot at the end.

55
00:02:57,120 --> 00:03:03,030
And then we have author, which is every author's last name and then a comma, and then their first

56
00:03:03,030 --> 00:03:03,540
name.

57
00:03:03,870 --> 00:03:07,380
And then we have quantity, which is the number in stock.

58
00:03:07,380 --> 00:03:09,570
That's one of the fields on our table.

59
00:03:09,600 --> 00:03:16,260
If we do describe books, one of the columns is called Stock Quantity.

60
00:03:16,530 --> 00:03:17,970
So I want that stock quantity.

61
00:03:17,970 --> 00:03:22,140
But in stock as a sentence or as a I guess it's a fragment.

62
00:03:22,140 --> 00:03:24,210
I don't know why we would want that, but just do it.

63
00:03:24,210 --> 00:03:26,340
It's a last piece of the exercise.

64
00:03:26,340 --> 00:03:28,530
So there's a lot of pieces to this one.

65
00:03:28,770 --> 00:03:35,160
Go through this and pause with this video and try and figure out the correct code to generate the output

66
00:03:35,160 --> 00:03:36,930
that you see on each slide.

67
00:03:37,050 --> 00:03:38,910
But first, we have the simpler ones.

68
00:03:39,090 --> 00:03:42,750
So in the next video, we'll have a solution, but I hope you try it.

69
00:03:42,750 --> 00:03:43,350
Please do it.