1 00:01.680 --> 00:02.720 Hello everyone. 2 00:03.520 --> 00:04.600 Good morning all. 3 00:05.880 --> 00:07.640 Welcome back to my channel. 4 00:09.200 --> 00:12.640 So let's learn another SQL query today. 5 00:14.320 --> 00:21.280 They have given us the three tables customers table, orders table and seller table. 6 00:22.360 --> 00:27.000 So we have to write query based on all of these three table. 7 00:27.600 --> 00:32.600 So in the customer table they have given us customer ID and customer name. 8 00:32.640 --> 00:35.280 Customer ID is the primary key for this table. 9 00:36.000 --> 00:43.800 In each row of this table contains the information of each customer in the web store, whereas in the 10 00:43.800 --> 00:51.760 orders table they have given us order ID, sales date, order cost, customer ID and seller ID. 11 00:52.160 --> 01:02.600 So over here customer ID is matching with the customers table customer ID so order ID is the primary 12 01:02.600 --> 01:08.440 key for this table in each row of this table contains all orders made in the web store. 13 01:10.400 --> 01:18.040 Now, sales date is the date when the transaction was made between the customer and the seller, and 14 01:18.040 --> 01:19.920 now the seller table. 15 01:19.920 --> 01:23.600 In the seller's table, seller ID and seller name is given. 16 01:23.880 --> 01:26.920 Seller ID is the primary key for this table. 17 01:27.840 --> 01:31.760 In each row of this table contains the information of each seller. 18 01:33.160 --> 01:40.040 Now Brighton SQL query to report the name of all the sellers who did not make any sales in. 19 01:41.880 --> 01:46.240 Who did not make any sales in 2020. 20 01:46.520 --> 01:50.120 Return the result table ordered by seller in ascending order. 21 01:50.960 --> 01:54.320 The query result format is in the following example. 22 01:56.440 --> 02:03.600 So here they have given us the data and I have created already created the table and insert the data 23 02:03.600 --> 02:04.680 into the same. 24 02:05.480 --> 02:08.200 Now if you see the these tables. 25 02:10.240 --> 02:16.640 In the orders table, they have given us the sales date and as well as seller ID and customer ID. 26 02:17.160 --> 02:24.840 Now we have to identify those seller who did not make any sales in 2020. 27 02:25.760 --> 02:31.760 So if you see for seller ID one it has done sales in 2020 right. 28 02:33.240 --> 02:40.240 Then seller ID two has also done the sales in 2020 over here as well as over here two times. 29 02:41.120 --> 02:47.880 But if you see for the seller ID three, seller ID three does not make any sales in 2020. 30 02:47.920 --> 02:51.360 It has made sales in 2019. 31 02:51.960 --> 02:52.440 Right. 32 02:53.600 --> 02:54.200 So now. 33 02:57.240 --> 03:01.200 So Frank, now we need to go to the seller ID. 34 03:01.200 --> 03:06.200 And then with the seller ID we need to look up into the seller table. 35 03:07.040 --> 03:08.550 We have to identify the three. 36 03:08.550 --> 03:09.750 And then Frank. 37 03:10.150 --> 03:12.910 So Frank will be our answer, right? 38 03:15.150 --> 03:17.510 Now, how can we get into that? 39 03:17.910 --> 03:26.310 So basically firstly what we need to do, we need to do identify those. 40 03:26.310 --> 03:30.950 Zeller who has done sales in 2020. 41 03:31.830 --> 03:36.670 So we can identify it by using the seller and orders table join. 42 03:37.750 --> 03:44.870 So now if you see Daniel and Elizabeth has done the sales in the 2020 right, that you can see in the 43 03:44.870 --> 03:46.270 above table as well. 44 03:47.910 --> 03:55.310 So seller ID one one means Daniel and seller ID two two means Elizabeth. 45 03:55.310 --> 03:59.310 So Daniel and Elizabeth has done the sales in 2020. 46 03:59.350 --> 04:00.790 Now what we need to look up. 47 04:01.950 --> 04:07.110 We need to look up those sellers who has not made the sales in 2020. 48 04:07.790 --> 04:13.350 So we just need to Do the left join over here? 49 04:14.190 --> 04:14.790 Because. 50 04:18.630 --> 04:20.070 We need all the data, right? 51 04:20.070 --> 04:23.310 We need all the data from the seller table. 52 04:24.430 --> 04:33.230 Now, what we need to do, we just need to write the accept condition from the first block to the 2020 53 04:33.270 --> 04:33.790 sales. 54 04:34.550 --> 04:41.150 So now accept will retrieve only those data which is found in the first Select query, which is not 55 04:41.150 --> 04:43.510 available in the second select query. 56 04:44.230 --> 04:46.550 So simply the answer will be the Frank. 57 04:47.150 --> 04:49.470 Frank is not done. 58 04:49.470 --> 04:51.590 The sales in the 2020. 59 04:53.270 --> 05:01.430 So I will put this create table script in insert statement script into the video description. 60 05:03.030 --> 05:10.670 Please kindly subscribe to my channel and do like, share and comment as much as possible. 61 05:11.030 --> 05:11.750 Thank you.