1 00:00.640 --> 00:01.720 Hello everyone. 2 00:03.280 --> 00:04.320 Good morning all. 3 00:05.480 --> 00:11.360 So let's solve another SQL problem which is asked in MNC company. 4 00:12.120 --> 00:15.680 So they have given us the players table and championship table. 5 00:16.200 --> 00:17.200 In the players table. 6 00:17.240 --> 00:19.680 They have given us the player ID and player name. 7 00:20.240 --> 00:27.040 Player ID is the primary key for this table in player name and in the championship table here. 8 00:27.080 --> 00:32.320 Wimbledon, French Open, US Open and Australian Open column is given. 9 00:33.520 --> 00:39.320 So in the players table, player ID will be the same as in the championship table. 10 00:39.680 --> 00:44.400 All these four columns Wimbledon, French Open, US Open and Australian Open. 11 00:45.720 --> 00:47.600 Here is the primary key for this table. 12 00:47.600 --> 00:55.760 In each row of this table contains the ID of the players who won each tennis tournament of Grand Slam. 13 00:58.760 --> 01:02.960 Now, what is our requirement writer solution to report? 14 01:02.960 --> 01:07.160 The number of Grand Slam tournaments won by each player. 15 01:07.900 --> 01:11.740 Do not include the players who did not win any tournament. 16 01:12.820 --> 01:15.020 Return the result table in any order. 17 01:16.340 --> 01:21.580 So over here we need to identify the number of tournaments won by each player. 18 01:22.180 --> 01:29.860 So now if you see the example table then you will able to understand what is our requirement in the 19 01:29.860 --> 01:30.500 players table. 20 01:30.500 --> 01:33.700 Player ID 123 is given and player name is given. 21 01:34.180 --> 01:41.900 And in the championship table that same player ID will be given as different tournament names like Wimbledon, 22 01:41.900 --> 01:44.260 French Open, US Open and Australian Open. 23 01:44.700 --> 01:50.820 Now if you see in different three years has been given 2018, 19 and 20. 24 01:51.940 --> 02:00.820 So if you want to consider us open has been won by which player in 2019 then it is player ID two. 25 02:01.540 --> 02:05.700 So this same player ID will be considered as a player ID in the players table. 26 02:06.980 --> 02:12.140 Now our requirement is to identify the number of tournaments won by each player. 27 02:14.460 --> 02:22.170 Now if you see over here Wimbledon, French Open, US Open and Australian Open. 28 02:22.410 --> 02:23.370 So one is coming. 29 02:23.370 --> 02:31.730 How many times in all these four columns we need to consider that as a player ID one one. 30 02:31.770 --> 02:33.090 How many tournaments. 31 02:33.370 --> 02:36.490 So 1234567. 32 02:36.930 --> 02:38.530 So one is coming seven times. 33 02:38.530 --> 02:40.050 So that is our requirement. 34 02:40.250 --> 02:41.370 Now two is coming. 35 02:41.410 --> 02:47.370 How many times 123452 is coming five times and three is coming. 36 02:47.370 --> 02:47.970 Not at all. 37 02:47.970 --> 02:50.250 So we do not need to consider that one. 38 02:50.250 --> 02:50.770 Right. 39 02:52.170 --> 02:54.970 So this is the required expected output. 40 02:55.410 --> 02:59.890 Now I have created all the two tables and inserted that into the same. 41 03:00.450 --> 03:03.570 I will put the script into the description box of the video. 42 03:04.570 --> 03:06.810 Now how can we approach the solution. 43 03:07.450 --> 03:10.810 You can write your solution in the comment box. 44 03:10.810 --> 03:14.890 And then we will check whether your solution is correct or not. 45 03:15.410 --> 03:18.170 Let me proceed with my solution first. 46 03:19.210 --> 03:29.350 So I want all the Addition of all the columns from the championship table, right? 47 03:29.750 --> 03:33.150 So I will consider it as a union on all over here. 48 03:33.830 --> 03:41.230 So union on player ID means Union of Wimbledon, French Open, US Open and Australian Open from the 49 03:41.230 --> 03:42.190 Championship table. 50 03:42.230 --> 03:44.470 Now let me select the data from that. 51 03:50.070 --> 03:53.870 So this has been given in the only one column right. 52 03:54.270 --> 03:55.190 Now one is coming. 53 03:55.190 --> 03:59.670 How many times over here 123, 456 and seven times. 54 04:00.230 --> 04:02.230 And two is coming five times. 55 04:02.590 --> 04:04.910 So seven and five is our requirement. 56 04:05.830 --> 04:10.670 Now this one we need to consider as a player ID. 57 04:11.070 --> 04:14.070 Now what we can do we can join this. 58 04:14.110 --> 04:20.310 I have considered this entire inner query as one table by putting the alias as t. 59 04:21.030 --> 04:29.110 Now t dot player id is equal to p dot player id we need to consider and then we need to write select 60 04:29.110 --> 04:36.060 player ID player name, player player name and we need to do Count of Teapot player ID to count the 61 04:36.060 --> 04:38.580 number of tournaments won by each player. 62 04:39.540 --> 04:44.900 And because of we have used the count aggregate function, we need to do group by player ID and player 63 04:44.940 --> 04:45.420 name. 64 04:47.020 --> 04:47.660 Right. 65 04:48.140 --> 04:50.300 Now let me execute this query. 66 04:54.420 --> 04:56.940 So player ID player name and grand same. 67 04:59.300 --> 05:00.620 One by each player. 68 05:00.620 --> 05:02.820 So number one player ID one. 69 05:03.340 --> 05:06.300 Nadal has won the seven Grand Slam. 70 05:06.820 --> 05:10.460 And player 82 Federer has won the five Grand Slam. 71 05:10.780 --> 05:13.580 So this is what our requirement is right? 72 05:15.540 --> 05:16.380 Now let me. 73 05:19.380 --> 05:20.380 Complete this video. 74 05:20.380 --> 05:25.500 As of now you can write your solution and put it in the comment box. 75 05:26.580 --> 05:30.060 I will do reply whether your solution is correct or not. 76 05:32.260 --> 05:33.700 Thank you so much everyone. 77 05:33.700 --> 05:35.660 We will meet in the next video. 78 05:35.860 --> 05:36.620 Thank you.