1 00:02.300 --> 00:02.960 Okay. 2 00:02.960 --> 00:09.170 So today we are going to cover the cursor cursor in SQL. 3 00:10.130 --> 00:12.230 So one thing is cursor. 4 00:12.260 --> 00:13.370 Actually. 5 00:16.220 --> 00:18.530 Let's understand first what is a cursor. 6 00:18.530 --> 00:23.210 Cursor process rose from a result set one at a time. 7 00:23.630 --> 00:27.830 So it is not processing all the rows from the result set. 8 00:27.860 --> 00:33.020 It will processing one row and it will do some operation on that one row. 9 00:33.470 --> 00:36.650 Then second row it will do some operation on the second row. 10 00:36.680 --> 00:43.160 Like that it will process rows one by one row from the result set. 11 00:43.160 --> 00:45.980 And that is why it is slower in performance. 12 00:46.610 --> 00:52.640 Cursor is slower in performance and it is very rarely used now. 13 00:52.640 --> 00:55.340 Result set is stored in SQL server. 14 00:56.030 --> 01:04.160 Cursor is a database object that allows us to retrieve each row at a time and manipulate its data. 15 01:05.990 --> 01:12.740 So at one time it will retrieve one row and do some perform do some manipulation. 16 01:13.220 --> 01:16.910 It is like a pointer to a row cursor. 17 01:16.910 --> 01:22.370 I are used to access the result set row by row from T-sql program. 18 01:22.370 --> 01:29.720 If we submit a query to SQL server, it goes to the database and gets the data from the table and copies 19 01:29.720 --> 01:31.730 it into temporary memory. 20 01:33.800 --> 01:42.590 Using cursor, we can give name to that memory and access the result set row by row and do the processing 21 01:42.620 --> 01:43.580 as per need. 22 01:44.660 --> 01:48.680 So we will understand that thing by one example. 23 01:48.680 --> 01:56.630 So this is how we can create the cursor steps to use a cursor or life cycle of a castle. 24 01:56.960 --> 01:58.700 How we can create the cursor. 25 01:58.730 --> 01:59.330 Right? 26 02:00.530 --> 02:04.190 So number one will be we have to declare the cursor. 27 02:04.220 --> 02:08.120 Number one will be we have to declare the cursor. 28 02:08.780 --> 02:10.730 Then we have to open the cursor. 29 02:12.740 --> 02:16.190 Once the cursor is declared we have to open the cursor. 30 02:16.520 --> 02:23.000 Once the cursor is open, we have to fetch the records from the cursor into some variables. 31 02:23.930 --> 02:29.060 We can say that into some variables. 32 02:30.440 --> 02:37.400 After fetching the records from the cursor, we can close the cursor and deallocate the cursor. 33 02:38.660 --> 02:41.090 So it will release the memory, right? 34 02:41.120 --> 02:43.190 Deallocation means it will. 35 02:49.970 --> 03:07.220 Used to store the cursor right Now in in any cursor, we have to, uh, use these five commands mandatory 36 03:07.250 --> 03:09.470 while we are creating the cursor. 37 03:09.770 --> 03:11.360 Now declare a cursor. 38 03:11.360 --> 03:12.890 How we can declare the cursor. 39 03:12.890 --> 03:14.150 It is simple. 40 03:14.930 --> 03:15.830 Declare. 41 03:17.960 --> 03:18.650 Cursor. 42 03:18.860 --> 03:19.040 Uh. 43 03:19.040 --> 03:19.580 Declare. 44 03:19.610 --> 03:20.180 Cursor. 45 03:22.670 --> 03:23.450 Cursor. 46 03:23.450 --> 03:29.060 For select statement, we have to use select statement while we have to declare the cursor. 47 03:29.930 --> 03:32.750 Just define the select statement to be used. 48 03:34.220 --> 03:35.570 Declare seven. 49 03:35.570 --> 03:37.130 So what is this seven? 50 03:37.250 --> 03:39.920 Seven is a cursor name. 51 03:39.920 --> 03:41.720 Seven is a cursor name. 52 03:42.500 --> 03:44.870 Then cursor four. 53 03:44.900 --> 03:46.850 We have to use this for keyword. 54 03:47.210 --> 03:54.230 And over here we have to write, uh write any of the select statement which we want to use further. 55 03:54.230 --> 03:58.870 Right Now this is my C1 cursor is declared. 56 03:58.900 --> 04:01.330 This is my C1 cursor is declared. 57 04:01.510 --> 04:05.440 Now after the declare, what is the second point? 58 04:06.070 --> 04:08.050 We have to open the cursor, right? 59 04:08.080 --> 04:09.490 We have to open the cursor. 60 04:09.490 --> 04:11.350 So open C1. 61 04:12.550 --> 04:15.970 We have to know what is our cursor name. 62 04:16.210 --> 04:17.830 Our cursor name is C1. 63 04:17.830 --> 04:19.750 So we have to open C1. 64 04:21.490 --> 04:26.020 Right now select statement will be sent to SQL server. 65 04:27.160 --> 04:30.400 So when we are doing the open C1 what will happen. 66 04:30.400 --> 04:33.130 Select statement will be sent to SQL server. 67 04:34.030 --> 04:38.530 The data returned by the Select statement will be saved in the temporary memory. 68 04:40.780 --> 04:43.570 In cursor seven points to temporary memory. 69 04:46.210 --> 04:49.480 So now open seven. 70 04:54.130 --> 04:56.620 Select statement will be sent to SQL server. 71 04:56.650 --> 05:03.460 The data returned by the Select statement will be saved in temporary memory and cursor seven points 72 05:03.460 --> 05:04.900 to temporary memory. 73 05:04.930 --> 05:07.120 Now we have to fetch the record. 74 05:07.120 --> 05:10.330 Once we have, we are opening the cursor. 75 05:10.600 --> 05:13.150 After that we have to fetch the records. 76 05:14.500 --> 05:17.500 After that, we have to fetch the records from the cursor. 77 05:17.710 --> 05:21.850 So fetch statement is used to fetch records from cursor. 78 05:22.630 --> 05:27.070 Fetch statement fetches one row at a time, but to fetch multiple rows. 79 05:27.100 --> 05:31.120 Fetch statement should be executed multiple times. 80 05:32.110 --> 05:36.940 And how can we execute some statement multiple times in SQL? 81 05:37.000 --> 05:43.660 So for that purpose we need to write this statement inside a loop right. 82 05:43.810 --> 05:47.050 So first statement should be used inside a loop. 83 05:47.230 --> 05:55.090 If we want to fetch the multi uh statement multiple times right Now what is the syntax? 84 05:55.810 --> 05:59.650 Fetch next from cursor name into variables. 85 05:59.920 --> 06:04.960 So for that purpose we have to declare the variable. 86 06:04.960 --> 06:11.560 And we have to store the store the value of the fetch statement inside some variables. 87 06:14.710 --> 06:18.640 So we will understand it practically in some time now. 88 06:20.380 --> 06:23.380 After the fetching we have to close the cursor. 89 06:23.410 --> 06:26.560 After the fetching we have to close the cursor right. 90 06:27.460 --> 06:31.750 So when the desired data is fetched cursor should be closed. 91 06:32.590 --> 06:34.180 How we can close the cursor. 92 06:34.210 --> 06:35.500 Close cursor name. 93 06:35.500 --> 06:36.670 Close seven. 94 06:38.380 --> 06:41.560 Close seven and deallocate the cursor. 95 06:41.590 --> 06:44.170 Finally, the cursor should be removed. 96 06:44.500 --> 06:47.230 Finally, the cursor should be removed. 97 06:47.260 --> 06:49.120 Deallocates See1. 98 06:49.900 --> 06:58.060 So even after closing the cursor, we need to explicitly deallocate the cursor so that it will be removed 99 06:58.060 --> 07:01.900 permanently right from the memory only. 100 07:02.410 --> 07:06.100 Right now, what is a fetch status? 101 07:06.610 --> 07:17.380 So while we are using the loop inside the cursor, we have to specify the exit condition or fetch status 102 07:17.410 --> 07:18.130 is something. 103 07:18.130 --> 07:23.200 It is a system variable that tells that tells the status of fetch. 104 07:23.230 --> 07:27.670 Using this we can find whether the fetch statement is successful or not. 105 07:28.510 --> 07:29.350 Zero. 106 07:29.380 --> 07:30.700 Fetch is successful. 107 07:30.700 --> 07:30.970 One. 108 07:30.970 --> 07:32.650 Fetch is unsuccessful. 109 07:33.820 --> 07:37.570 Now let's write our first program using a cursor. 110 07:39.820 --> 07:43.060 Now write our first program using a cursor. 111 07:44.170 --> 07:50.980 So write a program to print all employee names and salaries from employee MP one. 112 07:51.610 --> 07:53.230 Table using cursor. 113 07:54.220 --> 08:00.610 Write a program to print all employee names and salaries from EMP one table using cursor. 114 08:01.570 --> 08:06.220 So now our requirement is we want name and salary right. 115 08:07.090 --> 08:13.900 We want name and salary information from EMP one table of all the employees. 116 08:14.530 --> 08:17.590 So we have to declare two variables right. 117 08:18.970 --> 08:21.580 So what is the first statement. 118 08:21.700 --> 08:23.350 Declare c one. 119 08:23.710 --> 08:24.970 Declare c one. 120 08:24.970 --> 08:26.560 This is my cursor name. 121 08:26.650 --> 08:30.550 Now cursor I have to write and for I have to write. 122 08:30.850 --> 08:33.100 Now I have to write a select statement. 123 08:33.100 --> 08:35.590 Right I have to write a select statement. 124 08:36.340 --> 08:38.350 So what is my Select statement. 125 08:38.920 --> 08:45.580 My Select statement will be I want employee name and salary of all the employees. 126 08:46.300 --> 08:50.620 I want salary and employee name of all the employees. 127 08:50.770 --> 08:53.680 So this is my select statement. 128 08:53.710 --> 08:57.970 So I need to store this employee name and salary into some variable. 129 08:58.000 --> 08:58.600 Right. 130 08:58.960 --> 09:03.670 I need to store this employee name in salary into some variable. 131 09:05.170 --> 09:08.350 So for that purpose I have declared two variables. 132 09:08.350 --> 09:09.910 Declare at the rate name. 133 09:10.150 --> 09:13.660 Worker of 20 and salary of money. 134 09:15.490 --> 09:17.770 Now these two thing will be done. 135 09:17.800 --> 09:19.930 Now I have to open the cursor. 136 09:19.930 --> 09:20.260 Right. 137 09:20.260 --> 09:21.700 I have to open the cursor. 138 09:21.700 --> 09:24.070 So this is how we can open. 139 09:24.280 --> 09:25.780 Open C one. 140 09:26.860 --> 09:28.240 Open C one. 141 09:28.390 --> 09:31.180 Now after opening what is the next step. 142 09:32.410 --> 09:34.000 We have to fetch the records. 143 09:34.000 --> 09:34.330 Right. 144 09:34.360 --> 09:36.340 We have to fetch the records. 145 09:37.600 --> 09:39.010 We have to fetch the records. 146 09:39.010 --> 09:39.640 Right. 147 09:42.670 --> 09:45.070 So for that purpose. 148 09:45.100 --> 09:45.610 Fetch. 149 09:45.640 --> 09:50.760 Next we have to write Fetch next from C1. 150 09:51.420 --> 09:52.650 Into where? 151 09:53.100 --> 09:57.150 Into where we can store the result of employee name and salary. 152 09:57.180 --> 10:01.260 We can store the result of employee name and salary into variables. 153 10:02.430 --> 10:11.220 So over here into first variable it will store employee name and second variable it will store salary 154 10:11.220 --> 10:11.940 value. 155 10:12.240 --> 10:17.190 So fetch next from C1 into two variables. 156 10:17.820 --> 10:23.400 If I have three columns over here then I have to declare over here three variables. 157 10:23.400 --> 10:28.470 And I have to fetch from C1 into three variables. 158 10:31.590 --> 10:34.500 Now while fetch status equal to zero. 159 10:37.590 --> 10:40.290 Now while fetch status equal to zero. 160 10:42.120 --> 10:48.540 So this condition we have to use to exit the loop to exit the loop. 161 10:48.540 --> 10:51.210 We need to use this specific condition. 162 10:52.380 --> 10:54.300 Then what I want to do. 163 10:56.430 --> 11:01.380 Begin print employee name and salary. 164 11:01.980 --> 11:04.110 Print employee name and salary. 165 11:04.260 --> 11:05.940 And again, I have to fetch. 166 11:05.970 --> 11:07.110 Again, I have to fetch. 167 11:07.110 --> 11:12.180 Because if I do not write this statement. 168 11:12.180 --> 11:15.570 If I do not write this statement, let me see what will happen. 169 11:18.240 --> 11:20.640 Let me run this query. 170 11:23.010 --> 11:24.660 See over here. 171 11:28.140 --> 11:30.120 It is not ending my loop, right? 172 11:30.120 --> 11:31.680 It is not ending my loop. 173 11:32.040 --> 11:32.610 It is. 174 11:32.640 --> 11:34.140 It become infinite loop. 175 11:34.170 --> 11:34.770 See? 176 11:36.210 --> 11:42.090 So now again I have to fetch next from C1 into fetch. 177 11:42.120 --> 11:44.430 Next from c1 into same variable. 178 11:44.460 --> 11:54.210 So this statement we have to write two times one before the while loop and one inside the while loop 179 11:54.210 --> 11:55.980 before the end condition. 180 11:57.540 --> 12:03.480 Because one record it will store then second record it will store third record it will store. 181 12:04.050 --> 12:07.080 So like the same way we have to use. 182 12:07.080 --> 12:08.040 So that is why. 183 12:08.070 --> 12:15.030 See now my this is my result right. 184 12:15.060 --> 12:16.140 This is my result. 185 12:16.140 --> 12:17.640 This is what I want. 186 12:21.780 --> 12:23.730 This is what I want. 187 12:23.760 --> 12:26.430 Now let me know if you have any doubt. 188 12:34.320 --> 12:35.640 Need a cursors? 189 12:35.670 --> 12:39.300 What have you ever used in your life? 190 12:39.360 --> 12:41.220 You know the this curses and all. 191 12:43.530 --> 12:53.070 Yeah actually, sometimes in the programming or development, in SQL development, cursors are when 192 12:53.070 --> 12:56.430 there is no other way in front of us at that time. 193 12:56.430 --> 13:01.800 Maybe they are using cursors, but I haven't used cursors. 194 13:01.800 --> 13:04.350 Many I haven't used cursors. 195 13:06.900 --> 13:08.850 Was it creating a database or not? 196 13:08.880 --> 13:09.690 It's not right. 197 13:09.720 --> 13:11.160 It's just a temporary memory. 198 13:11.160 --> 13:14.580 It will store it and it will, uh, goes off. 199 13:14.580 --> 13:15.000 Right? 200 13:15.240 --> 13:19.410 Yes, it's right, but it's storing the entire table as well if I. 201 13:20.970 --> 13:21.390 Okay. 202 13:21.390 --> 13:24.060 Now what what. 203 13:24.060 --> 13:26.100 We can see the actual use of cursor. 204 13:26.100 --> 13:28.020 Let me remove this one. 205 13:28.620 --> 13:38.370 Now suppose suppose there is some requirement is that we have a several databases. 206 13:38.400 --> 13:38.880 Are there. 207 13:38.880 --> 13:39.510 Right. 208 13:39.510 --> 13:42.000 Several databases are there. 209 13:42.000 --> 13:53.370 And we want to take the backup of the databases occasionally or on a daily basis or weekly basis, right? 210 13:54.090 --> 14:06.030 So at that time, we cannot do do the backup, like right clicking of one, one, one single database. 211 14:06.030 --> 14:07.830 And we cannot do like this. 212 14:07.830 --> 14:08.430 Right. 213 14:08.580 --> 14:11.370 Every time we cannot do and click okay. 214 14:11.400 --> 14:12.990 It will take the backup. 215 14:13.350 --> 14:16.350 Now again right click on one more database. 216 14:16.560 --> 14:19.080 What if 100 databases are there? 217 14:19.170 --> 14:20.820 It is a good process. 218 14:20.850 --> 14:23.310 No, it is not a good process to do. 219 14:23.340 --> 14:27.630 Right click 100 times and store the backup right? 220 14:32.370 --> 14:37.860 So for that purpose, write a program to backup selected databases. 221 14:37.920 --> 14:42.840 So now over here we have some AP databases is their examples. 222 14:42.840 --> 14:45.840 Database is their and product orders databases their. 223 14:46.320 --> 14:51.780 So I want to back up this databases on weekly basis. 224 14:51.780 --> 14:53.190 Suppose weekly basis. 225 14:53.190 --> 14:58.980 So for that purpose also we can use the cursor for that purpose. 226 14:58.980 --> 15:00.990 Also we can use the cursor. 227 15:01.230 --> 15:02.880 Now how we can use. 228 15:03.030 --> 15:04.860 So see over here. 229 15:07.110 --> 15:11.760 Over here I have store declare the variable of db name. 230 15:12.960 --> 15:19.290 Which of the database name databases I want to take the backup. 231 15:21.150 --> 15:22.680 Now what is the path? 232 15:22.710 --> 15:24.240 What is the path? 233 15:24.240 --> 15:26.250 I want to store the backup. 234 15:27.330 --> 15:28.500 In which path? 235 15:28.500 --> 15:33.540 I want to store the backup now declare. 236 15:35.550 --> 15:36.840 This is my cursor name. 237 15:36.840 --> 15:38.130 Declare db list. 238 15:38.160 --> 15:39.390 Declare db list. 239 15:39.390 --> 15:42.740 Cursor for now. 240 15:42.950 --> 15:52.940 There is one table in which all the database names are stored, so select star from Sis databases. 241 15:53.960 --> 15:56.240 Select star from Sis databases. 242 15:56.330 --> 16:00.680 So this is my all the database name available in this server. 243 16:03.080 --> 16:10.610 And I want only database name right I want only name from this table I don't want any other information 244 16:11.780 --> 16:15.980 I don't this information is not required for me as of now right. 245 16:17.570 --> 16:21.920 So I have written this select statement. 246 16:21.920 --> 16:24.170 Written this select statement. 247 16:24.320 --> 16:34.640 Select name from the system databases where name in these three databases, this app examples and product 248 16:34.670 --> 16:36.440 orders are my databases. 249 16:37.160 --> 16:40.340 I want to store the backup of these three databases. 250 16:41.000 --> 16:44.660 So declare cursor name cursor for select. 251 16:44.690 --> 16:46.760 This is my Select statement right? 252 16:48.110 --> 16:49.760 This is my Select statement. 253 16:49.790 --> 16:52.520 Now open I have to open the DB list. 254 16:52.820 --> 16:58.280 Fetch next from DB list into where into this database name variable right. 255 17:00.290 --> 17:07.640 Now from where I have to fetch from until where guys one minute. 256 17:10.760 --> 17:15.920 Now we want to take the take the backup of these three databases. 257 17:16.010 --> 17:18.260 Now we have to set the path right. 258 17:18.500 --> 17:21.110 We have to store the data somewhere. 259 17:21.230 --> 17:23.240 So let me store it over here. 260 17:23.240 --> 17:25.700 Select E drive DB backup. 261 17:25.880 --> 17:30.830 So see over here this is my folder. 262 17:30.950 --> 17:34.610 This is my folder is there right DB backup. 263 17:35.180 --> 17:36.680 And it is currently empty. 264 17:36.710 --> 17:47.030 Right Now I am storing this entire path E drive, DB backup, edit db name and dot back so any of the 265 17:47.030 --> 17:50.600 database file will have database backup file. 266 17:50.600 --> 17:52.490 We have this extension. 267 17:52.610 --> 17:57.080 We have this extension write dot back. 268 17:57.770 --> 18:01.670 Now backup database edit db name to disk. 269 18:02.810 --> 18:04.850 So this is a command you have to remember. 270 18:04.880 --> 18:09.770 Backup database edit db name to disk edit queue path. 271 18:10.640 --> 18:13.760 So at first time it will take the backup of AP. 272 18:14.510 --> 18:17.240 Now one second time it will run. 273 18:17.780 --> 18:24.680 It will take the backup of this examples into the DB name. 274 18:24.920 --> 18:29.600 And it will store over here in this in this path. 275 18:29.600 --> 18:32.450 Right in this path queue path. 276 18:35.030 --> 18:40.970 A third time it will take the this disk name, product orders name it will store into this particular 277 18:40.970 --> 18:41.570 path. 278 18:42.170 --> 18:45.290 And after that you have to close and deallocate. 279 18:45.410 --> 18:47.030 So now see this step? 280 18:47.150 --> 18:48.320 This one is empty. 281 18:48.320 --> 18:53.570 Currently if I execute this query let me execute this query. 282 18:56.210 --> 18:58.940 See it is giving something like this right. 283 18:59.480 --> 19:04.820 Backup database successfully processed 298 pages in something. 284 19:04.820 --> 19:05.540 Something. 285 19:05.960 --> 19:07.910 Now if I go to this path. 286 19:07.940 --> 19:09.020 See over here. 287 19:12.140 --> 19:13.280 See over here? 288 19:13.280 --> 19:14.750 It is there. 289 19:14.750 --> 19:15.050 Right? 290 19:15.050 --> 19:16.010 It is there. 291 19:25.280 --> 19:28.370 Now let me know if you have any question in this one. 292 19:28.370 --> 19:29.720 Let me know 293 19:36.680 --> 19:37.730 This is okay. 294 19:37.760 --> 19:38.030 Fine. 295 19:38.030 --> 19:39.380 But, um. 296 19:39.530 --> 19:40.070 Yes. 297 19:40.070 --> 19:40.340 Go ahead. 298 19:40.340 --> 19:41.180 Krishna, please. 299 19:41.300 --> 19:41.930 Sorry. 300 19:43.070 --> 19:43.310 Uh. 301 19:43.340 --> 19:44.120 No issues. 302 19:44.180 --> 19:47.060 So, like, uh, could you please repeat this one? 303 19:47.060 --> 19:51.680 Like I like, I'm getting confused and in the while loop statement here. 304 19:53.510 --> 20:02.450 Okay, so actually we have declared this two variable which database name we want to take the backup. 305 20:02.450 --> 20:06.140 And from where from which path we want to take the backup. 306 20:06.140 --> 20:06.770 Right. 307 20:07.220 --> 20:08.030 In which path? 308 20:08.030 --> 20:09.530 We want to take the backup. 309 20:09.590 --> 20:12.740 So at the fetch status is something. 310 20:12.740 --> 20:21.290 So until this this query actually this query it will take take right. 311 20:21.320 --> 20:23.570 This query it will take into the DB list. 312 20:23.600 --> 20:24.380 Right. 313 20:25.070 --> 20:28.520 So it will we have a three rows over here at the moment. 314 20:28.520 --> 20:32.570 But we have to process each row one by one. 315 20:32.570 --> 20:32.990 Right. 316 20:32.990 --> 20:35.390 We have to process each row one by one. 317 20:36.050 --> 20:41.150 We have to process each row one by one so we can process it into the while loop only. 318 20:41.180 --> 20:41.810 Right? 319 20:42.650 --> 20:45.740 So while at the red phase state is equal to zero. 320 20:45.770 --> 20:53.090 So while all of these three row will be processed, the first traces will become zero. 321 20:54.680 --> 20:57.890 So at that time the loop will be stopped. 322 20:58.670 --> 21:00.800 At this time the loop will be stopped. 323 21:00.830 --> 21:04.940 So we can say that this is a exit condition. 324 21:08.060 --> 21:10.430 This is a exit condition for the loop. 325 21:10.730 --> 21:15.560 So open DB list fetch next from the DB list. 326 21:15.920 --> 21:20.420 So actually it will be using first row. 327 21:20.450 --> 21:22.520 It will be using first row. 328 21:29.720 --> 21:33.670 So while fetch state is equal to zero Begin. 329 21:33.700 --> 21:34.630 Set. 330 21:34.660 --> 21:34.990 Q. 331 21:35.020 --> 21:35.350 Path. 332 21:35.350 --> 21:41.080 So it will start with a first row from this query. 333 21:41.380 --> 21:44.530 AP and it will take the backup of AP. 334 21:44.560 --> 21:47.110 AP into this path. 335 21:47.740 --> 21:49.120 Into this path. 336 21:50.410 --> 21:55.090 And for the backup backup database Dbname two desc. 337 21:55.090 --> 21:58.780 So ideally manually also we can run this right manually. 338 21:58.780 --> 22:00.190 Also we can run this. 339 22:00.520 --> 22:09.010 If we do this one then we have to process this AP or something else like SQL underscore projects. 340 22:09.010 --> 22:10.600 So let me do that. 341 22:12.520 --> 22:19.510 SQL underscore projects in which path we have to store into this path. 342 22:19.510 --> 22:21.640 So let me try this one. 343 22:27.490 --> 22:28.870 Now let me try it. 344 22:28.900 --> 22:34.540 Whether it is succeeding or not Okay, let me remove this one. 345 22:39.910 --> 22:42.070 Cannot open backup device. 346 22:42.940 --> 22:46.870 D e cannot find the path specified. 347 22:46.900 --> 22:49.180 Okay, let me remove this one. 348 22:49.180 --> 22:50.530 I am not sure. 349 22:57.670 --> 22:59.770 Two disk E. 350 23:01.870 --> 23:03.280 Okay, then. 351 23:03.310 --> 23:03.700 Okay. 352 23:03.730 --> 23:04.270 Over here. 353 23:04.270 --> 23:10.330 We need to store like this and not back. 354 23:10.570 --> 23:12.070 Now let me try. 355 23:15.160 --> 23:18.340 And now let me put this into single quote. 356 23:22.510 --> 23:23.950 See over here. 357 23:25.510 --> 23:27.370 It has taken the backup. 358 23:27.400 --> 23:28.510 Backup of this one. 359 23:28.510 --> 23:29.350 Now, let me see. 360 23:29.380 --> 23:31.930 See 1032. 361 23:32.170 --> 23:33.760 Time it is showing, right? 362 23:34.330 --> 23:35.110 SQL projects. 363 23:35.110 --> 23:36.730 So this is manual approach. 364 23:36.970 --> 23:41.860 So every time this statement will run three times this. 365 23:41.860 --> 23:45.430 That is why we have written this statement inside the loop right. 366 23:45.700 --> 23:46.870 This is loop. 367 23:46.900 --> 23:51.190 And that is why we have written this statement inside the loop. 368 23:51.190 --> 23:52.960 It will run three times like this. 369 23:52.960 --> 23:55.690 So first time it will take the backup of AP. 370 23:58.390 --> 24:01.540 Second time it will take the backup of examples. 371 24:03.280 --> 24:06.070 Third time it will take the backup of this one. 372 24:07.300 --> 24:10.510 Right now let me know your question. 373 24:15.400 --> 24:17.170 Anyone has any question. 374 24:19.180 --> 24:19.600 That's it. 375 24:21.580 --> 24:22.030 Yeah. 376 24:22.090 --> 24:28.870 Basically our question here is now we see that it is a it is takes a backup as a back. 377 24:28.900 --> 24:32.680 How do we able to push it back again to database then? 378 24:32.680 --> 24:41.080 So can we use the same one and, uh, you know, uh, queue path need to be changed to the uh, different 379 24:41.080 --> 24:44.770 folder and dot back should be dot DB. 380 24:44.800 --> 24:45.610 Oh right. 381 24:46.960 --> 24:48.880 No, no I think that is not. 382 24:50.260 --> 24:54.040 Now if you see over here we have system databases are there. 383 24:54.040 --> 24:54.640 Right. 384 24:54.850 --> 24:57.430 We have system databases are there. 385 24:57.550 --> 25:03.760 And if you expand then master model msdb and temp DB databases are there. 386 25:03.760 --> 25:04.300 Right. 387 25:04.450 --> 25:08.530 Master model Msdb and temp DB databases are there. 388 25:09.250 --> 25:12.910 Now I want to over here what we have done. 389 25:12.910 --> 25:16.270 We have done this backup of these three databases. 390 25:16.450 --> 25:25.090 But now I want to take backup of all the other databases apart from these four system databases. 391 25:25.900 --> 25:28.720 Apart from these four system databases. 392 25:28.870 --> 25:35.470 So for that purpose also, we have to just change in the query right where name. 393 25:35.470 --> 25:38.680 Not in this for system databases. 394 25:38.680 --> 25:42.010 So it will give me all the all the other databases right. 395 25:42.010 --> 25:43.750 All the other databases. 396 25:45.220 --> 25:55.270 Now if I do the remove of this databases over here and if I run this one now it will take database of 397 25:55.270 --> 26:00.340 all the databases apart from this for system databases. 398 26:03.340 --> 26:06.160 Apart from this for system databases. 399 26:09.280 --> 26:14.710 Now if I go to this one see all database backup are now available. 400 26:17.320 --> 26:18.760 Process will be the same. 401 26:18.760 --> 26:19.960 Nothing is changed. 402 26:19.990 --> 26:22.600 Only this query is changed over here. 403 26:22.630 --> 26:24.700 But process will be the same. 404 26:24.700 --> 26:25.420 Only 405 26:30.400 --> 26:33.190 Okay, now let's see one more example. 406 26:35.950 --> 26:43.450 Write a program using cursor to increment the salary based on the percentage in raised salary table. 407 26:43.480 --> 26:50.350 So if you see over here we have created this race, I have created this race salary table and inserted 408 26:50.350 --> 26:51.730 the data into the same. 409 26:51.820 --> 26:58.960 So I have this two tables available with me over here one amp number table. 410 26:59.140 --> 27:04.420 So if the amp number is this one then increase the salary by 15%. 411 27:04.540 --> 27:08.980 If the MP number is this one, increase the salary by 20%. 412 27:09.130 --> 27:13.750 If the AMP number is this one, increase the salary by 12%. 413 27:14.620 --> 27:17.650 So the same thing we can do using the cursor. 414 27:17.800 --> 27:24.790 So for that purpose we need to store the race salary information somewhere right. 415 27:24.910 --> 27:28.650 So declare C1 cursor for select statement. 416 27:29.280 --> 27:34.290 Then MP number and pc t I want to retrieve from this race salary table. 417 27:34.290 --> 27:36.120 This is my race salary table. 418 27:37.020 --> 27:38.970 This is my race salary table. 419 27:41.280 --> 27:45.870 Now declare MP number percentage open C1. 420 27:46.050 --> 27:51.090 Now fetch next from C1 into this two variable. 421 27:52.500 --> 27:54.600 While fetch status equal to zero. 422 27:54.630 --> 27:58.260 What I want to do I want to update MP one. 423 27:58.290 --> 28:01.920 Set salary equal to salary plus salary. 424 28:01.920 --> 28:11.400 Multiply by at the rate PC divided by 100 and over here I need to specify MP number. 425 28:11.400 --> 28:12.030 Right? 426 28:12.180 --> 28:20.310 Because in this race salary table I have MP number is given in race salary table I have MP number is 427 28:20.310 --> 28:21.060 given. 428 28:21.660 --> 28:28.890 So for that particular employee It will pick every time and it will raise the salary. 429 28:28.890 --> 28:30.990 So let me take the database. 430 28:31.380 --> 28:33.360 Let me take the backup. 431 28:33.390 --> 28:35.400 Let me show you. 432 28:40.830 --> 28:42.780 This is my this table. 433 28:42.780 --> 28:43.980 And this is my. 434 28:47.610 --> 28:48.900 Employee table. 435 28:49.200 --> 28:57.780 Now, if I execute this one, it will increase increment the salary of how many employees? 436 28:57.930 --> 29:01.770 It will increment the salary of only four employees. 437 29:02.220 --> 29:06.120 Because in raise salary, only four employees are there, right? 438 29:06.390 --> 29:14.070 So now if I again see if I again execute this query, see over here 7369. 439 29:14.820 --> 29:19.620 So what is the previous salary of 7369920? 440 29:21.510 --> 29:24.540 Now it is incremented by 15%. 441 29:24.690 --> 29:32.520 So C select 9 to 0 plus 9 to 0. 442 29:32.760 --> 29:36.090 Multiply by 15 divided by 100. 443 29:37.170 --> 29:40.470 Let me run this 11058. 444 29:40.500 --> 29:41.130 Right. 445 29:42.960 --> 29:43.950 See over here. 446 29:43.950 --> 29:46.740 What what it is showing over here in this table. 447 29:50.670 --> 29:52.500 1058 right. 448 29:56.880 --> 30:02.550 But all other employee apart from this for employee, the salary will be the same. 449 30:03.210 --> 30:05.250 The salary will be the same. 450 30:05.850 --> 30:11.190 If you want to increase the salary of other employee, then you need to mention that employee detail 451 30:11.190 --> 30:13.830 over here in the salary table. 452 30:13.860 --> 30:17.250 Right in the raise salary table. 453 30:18.900 --> 30:20.790 So again nothing. 454 30:20.820 --> 30:21.660 Same process. 455 30:21.960 --> 30:24.720 Now we are using over here update statement. 456 30:24.840 --> 30:28.500 Again we have to use fetch next from seven. 457 30:28.590 --> 30:32.550 Again every syntactically every everything will be the same. 458 30:32.550 --> 30:38.310 You have to understand how many variables information you required, how many. 459 30:39.720 --> 30:41.910 That is main thing over here right? 460 30:50.820 --> 30:52.170 There is one more example. 461 30:52.170 --> 30:53.880 Let me take it quickly. 462 30:59.190 --> 31:06.120 Write a program to calculate all the students total average result and insert the records into result 463 31:06.150 --> 31:06.690 table. 464 31:06.720 --> 31:07.890 Now. 465 31:10.290 --> 31:13.260 We have one result table available with us 466 31:19.980 --> 31:24.750 Select star from result. 467 31:27.810 --> 31:28.950 Let me trunk. 468 31:28.980 --> 31:30.570 Let me do the truncate. 469 31:36.330 --> 31:36.750 Table. 470 31:36.750 --> 31:37.560 Result. 471 31:38.700 --> 31:41.550 Now there will be no data in the result table. 472 31:41.940 --> 31:43.470 Now what is our requirement? 473 31:43.500 --> 31:49.020 We have student table, student S1 and result table. 474 31:49.650 --> 31:51.630 So these are the data. 475 31:51.660 --> 32:01.260 Write a program to calculate all students total, average and result and insert the records into the 476 32:01.260 --> 32:01.980 result table. 477 32:01.980 --> 32:05.880 Passing marks in any subject should be minimum 35. 478 32:05.910 --> 32:12.570 A student will be considered as pass only when he and she is passed in all the subject. 479 32:12.660 --> 32:21.930 So in all the subject when there will be a 35 at that time, the student will be passed At that time, 480 32:21.930 --> 32:23.610 only the student will be passed. 481 32:23.610 --> 32:24.840 So over here. 482 32:24.840 --> 32:26.280 How many columns are there? 483 32:26.310 --> 32:34.050 See, we have to store this, uh, serial number into one variable. 484 32:34.050 --> 32:38.610 We need to store this first subject ma into some variable. 485 32:38.730 --> 32:42.000 We have to store the second subject ma into some variable. 486 32:42.000 --> 32:46.380 We have to store this third subject mark also into some variable. 487 32:46.590 --> 32:51.330 Then we have to do total as well as average and as well as result. 488 32:51.360 --> 32:51.750 Right. 489 32:51.780 --> 32:52.740 As well as result. 490 32:52.740 --> 32:55.740 Either it is a pass or fail. 491 32:55.740 --> 32:57.630 We have to store that data also. 492 32:57.630 --> 33:06.390 Now we are storing open C1 fetch next from C1 into fetch, next from C1 into all these four variables. 493 33:07.890 --> 33:09.210 While phase status. 494 33:09.210 --> 33:16.080 Now how we can get the total total of some particular serial number one. 495 33:16.080 --> 33:19.520 So s one plus x two S2 plus S3, right? 496 33:20.120 --> 33:21.380 S1 plus s. 497 33:30.440 --> 33:34.220 We require x1 plus x2 plus S3, right? 498 33:34.280 --> 33:35.180 Right. 499 33:35.330 --> 33:44.570 Now average of total S3 store and average total divided by three S3 total divided by three. 500 33:44.600 --> 33:54.830 Now I have to check if condition if S1 greater than 35 and S2 greater than 35 and S3 greater than 35. 501 33:54.830 --> 34:00.350 All of these three subject mark greater than 35, then pass. 502 34:00.380 --> 34:01.640 Otherwise fail. 503 34:04.250 --> 34:13.490 And we want to store the result into insert into result table, serial number, total average and result. 504 34:14.090 --> 34:15.380 Pass or fail. 505 34:15.380 --> 34:24.470 And we want to run the process for each of the employee, so each of the student for each of the student, 506 34:24.500 --> 34:26.810 we want to pass one by one row, right. 507 34:26.870 --> 34:35.900 So first it will check one serial number one it will check passing mark of all these three subject is 508 34:35.900 --> 34:36.860 greater than 35. 509 34:36.890 --> 34:37.550 Yes. 510 34:39.050 --> 34:45.440 So it will do the total like select. 511 34:48.170 --> 34:51.020 Plus plus plus. 512 34:51.650 --> 34:54.500 Now it will store the total somewhere right. 513 34:54.740 --> 34:57.050 It will store the total somewhere. 514 34:57.650 --> 35:01.400 Now I have to do 240 divided by three. 515 35:02.090 --> 35:06.530 So it will store the average also somewhere this is my average. 516 35:07.790 --> 35:10.010 And it will it will be a pass. 517 35:10.760 --> 35:12.860 So it will store the pass value. 518 35:12.860 --> 35:17.750 Also somewhere in the result table Now let me execute this query. 519 35:18.080 --> 35:18.560 Mhm. 520 35:20.360 --> 35:21.800 Three rows affected. 521 35:21.830 --> 35:24.410 Now if I see the result table. 522 35:29.300 --> 35:33.530 If I see the result table see over here. 523 35:33.560 --> 35:34.010 Pass. 524 35:34.010 --> 35:34.700 Fail. 525 35:34.940 --> 35:35.840 Pass. 526 35:42.830 --> 35:44.210 Now compare both. 527 35:44.240 --> 35:44.420 See. 528 35:44.450 --> 35:45.860 Check both of the table. 529 35:53.300 --> 35:54.830 Now check both of the table. 530 35:54.860 --> 35:55.340 See? 531 35:56.540 --> 35:57.650 This one is fail. 532 35:57.650 --> 35:58.250 Why? 533 35:58.340 --> 36:02.690 Because in one subject it is getting less than 35 marks, right? 534 36:04.250 --> 36:06.050 That is why it is failed. 535 36:10.070 --> 36:13.100 So now you can ask if you have any question