1 00:01.490 --> 00:01.820 Okay. 2 00:01.820 --> 00:05.600 So today we will see the, uh, start with the delete command. 3 00:05.990 --> 00:09.500 We have already seen the update command yesterday. 4 00:10.220 --> 00:14.060 So delete command is used to delete the records in the table. 5 00:14.060 --> 00:14.660 Right. 6 00:14.690 --> 00:19.250 Then we can delete specific records or all the rows. 7 00:19.250 --> 00:21.560 It depends on the weather. 8 00:21.560 --> 00:24.170 We have mentioned the where condition or not. 9 00:24.200 --> 00:30.140 So if we specify the where condition then we will able to delete the specific records. 10 00:30.680 --> 00:34.130 Then we can delete entire records or rows. 11 00:34.910 --> 00:39.140 If we do not specify the where condition then it will delete the entire records. 12 00:39.890 --> 00:42.620 Then suppose I delete all the records of the table. 13 00:42.620 --> 00:45.830 In that case, only data will be deleted. 14 00:45.830 --> 00:49.010 The structure will not be deleted. 15 00:49.040 --> 00:52.610 I will explain you this later on as well. 16 00:53.510 --> 00:55.310 So what about the table structure? 17 00:55.310 --> 00:55.910 Is there? 18 00:55.910 --> 00:57.500 It will remain the same. 19 00:57.500 --> 00:59.720 All the columns will remain the same. 20 00:59.870 --> 01:02.150 Data types will be remain the same. 21 01:02.150 --> 01:02.690 Cons. 22 01:03.230 --> 01:05.300 Everything be will be remain the same. 23 01:05.300 --> 01:06.890 Only the data will be deleted. 24 01:06.890 --> 01:09.200 In case of delete statement. 25 01:09.890 --> 01:10.580 Then. 26 01:12.170 --> 01:14.420 Then delete will record the delete. 27 01:14.420 --> 01:19.070 Delete will delete the record row by row. 28 01:19.070 --> 01:20.990 So once we delete. 29 01:20.990 --> 01:27.440 Suppose if we write this delete from employee then it will delete the first row, then second row, 30 01:27.470 --> 01:28.610 then third row. 31 01:28.610 --> 01:30.350 Like that it will delete. 32 01:30.500 --> 01:36.230 So it will log that information into the transaction log. 33 01:41.720 --> 01:43.790 Uh, what is the transaction log? 34 01:43.970 --> 01:52.460 Yeah, it is something to maintain the delete, uh, deleting information. 35 01:52.520 --> 01:54.620 We cannot see it anywhere. 36 01:54.620 --> 01:58.880 But this is a architecture. 37 01:58.880 --> 02:01.580 You can, uh, say that of SQL server. 38 02:01.580 --> 02:07.640 So it will store every row which is deleted into the transaction load log. 39 02:07.640 --> 02:11.810 We cannot see it manually by writing the query. 40 02:15.500 --> 02:18.680 Then it does not reset identity delete. 41 02:19.130 --> 02:23.810 Okay, can we able to re uh retry back when once it is deleted. 42 02:23.810 --> 02:24.110 Yeah. 43 02:24.110 --> 02:26.690 If we, if we have done the begin transaction. 44 02:26.690 --> 02:31.730 And if we do then only we can retrieve with the rollback command. 45 02:31.730 --> 02:37.700 Otherwise we cannot rollback that we have seen in the very first lecture. 46 02:41.930 --> 02:45.140 Then it does not reset identity that one. 47 02:45.140 --> 02:47.930 Also, I will show you later on today only. 48 02:48.980 --> 02:53.510 So let's move ahead with the example. 49 03:03.740 --> 03:09.950 All the records or row of the EMP table will be deleted, but the structure of the EMP table will remain. 50 03:10.100 --> 03:15.140 So now if I let me show you this table select star from EMP. 51 03:15.140 --> 03:15.160 employee. 52 03:22.900 --> 03:25.720 So this is my employee table right now. 53 03:25.720 --> 03:29.230 If I, uh I need to copy that table. 54 03:52.210 --> 03:54.970 So now suppose I want to delete from this table. 55 03:54.970 --> 03:58.480 Don't want to delete from the original table. 56 03:58.480 --> 04:01.750 So if I it will be the same data right. 57 04:01.750 --> 04:03.100 In both of the table. 58 04:03.100 --> 04:04.600 It will be the same data. 59 04:08.590 --> 04:11.080 Now, if I execute this query. 60 04:11.440 --> 04:19.030 If I execute this query delete from EMP 28 date, then 18 rows affected and you cannot. 61 04:20.380 --> 04:23.830 Data will be not available, but the structure is available right? 62 04:23.860 --> 04:27.010 The structure is there in this delete table. 63 04:27.130 --> 04:29.140 But data will be not be there. 64 04:29.230 --> 04:32.080 So it has logged. 65 04:32.080 --> 04:34.780 It has deleted one by one row. 66 04:34.810 --> 04:36.100 You cannot see like that. 67 04:36.100 --> 04:38.620 One row is deleted, two second row is deleted. 68 04:38.620 --> 04:39.940 You cannot see that. 69 04:39.940 --> 04:46.630 But it is a architecture of SQL server that it has deleted one by one row and lock the entry into the 70 04:46.630 --> 04:53.020 transaction log so that you have to understand that thing. 71 04:53.020 --> 04:59.650 And then if we want, if we are mentioning one record, if we want to delete only one record or few 72 04:59.650 --> 05:00.460 of the record, right. 73 05:00.460 --> 05:03.550 So delete from sorry select star from EMP. 74 05:04.060 --> 05:05.830 Now let's see the table. 75 05:06.010 --> 05:15.010 So suppose if I want to delete this Ellen record then what I have to mention delete from. 76 05:18.010 --> 05:25.870 Where e name is equal to l n, but in this case there is a one problem. 77 05:25.870 --> 05:28.090 Suppose if we. 78 05:28.090 --> 05:30.310 If there are multiple islands are then in. 79 05:30.310 --> 05:35.260 In this our particular table, then all the l n will be deleted. 80 05:35.260 --> 05:37.480 All the l n records will be deleted. 81 05:37.480 --> 05:45.220 So ideally what we have to do, we have to mention where in place of this one we do not. 82 05:45.220 --> 05:47.530 We have to mention where MP number. 83 05:51.220 --> 05:53.530 Equal to seven for nine nine. 84 05:57.640 --> 05:59.710 Equal to seven for nine nine. 85 05:59.710 --> 06:01.840 So it will effect only one row. 86 06:01.840 --> 06:05.020 So here we can mention where condition as well. 87 06:05.020 --> 06:05.710 Right. 88 06:05.710 --> 06:08.260 This is one more thing about delete statement. 89 06:08.260 --> 06:16.150 If we specify the where condition then whichever records are satisfying this particular condition that 90 06:16.150 --> 06:17.200 will be deleted only. 91 06:17.200 --> 06:23.260 But if I mentioned this like this then all the records will be deleted. 92 06:27.730 --> 06:30.370 So that is what we have right over here. 93 06:30.370 --> 06:36.790 I have instead of using the name better using some of the thing which uniquely identifies the alien. 94 06:37.210 --> 06:44.740 So over here, that column which is uniquely identify that particular record is MP id, right. 95 06:44.740 --> 06:48.190 Because MP id is unique in all the rows. 96 06:50.710 --> 06:52.840 MP id is unique in all the rows. 97 06:53.800 --> 06:55.180 So let's move. 98 06:59.740 --> 07:04.120 Now suppose if we want to delete multiple rows right right. 99 07:04.120 --> 07:15.490 Suppose if we want to delete multiple rows like those employees who has been joining 2021. 100 07:18.340 --> 07:19.300 Who has been joining? 101 07:19.330 --> 07:21.130 2021 so let me check. 102 07:21.130 --> 07:22.390 Is there any employees? 103 07:22.390 --> 07:23.590 Are there currently? 104 07:23.590 --> 07:24.970 It is not there. 105 07:25.960 --> 07:27.700 Let me check again. 106 07:29.350 --> 07:31.330 Okay I think this table is deleted right. 107 07:31.330 --> 07:32.710 So let me. 108 07:38.680 --> 07:40.330 Create some other table. 109 07:47.770 --> 07:49.240 Now let me see. 110 07:49.870 --> 07:51.550 So there are three records, are there. 111 07:51.550 --> 07:52.360 Right all. 112 07:52.360 --> 08:01.180 Now if I my requirement is I want to delete all the data related to employee who's higher rate or who 113 08:01.180 --> 08:03.880 has been joined the company into 2021. 114 08:03.880 --> 08:10.180 So in this particular case I can mention delete what I have to do. 115 08:10.180 --> 08:14.020 I just have to mention in place of select star I need to mention. 116 08:16.180 --> 08:21.100 Delete from EMP 29 where higher date like 2021. 117 08:21.100 --> 08:23.080 So it will affect three rows right? 118 08:23.530 --> 08:25.060 Three rows will be affected. 119 08:25.060 --> 08:30.490 Now I can again check so it will not give me any of the record right. 120 08:35.920 --> 08:38.140 Now data definition language. 121 08:40.540 --> 08:43.090 Can you say how rollback will work here. 122 08:45.130 --> 08:45.940 Sorry. 123 08:47.230 --> 08:49.630 Rollback how rollback will work here. 124 08:52.090 --> 08:54.970 Like suppose accidentally we deleted the data. 125 08:55.000 --> 08:57.340 Then how we can get back? 126 08:57.880 --> 08:58.390 Yeah. 127 08:58.390 --> 09:05.590 For that you have to mention that begin Tran and then you can go through the recording. 128 09:05.860 --> 09:08.710 I have seen three, four times. 129 09:10.360 --> 09:13.360 Yeah, but you have to write begin. 130 09:13.360 --> 09:13.660 Tran. 131 09:13.660 --> 09:17.620 Then this statement should be inside this particular statement. 132 09:17.800 --> 09:18.670 Then what? 133 09:18.880 --> 09:22.290 We have to execute this query and then you you have to rollback. 134 09:26.640 --> 09:29.010 Without digging there is no option. 135 09:29.010 --> 09:29.370 No, no. 136 09:29.430 --> 09:31.530 Without we begin there is no option. 137 09:32.400 --> 09:33.900 We cannot do it. 138 09:37.230 --> 09:37.890 Okay. 139 09:37.890 --> 09:39.090 Now let's do the. 140 09:42.510 --> 09:45.360 Let's move to the data definition language. 141 09:45.360 --> 09:48.330 So we have seen insert, update and delete right. 142 09:48.330 --> 09:53.040 Basic basic idea we have now regarding to insert, update and delete. 143 09:53.310 --> 09:57.180 Now let's start with the data definition language statement. 144 09:58.140 --> 10:04.140 So they deal with the structure of the data like we have already seen this create statement right. 145 10:04.140 --> 10:06.270 We have already created the table. 146 10:06.600 --> 10:12.150 So create table is can be considered as a data definition language statement. 147 10:13.830 --> 10:21.690 Now alter, delete and drop are alter, delete and truncate are also the part of the data definition 148 10:21.690 --> 10:22.920 language statement. 149 10:24.960 --> 10:29.280 Alter, drop and truncate are also the part of the data definition language statement. 150 10:29.310 --> 10:31.650 Now let's start with the alter command. 151 10:31.650 --> 10:35.950 So in which of the condition we can write the alter command. 152 10:36.460 --> 10:44.440 So this command is used to modify the table structure using the alter command we can we can add column 153 10:44.440 --> 10:45.370 in the table. 154 10:46.270 --> 10:48.790 We can add column in the table. 155 10:48.790 --> 10:51.040 We can drop the columns as well. 156 10:51.880 --> 10:56.820 Then we can modify a column like we can change the data type of the column. 157 10:56.820 --> 10:59.130 In modify we can mention. 158 11:06.030 --> 11:12.270 Change the data type of the column and change the size of the. 159 11:16.860 --> 11:19.260 Change the size of the data type of the column. 160 11:19.260 --> 11:24.090 These This two thing we can do in the modify a column, then add a. 161 11:24.090 --> 11:25.710 We can add the constraint as well. 162 11:25.710 --> 11:27.840 We can delete the constraint as well. 163 11:28.410 --> 11:32.640 Now let's start with one by one adding a column. 164 11:33.780 --> 11:36.630 So what we have to we we can do. 165 11:37.890 --> 11:39.930 Let me start with the example. 166 11:39.930 --> 11:43.110 We can add a new column in the table. 167 11:43.110 --> 11:45.060 So let's see. 168 11:45.060 --> 11:49.530 Check this table select star from EMP. 169 11:50.370 --> 11:58.410 So as of now we do not have a phone number in this particular table. 170 11:58.410 --> 11:58.980 Right. 171 11:58.980 --> 12:03.840 So it is a requirement to add the phone number or date of birth. 172 12:03.840 --> 12:05.700 Let me add the phone number. 173 12:14.490 --> 12:19.530 Phone number and it will be big integer. 174 12:19.830 --> 12:27.780 Now this state this statement you have to write alter table emp add phone number, big integer. 175 12:29.310 --> 12:31.470 This is a syntax of adding a column. 176 12:31.470 --> 12:34.770 Alter table table name eight column data type. 177 12:37.770 --> 12:40.170 So command executed successfully. 178 12:40.320 --> 12:48.570 Now if you run this query phone number will be there but the data will not be there. 179 12:48.750 --> 12:53.100 All the data will be null because we have just added the column. 180 12:53.550 --> 12:57.600 We do not have any enter data into this particular column, right? 181 12:57.930 --> 13:00.360 So that data will be null. 182 13:05.310 --> 13:09.540 Then again, this is the same example of adding the column. 183 13:09.720 --> 13:15.510 Now after adding the column, the column is filled with the null by default. 184 13:15.510 --> 13:17.460 So that we have seen over here right. 185 13:17.460 --> 13:21.450 The column will be filled the null by default. 186 13:22.410 --> 13:28.350 So now we have to insert the data in new column using the update command. 187 13:30.150 --> 13:33.930 We can insert the data into the new column using the update command. 188 13:33.930 --> 13:41.130 So we can write the simple update statement to, uh, update the phone number of the employee. 189 13:41.670 --> 13:44.430 So we can write the multiple. 190 13:46.300 --> 13:49.720 multiple statement. 191 13:49.720 --> 13:52.120 Or we can write the case statement as well. 192 13:52.150 --> 13:55.930 So as of now let me write like this. 193 13:58.000 --> 14:03.280 Where employee number equal to 751 1 or 7 389. 194 14:08.200 --> 14:12.690 Like this we can add update phone number for multiple employees as well. 195 14:12.930 --> 14:13.440 Right. 196 14:13.440 --> 14:14.760 This is the same thing. 197 14:15.810 --> 14:17.820 Or you can mention the case statement. 198 14:17.820 --> 14:21.510 It is depends on your. 199 14:23.670 --> 14:24.780 Requirement. 200 14:26.220 --> 14:31.050 There will be another phone number and this will be another phone number. 201 14:31.650 --> 14:32.850 Now let me execute. 202 14:32.850 --> 14:35.760 We can execute all these three statements at one go. 203 14:37.590 --> 14:38.910 Let me try. 204 14:40.620 --> 14:42.270 Three rows are affected right. 205 14:42.270 --> 14:48.240 So now we can see in this particular table there will be phone number will be available in. 206 14:50.760 --> 14:51.960 Three of the rows. 207 14:51.960 --> 14:53.430 It is available right. 208 15:03.510 --> 15:05.220 Now dropping a column. 209 15:07.770 --> 15:11.880 Now dropping a column so we can drop the column as well. 210 15:12.360 --> 15:16.290 Suppose there is a requirement to drop unnecessary. 211 15:18.510 --> 15:21.000 Unnecessary column from the table. 212 15:21.320 --> 15:23.660 Then we can drop the column as well. 213 15:24.830 --> 15:26.060 And one more thing. 214 15:26.060 --> 15:30.530 We can drop the multiple columns as well as well as single column as well. 215 15:30.530 --> 15:33.830 So let me add one more column in this table. 216 15:34.820 --> 15:39.710 I am just to show you how we can drop multiple columns. 217 15:40.790 --> 15:42.170 So. 218 15:46.670 --> 15:47.510 Which column? 219 15:47.510 --> 15:50.570 We have added this one. 220 15:50.570 --> 15:51.170 And phone number. 221 15:51.170 --> 15:51.740 Right. 222 15:53.840 --> 15:56.270 So this one and. 223 16:00.230 --> 16:01.460 Phone number. 224 16:03.740 --> 16:06.020 So now let me try. 225 16:06.020 --> 16:11.150 First of all I have added that column that let me. 226 16:18.320 --> 16:21.560 Two columns I want to drop right from this particular table. 227 16:21.560 --> 16:23.750 These two columns are unnecessary added. 228 16:23.870 --> 16:26.390 So I want to drop this two column. 229 16:26.390 --> 16:27.920 So what I can do. 230 16:30.740 --> 16:33.050 Alter table EMP drop column. 231 16:33.650 --> 16:35.480 Column number one and comma. 232 16:35.480 --> 16:36.650 Column number two. 233 16:36.740 --> 16:40.670 So this is how we can drop the both of the columns together. 234 16:40.790 --> 16:44.810 We do not need to write two drop statements one by one right. 235 16:45.140 --> 16:46.550 That is not required. 236 16:50.270 --> 16:55.130 By this way also you can do in by above way. 237 16:55.130 --> 16:59.270 Also you can do so both the ways could be possible. 238 16:59.270 --> 17:04.640 But now in this particular table, both the columns will be dropped. 239 17:04.640 --> 17:05.450 Right? 240 17:10.370 --> 17:11.840 Now suppose. 241 17:14.780 --> 17:22.970 Modify a column so we can modify the column by changing the size of the size of the column, as well 242 17:22.970 --> 17:24.710 as data type of the column. 243 17:25.040 --> 17:28.370 Now let's see few of the examples of modifying a column. 244 17:28.850 --> 17:32.030 So let me show you this. 245 17:43.460 --> 17:45.290 This is the data type. 246 17:45.290 --> 17:46.310 One minute. 247 17:55.760 --> 17:56.090 Okay. 248 17:56.090 --> 18:06.260 Now suppose I want to alter the employee name length to from 10 to 20. 249 18:06.290 --> 18:06.830 Right. 250 18:06.920 --> 18:10.490 Then what I can write I can write alter Table. 251 18:11.030 --> 18:11.690 EMP. 252 18:13.340 --> 18:14.240 Alter. 253 18:17.300 --> 18:18.800 Alter e name. 254 18:20.000 --> 18:20.660 Where? 255 18:20.660 --> 18:21.530 Care of. 256 18:23.630 --> 18:24.830 200. 257 18:27.920 --> 18:30.230 Now let me write this. 258 18:34.370 --> 18:35.570 Now see the. 259 18:35.570 --> 18:37.580 What is the length of the name? 260 18:37.580 --> 18:38.030 Column? 261 18:38.120 --> 18:38.330 Name. 262 18:38.330 --> 18:39.680 Column length is ten. 263 18:39.680 --> 18:42.590 But now I want to make it to 200. 264 18:42.590 --> 18:44.420 So I have to execute this query. 265 18:46.490 --> 18:54.350 Now if I execute this same statement again then in place of ten it will be 200. 266 18:54.350 --> 18:55.910 You can see over here right. 267 18:59.300 --> 19:01.160 It is 200, not ten. 268 19:01.160 --> 19:01.760 Right. 269 19:06.860 --> 19:10.190 So this is how we can change the modify the column as well. 270 19:10.220 --> 19:15.410 Now there is one more thing for this Ename column. 271 19:16.640 --> 19:19.520 We can not do as integer. 272 19:22.310 --> 19:23.690 Let me try. 273 19:26.360 --> 19:31.010 Conversion failed when converting the worker value such into data type integer. 274 19:31.760 --> 19:39.740 So you have to remember from worker to worker only we can connect convert. 275 19:40.490 --> 19:43.550 We can change the size from worker to worker. 276 19:43.790 --> 19:47.780 But now suppose for this dept column. 277 19:48.740 --> 19:54.890 Now suppose for this dept column I it is what it is integer right. 278 19:55.400 --> 19:57.710 Now I want to change it to worker. 279 20:02.990 --> 20:03.770 Ten. 280 20:04.220 --> 20:05.630 Now let's try. 281 20:09.320 --> 20:10.460 This will work. 282 20:11.300 --> 20:12.170 This will work. 283 20:12.170 --> 20:12.590 Why? 284 20:12.590 --> 20:21.020 Because worker value will be able to store our integer data as well as numeric data, or as well as 285 20:21.020 --> 20:22.460 string data as well. 286 20:24.650 --> 20:32.090 But from worker column we cannot change it to integer, but from integer column we can change it to 287 20:32.090 --> 20:32.720 worker. 288 20:33.230 --> 20:35.870 So this is what I want to show you. 289 20:35.900 --> 20:36.920 This thing. 290 20:41.210 --> 20:42.590 We have changed the size. 291 20:42.590 --> 20:44.780 We have changed the data type as well. 292 20:46.520 --> 20:49.220 Now let's try. 293 20:49.220 --> 20:50.210 Let's try this one. 294 20:50.210 --> 20:52.130 I am not sure about this. 295 20:54.830 --> 20:58.400 First, can we change the, uh, can we rename the table name as. 296 21:00.520 --> 21:04.690 Means, uh, can we rename the table name? 297 21:04.690 --> 21:06.010 Also, with the help of. 298 21:06.010 --> 21:08.050 We will see in few minutes. 299 21:08.050 --> 21:08.860 We will see. 300 21:09.340 --> 21:17.950 Okay, now let's try to change the data type of salary to tiny integer. 301 21:20.470 --> 21:21.550 Let's see. 302 21:25.240 --> 21:26.350 Arithmetic overflow. 303 21:26.350 --> 21:26.620 Error. 304 21:26.620 --> 21:28.360 Converting expression to data type. 305 21:28.390 --> 21:29.290 Tiny integer. 306 21:29.500 --> 21:30.940 Because. 307 21:31.630 --> 21:32.590 Because. 308 21:32.590 --> 21:33.580 Integer. 309 21:35.740 --> 21:38.470 Tiny integer and big integer. 310 21:39.850 --> 21:47.590 Everything has a different ability to store up to some digits, so you can check in. 311 21:47.590 --> 21:53.680 The Google Tiny integer will store very less amount of data like one. 312 21:53.830 --> 22:00.070 Or we can say that ten or maximum may be 100, not more than that. 313 22:02.290 --> 22:06.010 Whereas integer will be store some good amount of data. 314 22:06.010 --> 22:11.920 We can store 1000, 10,000, maybe up to one lakh. 315 22:12.550 --> 22:13.390 Ten lakh. 316 22:13.390 --> 22:22.150 But when storing data more than ten lakh or millions of amount at that time, we have to use big integer 317 22:23.740 --> 22:28.420 because salary of the employee is currently in thousands, right? 318 22:28.480 --> 22:31.390 2000 3000 4000 5000. 319 22:31.540 --> 22:35.020 That is why we cannot change it to tiny integer. 320 22:38.140 --> 22:40.690 That is why we cannot change it to tiny integer. 321 22:40.690 --> 22:42.130 It will show you this error. 322 22:47.470 --> 22:48.730 Arithmetic overflow. 323 22:48.730 --> 22:49.210 Error. 324 22:56.800 --> 23:00.370 Because it is, it is a numeric integer kind of thing, right? 325 23:00.370 --> 23:03.100 That is, it is known as arithmetic overflow. 326 23:06.940 --> 23:08.860 Now let's see further. 327 23:09.880 --> 23:14.080 So I said, uh, suppose that there is a value 200. 328 23:14.080 --> 23:16.570 Can we increase that. 329 23:16.570 --> 23:22.510 Can we make it to less to 220 like so there is a 200 already. 330 23:22.510 --> 23:22.780 Yeah. 331 23:22.780 --> 23:24.580 And we want to reduce it to 20. 332 23:24.580 --> 23:28.840 Yeah, but there is one condition over here we can. 333 23:29.470 --> 23:30.520 Let's try. 334 23:30.520 --> 23:32.440 Let's try this in a maze. 335 23:33.790 --> 23:34.780 Let me. 336 23:40.120 --> 23:48.520 I guess there there also should be give overflow I mean let's try select star from MP right. 337 23:51.490 --> 23:54.910 We have name is six right now. 338 23:54.910 --> 23:57.940 Let me try to do something different. 339 23:59.050 --> 24:01.540 Try to do like this. 340 24:02.200 --> 24:03.160 Let's see. 341 24:05.410 --> 24:08.590 String or binary data would be truncated over here. 342 24:08.590 --> 24:12.440 Error is different but it want to tell you the same thing. 343 24:14.510 --> 24:15.200 Yeah. 344 24:15.590 --> 24:15.920 Correct. 345 24:16.430 --> 24:17.000 Okay. 346 24:17.000 --> 24:18.440 Because we have Sachin. 347 24:18.470 --> 24:20.930 Sachin is more than three three length, right? 348 24:23.000 --> 24:23.720 Yeah, yeah. 349 24:23.810 --> 24:25.430 So we cannot do. 350 24:29.870 --> 24:33.050 Now less with the start with the drop command. 351 24:33.590 --> 24:36.940 So used to drop the table from the database. 352 24:37.780 --> 24:42.520 Drop command is used to drop the table from the database drop drop table. 353 24:43.690 --> 24:47.860 Drop command will drop the structure of the table as well. 354 24:48.580 --> 24:50.980 So let me try. 355 24:57.640 --> 24:59.530 Which table we have created. 356 25:04.270 --> 25:05.290 Where is that table? 357 25:15.730 --> 25:17.440 Okay, we have this table with us. 358 25:17.440 --> 25:18.040 Right? 359 25:20.860 --> 25:23.890 So currently this table has data. 360 25:23.920 --> 25:25.360 Now let me drop. 361 25:26.770 --> 25:32.440 Sorry let me delete from AMP 29. 362 25:34.540 --> 25:36.340 So it will delete all the rows. 363 25:36.370 --> 25:40.660 Now let me show you the structure is there. 364 25:41.560 --> 25:48.100 The structure is there but data is deleted right now let me try to drop drop this table. 365 25:53.140 --> 25:55.330 Now let me try to drop this table. 366 25:55.330 --> 25:56.290 Now let's see. 367 26:01.150 --> 26:02.470 Where is the table? 368 26:02.470 --> 26:03.880 Table is dropped. 369 26:04.060 --> 26:06.130 Structure is also dropped. 370 26:07.030 --> 26:09.160 Now table is not there in the database. 371 26:09.160 --> 26:09.670 Right. 372 26:09.820 --> 26:12.010 Drop command will drop. 373 26:12.500 --> 26:16.880 Drop command will drop the structure as well. 374 26:16.880 --> 26:19.040 Structure as well from the database. 375 26:19.040 --> 26:19.850 Right. 376 26:21.830 --> 26:28.580 Um, basically we create a database and from the database we create, you know, table. 377 26:28.580 --> 26:29.480 Correct or not. 378 26:30.530 --> 26:31.400 Uh, yes. 379 26:31.880 --> 26:39.280 So here the database only, I mean, the table only get deleted whereas the database will all exist. 380 26:39.280 --> 26:39.760 Right? 381 26:39.790 --> 26:40.810 Yes, yes. 382 26:41.200 --> 26:41.920 Okay. 383 26:45.070 --> 26:47.800 So now drop table table name. 384 26:47.800 --> 26:51.940 So drop table will drop the table as well as structure of the database. 385 26:52.450 --> 26:53.650 Now suppose. 386 26:57.820 --> 27:05.370 Let me try to create one more table Select star from EMP 30 from EMP. 387 27:13.200 --> 27:13.650 Parent. 388 27:18.720 --> 27:21.660 Now let me try to use this truncate command. 389 27:32.640 --> 27:34.530 Truncate table MP 30. 390 27:35.250 --> 27:40.530 So now truncate with will also remove the data from the table. 391 27:40.620 --> 27:45.540 Delete will also remove the data from the table but the structure will be there. 392 27:45.540 --> 27:50.340 So if I select from this particular table. 393 27:53.070 --> 27:57.150 Data will be not be there but structure is again there. 394 27:58.920 --> 28:05.670 And now using the drop drop command we can we can remove the column as well right. 395 28:05.670 --> 28:07.260 That we have already seen. 396 28:08.190 --> 28:12.720 So drop command we can remove the entire table as well as we have remove the column. 397 28:12.720 --> 28:18.990 We have removed that phone number and some other column as well from the table using drop command. 398 28:21.720 --> 28:24.570 Now the thing is. 399 28:28.860 --> 28:31.470 Now these are all the important thing. 400 28:31.980 --> 28:38.730 Let's understand delete all the data from the table but keeps the structure. 401 28:38.730 --> 28:39.840 So truncate command. 402 28:39.840 --> 28:45.630 What will truncate command do truncate command will also delete all the data from the table, but it 403 28:45.630 --> 28:47.220 will keep the structure. 404 28:47.220 --> 28:50.250 Structure will not be deleted, not be removed. 405 28:50.460 --> 28:52.050 Then will empty the table. 406 28:52.050 --> 28:52.560 Right. 407 28:52.560 --> 28:54.960 Cannot delete the specific record. 408 28:55.050 --> 29:03.000 Now over here there is one difference between delete and truncate table. 409 29:03.000 --> 29:13.050 We cannot mention over here where MP id equal to ten, whereas in delete statement we can we can mention. 410 29:16.890 --> 29:24.720 We can mention in delete statement we can delete specific rows where in truncate table we cannot delete. 411 29:26.430 --> 29:27.540 specific rows. 412 29:27.540 --> 29:30.930 All the rows will be deleted in truncate statement. 413 29:33.810 --> 29:35.880 So this is what is written over here. 414 29:35.880 --> 29:37.920 Cannot delete specific rows. 415 29:38.250 --> 29:42.240 That means where clause is not allowed in truncate statement. 416 29:42.240 --> 29:43.230 Now one. 417 29:43.410 --> 29:52.650 When truncate command is executed, then SQL server goes to the memory and releases all the pages allocated 418 29:52.650 --> 29:53.700 for the table. 419 29:54.660 --> 30:00.450 And when the pages will be released, then data stored inside the pages will also be deleted. 420 30:00.630 --> 30:09.120 So at the time of delete statement, I told that data will be stored in the transaction log. 421 30:09.150 --> 30:10.800 So what is transaction log? 422 30:10.830 --> 30:14.400 Transaction log will you can say that some of the pages. 423 30:14.400 --> 30:21.210 You can say that all the deleted data will be mentioned over there in the transaction log. 424 30:21.300 --> 30:23.550 And it will maintain pages by pages. 425 30:23.550 --> 30:29.310 Maybe suppose in one page 100 record will be deleted, 100 deleted record will be stored. 426 30:29.490 --> 30:33.240 Then in second page another 100 records will be stored. 427 30:33.570 --> 30:37.620 Then in third page another 100 deleted record will be stored like that. 428 30:37.920 --> 30:45.360 So when the truncate command is executed, then SQL server goes to the memory and release all the pages 429 30:46.620 --> 30:48.060 allocate for the table. 430 30:48.060 --> 30:54.660 So suppose in one table 300 row 400 rows are there or 450 rows are there. 431 30:54.810 --> 30:58.530 Then all the five five pages data. 432 30:58.530 --> 31:06.330 It will be deleted at one go and it will release all the memory of all that five pages. 433 31:09.180 --> 31:15.270 So it looks like the pages concept is like a little bit tricky. 434 31:15.270 --> 31:18.600 Like could you please explain it more elaborate it. 435 31:18.690 --> 31:23.790 I think I think that thing only I can say that it is a transaction log. 436 31:23.910 --> 31:26.400 You can say that it is a transaction log. 437 31:26.400 --> 31:32.370 All the deleted data will be stored in the transaction log in different pages. 438 31:32.370 --> 31:34.770 So it depends on the size of the data. 439 31:34.770 --> 31:36.990 Suppose one lakh record is there. 440 31:36.990 --> 31:41.760 So in for 10,000 row it will create one page. 441 31:41.760 --> 31:42.960 It is not fixed. 442 31:43.290 --> 31:44.370 It is not fixed. 443 31:44.370 --> 31:46.260 I am giving you just one example. 444 31:46.260 --> 31:51.930 For 10,000 rows it will create one page second 10,000 row. 445 31:51.930 --> 31:54.000 It will create second page like that. 446 31:54.900 --> 31:56.730 You have to just imagine you. 447 31:56.730 --> 31:58.710 There is no fixed thing over here. 448 31:58.830 --> 32:00.330 You have to just imagine. 449 32:00.330 --> 32:05.850 So this truncate can you see the transaction log. 450 32:05.880 --> 32:08.100 No, no, we cannot see that. 451 32:08.670 --> 32:16.200 We cannot see that, uh, physically, we cannot see that it is a concept or architecture of SQL server. 452 32:16.200 --> 32:17.670 We cannot see that. 453 32:19.800 --> 32:20.040 Yet. 454 32:21.570 --> 32:30.360 So while deleting it will suppose it will delete employee ID one and it will store that one record into 455 32:30.360 --> 32:31.620 the transaction log. 456 32:33.450 --> 32:39.900 Then employee ID two it will delete and it is stored in the transaction log over here in truncate. 457 32:39.900 --> 32:43.380 It will delete all the row in one go. 458 32:46.950 --> 32:48.090 And it will. 459 32:50.910 --> 32:57.000 It will delete all the rows from the transaction log as well internally. 460 32:59.040 --> 33:01.830 And it will keep the memory sorry. 461 33:01.830 --> 33:04.670 It will release the memory of the transaction log as well. 462 33:08.210 --> 33:10.640 That only thing you have to understand. 463 33:14.990 --> 33:17.210 Then truncate will release the identity. 464 33:17.210 --> 33:19.460 Delete will not reset the identity. 465 33:20.030 --> 33:21.650 So we will. 466 33:21.740 --> 33:26.210 For that we have to check. 467 33:26.240 --> 33:27.440 Take one example. 468 33:27.440 --> 33:29.060 I will take it in later. 469 33:31.160 --> 33:32.270 Now let's. 470 33:37.040 --> 33:39.590 Let understand what is the drop. 471 33:39.590 --> 33:40.880 Delete and truncate. 472 33:41.600 --> 33:45.110 So uh drop will delete the data. 473 33:45.110 --> 33:45.530 Right. 474 33:45.530 --> 33:46.280 Drop it. 475 33:46.280 --> 33:54.950 Delete all the data as well as structure Dot will drop, will delete all the data as well as structure. 476 33:55.400 --> 34:00.470 Whereas delete will delete all the data but keeps the structure. 477 34:02.360 --> 34:04.130 Truncate will also do the same thing. 478 34:04.130 --> 34:06.710 It will delete the data and keeps the structure. 479 34:10.880 --> 34:17.750 Now this is very frequently asked interview question delete versus truncate. 480 34:20.690 --> 34:24.230 So you can say that delete is a DML command. 481 34:24.230 --> 34:31.010 Delete is a DML command while truncate is a DDL command. 482 34:34.700 --> 34:38.480 Now delete can delete all or specific rows. 483 34:38.660 --> 34:41.570 We can mention the where condition in the delete statement. 484 34:41.570 --> 34:42.140 Right. 485 34:42.500 --> 34:44.360 So we can say that. 486 34:47.090 --> 34:48.200 Delete will. 487 34:50.900 --> 34:52.880 Delete the specific rows as well. 488 34:52.880 --> 34:57.230 Whereas truncate can only delete all row not specific row. 489 34:57.260 --> 34:59.480 We cannot write where clause in truncate. 490 35:00.290 --> 35:01.910 That is the second difference. 491 35:04.160 --> 35:10.100 This is also the same thing where condition can be used with delete, where condition cannot be used 492 35:10.100 --> 35:13.430 with truncate deletes row by row. 493 35:18.230 --> 35:22.580 Whereas truncate is not deleting row by row, it will. 494 35:22.580 --> 35:25.100 Multiple rows are deleted at the same time. 495 35:25.220 --> 35:36.080 So deallocates the pages That means it will release all the memory of the pages of that particular table. 496 35:40.190 --> 35:43.880 Suppose 100 pages and one lakh record are there. 497 35:43.910 --> 35:49.190 Then all the 100 pages memory it will release at one time in truncate. 498 35:49.310 --> 35:52.040 But in delete it will not happen like that. 499 35:52.250 --> 35:53.630 One by row, one row. 500 35:53.630 --> 35:55.970 It will be happen in delete. 501 35:56.000 --> 36:00.740 Now after deletion of every row, it makes entry in the log, right? 502 36:01.250 --> 36:01.490 This. 503 36:01.490 --> 36:07.760 Also, I have already told you it makes entry in the log after the page De-allocation. 504 36:15.290 --> 36:16.760 Then which is faster? 505 36:17.030 --> 36:19.550 Delete versus truncate which is faster? 506 36:20.720 --> 36:23.030 Of course truncate is more faster. 507 36:26.510 --> 36:33.500 Of course truncate is more faster because in the delete statement it is deleting one by one row and 508 36:33.500 --> 36:35.990 it will lock the entry into the transaction log. 509 36:36.290 --> 36:43.350 So delete is slower in performance whereas truncate is faster in performance because it will delete 510 36:43.350 --> 36:45.240 all the rows at one go. 511 36:49.380 --> 36:55.020 So this thing also you can told that will not release memory. 512 36:55.020 --> 37:02.310 Release memory in truncate will not reset identity will reset identity in truncate. 513 37:02.310 --> 37:03.960 So this thing you can. 514 37:06.020 --> 37:08.150 Well, one more thing. 515 37:08.420 --> 37:13.850 Delete will internally execute the trigger because. 516 37:13.880 --> 37:16.610 Because it is a DML statement. 517 37:16.700 --> 37:22.730 It will internally execute some menu data manipulation language trigger. 518 37:23.270 --> 37:26.540 Whereas truncate will not invoke the trigger. 519 37:31.670 --> 37:36.080 Truncate will not invoke the trigger because delete is a DML statement. 520 37:36.140 --> 37:43.850 It will internally execute the trigger that also you can say and. 521 37:48.320 --> 37:50.060 Requires more log space. 522 37:50.060 --> 37:52.310 It requires less log space. 523 37:57.650 --> 37:58.100 Testing. 524 37:58.100 --> 38:03.950 Also, you can say delete will require more space because maybe one by one row it is deleting. 525 38:04.130 --> 38:12.020 So maybe log space will be require more, whereas truncate will release the space at the time of the 526 38:12.020 --> 38:13.250 truncate table. 527 38:13.280 --> 38:15.890 It require less log space. 528 38:22.830 --> 38:24.780 Now there is one more thing. 529 38:24.780 --> 38:27.810 So over here two thing we have to see practically. 530 38:28.830 --> 38:35.400 Delete can be used on a parent table if foreign key relationships are present on the primary key column. 531 38:35.610 --> 38:37.170 So now suppose. 532 38:40.450 --> 38:42.520 Let me try to see you. 533 38:42.520 --> 38:44.080 This one MP. 534 38:52.750 --> 38:57.040 So do we have any, uh, foreign key over here in the MP table? 535 38:57.040 --> 38:57.730 Let's see. 536 39:08.260 --> 39:09.910 Foreign key. 537 39:14.560 --> 39:16.690 It shows f k and PK, right? 538 39:18.040 --> 39:18.910 Yeah, yeah. 539 39:18.970 --> 39:20.170 Where is f k? 540 39:25.210 --> 39:26.290 I can see PK. 541 39:26.320 --> 39:27.580 Their primary key is there. 542 39:28.660 --> 39:30.940 and DPT in the DPT table. 543 39:30.940 --> 39:32.290 Let's see this one. 544 39:39.550 --> 39:40.840 Please be on mute. 545 39:49.600 --> 39:49.960 Okay. 546 39:49.960 --> 39:55.150 Now let me try to create a foreign key in the employee table. 547 39:55.150 --> 39:56.830 Alter table EMP. 548 40:20.230 --> 40:22.960 I am not sure whether it will work or not. 549 40:26.740 --> 40:29.170 Number of reference column in the table. 550 40:30.790 --> 40:32.770 Okay, now what is the error? 551 40:34.240 --> 40:36.460 Ten 2030. 552 40:39.850 --> 40:42.820 Ten 20 2030 are there and. 553 40:47.830 --> 40:48.340 Okay. 554 40:48.340 --> 40:50.680 40 let me try to delete. 555 40:50.680 --> 40:52.090 Delete. 556 41:04.270 --> 41:05.740 Now let me try. 557 41:08.800 --> 41:12.040 Number of referencing columns in foreign key. 558 41:19.030 --> 41:21.610 Differs from number of reference columns. 559 41:21.610 --> 41:22.600 Table EMP. 560 41:30.280 --> 41:31.480 What this says. 561 41:31.480 --> 41:35.950 The number of references column in foreign key ref differs from number of. 562 41:37.360 --> 41:39.220 What is the column name over here? 563 41:44.980 --> 41:45.730 PT. 564 41:53.020 --> 41:54.580 Ten, 2030. 565 42:11.650 --> 42:12.940 Ten 2030. 566 42:12.970 --> 42:14.260 Alter table EMP. 567 42:14.290 --> 42:15.520 Add foreign key. 568 42:15.520 --> 42:16.960 References. 569 42:19.600 --> 42:22.390 DPT, DP, dpt number I'm not sure. 570 42:24.730 --> 42:28.360 Okay, I will, uh, see both of the examples tomorrow. 571 42:28.360 --> 42:33.220 This, uh, Monday this delete an identity related example. 572 42:33.220 --> 42:35.860 I will show you both of that example tomorrow. 573 42:37.570 --> 42:40.060 Now let's quickly see this one. 574 42:44.140 --> 42:48.640 Now suppose if I want to change the table, uh. 575 42:48.640 --> 42:49.840 Table name. 576 42:54.190 --> 42:56.290 From MP two. 577 42:58.930 --> 42:59.920 MP to. 578 42:59.920 --> 43:01.300 Let's take MP. 579 43:01.330 --> 43:04.990 Right now we have MP. 580 43:07.000 --> 43:07.990 New. 581 43:08.380 --> 43:09.370 Then some. 582 43:09.640 --> 43:11.440 Someone asked the question right. 583 43:11.440 --> 43:15.250 We can use the SP rename MP comma MP new. 584 43:15.280 --> 43:16.600 Let me try this one. 585 43:20.110 --> 43:20.680 Break two. 586 43:20.680 --> 43:21.490 Script two. 587 43:22.150 --> 43:22.870 Okay. 588 43:22.870 --> 43:25.960 Now let's see whether it has changed or not. 589 43:25.960 --> 43:27.010 I am not sure. 590 43:40.180 --> 43:41.710 Okay, now let's see. 591 43:41.740 --> 43:42.580 MP new. 592 43:50.560 --> 43:52.210 It has changed, right? 593 43:52.510 --> 43:54.820 So for that we have to mention this. 594 43:55.060 --> 43:56.440 This is the syntax. 595 43:56.980 --> 44:05.230 SP underscore rename is the inbuilt store procedure which we can use where we can mention SP rename 596 44:05.350 --> 44:06.130 MP. 597 44:08.380 --> 44:11.830 As the old table name in MP new is the new table name. 598 44:11.830 --> 44:17.620 So we can change the table name also in the database by writing this command. 599 44:19.690 --> 44:21.730 And we can change the column name as well. 600 44:21.730 --> 44:23.890 So let me try with this one. 601 44:24.520 --> 44:29.260 If we want to change the column name in the salary to EMP cell. 602 44:29.650 --> 44:32.140 So let me try this one. 603 44:36.070 --> 44:39.220 Only the current database given okay. 604 44:39.670 --> 44:40.390 Okay. 605 44:40.390 --> 44:42.600 Here we have to mention EMP MP new. 606 45:09.300 --> 45:11.310 Oh, cell. 607 45:24.750 --> 45:29.580 Okay, what we have to mention over here, we have to mention over here. 608 45:30.990 --> 45:32.340 Celery, celery. 609 45:32.340 --> 45:33.540 And then over here. 610 45:33.540 --> 45:34.500 Empty cell. 611 45:35.370 --> 45:36.960 Not the table we have. 612 45:36.960 --> 45:39.450 Just like old table and new table. 613 45:39.450 --> 45:42.030 We have to mention old column and new new column. 614 45:42.030 --> 45:46.860 So if we mention like this one again it will not working. 615 45:55.200 --> 45:58.920 Okay, we need to mention the table name as alias. 616 45:59.160 --> 46:00.360 Let me try. 617 46:00.390 --> 46:03.210 Now this one we have to mention like this. 618 46:04.830 --> 46:06.210 Let me try. 619 46:07.380 --> 46:09.150 Yeah, it is working. 620 46:09.150 --> 46:13.950 Now let me say in into this particular table select star from EMP new. 621 46:14.310 --> 46:17.610 It will be EMP cell. 622 46:19.050 --> 46:21.990 It will be empty cell from salary write. 623 46:22.260 --> 46:24.420 The column name is also changed. 624 46:26.850 --> 46:30.030 So in this case we have to mention the alias. 625 46:30.540 --> 46:33.630 We have to mention the table name dot column name. 626 46:37.830 --> 46:39.540 Or table name dot column name. 627 46:39.540 --> 46:41.850 And then new new column name. 628 46:41.850 --> 46:42.360 Right. 629 46:44.340 --> 46:44.850 Oh, yes. 630 46:44.850 --> 46:45.060 Yes. 631 46:45.060 --> 46:46.050 Table name dot. 632 46:46.140 --> 46:49.470 Can you, can you put a small description over there in the notes saying that. 633 46:49.470 --> 46:50.490 Yeah it is over here. 634 46:50.910 --> 46:53.400 Change the column name commission to bonus. 635 46:53.730 --> 46:59.670 Or we can say that change the column name from salary to empty cell. 636 47:05.310 --> 47:09.540 In this in any of the table EMP new table. 637 47:10.740 --> 47:11.850 This table. 638 47:20.370 --> 47:25.620 So like this way we can change the table name as well as the column name into the table.