WEBVTT 00:00.120 --> 00:02.790 OK let's go through some of these solutions. 00:02.820 --> 00:10.320 So the first one versed in uppercase is following sentence despite the cat look at me with such hatred. 00:10.500 --> 00:11.880 So I'm just going to copy it. 00:12.180 --> 00:19.410 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 00:19.920 --> 00:22.470 and I'll just call this exercises. 00:22.520 --> 00:26.010 Q Well done in there. 00:26.010 --> 00:27.210 I'll paste this. 00:27.390 --> 00:31.820 And we know that we need to do two things we need to reverse it in upper case. 00:31.980 --> 00:33.620 The order doesn't really matter. 00:33.990 --> 00:36.710 So let's do a we need to do a select first. 00:36.720 --> 00:39.920 Either way select reverse. 00:40.680 --> 00:52.100 And we're going to select the reverse of this and then we can do Opper here just like that or we could 00:52.100 --> 00:55.220 switch them and do select upper reverse. 00:55.430 --> 01:01.080 Well let's give it a shot and I'm missing my quote there. 01:01.170 --> 01:03.200 Let's get out of here. 01:03.330 --> 01:05.020 Missing that closing quote. 01:05.430 --> 01:07.260 Go try it again. 01:10.020 --> 01:10.900 And there we go. 01:10.920 --> 01:13.830 It's reversed and all uppercase. 01:13.890 --> 01:18.050 So now I'm going to comment that out and we'll move on to the next one. 01:18.810 --> 01:20.660 What does this printout. 01:20.790 --> 01:23.360 So let's start on the inside. 01:23.640 --> 01:29.250 Concatenate I with the space with like space and cat. 01:29.280 --> 01:36.240 So it will be I like casse the sentence with spaces but then we have a replace and replace. 01:36.270 --> 01:39.220 It's going to get rid of all those spaces and put dashes. 01:39.510 --> 01:44.010 So it should be I dash like Dash cats. 01:44.280 --> 01:48.570 Let's try it. 01:48.570 --> 01:49.430 There we go. 01:49.800 --> 01:56.910 I like cats with dashes so that one is really just testing that you understand how these are actually 01:56.910 --> 02:02.630 executed and that you can stack multiple nest multiple functions. 02:02.640 --> 02:05.780 Next up we have our first one really working with the book's data. 02:06.090 --> 02:10.350 So replace all spaces in book titles with the arrow. 02:11.520 --> 02:19.350 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 02:19.350 --> 02:21.410 to be from books. 02:21.420 --> 02:23.000 So let's start with that. 02:23.640 --> 02:24.890 So what are we replacing. 02:25.080 --> 02:32.700 Well we're looking for all titles and we're replacing a space with the arrow. 02:33.450 --> 02:37.540 Double check or play spaces with arrows that should be good. 02:37.980 --> 02:41.680 And the other thing was that appear we had title. 02:41.730 --> 02:46.000 So we need to use as a title. 02:46.290 --> 02:48.030 So let's copy it. 02:48.030 --> 02:49.950 Make sure it works. 02:49.950 --> 02:50.730 There we go. 02:50.790 --> 02:54.250 Title the arrow namesake Norse arrow. 02:54.270 --> 02:55.670 Mythology and so on. 02:56.190 --> 03:00.660 I'll comment that one out and we'll move on to the next one. 03:00.660 --> 03:01.830 Print this out. 03:02.190 --> 03:04.630 So we should have forward and backwards. 03:04.920 --> 03:11.060 And this is last name printed regularly and this is last name reversed. 03:12.480 --> 03:13.290 So let's start. 03:13.320 --> 03:20.010 We know we need to have a select and the first thing that we know is that we're going to have author 03:21.080 --> 03:26.250 Ellen name as forward's. 03:26.430 --> 03:27.990 Just make sure I have that. 03:28.020 --> 03:30.890 So that's says that that will give us this right here. 03:31.230 --> 03:38.730 And let's close off let's do from books just like that so we can start with this if we want it and we 03:38.730 --> 03:40.170 get that left portion. 03:40.170 --> 03:46.480 Now we need this right portion so we need a comma because we're now selecting two different things. 03:46.830 --> 03:59.240 And what we want to do here is use reverse author our name as backwards just like that. 03:59.340 --> 04:05.690 Let's copy it and then we go forwards backwards. 04:05.960 --> 04:09.320 Regular last name reversed last name. 04:09.320 --> 04:14.550 All right comment that went out and we move on this one. 04:14.750 --> 04:21.020 We need to print a full name so first and last name of the author all in caps. 04:21.020 --> 04:23.220 So there's a couple of ways we can approach this. 04:23.330 --> 04:26.420 The easiest way is just to capitalize one string. 04:26.480 --> 04:30.090 So we combine the two first in mice and then capitalized. 04:30.410 --> 04:36.050 If you wanted to though you could take capital first and then capital second or excuse me capital first 04:36.050 --> 04:38.820 name capital last name then combine them. 04:38.930 --> 04:40.280 And that works just as well. 04:40.310 --> 04:42.260 But it's easier to just do it once. 04:42.260 --> 04:44.240 And it's also faster. 04:44.240 --> 04:47.450 So the other thing is we have four names in caps up here. 04:47.780 --> 04:49.490 So we need to use as. 04:50.090 --> 04:52.800 So let's start by printing fullname. 04:53.150 --> 05:04.790 So we have a select from books and what we need to do is concat And what are we can catting author f 05:04.910 --> 05:13.670 name comma author LNA and I'll leave it like this for now but hopefully you can predict what the problem 05:13.670 --> 05:14.250 is. 05:14.540 --> 05:23.090 When I do that we don't have spaces so let's at our space in and that should give us the concatenated 05:23.150 --> 05:29.080 version and well free at the right comma. 05:29.470 --> 05:31.320 Now let's try it. 05:31.320 --> 05:32.080 There you go. 05:32.110 --> 05:36.640 So you get Jhumpa Lahiri Dave Eggers and so on with the spaces. 05:36.910 --> 05:42.310 So now what we want to do is just uppercase that so we could do it on the same line because uppercase 05:42.310 --> 05:51.280 is so short we can just say upper of that copy and paste it in. 05:51.790 --> 05:58.220 We also need to rename this up there so we need as and what to call it full name and caps. 05:58.240 --> 05:59.860 So there are spaces in that. 05:59.860 --> 06:04.170 So we need to use quotes full name in caps. 06:04.210 --> 06:06.490 Copy that. 06:06.520 --> 06:08.960 There we go full name in caps. 06:09.590 --> 06:13.060 And if you want to format a little bit better here we can do this 06:18.360 --> 06:23.480 just like that and it will work just the same. 06:23.520 --> 06:27.430 Ok let's come with that one now and move on. 06:27.600 --> 06:28.500 Make this happen. 06:28.620 --> 06:35.560 So what we have again is first that excuse me the title was released in and then the year. 06:35.790 --> 06:38.710 So we're going to need to make use of concatenates. 06:38.790 --> 06:47.520 So let's start off with our select and we'll have a concat but we will also have from books just like 06:47.520 --> 06:49.440 everything else we've done. 06:49.500 --> 06:51.050 So what are we going to concatenate. 06:51.210 --> 06:54.990 Well first is the title after the title. 06:55.080 --> 07:01.160 We have space was released in space so we need those spaces. 07:01.570 --> 07:05.030 Comma like that. 07:05.030 --> 07:06.810 And we could just start with that. 07:06.830 --> 07:15.530 Want to take a look and we get the namesake was released and Norse mythology was released and then we 07:15.530 --> 07:23.600 just need to add in the year comma released year and we haven't really worked with a year in a while 07:23.600 --> 07:26.540 sort of you forgot what the name of that column is. 07:26.810 --> 07:31.610 Earlier I when I was recording something I ended up having to redo it because I was referring to year 07:31.670 --> 07:33.310 rather than released year. 07:33.440 --> 07:39.010 So you could just you describe books and see that. 07:39.020 --> 07:39.280 OK. 07:39.290 --> 07:41.890 It's called released here. 07:41.930 --> 07:42.630 All right. 07:42.800 --> 07:50.400 So then we'll copy that and paste it and we get the namesake was released in 2003 and so on. 07:50.480 --> 07:52.530 The only thing we need to change is this appear. 07:52.670 --> 07:58.710 It's supposed to be called blurb as blurb. 08:01.040 --> 08:02.840 And there we go. 08:02.900 --> 08:07.460 So we're done with that one commented out. 08:07.550 --> 08:12.220 Next up print book titles and the length of each title. 08:12.920 --> 08:14.680 So printing the title is easy. 08:14.930 --> 08:19.360 Then we just need to use char length and print the character length. 08:19.580 --> 08:26.550 So let's do that now select from books and what do we want to select. 08:26.600 --> 08:32.650 Well first we want title and then after we select the title we want char length. 08:32.810 --> 08:38.150 And one thing I should highlight there is that because this is a sequel file might have noticed I get 08:38.150 --> 08:43.550 these autocomplete suggestions so I can either click or I can hit tab. 08:44.110 --> 08:46.970 And that's much faster in my experiences to hit tab. 08:47.270 --> 08:53.810 So we want the length of the title just like that 08:57.060 --> 09:01.160 and we get the namesake 12 Norse mythology 15. 09:02.440 --> 09:03.340 And that's what we want. 09:03.340 --> 09:08.020 Except we need character count just like that. 09:08.020 --> 09:12.330 However this will cause an error. 09:12.940 --> 09:17.530 We need to put it in quotes because there is a space there. 09:17.530 --> 09:23.360 Now we give it a shot and it works out right. 09:23.590 --> 09:25.570 Last one I promise. 09:25.570 --> 09:27.840 So this one is more complex. 09:27.910 --> 09:32.710 Three different things for selecting each one is complex on its own. 09:33.070 --> 09:34.680 So let's tackle the first bit. 09:34.750 --> 09:36.010 The short title. 09:36.310 --> 09:43.140 So we want the first 10 characters of the title plus dot dot dot. 09:43.320 --> 09:45.270 So we'll comment this out. 09:45.610 --> 09:55.060 We'll do our same select from books and we'll start by selecting the first 10 characters so that substring 09:55.360 --> 10:00.180 or fairly easy substrate or whatever that's the substring. 10:00.640 --> 10:07.420 And we want it of title from 1 to 10 just like that. 10:07.540 --> 10:13.990 So that should just give us at this point should know what it does gives us the first 10 characters. 10:13.990 --> 10:18.510 But we also want to concatenate that with dot dot dot. 10:18.580 --> 10:25.660 So we'll do that here concat with the string dot dot dot dot com Economy.com. 10:27.070 --> 10:30.000 And now we copy that. 10:30.270 --> 10:34.290 That works but it's supposed to be called short title. 10:34.570 --> 10:45.540 So we add as short title and we just triple check our work looks good. 10:45.940 --> 10:52.400 Now we have our next thing we're selecting which is the author but we want last name comma first thing 10:53.230 --> 10:55.800 so it's going to be another concat actually. 10:56.020 --> 11:04.960 And we'll start with the author L name comma a comma in quotes. 11:04.960 --> 11:12.040 Kind of hard to talk about comma comma common but this coming here is actually going to be concatenated 11:12.070 --> 11:13.210 in. 11:13.250 --> 11:18.410 This is quite a string to look at their author name. 11:19.090 --> 11:25.090 And we want it to be as author so we don't have to use quotes for that. 11:25.210 --> 11:29.140 Let's try it and see if we made any mistakes. 11:29.140 --> 11:29.960 It looks good. 11:30.110 --> 11:34.440 So we get here we come a jumper game and come in Neal. 11:34.480 --> 11:36.110 That's what we expect. 11:36.350 --> 11:38.940 And it's called author. 11:39.280 --> 11:44.770 Finally we need this last portion quantity and this should be the number in-stock followed by the string 11:45.010 --> 11:46.360 in stock. 11:46.930 --> 11:55.030 So if you don't remember what the column name is we can do describe books and you can see OK we have 11:55.690 --> 11:57.260 stock quantity. 11:57.310 --> 11:59.060 So that's what we want to use. 11:59.650 --> 12:08.370 So this time we're starting off with a stock quantity comma and then we want space in space stock 12:11.600 --> 12:13.380 just like that. 12:14.060 --> 12:19.720 And then we need to use as quantity boy. 12:19.970 --> 12:20.850 That's a long one. 12:21.100 --> 12:25.440 So let's try it out make sure we didn't do anything wrong and that looks good. 12:25.450 --> 12:28.220 32 in-stock hundred fifty four in-stock. 12:28.540 --> 12:31.620 And then we have author and short title both working. 12:31.630 --> 12:37.220 Now one thing I want to point out here that stock quantity is a number it's an it. 12:37.660 --> 12:39.940 But it doesn't break everything. 12:39.940 --> 12:43.060 Concat is OK with numbers like this. 12:43.060 --> 12:50.250 What it does is it just takes the string version the text version of 32 and adds it to in-stock. 12:50.350 --> 12:56.350 So it basically does this 32 plus in stock. 12:56.980 --> 13:01.040 So that's very different than trying to add a number to text. 13:01.060 --> 13:04.300 All right so that was a lot I know but we're done now. 13:04.540 --> 13:07.840 We're done with these string functions and we're moving on.