WEBVTT 00:07.190 --> 00:09.920 I will call them back again to one another within my circle. 00:09.920 --> 00:14.780 And in this video lecture we will go ahead and learn about soft query in MySQL. 00:14.960 --> 00:19.280 So in MySQL we have a subquery and it will be like what is soft query. 00:19.310 --> 00:22.700 So soft query is a query inside another query. 00:22.760 --> 00:25.790 Everything is about query in MySQL. 00:25.820 --> 00:28.520 Yes everything is about query. 00:28.550 --> 00:32.630 Now let's go ahead and say we have this table or students table in here. 00:32.660 --> 00:34.040 And we have the students GPA. 00:34.190 --> 00:42.560 And you can see the GPA of 3.5 to 4 point 7 to 2.7, 4.8 to 0 point 9 to 2.8 and 1.0. 00:42.560 --> 00:44.510 So once you get the average of this GPA. 00:44.540 --> 00:46.970 Let's go ahead and select. 00:46.970 --> 00:50.960 And I'm going to say select uh avg. 00:51.320 --> 00:56.180 So I can go ahead and pass in this table name GPA right in there I'll go ahead and say GPA. 00:56.330 --> 01:03.260 And then I'll go ahead and uh say from this table and the name of this table is students. 01:03.260 --> 01:05.060 And go ahead and execute this query. 01:05.090 --> 01:12.030 And you can see Avg and that is 3.49. 01:12.060 --> 01:12.420 All right. 01:12.420 --> 01:16.200 So 3.49 or 3.4857143. 01:16.380 --> 01:16.920 All right. 01:16.950 --> 01:17.580 That is cool. 01:17.610 --> 01:22.500 You can go ahead and make this to uppercase SVG and go ahead and execute the query. 01:22.500 --> 01:23.760 And that is what we have. 01:23.760 --> 01:26.700 So I'll be using the uppercase to do this. 01:27.360 --> 01:30.810 And let's go ahead and create a sub query from this. 01:30.840 --> 01:31.200 Okay. 01:31.230 --> 01:36.660 Let's go ahead and make this a subquery inside another query in this table. 01:36.660 --> 01:44.730 So let me go ahead and select all the first name and last name of students who have their GPA and a 01:44.730 --> 01:47.370 warhead and say select. 01:47.370 --> 01:55.530 And I'll go ahead and say select underscore first name and I will select underscore last name. 01:55.530 --> 02:00.540 And I'll select the GPA and the comma. 02:00.570 --> 02:09.750 So I'll go ahead and pass in the uh the average of this GPA from the students. 02:12.150 --> 02:18.820 So pass out from two To Strange Table, and I forgot that these activities would go ahead and get a 02:18.820 --> 02:23.710 first name, the last name and a GP and the average of this GP last year. 02:23.710 --> 02:27.790 So you can see select from GP avg is too big. 02:27.790 --> 02:29.230 So we have to make this an alias. 02:29.260 --> 02:32.380 And that's why I shorten the name so that it can doesn't look good. 02:32.410 --> 02:32.980 All right. 02:32.980 --> 02:41.260 So if I'm choosing the name, what I need to do is to go ahead and say as semicordata be avg. 02:43.270 --> 02:46.540 So avg underscore GP I hope that is cool. 02:46.540 --> 02:52.480 And if we run this again so I can query and have the average of each of these GP, is this. 02:52.480 --> 02:58.480 So the average of each of today's GP is 3.49. 02:58.600 --> 02:59.830 So I hope that's cool. 02:59.830 --> 03:04.060 So we can now find the students whose GPAs are above the average. 03:04.060 --> 03:07.720 So how can you be able to find students whose GPA is above the average. 03:07.720 --> 03:11.740 So you can see how we are using the query inside another query. 03:11.740 --> 03:14.860 And we are still going to use this right here. 03:14.890 --> 03:16.600 So how can we do that. 03:16.630 --> 03:22.350 Now let's go ahead and say we select all first name and last name and GP from students. 03:22.380 --> 03:29.100 Go ahead and select that from violence from students. 03:29.730 --> 03:34.320 And uh, we say students. 03:35.070 --> 03:39.840 And then I'll go ahead and remove this from here and put this. 03:40.620 --> 03:43.680 And I'm going to say I'm selecting this from students. 03:43.710 --> 03:52.860 We are using the Where clause where the IGP is greater than the average of this GP. 03:52.890 --> 03:56.190 So remember that this is just average of this GP. 03:56.310 --> 03:56.850 All right. 03:56.850 --> 04:00.150 So we're going to put a semicolon at the end of this. 04:00.210 --> 04:03.870 And we still have our an arrow here. 04:03.870 --> 04:06.600 Or I need to just go ahead and remove this comma here. 04:06.600 --> 04:10.830 And it says select all from students. 04:11.280 --> 04:16.380 The first name last name from students where the GP is this. 04:16.380 --> 04:21.210 So I can go ahead and remove this here and I'll go ahead and run this. 04:21.240 --> 04:22.170 Let's check it out. 04:24.240 --> 04:32.440 And these are the students whose GP are above the average because the average of the GP is 3.49, and 04:32.440 --> 04:40.150 you can see we have our 3.5, 4.7, 3.7, 4.80 and 3.8. 04:40.240 --> 04:40.750 All right. 04:40.750 --> 04:49.780 So these are the students whose uh GP are above the average of the GP, which we have in the previous 04:49.780 --> 04:54.490 table we created or with the previous query we just executed. 04:54.580 --> 04:55.180 All right. 04:55.180 --> 05:01.540 So from the average we are able to get the students who they are GPS are above average and that looks 05:01.540 --> 05:02.170 good. 05:02.680 --> 05:04.960 So put this down practice with it. 05:04.960 --> 05:09.130 And uh I hope from here you can understand what we're doing. 05:09.130 --> 05:13.780 And, uh, in the next lecture, we'll go ahead and tackle the example and see how it works. 05:13.780 --> 05:15.970 So let's take it one step at a time. 05:16.000 --> 05:16.570 Check out this. 05:16.570 --> 05:19.900 And if you have any questions on this or how to use the question and answer section. 05:19.900 --> 05:22.120 And I'm going to get back to you as soon as possible. 05:22.150 --> 05:23.020 Thank you so much. 05:23.020 --> 05:25.510 And I'm going to see you in the next video lecture.