1 00:01.640 --> 00:02.560 Hello everyone. 2 00:03.520 --> 00:04.560 Good morning all. 3 00:05.520 --> 00:10.000 So let's have sold the another SQL problem. 4 00:10.720 --> 00:13.120 So they have given us the salesperson table. 5 00:13.320 --> 00:20.680 And in the salesperson sales ID name salary commission rate and higher date column is given in sales 6 00:20.680 --> 00:22.840 ID is the primary key for this table. 7 00:23.920 --> 00:30.640 Each row of this table indicates the name and the ID of a salesperson alongside this salary, commission 8 00:30.640 --> 00:31.840 rate and higher date. 9 00:33.840 --> 00:40.120 Another table is company table is given and in the company table, company ID name and city column is 10 00:40.120 --> 00:40.600 given. 11 00:41.720 --> 00:44.160 Company ID is the primary key for this table. 12 00:45.360 --> 00:51.120 Each row of this table indicates the name and the ID of the company and the city in which the company 13 00:51.160 --> 00:54.960 is located in the orders table is given in. 14 00:54.960 --> 01:01.040 In the orders table, order, ID, order date, company ID, sales ID and amount column is given. 15 01:02.600 --> 01:09.980 Order ID is the primary key for this table Company ID is a foreign key to company ID from the company 16 01:10.020 --> 01:10.460 table. 17 01:11.460 --> 01:19.060 So if you see over here, company ID over here is matching with company ID from company stable and sales 18 01:19.060 --> 01:25.660 ID over here is matching with the sales ID from the salesperson table. 19 01:28.620 --> 01:30.060 An amount column is given. 20 01:30.060 --> 01:34.380 Each row of this table contains information about one order. 21 01:36.260 --> 01:42.860 This includes the ID of a company, the ID of a salesperson, and the date of the order in the amount 22 01:42.900 --> 01:43.420 paid. 23 01:45.100 --> 01:52.420 The right solution to find the name of all the salesperson who did not have any order related to the 24 01:52.420 --> 01:54.060 company with the name red. 25 01:55.460 --> 01:57.660 Return the result table in any order. 26 01:58.740 --> 02:01.620 The result format is in the following example. 27 02:02.740 --> 02:05.580 So here this is the example table. 28 02:05.580 --> 02:10.290 They have given a salesperson company table and Older stable. 29 02:11.250 --> 02:15.370 So I have created all these three tables in, inserted the data into the same. 30 02:16.290 --> 02:22.210 I will provide this create table in insert statement script into the description box of the video. 31 02:23.450 --> 02:26.970 Now let's see what is our requirement. 32 02:27.770 --> 02:35.130 We need to identify those salesperson who are not dealing with the company name red. 33 02:36.130 --> 02:40.970 So now let's see the first example sales ID one which is John. 34 02:42.130 --> 02:44.250 Now let's go to the orders table. 35 02:44.250 --> 02:49.290 In that orders table sales ID column is given in sales ID column one. 36 02:49.290 --> 02:51.050 We need to check over here. 37 02:52.170 --> 02:55.690 So sales ID one is dealing with company ID equal to one. 38 02:55.690 --> 02:57.650 And company ID one is red. 39 02:58.730 --> 03:02.410 So that we need to exclude this record of John. 40 03:02.650 --> 03:03.170 Right. 41 03:04.850 --> 03:09.210 Now let's go to the second sales ID which is Emmy. 42 03:10.410 --> 03:17.790 Now Emmy two is not at all Is there in the sales ID column of orders table? 43 03:18.910 --> 03:25.070 So we can include this record because he is not dealing with company name equal to red. 44 03:27.270 --> 03:30.870 Now let's move to the third record which is Mark. 45 03:32.110 --> 03:39.190 So again third record Mark is not given in the sales ID table over here. 46 03:40.030 --> 03:42.390 So which is not dealing with red. 47 03:42.790 --> 03:44.790 So we need to include Mark as well. 48 03:45.670 --> 03:48.430 Now let's go to the sales ID column. 49 03:49.110 --> 03:51.870 Sales ID four which is spam. 50 03:53.310 --> 03:54.350 So spam. 51 03:54.670 --> 04:01.830 If you see over here palm is dealing with sales ID equal to one right. 52 04:02.910 --> 04:07.190 So we need to exclude one means company name equal to red. 53 04:08.750 --> 04:12.990 So we need to exclude this record of palm. 54 04:13.750 --> 04:15.910 Now for fifth one. 55 04:16.710 --> 04:17.470 Let me check. 56 04:17.510 --> 04:21.410 Fifth one is dealing with the fork, which is green, right? 57 04:21.450 --> 04:22.130 Company name? 58 04:22.130 --> 04:22.610 Green. 59 04:23.210 --> 04:24.730 So we can include this record. 60 04:24.730 --> 04:34.730 So ultimately me, Mark and Alex, we need to include right now how can we write the solution for this 61 04:35.610 --> 04:36.090 one. 62 04:37.410 --> 04:39.610 So let me move to the solution. 63 04:39.610 --> 04:43.010 So now I have first block. 64 04:43.650 --> 04:44.850 What is my requirement. 65 04:44.850 --> 04:52.610 I need all the salesperson name from all from the salesperson table right. 66 04:53.170 --> 04:59.610 So I have done the left join over here because I need all the salesperson whether it is matching or 67 04:59.650 --> 05:02.610 not matching with the other two tables. 68 05:02.650 --> 05:03.130 Right. 69 05:04.050 --> 05:07.610 So that it will give me around six record. 70 05:07.610 --> 05:07.850 Right. 71 05:07.850 --> 05:15.890 Because I want all the records now from this six record, I want to identify those salesperson name 72 05:16.730 --> 05:19.850 which is dealing with the company name equal to red. 73 05:20.250 --> 05:20.770 Right. 74 05:21.810 --> 05:29.830 So So for that second thing, I have written this condition over here. 75 05:29.830 --> 05:31.390 I need to write the inner join. 76 05:31.390 --> 05:31.910 Right. 77 05:35.470 --> 05:38.670 So this will indicate the company name equal to red. 78 05:39.390 --> 05:41.230 So let me check this one. 79 05:43.950 --> 05:44.990 John and Pam. 80 05:45.750 --> 05:53.510 So if you see over here in the first example John we have excluded. 81 05:53.510 --> 05:53.910 Right. 82 05:54.470 --> 06:01.070 So John and Pam also we have excluded John and Pam are dealing with the company name equal to Red. 83 06:04.110 --> 06:07.630 Now what is our requirement from the all the salesperson name. 84 06:08.430 --> 06:15.190 I want to exclude those records who are dealing with the company name equal to red. 85 06:16.430 --> 06:21.230 So in this scenario we can use the except. 86 06:23.470 --> 06:26.940 Operator over here or accept clause over here. 87 06:27.980 --> 06:35.940 So now if I run this query looks me and Mark are the result set right. 88 06:36.980 --> 06:41.380 Now, if you see over here in my example. 89 06:42.820 --> 06:43.620 Alex. 90 06:44.380 --> 06:46.540 Sorry, Emmy, Mark and Alex. 91 06:47.460 --> 06:49.260 And that is what our result. 92 06:50.100 --> 06:50.500 Mark. 93 06:50.500 --> 06:50.940 Right. 94 06:51.940 --> 06:54.380 So this will be our final result set. 95 06:57.060 --> 07:00.620 So by this way we can write the solution. 96 07:04.900 --> 07:05.700 Of the query. 97 07:05.700 --> 07:06.100 Right. 98 07:08.060 --> 07:09.980 So thank you so much everyone. 99 07:10.740 --> 07:14.540 If you really like this video please do subscribe to my channel. 100 07:14.540 --> 07:14.940 And. 101 07:17.380 --> 07:23.860 Do like, comment and share as much as possible so that it will reach to the larger audience. 102 07:26.020 --> 07:27.180 Thank you everyone! 103 07:28.100 --> 07:28.940 Have a nice day.