1 00:04.820 --> 00:11.000 Okay, so yesterday we have seen up to here only that. 2 00:11.000 --> 00:15.050 What are the basic rules are there for creating the tables. 3 00:15.800 --> 00:20.390 So now let's start with the create create one simple table. 4 00:21.050 --> 00:25.010 I will explain you constraint and all this thing in detail level. 5 00:25.010 --> 00:28.760 But right now just create this table. 6 00:29.640 --> 00:31.860 So this is a employee table right. 7 00:31.860 --> 00:32.970 Employee table. 8 00:33.180 --> 00:33.900 Employee number. 9 00:33.900 --> 00:35.310 And this is integer. 10 00:35.310 --> 00:40.260 So you know what is this integer where care date time numeric. 11 00:42.120 --> 00:44.880 What what we can call this one. 12 00:45.450 --> 00:50.850 Anyone has idea this integer or numeric values right. 13 00:51.330 --> 00:54.040 And Varchar can store alphabets and numbers both. 14 00:54.130 --> 00:55.330 Yeah, that is right. 15 00:55.330 --> 00:56.590 But what is that? 16 00:56.950 --> 00:59.080 Yeah that is data type right. 17 00:59.590 --> 01:00.130 Data type. 18 01:00.130 --> 01:00.340 Right. 19 01:00.340 --> 01:00.640 Yeah. 20 01:00.640 --> 01:00.790 Yeah. 21 01:00.790 --> 01:02.560 These all are the data type. 22 01:02.590 --> 01:04.960 Then this primary key is a constraint. 23 01:05.290 --> 01:08.230 So I will explain constraint later on. 24 01:09.220 --> 01:12.220 So now just let me create this table. 25 01:14.710 --> 01:16.030 These are all the columns. 26 01:16.030 --> 01:21.020 And then uh, what does this indicate? 27 01:21.020 --> 01:25.400 Uh, commission numeric seven comma two, and then null. 28 01:25.400 --> 01:26.240 I have specified. 29 01:26.240 --> 01:26.600 Right. 30 01:29.960 --> 01:32.570 So what does this null indicate? 31 01:34.940 --> 01:37.970 Now this means like I think it can store null values as well. 32 01:49.120 --> 01:49.600 Uh, sorry. 33 01:49.600 --> 01:50.620 Can you repeat? 34 01:53.350 --> 01:54.820 I think this means like it can. 35 01:54.820 --> 01:56.920 This column can store the null values as well. 36 01:56.920 --> 01:57.400 Okay. 37 01:57.400 --> 01:57.640 Yes. 38 01:57.640 --> 01:57.850 Yes. 39 01:57.850 --> 01:58.420 Right. 40 02:00.100 --> 02:00.910 One minute. 41 02:04.750 --> 02:06.670 This column can be null. 42 02:06.700 --> 02:08.170 Store the null values as well. 43 02:08.170 --> 02:11.230 So let me create this table. 44 02:15.370 --> 02:16.270 Now. 45 02:24.190 --> 02:28.330 Now we are inserting the data into the same right. 46 02:28.330 --> 02:33.920 So what I have written over here, I have written over here, begin to run. 47 02:36.230 --> 02:36.710 Right. 48 02:36.710 --> 02:37.700 I have written over here. 49 02:37.700 --> 02:38.330 Begin to run. 50 02:38.330 --> 02:39.350 So it will. 51 02:41.720 --> 02:44.600 It will indicating the begin transaction. 52 02:46.220 --> 02:50.270 So when anyone is not speaking, please be on mute. 53 02:50.270 --> 02:55.980 Everyone at the time of speaking, only unmute yourself and speak. 54 02:58.470 --> 02:59.130 Yeah. 55 02:59.970 --> 03:05.220 So this begin Tran indicates the transaction will be. 56 03:05.220 --> 03:06.060 Begin now. 57 03:06.060 --> 03:06.360 Right. 58 03:06.360 --> 03:07.950 So transaction will be begin. 59 03:07.950 --> 03:15.900 And if we want to commit and uh, if we are inserting this data without doing the begin tran that I 60 03:15.900 --> 03:17.860 have told already yesterday, right? 61 03:18.550 --> 03:20.500 So let me insert this data. 62 03:21.430 --> 03:23.560 So all this data are inserted. 63 03:23.560 --> 03:29.530 So we have specify the values of all the columns mentioned above right. 64 03:29.710 --> 03:31.780 Employee number then name. 65 03:32.440 --> 03:33.520 Then this is a job. 66 03:33.520 --> 03:33.910 Job. 67 03:33.910 --> 03:41.900 We can say manager ID then hire date and then other columns like salary commission and department number. 68 03:42.620 --> 03:45.710 So this null because it is allowing null. 69 03:45.710 --> 03:48.170 So I have mentioned null over here as well. 70 03:49.580 --> 03:55.190 But now can we rollback this uh this as of now can we rollback this one. 71 03:56.510 --> 03:56.930 Uh, yes. 72 03:56.930 --> 03:57.380 Yes. 73 03:57.590 --> 04:04.070 Because we initiated the, you know, no, no, uh, no, no, I haven't initiated I have. 74 04:06.600 --> 04:08.760 I haven't initiated, let's see. 75 04:08.910 --> 04:12.840 The rollback transaction requires no corresponding begin transaction. 76 04:13.800 --> 04:16.710 I have not I have not executed this query. 77 04:16.710 --> 04:19.500 I have just started from this one. 78 04:20.820 --> 04:23.760 So it is now we can't rollback. 79 04:23.790 --> 04:24.210 Yeah. 80 04:24.210 --> 04:27.540 So this is called Autocommit in SQL server. 81 04:28.500 --> 04:29.350 Uh I'm sorry. 82 04:30.160 --> 04:30.490 Um. 83 04:30.490 --> 04:31.300 I'm sorry. 84 04:31.450 --> 04:39.040 Um, since I'm not completely clear on it, uh, one is if you go up, begin to run. 85 04:39.040 --> 04:46.900 If I don't mention begin, Tran and I and I start including and I start executing insert into will it 86 04:46.900 --> 04:47.860 work or not? 87 04:47.860 --> 04:48.970 Because I tested it. 88 04:48.970 --> 04:49.870 It is working. 89 04:49.870 --> 04:51.490 Yeah, yeah, it is working. 90 04:51.490 --> 04:52.630 It is working. 91 04:52.820 --> 04:54.380 It is working. 92 04:54.380 --> 04:59.240 But but you cannot rollback that data, okay. 93 04:59.240 --> 05:00.320 What is called rollback. 94 05:00.320 --> 05:02.180 What is the meaning of rollback okay. 95 05:02.180 --> 05:08.000 Rollback means suppose uh if I have inserted the data into the same. 96 05:08.000 --> 05:08.510 Right. 97 05:08.660 --> 05:14.810 And uh, suppose I after some time I found that this data is wrong. 98 05:14.810 --> 05:20.820 I don't want to insert this data, I want to completely rollback my transaction. 99 05:20.820 --> 05:22.350 Which means remove the transaction. 100 05:22.350 --> 05:22.710 Right? 101 05:22.710 --> 05:23.010 Yeah. 102 05:23.010 --> 05:23.940 Remove this data. 103 05:23.940 --> 05:25.440 Remove this data. 104 05:25.980 --> 05:26.310 Okay. 105 05:26.310 --> 05:30.240 So that what I will do I will do first begin run. 106 05:30.240 --> 05:33.120 Then I will insert the data, all this data. 107 05:33.120 --> 05:35.730 And I want to insert some new data. 108 05:35.730 --> 05:38.220 Only this data is not required. 109 05:38.220 --> 05:41.650 So what I will do I will do rollback over here. 110 05:44.590 --> 05:48.940 If I do the roll back, then again this entire table will be empty. 111 05:49.870 --> 05:51.820 This entire table will be empty. 112 05:53.590 --> 06:01.510 So initial time let's say step one is if I insert, I will insert it uh, from line number. 113 06:01.510 --> 06:03.460 So here line number is not there okay fine. 114 06:03.460 --> 06:08.030 So from here to last end I will run one one time to execute it. 115 06:08.030 --> 06:11.000 So that line will get automatically updated in the database. 116 06:11.000 --> 06:17.300 And if I want to take that back from it then I have to start with uh what is that. 117 06:17.300 --> 06:20.420 Begin tran and then insert line item. 118 06:20.420 --> 06:22.550 And last will be the roll back and end. 119 06:22.550 --> 06:28.550 If I run all these at one at one time, that will data will come out of it, right. 120 06:28.550 --> 06:29.700 That's what you are trying to say? 121 06:29.700 --> 06:30.090 Yeah. 122 06:30.090 --> 06:33.990 If you run all this at one time, then data will not be inserted. 123 06:34.500 --> 06:35.160 Okay. 124 06:35.730 --> 06:37.800 Can you can you show it an example please. 125 06:37.800 --> 06:38.400 One more time. 126 06:38.400 --> 06:39.540 Yeah okay. 127 06:39.540 --> 06:44.010 And insert start from the insert I have I can see that create table has done. 128 06:44.010 --> 06:45.480 After that what happens then. 129 06:50.910 --> 06:51.750 What will happen. 130 06:51.750 --> 06:52.710 Truncate if you do it. 131 06:53.800 --> 06:56.080 Truncate table will empty the table. 132 06:56.080 --> 07:02.800 So if select star from EMP there will be no data in this table. 133 07:03.280 --> 07:03.670 Correct. 134 07:04.660 --> 07:07.990 Now you want to do begin Tran or not. 135 07:09.760 --> 07:13.030 Let's insert some lines and then we can uh. 136 07:13.570 --> 07:15.820 Okay I will insert all the, all the line. 137 07:15.970 --> 07:17.540 I have inserted all the line. 138 07:17.540 --> 07:18.050 Right. 139 07:18.410 --> 07:19.010 Okay, begin. 140 07:19.010 --> 07:19.370 Turn up. 141 07:19.490 --> 07:21.140 I have inserted all the line. 142 07:21.170 --> 07:23.450 Now, if I run this query. 143 07:23.570 --> 07:26.960 Now, if I run this query, you are able to see data, right? 144 07:27.320 --> 07:27.770 Perfect. 145 07:28.700 --> 07:35.840 But now if I do the rollback I have done the rollback. 146 07:35.840 --> 07:36.380 Right. 147 07:36.800 --> 07:37.160 Correct. 148 07:37.760 --> 07:39.950 Now data will not be there. 149 07:39.950 --> 07:40.790 Over here. 150 07:41.760 --> 07:42.330 Got it. 151 07:42.330 --> 07:42.840 Very clear. 152 07:42.840 --> 07:45.480 So what is the purpose of having an end to something called as end? 153 07:45.510 --> 07:46.320 You mentioned at the bottom. 154 07:46.320 --> 07:46.530 Yeah. 155 07:46.590 --> 07:51.450 Actually, whenever you have to do begin to run, it is mandatory. 156 07:51.450 --> 07:53.130 You have to write the end. 157 07:54.000 --> 07:54.450 Okay. 158 07:54.450 --> 07:56.700 But while executing you didn't select that end. 159 07:56.730 --> 07:58.800 That's the reason I'm asking you actually. 160 07:58.800 --> 08:01.620 Uh, now I can do also. 161 08:04.570 --> 08:08.530 Because after end, if rollback comes, then I'm fine with that. 162 08:08.530 --> 08:11.260 Begin Tran insert end. 163 08:12.010 --> 08:15.250 Uh, but in the middle of it you mentioned rollback. 164 08:15.250 --> 08:17.140 So that's where the confusion started for me. 165 08:18.160 --> 08:18.910 Yeah. 166 08:18.910 --> 08:19.930 Uh, yeah. 167 08:19.930 --> 08:22.270 Actually, if you do over here. 168 08:22.270 --> 08:25.690 Right, if you do over here, then it will not work. 169 08:25.690 --> 08:26.740 It will not work. 170 08:27.220 --> 08:28.240 Okay okay okay. 171 08:28.240 --> 08:28.540 Why? 172 08:28.540 --> 08:28.990 Why. 173 08:28.990 --> 08:34.220 Because the transaction is already ended over here, right, I got it, I got it, I got it, yes. 174 08:34.220 --> 08:34.700 Clear. 175 08:35.180 --> 08:35.420 Clear. 176 08:35.420 --> 08:35.720 Yeah. 177 08:35.720 --> 08:42.980 And if you do in place of rollback, if you do over here commit then what will happen. 178 08:42.980 --> 08:47.900 The data will be permanently stored in the table okay. 179 08:48.470 --> 08:51.110 You can't take it back then even rollback applies. 180 08:51.110 --> 08:51.740 Yeah. 181 08:51.770 --> 08:52.280 Yeah. 182 08:52.280 --> 08:58.230 Once it is commit you cannot uh rollback right. 183 08:58.230 --> 09:04.050 But in SQL server if you do not mention bigint run then it will be auto commit itself. 184 09:05.250 --> 09:05.970 Okay. 185 09:06.360 --> 09:13.590 You cannot do the rollback if you haven't mentioned the bigint run right okay. 186 09:13.650 --> 09:18.370 So begin run is mandatory for commit and rollback. 187 09:18.730 --> 09:24.520 If you want to do manual commit and manual rollback then begin run is mandatory. 188 09:26.110 --> 09:26.530 Okay. 189 09:26.530 --> 09:26.920 Got it. 190 09:26.920 --> 09:27.880 You got it right. 191 09:28.000 --> 09:28.630 Okay. 192 09:28.630 --> 09:32.200 So let me in I think no data is there. 193 09:32.200 --> 09:33.700 Let me insert again. 194 09:38.830 --> 09:39.280 Okay. 195 09:39.280 --> 09:40.750 It will be a data now. 196 09:42.320 --> 09:45.440 Now, let me create another table. 197 09:46.010 --> 09:48.080 Uh, I'm just creating. 198 09:49.220 --> 09:50.270 Hi, Harshita. 199 09:50.270 --> 09:56.930 Uh, shall I ask one thing like, uh, if we apply the commit also, then, uh, I. 200 09:59.930 --> 10:03.110 Actually, I am not getting your voice. 201 10:03.740 --> 10:05.000 Can you repeat? 202 10:08.910 --> 10:18.720 It will say okay if you are applying after rollback or if if we apply committer then we can do the rollback 203 10:18.720 --> 10:19.590 either not. 204 10:21.390 --> 10:22.890 Let me try that one. 205 10:22.890 --> 10:25.230 Also, I think it is not possible. 206 10:25.290 --> 10:27.180 Let me try. 207 10:28.230 --> 10:33.220 Rollback means uh, it is like, uh uh, all the data will be lost now. 208 10:33.220 --> 10:37.000 So if we commit also, it will be okay. 209 10:37.000 --> 10:37.270 Now. 210 10:37.270 --> 10:38.950 Now I have done the begin run. 211 10:38.950 --> 10:40.900 Now I am inserting the data. 212 10:40.900 --> 10:41.410 Right. 213 10:41.680 --> 10:43.390 All data has been inserted. 214 10:43.390 --> 10:45.550 Now you want to do commit, right? 215 10:46.720 --> 10:47.500 Yeah. 216 10:47.500 --> 10:48.760 You want to commit. 217 10:49.000 --> 10:49.510 Okay. 218 10:49.510 --> 10:50.050 Commit. 219 10:50.050 --> 10:51.400 I have done the commit. 220 10:51.700 --> 10:55.380 Now you want to check whether we can do the rollback or not, right? 221 10:56.370 --> 10:57.180 Yeah. 222 10:58.620 --> 11:01.050 Okay, first, let me see the data. 223 11:01.080 --> 11:02.640 Data should be there. 224 11:03.060 --> 11:04.260 Data is there. 225 11:04.260 --> 11:06.270 Now let me do the rollback. 226 11:08.670 --> 11:09.450 No. 227 11:09.450 --> 11:09.780 Begin. 228 11:09.780 --> 11:10.350 Transaction. 229 11:10.350 --> 11:10.890 Right. 230 11:11.310 --> 11:14.670 Uh, again we have already done the commit. 231 11:14.670 --> 11:20.910 So commit will commit is already done, then we cannot do the rollback right. 232 11:23.670 --> 11:25.110 So yeah okay. 233 11:25.290 --> 11:31.230 So if you want to do the rollback then you do not need to do commit right. 234 11:31.980 --> 11:32.520 You. 235 11:37.710 --> 11:43.300 I think it will be good if you could just uh spell a difference between commit rollback. 236 11:43.300 --> 11:46.060 That will help, uh, us to uh. 237 11:46.060 --> 11:57.880 Yeah, difference is simple one commit will permanently, permanently store the data into the table 238 11:58.450 --> 12:01.180 and and rollback is. 239 12:05.630 --> 12:15.920 You can remove the data if it is wrongly inserted. 240 12:18.830 --> 12:24.170 Okay, a small doubt when you say commit permanently stored the data into the table, which means I 241 12:24.170 --> 12:25.880 cannot use the delete command. 242 12:25.880 --> 12:27.080 Also no no no no no no. 243 12:27.080 --> 12:29.860 You will definitely use the delete command. 244 12:30.280 --> 12:32.530 You will definitely use the delete command. 245 12:32.530 --> 12:34.930 You will definitely use the truncate command. 246 12:35.350 --> 12:39.820 But for now you cannot do the rollback if you do the commit. 247 12:40.390 --> 12:44.200 So for that instance when I'm creating okay I understood now. 248 12:44.200 --> 12:45.010 Thank you thank you. 249 12:45.010 --> 12:46.180 This is helpful. 250 12:47.890 --> 12:50.020 It will be the same for the Oracle. 251 12:50.020 --> 12:55.180 Also like in SQL not a SQL server in Oracle. 252 12:55.180 --> 13:00.370 Also we can do the same like uh, yeah in Oracle. 253 13:00.370 --> 13:07.960 Also it will be the same only for uh this one rollback and commit. 254 13:07.960 --> 13:11.890 It will be the same in almost all the relational database. 255 13:16.840 --> 13:17.570 Oracle SQL. 256 13:17.570 --> 13:19.820 Also, it will be the same only. 257 13:23.630 --> 13:24.230 Yeah. 258 13:24.740 --> 13:25.280 Okay. 259 13:25.640 --> 13:26.060 Thank you. 260 13:26.750 --> 13:27.410 Okay. 261 13:27.410 --> 13:28.430 Now you can continue. 262 13:28.970 --> 13:29.540 Okay. 263 13:31.040 --> 13:33.620 Now let me drop this table. 264 13:38.540 --> 13:40.610 Let me create this table. 265 13:43.620 --> 13:46.290 Now, again, the same thing I am doing over here. 266 13:46.290 --> 13:52.440 Begin Tran and then inserting the data and doing an end transaction. 267 13:56.640 --> 13:57.420 Hopefully, please. 268 13:58.320 --> 13:59.370 Tran is not there. 269 13:59.370 --> 14:00.090 Is that fine? 270 14:00.120 --> 14:02.070 Yeah, it will be fine. 271 14:02.070 --> 14:08.710 If we write Tran, then also it will be fine If we do not write Tran, then also it will be fine. 272 14:10.180 --> 14:13.960 Tran for our, uh, more, more readable purpose write. 273 14:13.960 --> 14:16.660 The transaction is begin over here. 274 14:18.790 --> 14:20.350 So it is more readable. 275 14:20.350 --> 14:21.520 You can say that. 276 14:25.570 --> 14:25.750 Okay. 277 14:25.750 --> 14:26.170 Got it. 278 14:26.170 --> 14:26.860 Thanks. 279 14:32.300 --> 14:36.620 Now, uh, over here, uh, this table we are creating. 280 14:36.620 --> 14:37.070 Right. 281 14:37.070 --> 14:40.700 So this command will create the table's structure. 282 14:42.080 --> 14:43.730 So please be on mute. 283 14:43.730 --> 14:46.400 Mute only. 284 14:46.760 --> 14:49.310 I think one person is waiting in the lobby. 285 14:52.580 --> 14:53.360 Okay. 286 14:56.280 --> 14:56.790 Yeah. 287 14:56.790 --> 15:00.810 Above command create table structure definition table structure. 288 15:00.810 --> 15:04.080 So this is known as a table structure and definition. 289 15:04.080 --> 15:10.710 We can say also metadata as well in which includes the data types its sizes. 290 15:10.710 --> 15:14.640 These are known as a size size data type. 291 15:14.640 --> 15:24.400 And these are the columns right now if I want to see the table structure of this particular table right 292 15:24.400 --> 15:32.200 department table or EMP table, then there is one command over here we can say that SP underscore help 293 15:33.610 --> 15:37.360 and then table name over here we need to specify table name. 294 15:37.360 --> 15:39.640 So let me execute this one. 295 15:42.600 --> 15:45.270 So these are the entire structure. 296 15:45.270 --> 15:46.590 You can see the. 297 15:48.990 --> 15:50.040 Of the table. 298 15:50.610 --> 15:51.720 So right. 299 15:51.720 --> 15:57.270 So actually over this is the table name right now who is the owner. 300 15:57.360 --> 15:59.460 Who is the owner of this table. 301 15:59.460 --> 16:03.900 So we can say that dbo and dbo is schema. 302 16:07.350 --> 16:09.330 Schema of this particular table. 303 16:09.330 --> 16:17.430 So can you uh, able to let me know which is the default schema in SQL server. 304 16:28.140 --> 16:29.160 Is that star. 305 16:30.220 --> 16:33.640 No, no, no, it's tempdb, I think. 306 16:33.880 --> 16:34.720 No, no. 307 16:35.860 --> 16:37.240 DBO is gone. 308 16:37.660 --> 16:39.160 Uh, yes, exactly. 309 16:39.580 --> 16:41.800 Uh DBO is the default schema. 310 16:41.800 --> 16:44.020 DB review is the default schema. 311 16:44.020 --> 16:45.880 So how it is default. 312 16:46.150 --> 16:51.100 So because over here while creating the dept table. 313 16:53.900 --> 16:58.670 While creating the DTP table, I haven't mentioned anything over here right before that. 314 16:58.790 --> 17:01.460 Before the IT part, I haven't mentioned anything. 315 17:01.460 --> 17:04.580 So suppose if you want to do create a schema right? 316 17:04.580 --> 17:06.800 Then create schema. 317 17:07.610 --> 17:10.610 You can give any name like uh temp. 318 17:11.300 --> 17:13.730 Let me check. 319 17:13.730 --> 17:14.120 Okay. 320 17:14.120 --> 17:17.580 Now it is executed right now if my table. 321 17:17.580 --> 17:30.540 Create a table, create table temp dot dept 20 507 id integer only just one column I want to keep now. 322 17:31.680 --> 17:34.770 Okay, let me try to create with the same name. 323 17:35.250 --> 17:36.930 Will this allowed or not? 324 17:36.930 --> 17:41.530 Can anyone tell me because already one table is already there. 325 17:41.530 --> 17:43.480 Dipped right now. 326 17:43.480 --> 17:44.440 Will this allow? 327 17:45.820 --> 17:49.330 I think yes, because the schema name is different. 328 17:49.720 --> 17:51.490 Okay, let me try. 329 17:52.270 --> 17:53.830 Yes, it is allowing me. 330 17:53.830 --> 17:54.250 Right. 331 17:54.250 --> 17:57.610 Same table name with different schema will be allowed. 332 18:02.230 --> 18:04.300 What is the purpose of creating like this? 333 18:04.880 --> 18:05.600 Yeah. 334 18:05.690 --> 18:09.740 Uh, the purpose of creating like this is in organization. 335 18:09.770 --> 18:11.750 There are many teams are working. 336 18:11.750 --> 18:12.020 Right? 337 18:12.020 --> 18:17.330 Finance team will be there, then HR team will be there there, then it team will be there. 338 18:17.330 --> 18:21.320 So we cannot create all the tables into one schema. 339 18:21.320 --> 18:21.950 Right. 340 18:21.950 --> 18:23.330 For finance purpose. 341 18:23.330 --> 18:25.100 Some ten tables are there. 342 18:25.100 --> 18:27.830 So we will create a schema called finance. 343 18:27.830 --> 18:33.660 And then ten tables will be allocated to finance only then for it. 344 18:33.720 --> 18:34.980 So 20 tables. 345 18:34.980 --> 18:41.850 If ten tables are there for IT department then all the ten table will be stored in the IT department. 346 18:41.850 --> 18:49.470 So we can easily yeah we can easily rectify. 347 18:49.470 --> 18:49.740 Right. 348 18:49.740 --> 18:51.960 We can easily rectify over here if you see. 349 18:51.960 --> 18:52.440 Right. 350 18:52.950 --> 19:00.010 I think mostly our debut only okay over here are if you see human resource department. 351 19:00.010 --> 19:00.580 Right. 352 19:00.580 --> 19:03.250 So this all are human resource schema. 353 19:03.250 --> 19:05.950 All tables are created in human resource schema. 354 19:06.250 --> 19:12.490 Then below one are there person information like person address person address type. 355 19:12.490 --> 19:18.050 So this person schema I have created for that person related table. 356 19:18.050 --> 19:22.790 So we can easily rectify or differentiate the table, right. 357 19:27.410 --> 19:29.840 That is the main purpose we can. 358 19:31.940 --> 19:33.350 You got it right. 359 19:33.860 --> 19:40.940 So which means that uh it's like an header which is created, uh, if somebody wants to get into human 360 19:40.940 --> 19:44.310 resource department, they will get into the human human resource department. 361 19:44.310 --> 19:50.910 And they can see that a DPO, uh, which is created inside it, uh, correct me if I'm wrong and also, 362 19:50.910 --> 19:58.410 no, no, say like a a debo debo created inside it means inside the human resource department. 363 19:58.410 --> 20:01.410 No, no, Debo is totally separate schema. 364 20:01.410 --> 20:03.810 Debo is totally separate schema. 365 20:04.920 --> 20:10.570 Uh, DBO is a default schema which is provided by SQL server. 366 20:11.200 --> 20:16.000 DBO is default schema, but this production I have created manually. 367 20:16.000 --> 20:18.760 Then human resource I have by. 368 20:25.840 --> 20:28.330 Production I have created manually. 369 20:28.330 --> 20:32.630 Human resources I have created manually, but I cannot create. 370 20:32.630 --> 20:34.610 Can we try to create? 371 20:39.830 --> 20:41.840 Because it is already exist, I think. 372 20:41.840 --> 20:42.410 Right? 373 20:46.310 --> 20:50.600 Either does not exist or you do not have permission to use it right now. 374 20:50.600 --> 20:57.250 Can I drop this schema drop schema dbo I think it will not allowed me. 375 20:58.360 --> 21:00.010 Cannot drop the schema dbo. 376 21:00.100 --> 21:02.740 But now can I drop this schema temp. 377 21:05.410 --> 21:06.220 Yeah. 378 21:09.280 --> 21:13.570 Cannot drop because it is referencing by the object department okay. 379 21:13.570 --> 21:18.220 So what we have to do firstly we need to uh drop this table. 380 21:18.550 --> 21:19.990 If we drop. 381 21:19.990 --> 21:22.330 Okay, we need to drop this table. 382 21:25.120 --> 21:26.590 Drop table. 383 21:27.730 --> 21:29.770 Now, I have dropped the table. 384 21:29.770 --> 21:31.390 Now it should allow me. 385 21:34.870 --> 21:35.350 Okay. 386 21:35.350 --> 21:37.540 Department 20 507. 387 21:37.600 --> 21:38.440 Okay. 388 21:40.000 --> 21:41.630 One more table is there. 389 21:44.330 --> 21:48.680 But DBO is a default schema so that we cannot drop it right. 390 21:52.460 --> 21:53.120 It's clear. 391 21:53.120 --> 21:57.770 So which means that uh now by default it will be created as a DPO. 392 21:58.250 --> 22:05.850 And if you want to create a meaningful name then we can create a schema and then we can start working. 393 22:05.850 --> 22:06.870 That's what you are trying to say? 394 22:07.050 --> 22:07.920 Yes yes yes. 395 22:07.920 --> 22:08.670 Right. 396 22:12.240 --> 22:14.580 So yeah, we are over here. 397 22:17.430 --> 22:18.210 Um. 398 22:20.370 --> 22:23.790 So now over here in the SPL, all the columns. 399 22:27.630 --> 22:29.460 Please be on mute. 400 22:32.290 --> 22:32.830 Yeah. 401 22:32.830 --> 22:33.400 Over here. 402 22:33.400 --> 22:35.440 All the column names are will be mentioned. 403 22:35.440 --> 22:37.780 Then all the data types will be mentioned. 404 22:38.140 --> 22:40.960 Then you can say size of the data type. 405 22:40.960 --> 22:47.470 So whatever we we have mentioned at the time of creating the table right over here in this employee 406 22:47.470 --> 22:50.110 table where it is okay. 407 22:50.290 --> 22:52.270 Over here employee number. 408 22:52.840 --> 22:55.850 So it is given the four length automatically. 409 22:59.180 --> 23:03.830 Then ten we have uh we have uh declared over here ten then nine over. 410 23:03.830 --> 23:08.660 Also we have declared over here then vector whether it is null label or not. 411 23:08.870 --> 23:11.510 So MP name okay. 412 23:11.930 --> 23:14.600 All this column are by default nullable. 413 23:14.870 --> 23:20.100 And can anyone tell me why this is not not nullable? 414 23:20.520 --> 23:22.170 Nullable is no over here. 415 23:22.170 --> 23:22.470 Right? 416 23:22.500 --> 23:23.760 Nullable is no. 417 23:26.820 --> 23:27.720 Because it's a PK. 418 23:28.470 --> 23:28.710 Yes. 419 23:28.710 --> 23:28.950 Right. 420 23:28.950 --> 23:30.330 Because it is a PK. 421 23:32.070 --> 23:34.920 Then these are the thing. 422 23:34.920 --> 23:35.460 No, no. 423 23:35.460 --> 23:37.440 Identity column is defined. 424 23:37.440 --> 23:41.100 So over here we have haven't defined any identity column. 425 23:41.860 --> 23:48.790 I will let you know what is identity column in future class said increment value or all this I will 426 23:48.790 --> 23:49.690 explain you. 427 23:50.200 --> 23:52.000 Now this is a primary key. 428 23:52.000 --> 23:57.190 Only one primary key is there right now in which column primary key has been defined. 429 23:57.190 --> 24:02.620 So that also it will indicate over here employee number column right. 430 24:04.270 --> 24:05.780 Employee number column. 431 24:06.290 --> 24:12.260 Then this is the primary key name in what primary key will provide. 432 24:12.260 --> 24:12.950 Facility. 433 24:12.950 --> 24:13.520 Clustered. 434 24:13.520 --> 24:14.210 Unique. 435 24:14.360 --> 24:15.020 Primary. 436 24:15.020 --> 24:16.550 Key located on primary. 437 24:16.550 --> 24:19.460 So what primary key will provide cluster. 438 24:19.460 --> 24:28.340 So primary key will automatically create the cluster index unique index on the column employee number. 439 24:28.340 --> 24:34.390 So whenever we define the primary key, it will automatically edit the cluster and unique index that 440 24:34.390 --> 24:35.740 it is showing over here. 441 24:35.950 --> 24:38.710 Then again this same name will be over here right. 442 24:38.710 --> 24:45.100 Constraint name index name and constraint name is the same primary key. 443 24:45.100 --> 24:46.270 It is a clustered index. 444 24:46.270 --> 24:49.270 It is created and constraint key. 445 24:49.270 --> 24:50.320 So what is the key. 446 24:50.320 --> 24:53.050 What is the column employee number we have. 447 24:54.610 --> 24:59.680 I will explain you one more table when we will create tomorrow or day after tomorrow class. 448 24:59.680 --> 25:01.330 So this is clear right. 449 25:01.330 --> 25:02.980 How in Oracle. 450 25:02.980 --> 25:12.130 Oracle I think you will mention describe I think if someone is working in Oracle then describe employee. 451 25:13.330 --> 25:21.410 And over here SP underscore help employee in SQL server in Oracle I think this type command is there. 452 25:23.930 --> 25:25.190 This is clear right. 453 25:25.190 --> 25:27.650 This uh information. 454 25:29.360 --> 25:29.630 Right. 455 25:29.630 --> 25:31.160 Yes, it is basic. 456 25:31.160 --> 25:33.620 It is basic but very important. 457 25:33.800 --> 25:36.320 You should be aware about it. 458 25:40.430 --> 25:41.300 Okay. 459 25:41.370 --> 25:46.530 Now, now let me check the data into the table. 460 25:53.580 --> 25:53.910 Okay. 461 25:53.910 --> 25:55.200 Data is there. 462 25:55.200 --> 26:03.480 Now we can insert the record into the, uh, table into, uh, many different ways. 463 26:03.480 --> 26:06.460 But right now we can say two different ways. 464 26:06.460 --> 26:09.550 So one is inserting single row. 465 26:09.550 --> 26:15.910 So what, uh, where it is, it is inserting the single row only. 466 26:15.910 --> 26:16.480 Right. 467 26:16.480 --> 26:24.520 Because I have end the end statement with this is called a semicolon. 468 26:24.520 --> 26:29.230 What we can call this one with this operator. 469 26:29.250 --> 26:29.790 Right. 470 26:30.030 --> 26:31.590 So over here. 471 26:32.640 --> 26:33.060 Operator. 472 26:33.660 --> 26:33.780 Uh. 473 26:33.780 --> 26:34.470 Which one? 474 26:36.090 --> 26:37.320 Semicolon only. 475 26:37.320 --> 26:37.800 Okay, okay. 476 26:37.800 --> 26:38.040 Okay. 477 26:38.040 --> 26:38.760 Semicolon. 478 26:39.060 --> 26:43.380 So semicolon indicates the end of a statement. 479 26:43.380 --> 26:45.180 End of a single statement. 480 26:45.210 --> 26:48.570 So over here we are inserting the one record at a time. 481 26:48.570 --> 26:49.920 So let me try it. 482 26:51.210 --> 26:57.730 Column name or number of supplied value does not match table definition okay. 483 26:58.510 --> 27:00.790 So now let me. 484 27:10.060 --> 27:13.090 Can you tell me what this error means. 485 27:17.260 --> 27:23.590 What this error means that we have not supplied all the columns values. 486 27:24.880 --> 27:25.420 Okay. 487 27:25.420 --> 27:25.660 Yes. 488 27:25.660 --> 27:25.900 Right. 489 27:25.900 --> 27:26.440 It is not. 490 27:26.440 --> 27:31.510 Regarding the data type we haven't supplied, we may miss the some of the columns. 491 27:31.510 --> 27:34.930 So let me check that one MP number. 492 27:34.930 --> 27:35.800 Is there name. 493 27:35.800 --> 27:37.090 Is there job. 494 27:37.090 --> 27:40.720 Is there manager I think manager. 495 27:40.720 --> 27:42.530 Is there their hire date. 496 27:42.560 --> 27:43.760 What is this? 497 27:43.790 --> 27:45.680 I think we have to put it in semicolon. 498 27:45.710 --> 27:46.070 Okay. 499 27:46.070 --> 27:46.700 Manager is. 500 27:46.700 --> 27:48.890 I think miss, the manager is missed. 501 27:49.910 --> 27:51.260 Let me check. 502 27:54.410 --> 27:55.340 Manager. 503 27:55.340 --> 27:57.080 We can give any one. 504 28:02.060 --> 28:04.850 Manager was missed then higher date. 505 28:04.970 --> 28:08.010 So higher date will be over here. 506 28:12.510 --> 28:14.760 So we need to mention the same order. 507 28:15.060 --> 28:16.350 Salary table was created, right? 508 28:16.830 --> 28:17.220 Yes. 509 28:17.220 --> 28:18.390 Into the same order. 510 28:18.390 --> 28:21.990 Salary is mentioned. 511 28:21.990 --> 28:23.790 Then depart commission. 512 28:23.790 --> 28:26.670 We can mention null and department. 513 28:26.670 --> 28:28.500 We can say one. 514 28:28.500 --> 28:30.250 Now let me try this one. 515 28:32.350 --> 28:32.740 Okay. 516 28:32.740 --> 28:34.030 Now it is successful, right? 517 28:34.030 --> 28:34.660 Right. 518 28:34.720 --> 28:37.390 In the same order we have to mention. 519 28:38.680 --> 28:41.710 Now let me firstly copy this one. 520 28:49.750 --> 28:51.790 We can set anything. 521 28:53.960 --> 28:57.140 So we are inserting one by one row at a time over here. 522 28:57.140 --> 28:57.650 Right. 523 28:57.650 --> 29:00.860 But now we can insert the multiple rows as well. 524 29:00.860 --> 29:03.380 So how we can insert the multiple rows. 525 29:03.380 --> 29:08.270 So if you see over here insert into the. 526 29:10.400 --> 29:13.130 I need to mention this. 527 29:13.130 --> 29:13.260 School. 528 29:28.110 --> 29:30.570 I have just mentioned over here only comma. 529 29:30.570 --> 29:31.620 Comma, right. 530 29:32.280 --> 29:34.590 I have mentioned over here only comma. 531 29:35.040 --> 29:38.730 Now let me try to insert this one. 532 29:40.500 --> 29:41.920 So we have separated. 533 29:41.920 --> 29:45.580 This is one row with comma and this is the second row. 534 29:45.580 --> 29:48.550 And then after that we have mentioned semicolon. 535 29:48.550 --> 29:50.500 Now now let me try to insert. 536 29:50.650 --> 29:53.020 It should indicate two rows inserted. 537 29:58.540 --> 30:01.120 It is indicating two rows inserted. 538 30:01.120 --> 30:01.660 Right. 539 30:02.530 --> 30:06.830 So this is how you this is how you can insert multiple rows as well. 540 30:06.860 --> 30:12.590 Two 2200 2000 any number of rows you will insert like this. 541 30:12.590 --> 30:20.780 By separating the rows with comma you can mention one row, one more row over here just the same way, 542 30:20.780 --> 30:22.460 and you can mention comma. 543 30:22.460 --> 30:31.560 So it will insert now three rows right now can you let me know whether it will allowed or not? 544 30:34.830 --> 30:35.430 It will not. 545 30:36.210 --> 30:37.110 It will not. 546 30:37.620 --> 30:37.980 Why? 547 30:37.980 --> 30:38.730 It will not allow. 548 30:38.760 --> 30:39.240 Why? 549 30:39.330 --> 30:40.170 Primary key. 550 30:40.260 --> 30:41.370 Already it is submitted, right? 551 30:41.370 --> 30:41.910 Okay. 552 30:41.940 --> 30:42.750 1.31.3. 553 30:42.750 --> 30:43.350 Same number. 554 30:43.950 --> 30:44.520 Okay. 555 30:44.520 --> 30:44.850 Yes. 556 30:44.850 --> 30:45.390 Right. 557 30:45.390 --> 30:46.230 Perfect. 558 30:46.230 --> 30:46.800 Primary key. 559 30:46.800 --> 30:47.190 Same. 560 30:48.330 --> 30:51.030 So it will provide us this error. 561 30:52.500 --> 30:52.890 Why? 562 30:52.890 --> 30:56.620 You have to, uh, remember or identify the error as well. 563 30:56.620 --> 30:59.050 That what kind of error it is providing. 564 31:06.250 --> 31:13.570 Now, now inserting null value so we can we have already inserted the null values right in some of the 565 31:13.570 --> 31:14.320 column. 566 31:14.440 --> 31:18.140 But now let me try to do one thing. 567 31:20.660 --> 31:21.290 Okay. 568 31:21.290 --> 31:23.240 Let me try to do one thing. 569 31:23.870 --> 31:27.800 I don't want to insert this one now. 570 31:27.800 --> 31:33.710 I want to insert like this one zero over here I can mention five. 571 31:34.370 --> 31:38.300 Now let me try to insert this will does it will allow okay. 572 31:38.300 --> 31:41.150 Column name and number of supplied value are not same right. 573 31:41.400 --> 31:44.610 But now if I mention over here. 574 31:51.570 --> 31:53.130 How can I solve this error. 575 31:53.130 --> 31:53.940 Can anyone know. 576 31:53.940 --> 31:54.780 Let me know. 577 31:56.760 --> 31:59.100 We can remove primary key from the employee number. 578 31:59.190 --> 32:02.340 No, no this is not primary key related error. 579 32:02.850 --> 32:03.990 See over here. 580 32:05.530 --> 32:08.500 Column name or number of supplier does not match table definition. 581 32:09.430 --> 32:12.580 Maybe put one more comma over there with a space of comma. 582 32:14.500 --> 32:17.920 We can add a semicolon. 583 32:18.040 --> 32:21.790 Know what I have done over here in this I have removed this one. 584 32:21.790 --> 32:22.330 Null. 585 32:22.450 --> 32:24.940 Over here I have removed null from here. 586 32:26.890 --> 32:30.680 So what we can do is, you know, when we say insert into table name. 587 32:30.680 --> 32:35.510 So instead of table name we can define the column names where we want to insert the values. 588 32:35.510 --> 32:35.960 Okay. 589 32:35.960 --> 32:36.380 One. 590 32:36.380 --> 32:37.250 Yeah one. 591 32:37.250 --> 32:44.930 We have to mention the table name but we need to mention the column name as well MP number ten comma 592 32:45.170 --> 32:52.640 e name ten comma job ten comma mgr ten comma. 593 32:52.640 --> 32:58.560 Higher date then comma cell and then comma dept. 594 32:58.740 --> 33:01.620 Why I have removed the commission. 595 33:05.970 --> 33:06.690 By by. 596 33:12.060 --> 33:12.570 Why? 597 33:12.570 --> 33:15.060 Why I have removed the commission over here. 598 33:54.170 --> 33:54.890 Hello? 599 33:58.670 --> 33:59.090 Oh, yes. 600 34:01.160 --> 34:01.790 Yeah. 601 34:01.790 --> 34:04.760 Uh, y I, I have removed the commission over here. 602 34:08.670 --> 34:09.300 Uh, where? 603 34:11.940 --> 34:12.420 Uh, please. 604 34:12.420 --> 34:12.600 See? 605 34:12.630 --> 34:13.080 See? 606 34:14.820 --> 34:16.440 Uh, yes, exactly. 607 34:16.740 --> 34:17.850 Over here. 608 34:18.390 --> 34:26.490 Uh, and now, if I insert the data over here, if I insert the data over here, one row is successfully 609 34:26.490 --> 34:27.390 inserted. 610 34:27.510 --> 34:30.340 But what value will be there in the commission column? 611 34:30.340 --> 34:31.540 It will be null, right? 612 34:37.810 --> 34:38.590 Hello? 613 34:39.040 --> 34:39.940 Yes, it will be. 614 34:39.940 --> 34:41.980 Yeah, yeah, it will be null, right. 615 34:45.370 --> 34:51.160 Instead of getting, you know, error message, we are defining the column and then we are passing the 616 34:51.160 --> 34:51.700 value. 617 34:54.290 --> 34:55.100 I guess it's right. 618 34:55.100 --> 34:55.640 Right. 619 35:08.090 --> 35:08.630 Okay. 620 35:15.050 --> 35:16.220 Now. 621 35:17.420 --> 35:17.780 Okay. 622 35:17.780 --> 35:20.670 now let's see the one more thing. 623 35:39.930 --> 35:43.630 Okay, now let me create one, one more table. 624 35:45.910 --> 35:50.140 I will show you the how constraint will be created on the table. 625 35:50.140 --> 35:52.840 And what is the purpose of the constraint. 626 35:52.870 --> 35:54.670 I will show you that one. 627 35:56.770 --> 35:58.300 Till now, no doubt. 628 35:58.300 --> 35:58.900 Right. 629 36:01.510 --> 36:02.650 Uh, no questions. 630 36:02.650 --> 36:06.800 But, uh, probably you need to explain, uh, what is all about constraint. 631 36:08.150 --> 36:09.110 Okay. 632 36:13.100 --> 36:20.390 So over here, uh, we can, uh, say that constraint are used to specify rules for data in a table. 633 36:20.390 --> 36:26.600 So whenever we insert the data into the table, we cannot insert anything. 634 36:26.600 --> 36:26.960 Right? 635 36:26.960 --> 36:30.570 We cannot insert anything like suppose the gender. 636 36:30.570 --> 36:38.790 If the gender is there, then maybe gender will be male, female or other may be there. 637 36:43.170 --> 36:50.730 So a minute ago you showed one insert and then you specify the column and then you mention the values 638 36:50.730 --> 36:51.150 to it. 639 36:51.330 --> 36:56.080 Can we say that that is a constraint method of uh, sorry. 640 36:56.470 --> 36:59.710 Uh, can you can you repeat? 641 37:00.670 --> 37:01.450 Okay. 642 37:01.450 --> 37:05.740 So in a minute ago, uh, you mentioned the column names as well. 643 37:05.950 --> 37:08.650 Employee number, employee name I can see in the screen. 644 37:08.650 --> 37:10.330 Oh, don't don't scroll up. 645 37:10.330 --> 37:11.320 Uh, scroll up a little bit. 646 37:11.320 --> 37:12.880 Scroll up, scroll up. 647 37:14.740 --> 37:15.400 Okay. 648 37:15.400 --> 37:22.430 In this probably, uh, here you mentioned the column names and the values to pass on, right? 649 37:22.520 --> 37:25.430 So can we say that this is a constraint method? 650 37:25.580 --> 37:28.460 Oh, no no no no, this is not a constraint. 651 37:28.460 --> 37:30.920 Constraint is only this one over here. 652 37:30.920 --> 37:31.850 Primary key. 653 37:33.110 --> 37:33.710 Okay. 654 37:34.700 --> 37:35.780 Constraint is over. 655 37:35.780 --> 37:37.340 Only this one over here. 656 37:37.340 --> 37:40.790 So uh let me tell you. 657 37:40.790 --> 37:47.370 So constraint will be basically to maintain the data integrity in the into the table. 658 37:47.370 --> 37:47.940 Right. 659 37:47.970 --> 37:53.040 So in the organization if you anyone is working in the company right. 660 37:53.040 --> 37:57.570 So every employee has a unique employee ID right. 661 37:57.570 --> 37:58.380 Every employee. 662 37:58.380 --> 38:01.500 No no employee is the same employee ID right. 663 38:01.500 --> 38:02.580 That is not possible. 664 38:02.580 --> 38:03.150 Right. 665 38:03.150 --> 38:04.650 In the same company. 666 38:04.650 --> 38:06.010 So what is that? 667 38:06.010 --> 38:11.980 That is, we can say that a primary key that we can say is a primary key constraint. 668 38:12.100 --> 38:18.040 Also, any employee doesn't have a employee ID at all. 669 38:18.040 --> 38:20.050 That is also not possible right. 670 38:22.330 --> 38:23.680 Key that m. 671 38:23.860 --> 38:29.260 So that is also we can say that a primary key constraint. 672 38:31.040 --> 38:32.900 all employee ID has a unique. 673 38:33.140 --> 38:39.020 All employee is a unique employee ID, and everyone has an employee ID so that we can call. 674 38:39.170 --> 38:43.070 Call one example of constraint that is primary key constraint. 675 38:43.160 --> 38:45.320 Then we can say that salary right. 676 38:45.350 --> 38:46.910 Salary of the employee. 677 38:47.000 --> 38:50.180 So salary cannot be in the negative right. 678 38:52.220 --> 38:55.090 Salary column cannot be in the negative. 679 38:55.090 --> 38:56.830 No one is -100 salary. 680 38:56.830 --> 38:57.400 Right. 681 38:57.400 --> 39:03.820 So that is also we can mention as a constraint that constraint will be known as a check constraint. 682 39:03.820 --> 39:12.430 Check say cell I will explain you over here in the this only that is known as a check constraint. 683 39:12.430 --> 39:19.270 So constraint is basically rules defined on a particular table to maintain the data integrity. 684 39:21.190 --> 39:27.820 Over here it is mentioned that the limit, the type of data that can go into the table like garbage 685 39:27.820 --> 39:30.160 data is meaningless, right? 686 39:30.190 --> 39:32.260 Garbage data will be meaningless. 687 39:32.560 --> 39:36.850 This ensures the accuracy and reliability of the data in the table, right? 688 39:37.600 --> 39:40.900 This will ensure the accuracy as well as reality. 689 39:40.900 --> 39:43.490 And if the violation between the constraint. 690 39:44.300 --> 39:52.970 Now suppose I have a gender column and in gender column male will be there, female will be there and 691 39:52.970 --> 39:54.260 other will be there. 692 39:55.040 --> 40:03.860 But if someone tried to insert apart from this three value, then it is a violation of the constraint 693 40:03.860 --> 40:06.900 right violation between the constraint in the data. 694 40:07.140 --> 40:16.800 So at that time, data uh query will throw the error, it will throw the error and data will not be 695 40:16.800 --> 40:17.640 inserted. 696 40:18.960 --> 40:20.940 Data will not be inserted. 697 40:20.940 --> 40:21.570 Right. 698 40:26.340 --> 40:26.880 Okay. 699 40:26.880 --> 40:29.940 Now now now let me show you this one as well. 700 40:31.030 --> 40:31.990 Over here. 701 40:31.990 --> 40:33.730 We have created this amp table. 702 40:33.730 --> 40:34.270 Right? 703 40:40.630 --> 40:41.140 Okay. 704 40:41.140 --> 40:42.610 Over here I will show you. 705 40:49.540 --> 40:51.010 Now suppose. 706 40:54.050 --> 41:00.260 I want to create one one more table with the name of MP one select. 707 41:00.380 --> 41:05.030 Uh, we can say that MP one. 708 41:05.030 --> 41:11.000 And I want to take all the data from the MP table into the MP one table. 709 41:11.330 --> 41:13.670 Then how how I can do it. 710 41:13.670 --> 41:14.060 Can. 711 41:14.060 --> 41:17.300 Does anyone has any idea about it? 712 41:19.680 --> 41:24.450 I think we can use that insert into statement insert into EMP one from EMP. 713 41:25.500 --> 41:30.390 I think insert into EMP one. 714 41:33.030 --> 41:36.150 Then like this. 715 41:39.690 --> 41:39.870 Uh. 716 41:39.870 --> 41:40.830 Like this. 717 41:42.430 --> 41:44.200 Uh, not this way. 718 41:44.230 --> 41:48.070 Uh, so insert into EMP one from EMP. 719 41:51.880 --> 41:53.020 From EMP. 720 41:57.910 --> 41:58.840 I have one. 721 42:00.100 --> 42:03.790 Uh, we have the create table employee. 722 42:03.820 --> 42:08.780 One table we can use the alias as star select star from employee. 723 42:09.710 --> 42:10.730 We can do that one. 724 42:13.160 --> 42:15.740 Uh, it means you want to create one. 725 42:15.740 --> 42:16.850 This EMP one table. 726 42:16.850 --> 42:17.390 Right? 727 42:18.260 --> 42:18.890 Yeah. 728 42:18.920 --> 42:24.950 Create table EMP one uh alias as uh, select star from employee table. 729 42:24.950 --> 42:32.460 Whatever the data in employee, we can copy it to the, uh, employee one MP, one select star from 730 42:32.760 --> 42:33.270 MP. 731 42:34.620 --> 42:39.270 We can mention it as uh in after MP one. 732 42:39.570 --> 42:40.260 Okay. 733 42:40.260 --> 42:42.450 Select star like this right. 734 42:42.990 --> 42:43.800 Yeah. 735 42:45.090 --> 42:46.500 Let me try. 736 42:46.500 --> 42:49.500 I think it will not work. 737 42:50.760 --> 42:52.920 MP one s select star from MP. 738 42:52.950 --> 42:56.680 I think you are from the Oracle background background, right? 739 42:59.500 --> 43:00.790 I think it will. 740 43:01.150 --> 43:02.380 I think it will work. 741 43:02.380 --> 43:08.860 Maybe it will be work in the Oracle, but in SQL server I think it is not uh working. 742 43:09.490 --> 43:12.640 So over here let me tell you how we can do it. 743 43:12.640 --> 43:14.650 Select star. 744 43:15.220 --> 43:19.670 Select star into MP one. 745 43:21.830 --> 43:23.120 From MP. 746 43:23.120 --> 43:24.410 Now let me try. 747 43:29.930 --> 43:32.990 You have seen right 21 rows are inserted. 748 43:32.990 --> 43:36.170 So now my MP table. 749 43:43.050 --> 43:45.690 Both table will have the same data. 750 43:45.690 --> 43:48.510 2122 142 are there right? 751 43:53.490 --> 43:57.060 So this is the command for that one select star into. 752 43:57.060 --> 43:58.890 So what it will do. 753 43:58.890 --> 44:01.470 It will create the table EMP one. 754 44:01.470 --> 44:09.880 At the same time it will insert the that all the data which is mentioned over here from this EMP table. 755 44:16.240 --> 44:17.710 You got it right. 756 44:17.830 --> 44:18.400 Yeah. 757 44:18.880 --> 44:23.080 But now now I, I have one another requirement. 758 44:23.590 --> 44:27.700 I don't want to insert the data, I just want the structure. 759 44:28.180 --> 44:30.590 I just want the structure of. 760 44:32.090 --> 44:33.200 Actually, very. 761 44:33.860 --> 44:37.760 Please be on mute whenever you are not speaking. 762 44:37.760 --> 44:39.500 Only at the time of speaking. 763 44:39.710 --> 44:44.660 Unmute and mute immediately, please. 764 44:46.640 --> 44:47.180 Yeah. 765 44:48.650 --> 44:55.230 So now what I want to do I want to I want only the structure of the table. 766 44:55.230 --> 45:01.650 Like employee name, job manager, hire date, salary, commission dept. 767 45:02.220 --> 45:09.480 I just want this column name with the data type, but I don't want to insert the data into that new 768 45:09.480 --> 45:11.580 table like emp2 table. 769 45:14.640 --> 45:16.050 Then what can I do? 770 45:21.070 --> 45:22.330 A create table. 771 45:23.020 --> 45:25.270 We can create a table with MP two. 772 45:26.800 --> 45:29.890 No no no, that syntax will not work over here. 773 45:36.550 --> 45:37.150 Any. 774 45:37.150 --> 45:38.500 Anyone has any idea. 775 45:38.530 --> 45:39.310 I think that will. 776 45:39.310 --> 45:41.590 This will work in Oracle as well. 777 45:59.090 --> 45:59.450 Okay. 778 45:59.450 --> 46:03.020 Let me show you what I have done over here in the previous query. 779 46:03.410 --> 46:07.530 I have to mention where one equal to two. 780 46:09.600 --> 46:11.520 Where one equal to two. 781 46:11.880 --> 46:22.800 Now let's see zero rows affected right now if I do this one select star from EMP two. 782 46:26.640 --> 46:28.950 Now my requirement is clear. 783 46:28.950 --> 46:33.670 Right now we have achieved our purpose, right? 784 46:38.500 --> 46:45.940 So this is small, small thing, but it is very useful when you are working in the real time environment 785 46:46.660 --> 46:50.800 or the, uh, what is that one equal to two. 786 46:50.800 --> 46:52.660 Where how did it defines that. 787 46:52.660 --> 46:53.890 The one and the two and all. 788 46:54.290 --> 46:55.340 Did we define anywhere else. 789 46:55.340 --> 46:55.820 No no no no. 790 46:56.030 --> 47:03.350 That actually actually what I have done, it is not defined anywhere. 791 47:03.350 --> 47:09.260 When we are doing one equal to two so is this condition is true or false. 792 47:13.280 --> 47:15.710 This condition is always false right. 793 47:15.710 --> 47:17.240 Always false. 794 47:18.060 --> 47:20.370 This one is never equal to two. 795 47:20.370 --> 47:21.750 Always false. 796 47:21.780 --> 47:28.020 So what it will do, it will only create the structure of this table. 797 47:28.020 --> 47:30.540 Not it will not pick up the data. 798 47:31.680 --> 47:32.310 Got it. 799 47:32.520 --> 47:35.100 But now one more, more thing you can do. 800 47:35.580 --> 47:38.190 Now I want to create this one. 801 47:38.190 --> 47:40.650 Now can you tell me what it will do? 802 47:42.160 --> 47:42.940 Records as well. 803 47:43.030 --> 47:44.920 Copy one is equal to one. 804 47:45.100 --> 47:46.570 Yeah, one is equal to one. 805 47:46.570 --> 47:48.010 But what it will do. 806 47:48.580 --> 47:49.480 Records as well. 807 47:49.510 --> 47:49.960 Yeah. 808 47:49.960 --> 47:50.710 It will do. 809 47:51.010 --> 47:51.610 It will. 810 47:52.090 --> 47:53.320 Yeah exactly. 811 47:53.320 --> 47:55.240 It will end this one. 812 47:55.240 --> 47:57.070 And this one. 813 47:57.400 --> 47:59.920 Both are same. 814 47:59.920 --> 48:00.670 Right. 815 48:04.120 --> 48:08.870 Whether we mention one equal to one or we do not mention one equal to one. 816 48:09.050 --> 48:10.160 Both are same. 817 48:10.160 --> 48:12.380 Both will do the same thing, right? 818 48:14.420 --> 48:19.340 It will copy the data into the other table. 819 48:21.770 --> 48:25.550 So that that is only I have right over here. 820 48:28.430 --> 48:32.550 Now this one we have seen Diddle diddle diddle comet. 821 48:38.010 --> 48:40.650 Now let's see for the identity column. 822 48:40.650 --> 48:43.620 We will see the constraint in detail level tomorrow. 823 48:43.620 --> 48:46.620 Let's see first today the identity column. 824 48:51.510 --> 49:11.410 So let me create any of the table like, uh, customer 25 seven Cust id integer identity one comma one. 825 49:13.450 --> 49:26.270 Then cust name where worker of ten, then cast address only. 826 49:26.270 --> 49:29.060 Keep keeping this this much of column. 827 49:31.280 --> 49:32.750 Now create. 828 49:32.750 --> 49:33.920 Let me create this table. 829 49:33.920 --> 49:38.240 This table is created now in insert. 830 49:38.240 --> 49:39.800 Let me insert the data into. 831 49:39.800 --> 49:40.940 Insert into. 832 49:41.850 --> 49:44.190 Does anyone know about identity? 833 49:46.890 --> 49:47.220 Yes. 834 49:47.220 --> 49:47.790 Yes. 835 49:48.960 --> 49:49.470 What? 836 49:49.500 --> 49:50.340 What it will do. 837 49:50.850 --> 49:54.120 It will add the increment numbers starting from one. 838 49:54.120 --> 49:55.830 So it will be like one, two, three, four. 839 49:55.830 --> 49:56.970 Automatically it will come. 840 49:57.300 --> 49:57.960 Okay. 841 49:57.960 --> 50:00.420 Now Harshit. 842 50:02.940 --> 50:04.710 Now let me know it. 843 50:05.370 --> 50:06.090 Yeah, yeah. 844 50:07.540 --> 50:09.280 I think we don't need to define one. 845 50:09.280 --> 50:10.420 It will automatically come. 846 50:10.420 --> 50:11.020 Yeah. 847 50:11.020 --> 50:12.100 Perfect. 848 50:13.570 --> 50:15.160 We don't need to define one. 849 50:15.160 --> 50:17.830 So let me do it. 850 50:17.860 --> 50:20.170 Now let me add one more record. 851 50:26.530 --> 50:27.280 Now what? 852 50:27.460 --> 50:27.940 Now what? 853 50:27.940 --> 50:28.840 It will define. 854 50:32.420 --> 50:33.020 Now. 855 50:33.020 --> 50:33.500 Now what? 856 50:33.500 --> 50:34.460 It will define. 857 50:38.690 --> 50:40.430 It will show those numbers one, two. 858 50:40.460 --> 50:41.810 Whatever records we have inserted. 859 50:42.020 --> 50:42.410 Okay. 860 50:42.410 --> 50:43.220 One, two. 861 50:43.220 --> 50:43.910 Okay. 862 50:46.100 --> 50:47.450 So one, two. 863 50:47.450 --> 50:51.290 It is showing right how it is defined as one two. 864 50:51.320 --> 50:59.370 Because over here in the identity column I have written over here, the identity keyword is a property 865 50:59.370 --> 51:00.690 in SQL server. 866 51:01.200 --> 51:04.380 Identity keyword is a property in SQL server. 867 51:04.380 --> 51:11.190 When a column table column is defined with identity property, its value will be auto generated. 868 51:11.760 --> 51:12.510 Over here. 869 51:12.510 --> 51:14.340 We haven't mentioned anything, right? 870 51:14.460 --> 51:16.620 Its value will be auto generated. 871 51:16.620 --> 51:18.090 Incremental value. 872 51:18.310 --> 51:21.190 And this value is increment. 873 51:21.220 --> 51:24.970 This value is created by SQL server automatically. 874 51:24.970 --> 51:25.510 Right. 875 51:27.190 --> 51:31.660 Therefore we can't manually enter a value into the an identity column. 876 51:31.780 --> 51:32.830 As a user. 877 51:35.230 --> 51:38.770 So up to here it is clear right now over here. 878 51:40.990 --> 51:41.860 This one. 879 51:41.860 --> 51:46.910 This one is known as a seed starting point. 880 51:46.910 --> 51:47.570 Basically. 881 51:47.570 --> 51:48.020 Yeah. 882 51:48.020 --> 51:54.590 We can say the starting point in this one is known as a increment number. 883 51:56.240 --> 51:56.720 Yeah. 884 51:56.720 --> 51:58.970 Increment value or increment number. 885 51:58.970 --> 52:00.200 Anything you can say. 886 52:00.230 --> 52:03.620 Now suppose if I do like this. 887 52:08.130 --> 52:11.370 Now, what will happen now? 888 52:11.370 --> 52:14.130 It will show like ten, 20, 30, 40. 889 52:17.940 --> 52:18.930 Perfect. 890 52:23.190 --> 52:25.740 Now let me insert the data into the same. 891 52:31.180 --> 52:32.620 1020 right. 892 52:34.150 --> 52:38.410 But now suppose I will tell you one more thing. 893 52:40.030 --> 52:45.610 Now, suppose I want to insert the data into the identity column as well. 894 52:46.090 --> 52:47.380 Is it possible? 895 52:52.240 --> 52:53.350 I think no. 896 52:54.890 --> 52:55.640 Over here. 897 52:55.640 --> 52:56.960 What it is written. 898 52:56.960 --> 52:58.700 Read this last line. 899 53:01.310 --> 53:05.780 Therefore, we can't manually enter a value into an identity column as a user. 900 53:07.460 --> 53:10.310 But what is written over here at the last. 901 53:15.200 --> 53:16.910 Some conditions we can define. 902 53:16.910 --> 53:18.450 I think then it is possible. 903 53:18.990 --> 53:22.950 So now what we have to do. 904 53:23.760 --> 53:24.150 Okay. 905 53:24.150 --> 53:27.000 Now let me try what error it is showing me. 906 53:29.460 --> 53:30.210 Okay. 907 53:33.750 --> 53:36.180 Now understand. 908 53:39.840 --> 53:40.860 This error. 909 53:42.310 --> 53:43.720 Understand this error. 910 53:43.720 --> 53:45.580 This error is very important. 911 53:50.710 --> 53:51.850 Carefully. 912 53:55.930 --> 53:56.470 What? 913 53:56.470 --> 54:01.870 It is written an explicit value for the identity column in the table. 914 54:02.380 --> 54:11.240 This is a table name can only be specified when a column list is used and insert identity. 915 54:11.240 --> 54:13.160 Insert in is own. 916 54:13.640 --> 54:16.490 So now what I have to do set. 917 54:37.320 --> 54:39.390 Now let me try this one. 918 54:44.580 --> 54:47.850 First I am doing set identity insert on. 919 54:50.640 --> 54:52.230 Now let me try. 920 54:53.100 --> 54:54.150 It will not work. 921 54:54.160 --> 54:54.760 Why? 922 54:55.900 --> 54:57.580 What is the problem? 923 54:57.730 --> 54:59.590 Now can anyone tell me? 924 55:02.860 --> 55:05.170 We have to off it again. 925 55:05.320 --> 55:06.280 No no no no no no. 926 55:06.520 --> 55:07.840 It was by default. 927 55:07.840 --> 55:08.170 Can you. 928 55:08.500 --> 55:14.500 Can you show the, uh, commands and then the message as well, so that we can correlate things? 929 55:14.650 --> 55:15.190 Okay. 930 55:15.190 --> 55:15.670 Yeah. 931 55:15.910 --> 55:18.340 Uh, to compare it and, you know. 932 55:18.350 --> 55:18.770 Yeah. 933 55:18.770 --> 55:20.150 Message as well as the. 934 55:20.270 --> 55:20.690 Yeah. 935 55:20.690 --> 55:23.930 This is command message is above one everything. 936 55:26.060 --> 55:26.870 Over here. 937 55:31.340 --> 55:36.260 Set by default sent identity insert will be off only we have done the own. 938 55:36.590 --> 55:37.400 What now? 939 55:37.400 --> 55:38.840 Read the message again. 940 55:39.170 --> 55:40.790 Read the message two times. 941 55:40.790 --> 55:42.380 Read the message three times. 942 55:51.960 --> 55:54.060 Any idea you are getting? 943 56:07.330 --> 56:08.980 Or I will let you know. 944 56:09.820 --> 56:14.740 I think, uh, I think we have to define the column name along with the table name. 945 56:14.740 --> 56:19.900 And when we are defining insert into table name in brackets, we have to define the column name as well. 946 56:19.900 --> 56:21.130 Perfect. 947 56:24.070 --> 56:29.020 Because it says cust id when column name is used column list is used. 948 56:32.270 --> 56:33.710 Now let me try. 949 56:36.350 --> 56:38.240 Now it will be 50. 950 56:38.240 --> 56:38.750 Right? 951 56:38.750 --> 56:43.460 And now, once the your purpose will be solved, what you have to do. 952 56:46.100 --> 56:47.360 Set identity. 953 56:47.360 --> 56:48.410 Insert off. 954 56:48.500 --> 56:54.290 Now, now if you insert the data into this, select uh this table. 955 56:55.230 --> 56:58.380 the last one record will be there 50 is there. 956 56:59.970 --> 57:00.690 Right. 957 57:02.010 --> 57:06.990 What you have done, you have manually inserted the data into the identity column as well. 958 57:09.510 --> 57:12.000 It is available right? 959 57:12.990 --> 57:13.710 Right, right. 960 57:15.150 --> 57:18.300 So this is all about the identity property. 961 57:22.150 --> 57:25.960 Now the main thing we will see the constraint tomorrow. 962 57:26.470 --> 57:30.910 Then I will explain you normalization as well and other topics. 963 57:30.910 --> 57:32.710 We will go day by day. 964 57:33.730 --> 57:36.820 So in today's class everything is clear right. 965 57:37.120 --> 57:37.900 One minute.