1 00:01.720 --> 00:02.680 Hello everyone. 2 00:03.600 --> 00:05.760 Welcome back to my channel. 3 00:07.080 --> 00:13.520 So let's continuation of our find in MNC companies query. 4 00:15.200 --> 00:18.160 So they have given us the players table. 5 00:19.720 --> 00:24.400 And in the players table player ID and player name column is given. 6 00:25.320 --> 00:27.840 Player ID is the primary key for this table. 7 00:28.960 --> 00:31.280 And another table is Championships table. 8 00:31.960 --> 00:39.720 In that year Wimbledon then French Open, US Open and Australian Open column is given. 9 00:40.280 --> 00:44.480 So one player is playing multiple tournaments over here. 10 00:44.480 --> 00:44.920 Right. 11 00:45.760 --> 00:54.800 And just play this player ID will be the if you see Wimbledon, French Open, US open, Australian Open 12 00:55.160 --> 00:58.240 means those player has won. 13 00:58.240 --> 01:01.480 Which of the tournaments or those player has played? 14 01:01.480 --> 01:05.620 Which of the two tournaments they have given us that thing. 15 01:06.140 --> 01:08.780 Now, what is our requirement? 16 01:09.820 --> 01:15.140 Write a solution to record the number of Grand Slam tournaments won by each player. 17 01:17.700 --> 01:19.140 Do not include players. 18 01:21.580 --> 01:26.020 Do not include players who did not win any tournament. 19 01:26.020 --> 01:26.500 Right. 20 01:27.180 --> 01:32.580 So over here in place table player ID will be there. 21 01:32.580 --> 01:32.860 Right. 22 01:32.860 --> 01:39.660 And in the Championship table this Wimbledon, French Open, US Open and Australian Open in all the 23 01:39.660 --> 01:43.060 four columns, player ID will be there, right. 24 01:43.940 --> 01:50.460 So let me show you the data writer solution to report the number of Grand Slams won by each player. 25 01:51.100 --> 01:54.340 Do not include the players who did not win any tournament. 26 01:54.900 --> 01:56.540 That is our requirement, right? 27 01:56.700 --> 01:58.420 Return the result table in any order. 28 01:58.420 --> 02:01.900 The result format is in the following example. 29 02:02.700 --> 02:05.190 So now let us see the data of the players table. 30 02:06.430 --> 02:07.830 So in the players table. 31 02:10.430 --> 02:12.070 Player ID 123 is given in. 32 02:12.070 --> 02:13.270 Player name is Nadal. 33 02:13.670 --> 02:15.870 Federer and Novak is given. 34 02:16.270 --> 02:18.070 And in the championship table. 35 02:20.070 --> 02:22.510 20 1819 and 20 right. 36 02:22.830 --> 02:32.790 So Wimbledon has won in 2008 by player ID won then Wimbledon won by 2019 is also player ID won and Wimbledon 37 02:33.070 --> 02:36.230 is won in 2020 by playwright D2. 38 02:36.750 --> 02:39.230 So here what is our requirement? 39 02:40.030 --> 02:42.270 Let me read the question once again. 40 02:42.310 --> 02:47.430 Write a solution to report the number of tournaments won by each player. 41 02:47.990 --> 02:52.110 So here we need to use group by column right. 42 02:53.190 --> 02:58.310 So player ID has three has doesn't want any of the tournament. 43 02:58.310 --> 03:05.930 If you see over here in the table championships that player ID is three is not available right in any 44 03:05.930 --> 03:07.090 of the four columns. 45 03:07.530 --> 03:09.010 So player ID one has one. 46 03:09.050 --> 03:09.970 How many tournaments? 47 03:10.010 --> 03:10.450 One. 48 03:10.490 --> 03:11.130 Two three. 49 03:11.130 --> 03:11.490 Four. 50 03:11.890 --> 03:12.250 Five. 51 03:12.290 --> 03:13.090 Six seven. 52 03:13.410 --> 03:17.730 So that what I have written over here player ID one is seven tournament. 53 03:18.170 --> 03:23.290 We need name of the player ID one, which is Nadal and player two. 54 03:23.410 --> 03:23.730 One. 55 03:23.730 --> 03:24.690 How many tournaments? 56 03:24.690 --> 03:26.890 12345. 57 03:27.490 --> 03:30.010 So player ID two has won five tournaments. 58 03:31.010 --> 03:33.250 Which is Federer right. 59 03:33.690 --> 03:35.330 So now how can we? 60 03:36.250 --> 03:39.850 I have created both the tables and inserted the data into the same. 61 03:40.770 --> 03:44.210 I will put the scripts into the description box of the video. 62 03:45.170 --> 03:47.650 Now how can we approach this solution? 63 03:49.650 --> 03:51.890 I have written over here my solution. 64 03:51.890 --> 03:55.010 You can write your solution and put it in the comment box. 65 03:56.490 --> 04:02.050 Then we will check that whether your solution is correct or not. 66 04:03.090 --> 04:06.870 Now let's see the my solution over here. 67 04:07.510 --> 04:17.630 I am taking the players table in India as well as I am taking the union of all the four columns I want 68 04:17.950 --> 04:21.870 from the championship table, then only I can calculate the count right? 69 04:22.590 --> 04:27.470 So Union select Wimbledon as player ID from championships. 70 04:27.870 --> 04:30.870 Now select French Open, US Open and Australian Open. 71 04:31.390 --> 04:34.510 Then I need to do union on all of all the four. 72 04:36.990 --> 04:38.230 Select queries right. 73 04:38.270 --> 04:39.230 Now let's see. 74 04:42.270 --> 04:51.630 Okay so so here player ID one is coming seven times and player ID two is coming five times right. 75 04:52.150 --> 04:56.350 So seven and five equal to 12 rows are coming over here. 76 04:56.630 --> 04:57.990 Now what is our requirement. 77 04:57.990 --> 04:58.750 We want. 78 05:00.190 --> 05:04.990 We want player ID 1 in 1 column then player ID one name in another column. 79 05:04.990 --> 05:14.450 And then seven One is in another column, which is one by number of tournaments won by player ID one 80 05:14.450 --> 05:22.570 and same as two in another row, and then name of the player ID two, and then number of tournaments 81 05:22.570 --> 05:25.250 won by player ID two, which is five over here, right. 82 05:25.250 --> 05:29.210 123412345. 83 05:29.250 --> 05:29.650 Right. 84 05:30.770 --> 05:33.290 So now how can we approach this solution. 85 05:34.730 --> 05:36.770 So I have taken the union all. 86 05:37.130 --> 05:37.850 Now I am. 87 05:39.890 --> 05:43.690 Considering this number of tournament one as a player ID. 88 05:44.330 --> 05:47.210 So this entire entire. 89 05:50.490 --> 05:51.770 Bunch I have taken. 90 05:52.290 --> 05:56.330 I am considering it as a one table over here, right s t. 91 05:57.130 --> 06:05.770 And now I am joining this tree with the main table, original table with t dot player id and p dot player 92 06:05.770 --> 06:07.450 ID because that is the same right? 93 06:07.450 --> 06:11.140 Only Now just one thing is pending. 94 06:11.140 --> 06:13.300 We need to do group by player and player. 95 06:13.300 --> 06:16.620 Name and count of t dot player ID. 96 06:19.140 --> 06:24.020 So it will give me the player player ID wise combination. 97 06:26.100 --> 06:30.020 And that combination will be number of tournaments won by each player. 98 06:30.860 --> 06:32.700 Now let me execute this query. 99 06:33.340 --> 06:41.060 So if you see over here player ID one which is Nadal grand won by him is seven. 100 06:41.540 --> 06:47.940 And then player ID two which is Federer and Grand Won by Federer is five. 101 06:48.740 --> 06:50.820 So this will be our solution. 102 06:52.340 --> 06:55.380 You can write your solution and put it in the comment box. 103 06:57.140 --> 07:03.780 And if you really like this video, please do like, comment and subscribe and share as much as possible. 104 07:05.020 --> 07:07.900 We will come again with the next question as soon as possible. 105 07:07.900 --> 07:08.620 Thank you.