WEBVTT 00:07.370 --> 00:10.430 I welcome you back again to another video lecture in MySQL. 00:10.430 --> 00:15.260 And in this video lecture we go ahead and look at functions in MySQL. 00:16.310 --> 00:20.870 So MySQL we have some functions that are available. 00:20.870 --> 00:26.780 And let's go ahead and take a look at uh this uh stuff right in here. 00:26.780 --> 00:33.650 So under here under MySQL workbench we can see that we have some of these uh functions available. 00:33.650 --> 00:38.060 But as a beginner, if you begin to go through these functions, you might get lost. 00:38.270 --> 00:38.570 All right. 00:38.570 --> 00:40.490 So we're going to implement some of them. 00:40.490 --> 00:48.320 Then you can check out more on the documentations and read them the reader for you to always check out. 00:48.350 --> 00:52.400 Now we have the student table we created at the beginning of this course. 00:52.400 --> 00:54.260 And it's still right in there with us. 00:54.260 --> 00:58.820 So you can see the student table right in here we have the student ID, we have the firstName, we have 00:58.820 --> 01:01.910 the last name, the GPA and enroll date. 01:02.600 --> 01:07.280 Let's go ahead and say we want to concatenate the first name and last name. 01:07.310 --> 01:09.500 I want to join first name and last name. 01:09.530 --> 01:16.760 What I'm going to do is go ahead and select and I will have a function called concat. 01:16.790 --> 01:21.590 So the concat function will help us to actually concat this. 01:21.620 --> 01:30.260 Now let's go ahead and go right in here and specify that we want to the first underscore name. 01:30.290 --> 01:35.330 And I'll put a comma and then last underscore name. 01:35.330 --> 01:38.120 So these are the two we want to concatenate. 01:38.120 --> 01:42.830 And we identify the table known as students. 01:43.970 --> 01:46.700 All right so a semicolon at the end. 01:46.700 --> 01:49.520 And let's go ahead and execute this query. 01:49.760 --> 01:55.760 So you can see we have our the first name and last name joined together. 01:55.760 --> 01:58.610 And it is not looking good at all. 01:58.610 --> 02:03.420 So let's go ahead and give a nickname to these, because we have this name right in the first name and 02:03.420 --> 02:03.960 last name. 02:03.990 --> 02:04.560 You see that. 02:04.560 --> 02:06.540 So let's go ahead and give it a nickname. 02:06.540 --> 02:11.340 So I'm going to say s full underscore name. 02:11.370 --> 02:11.910 All right. 02:11.910 --> 02:13.020 So that is what I want to get. 02:13.020 --> 02:14.430 The nickname is full name. 02:14.430 --> 02:16.590 Go ahead and execute this code again. 02:16.590 --> 02:19.860 And you can see that it has changed to full name. 02:20.700 --> 02:22.740 But there is one thing that is still remaining. 02:22.740 --> 02:29.790 You can see that I have the first name and last name joined together to do Oscar, and nobody knows 02:29.790 --> 02:31.140 that these are different names. 02:31.140 --> 02:37.800 So what I'm going to do is we'll go to move right inside the concat function, and we're going to add 02:37.800 --> 02:38.700 this. 02:38.700 --> 02:45.030 So space is a character in even programming and even on database design. 02:45.030 --> 02:48.330 So go inside the double quotation mark and give a space. 02:48.330 --> 02:52.470 And let's go ahead and execute this query. 02:52.470 --> 02:55.770 So you can see that Peter Jude has been separated. 02:55.770 --> 02:58.170 And I should continue to give a space. 02:58.170 --> 03:01.370 Let's say I give three presses and I execute this query. 03:01.400 --> 03:03.830 You can see that we have enough space right in here. 03:03.860 --> 03:08.990 So first name is not different from the last name. 03:08.990 --> 03:11.210 And you can see that boldly right in there. 03:11.240 --> 03:15.050 So this is just one of the functions that we have. 03:15.080 --> 03:18.380 Let's go ahead and calculate let's say the GP. 03:18.530 --> 03:22.850 Let's go ahead and say I have different GP and want to know how many GP a day. 03:22.880 --> 03:26.180 And let's calculate the number of GP that is there. 03:26.180 --> 03:33.260 So I'll go over here and first of all say check out this query and we have seven 1 to 7. 03:33.260 --> 03:34.910 So how come we are we to do that. 03:34.910 --> 03:38.630 Let's go ahead I use what is called a count function. 03:38.630 --> 03:45.770 So I'll go ahead and say count and I'll ahead and pass in the GP right in there. 03:46.370 --> 03:48.590 So go ahead and execute this query. 03:48.590 --> 03:52.190 And we have seven and that is count GP. 03:52.220 --> 03:54.320 You can also give that a nickname. 03:54.320 --> 04:03.890 So we can go ahead and say uh this is a as saw students underscore GP or number of GP. 04:03.920 --> 04:14.030 Go ahead and say this is as then you go ahead and remove this and this is Non-vowel GP and go ahead 04:14.030 --> 04:15.170 and check it out. 04:15.200 --> 04:18.560 Number GP or student GP or anything. 04:18.830 --> 04:19.400 All right. 04:19.430 --> 04:22.550 So count is another function in MySQL. 04:22.970 --> 04:24.080 We have other functions. 04:24.110 --> 04:26.510 Again we can actually use. 04:26.510 --> 04:32.120 And before we go ahead I will also let you know that whenever I want to give a nickname, you can also 04:32.150 --> 04:33.770 go ahead and give that as a string. 04:33.770 --> 04:36.050 So go ahead and pass a string data type. 04:36.050 --> 04:40.220 And I can name this to be students GP. 04:40.340 --> 04:46.400 And if I run this check it out students GP and this looks a little bit better off. 04:46.520 --> 04:49.280 Can you see that student GP and that is seven. 04:49.550 --> 04:50.090 All right. 04:50.090 --> 04:55.250 So you can name that give it a nickname or go ahead and put the nickname in a string format. 04:55.250 --> 04:57.170 And it used to work out fine. 04:57.890 --> 04:58.430 Hi. 04:58.430 --> 05:02.390 And now let's go ahead and check out the GP racing the Maximo of the GP. 05:02.510 --> 05:10.040 Go ahead and put in max function and then you can name this to be maximum GP. 05:12.140 --> 05:12.530 All right. 05:12.560 --> 05:19.160 So maximum GP and go ahead and execute the query and have maximum GP to be 4.800. 05:20.060 --> 05:23.960 In that way we can also check for minimum GP by putting a mean. 05:23.960 --> 05:28.340 And then we go ahead and change this to be the minimum. 05:29.270 --> 05:33.710 So go ahead and execute this query and you have the minimum GP to be 1.0. 05:33.740 --> 05:34.640 You see that. 05:35.390 --> 05:37.250 So that is really very good. 05:37.250 --> 05:39.590 Now you can check the average of the GP. 05:39.680 --> 05:42.860 This guy has to say the average of the GP is going to be Avg. 05:43.100 --> 05:51.470 And we go ahead and change this to average GP and go ahead and execute the query and check it out. 05:51.470 --> 05:54.230 So the average is 3.485. 05:54.410 --> 06:00.200 We also have a sum you can actually sum all the OD GP, and I don't know if that is actually being used 06:00.200 --> 06:00.710 in schools. 06:00.740 --> 06:07.700 Go ahead and say the song and we'll go ahead and say addition. 06:09.830 --> 06:10.880 Or LGBT. 06:10.910 --> 06:11.510 Okay. 06:11.540 --> 06:14.870 Let's say add GP and go ahead and execute the query. 06:14.870 --> 06:21.380 And you have GP to be equal to 2.400. 06:21.410 --> 06:21.770 Okay. 06:21.800 --> 06:23.300 24.400. 06:23.330 --> 06:24.350 That is what it is. 06:24.350 --> 06:27.860 So these are some of the basic functions we use in MySQL. 06:27.890 --> 06:29.300 So go ahead and practice them. 06:29.300 --> 06:35.030 You can check out the MySQL documentation to play around with them and try out other uh functions. 06:35.030 --> 06:37.910 And there are many more functions out there to check out. 06:37.910 --> 06:40.520 So this is just the basics for now for beginners. 06:40.520 --> 06:43.520 And uh, I hope you start from here and you move on. 06:43.520 --> 06:44.390 Thank you so much. 06:44.390 --> 06:47.810 And I'm going to see you in the next video lecture.