1 00:00.360 --> 00:01.320 Hello everyone. 2 00:03.440 --> 00:05.560 Let's see the another SQL problem. 3 00:07.160 --> 00:07.720 Delete. 4 00:10.080 --> 00:12.200 Orders of inactive customers. 5 00:12.640 --> 00:14.720 So below is the problem statement. 6 00:16.280 --> 00:25.080 Given a customer table with an in is active status and an order table, write a single query to delete 7 00:25.080 --> 00:29.520 all orders placed by customers who are marked as inactive. 8 00:30.120 --> 00:32.440 Okay, so we are having two different tables. 9 00:32.440 --> 00:39.720 Are there customer status table is there and customer orders table is there and below is the data. 10 00:39.720 --> 00:45.360 So let me just ask the ChatGPT about the same. 11 00:47.600 --> 00:49.760 So let me just delete this comment. 12 00:54.680 --> 00:56.240 And then let me ask. 13 01:00.360 --> 01:01.740 So let's see. 14 01:03.820 --> 01:07.860 I am having two different. 15 01:10.620 --> 01:15.620 Tables and data as per below. 16 01:19.220 --> 01:22.540 So please say table structures and sample data. 17 01:23.020 --> 01:24.020 Okay fine. 18 01:24.500 --> 01:30.140 So I am sharing my table structure and sample data with the ChatGPT. 19 01:30.260 --> 01:31.380 Now let's see. 20 01:35.740 --> 01:43.700 Given a customer table with and is active status and in order table, write a single query. 21 01:43.940 --> 01:45.900 So this is my problem statement right? 22 01:45.940 --> 01:49.540 Let me share the problem statement with ChatGPT. 23 01:51.900 --> 01:57.940 As well okay so it has given me some solution right. 24 01:57.940 --> 02:04.160 So let me copy the solution over here and let's see what it is saying. 25 02:05.560 --> 02:11.360 So see over here I have done with the inner join and they have used the sub query over here. 26 02:11.360 --> 02:11.840 Right. 27 02:13.040 --> 02:20.800 So we can say solution provided by the ChatGPT. 28 02:20.840 --> 02:24.240 Right now I have just selecting the data over here. 29 02:24.240 --> 02:27.800 Let's see if I want to select data from this one. 30 02:31.800 --> 02:33.480 Then how many record it will give. 31 02:33.480 --> 02:35.640 It will it should give three records right. 32 02:36.440 --> 02:40.040 It should give three records I don't want to delete as of now. 33 02:42.240 --> 02:45.080 So see this will give three record perfect. 34 02:47.880 --> 02:53.560 And because of we are selecting the data from the customer orders table. 35 02:53.560 --> 02:57.280 So let me write it over here co-star. 36 02:57.920 --> 03:00.920 So now both of this query will give the same result right. 37 03:02.060 --> 03:03.540 Perfect both of these query. 38 03:03.580 --> 03:06.660 Query has given the same result. 39 03:07.260 --> 03:08.060 Perfect. 40 03:08.660 --> 03:09.940 So what it is doing? 41 03:11.700 --> 03:19.700 It is picking the customer ID from the customer orders table and then those customer ID it will select 42 03:19.700 --> 03:26.620 from the customer status table where is active equal to zero only right. 43 03:28.100 --> 03:32.620 So by using the sub query also we can do this by using the join. 44 03:32.620 --> 03:34.300 Also we can do this right. 45 03:34.620 --> 03:37.020 Now let's see what else it is saying. 46 03:39.420 --> 03:39.980 Okay. 47 03:40.020 --> 03:41.700 So alternative using join. 48 03:41.900 --> 03:44.060 So this is what I am talking about. 49 03:49.060 --> 03:53.500 So this one has been done using the join right. 50 03:55.180 --> 03:56.740 Which I have already done. 51 03:58.820 --> 03:59.660 Perfect. 52 03:59.980 --> 04:00.820 Now what else. 53 04:00.860 --> 04:01.340 Okay. 54 04:03.480 --> 04:09.040 Now it is asking, would you like me to show the before and after data result? 55 04:09.040 --> 04:10.360 So let me mention. 56 04:10.360 --> 04:10.800 Yes. 57 04:13.640 --> 04:17.680 Let me mention yes over here and let's see what query it is giving. 58 04:19.480 --> 04:20.240 Okay. 59 04:21.600 --> 04:25.520 Query to delete order placed by inactive customers. 60 04:28.920 --> 04:29.640 Okay. 61 04:32.000 --> 04:35.320 Is it showing the before and after I think uh. 62 04:38.680 --> 04:39.120 No. 63 04:39.120 --> 04:39.320 Right. 64 04:39.320 --> 04:43.280 It is not showing before and after as per our requirement. 65 04:46.520 --> 04:52.440 It is showing just by query, but by tabular data, but not by query. 66 04:52.440 --> 04:52.920 Right. 67 04:54.000 --> 04:55.480 So that will be fine. 68 04:58.200 --> 05:00.600 We will see the next question into the next lecture. 69 05:01.160 --> 05:01.840 Thank you.