1 00:01.430 --> 00:08.510 Okay, so today we will see how to retrieve some of the common information from the database level. 2 00:08.510 --> 00:10.850 This is database level mostly. 3 00:11.330 --> 00:19.340 So see if we want to display how many tables are created in the database. 4 00:19.790 --> 00:21.350 What are the total. 5 00:21.350 --> 00:25.280 Different tables are available in some particular database. 6 00:25.280 --> 00:28.280 So over here I have Udemy database with me. 7 00:28.970 --> 00:30.590 This is my Udemy database. 8 00:30.590 --> 00:35.300 And if I see over here so all the tables are there right. 9 00:35.720 --> 00:39.500 But I cannot take the name from over here manually. 10 00:39.500 --> 00:40.130 Right. 11 00:40.280 --> 00:42.680 So for that some command will be there. 12 00:42.680 --> 00:43.280 Right. 13 00:43.640 --> 00:50.750 So this is a query for that display all the tables in DB display all the tables in DB. 14 00:51.140 --> 00:55.160 So select star from information schema dot tables. 15 00:55.430 --> 00:57.800 Now if I run like this. 16 00:57.830 --> 01:02.480 If I run this command Then let's see what it is giving me. 17 01:03.260 --> 01:05.570 It will giving me table catalog. 18 01:05.570 --> 01:08.810 So this table table catalog is a database name. 19 01:09.410 --> 01:11.900 This table catalog is a database name. 20 01:12.170 --> 01:14.150 Then this is a schema name. 21 01:15.470 --> 01:17.240 This is a schema name. 22 01:18.950 --> 01:22.850 Then this is all the table names and table type. 23 01:22.970 --> 01:26.030 Table type equal to base base table. 24 01:26.390 --> 01:28.700 Table type equal to base table. 25 01:29.540 --> 01:34.220 So we have to mention over here base table in a where condition. 26 01:34.580 --> 01:37.460 Currently we have only single table types. 27 01:37.490 --> 01:39.080 See distinct. 28 01:41.990 --> 01:42.770 Table type. 29 01:42.770 --> 01:50.360 If I mention distinct table type then it will be only single one base table right. 30 01:50.360 --> 01:52.580 It will be only single one base table. 31 01:52.700 --> 01:58.160 But now let me quickly try to create a view over here in the same DB. 32 01:58.350 --> 02:00.270 So create view. 33 02:00.300 --> 02:01.470 DBO dot. 34 02:05.010 --> 02:09.450 Department s select star from dept. 35 02:10.020 --> 02:12.720 So let me try to create this view. 36 02:14.520 --> 02:16.350 Okay this view is created. 37 02:16.380 --> 02:26.400 Now if I retrieve the data from dbo dot d partment and do not know what is the spelling department. 38 02:27.270 --> 02:33.510 And now if I write this one select star from dept then it should return both. 39 02:33.510 --> 02:35.670 The query should return same result right? 40 02:36.150 --> 02:39.300 Both the query should return same result. 41 02:39.330 --> 02:41.490 But above one is a view. 42 02:43.560 --> 02:45.360 And this is a table. 43 02:46.620 --> 02:51.000 Now if I run this query let's observe this query fully. 44 02:51.360 --> 02:56.820 If I run this query now distinct table type is see what are the distinct table type. 45 02:56.820 --> 02:57.480 Now. 46 02:57.870 --> 03:01.410 Now you is also one distinct table type, right? 47 03:01.950 --> 03:10.620 So if I do now this select star from this information schema dot tables, then it will not only retrieve 48 03:10.620 --> 03:14.130 only the tables, it will retrieve me the view as well. 49 03:14.790 --> 03:17.460 It will retrieve me the view as well. 50 03:17.700 --> 03:21.360 All the views which is created in the same database. 51 03:22.290 --> 03:23.010 Right? 52 03:23.250 --> 03:25.140 Now what I have to do. 53 03:26.400 --> 03:29.370 So that is why I have added this condition. 54 03:29.370 --> 03:35.430 That is why I have added this condition where table type equal to base table. 55 03:35.430 --> 03:37.380 So it will give me only tables. 56 03:38.280 --> 03:40.680 So 17 tables are created right. 57 03:41.370 --> 03:45.000 So you have to remember this one information schema dot tables. 58 03:45.000 --> 03:46.710 You have to remember this one. 59 03:47.280 --> 03:50.160 Now again one more query is there. 60 03:50.190 --> 03:52.890 Select star from system dot tables. 61 03:54.180 --> 03:56.400 Select star from system dot tables. 62 03:56.400 --> 03:59.560 Now what it will give me, it will give me 17 record. 63 03:59.950 --> 04:01.570 But this time. 64 04:01.870 --> 04:03.940 Number of columns are different. 65 04:03.970 --> 04:05.380 See name. 66 04:05.380 --> 04:07.540 So this is a table name object ID. 67 04:07.570 --> 04:13.240 So particular table there is one object ID will be attached with that particular table. 68 04:14.050 --> 04:16.660 So that and it will be unique. 69 04:16.690 --> 04:18.130 It will be unique. 70 04:19.420 --> 04:20.530 Then schema. 71 04:20.530 --> 04:22.270 So dbo dbo schema. 72 04:22.300 --> 04:26.770 Schema ID of dbo is what schema id of dbo is one. 73 04:28.480 --> 04:31.060 So if you want to see the schema also. 74 04:31.090 --> 04:32.740 Then this is one more table. 75 04:32.770 --> 04:40.720 Select star from system I think this is that particular table. 76 04:40.750 --> 04:42.910 See dbo. 77 04:43.390 --> 04:47.620 These are all the some schema are by default schema. 78 04:47.620 --> 04:56.500 All these all I think in our case all this schema is by default schema only in particular, schema ID 79 04:56.500 --> 04:58.900 is attached with the particular schema. 80 04:58.930 --> 05:06.700 Right schema name and some principal ID is there like which user has created that specific schema. 81 05:06.730 --> 05:08.770 I think that is a principal ID. 82 05:09.910 --> 05:12.220 Now let me move forward. 83 05:12.520 --> 05:14.710 So see select six dot tables. 84 05:14.710 --> 05:20.620 It will give me 17 record schema ID then ten type is equal to u. 85 05:20.860 --> 05:22.510 U means user table. 86 05:22.510 --> 05:33.400 So if you currently let me see okay there is no procedure is created currently. 87 05:33.400 --> 05:35.830 So let me create a procedure as well. 88 05:37.240 --> 05:46.420 Create procedure dbo dot departs procedure as select star from dept. 89 05:46.450 --> 05:48.040 Same procedure I am creating. 90 05:48.040 --> 05:49.090 Let me try. 91 05:49.930 --> 05:53.230 Okay, it is created now. 92 05:53.260 --> 05:54.410 If I Why? 93 05:54.500 --> 05:57.890 Select the data from the select star from select tables. 94 05:59.360 --> 06:02.720 Let me see. 95 06:02.960 --> 06:03.290 Okay. 96 06:03.320 --> 06:03.710 Sis. 97 06:03.710 --> 06:04.310 Dot. 98 06:04.910 --> 06:05.420 Okay. 99 06:05.450 --> 06:08.450 I think this one will indicating only tables. 100 06:08.450 --> 06:10.400 Sit dot tables only. 101 06:10.400 --> 06:13.250 So for procedure some other table will be there. 102 06:13.670 --> 06:16.250 So there will be procedure will be over here. 103 06:16.280 --> 06:16.820 Sis. 104 06:16.820 --> 06:17.420 Dot. 105 06:18.980 --> 06:19.310 Okay. 106 06:19.340 --> 06:20.720 Only one procedure is there. 107 06:20.750 --> 06:21.530 Okay. 108 06:23.960 --> 06:32.210 So see both of these query will give me 1717 record 17 record in above query and 17 record in below 109 06:32.210 --> 06:32.870 query. 110 06:33.440 --> 06:41.720 All these column are not much important Createdat on which date that particular table is created, on 111 06:41.720 --> 06:44.900 which date that particular table is modified. 112 06:44.900 --> 06:50.990 When it is modified, when we are adding new column, or when we are dropping new column, or when we 113 06:51.020 --> 06:54.140 are changing the data type of a particular column. 114 06:54.470 --> 06:56.420 So that is a modified date. 115 06:56.450 --> 07:01.040 Now these columns are not much useful. 116 07:01.040 --> 07:04.250 This is a table schema and data. 117 07:04.820 --> 07:07.400 All these columns are not much needed. 118 07:08.030 --> 07:14.120 All these columns are not much needed apart from beginner beginning four five columns. 119 07:15.470 --> 07:17.000 This column is required. 120 07:17.030 --> 07:18.320 Object ID is required. 121 07:18.350 --> 07:19.880 Schema ID is required. 122 07:22.220 --> 07:23.600 Then type is required. 123 07:23.630 --> 07:27.290 Type description is required and created date and modified date is required. 124 07:27.440 --> 07:29.720 Mostly by using this column. 125 07:29.750 --> 07:32.660 Our tasks general tasks will be achieved. 126 07:33.260 --> 07:33.680 Right. 127 07:33.680 --> 07:35.660 So you have to remember these two tables. 128 07:35.690 --> 07:39.710 Six dot table and information schema dot tables. 129 07:39.710 --> 07:39.980 Right. 130 07:39.980 --> 07:44.360 You need to remember these three two tables. 131 07:44.390 --> 07:48.410 Now same thing if I want the you information. 132 07:48.710 --> 07:53.790 So over here we have seen what are the different table types are stored. 133 07:57.240 --> 08:03.180 Different table types are stored as tables and view right tables and view only. 134 08:03.180 --> 08:05.820 Not procedure right not function. 135 08:05.850 --> 08:08.850 See only base table and you are stored. 136 08:13.860 --> 08:15.090 So select star. 137 08:15.090 --> 08:19.620 From this time we can specify view in the where condition. 138 08:19.620 --> 08:24.870 So this is the only view which is available in my database as of now. 139 08:24.870 --> 08:25.530 Right. 140 08:29.790 --> 08:38.670 So and uh one more table for every object there will be one more table mostly will be there in the database. 141 08:38.670 --> 08:42.480 So you can see detail level information over here. 142 08:42.480 --> 08:46.110 You will see high level information right for you. 143 08:46.110 --> 08:53.550 But now since you will give you detail level information, let's see So what is the name? 144 08:53.550 --> 08:56.790 Department is a name then? 145 08:56.790 --> 09:01.140 This is a object id schema id then type is equal to you. 146 09:01.260 --> 09:01.800 Sorry. 147 09:01.830 --> 09:03.090 Type is equal to v. 148 09:07.350 --> 09:08.700 V means view. 149 09:08.700 --> 09:10.320 So these are the information. 150 09:10.350 --> 09:14.370 Again all this information are not much used. 151 09:15.540 --> 09:17.760 But you have then for procedure. 152 09:17.790 --> 09:20.310 See we have created one procedure right. 153 09:20.310 --> 09:23.880 So for say procedure there is one more table is there. 154 09:23.910 --> 09:25.410 We have created this procedure. 155 09:25.440 --> 09:26.040 See over here. 156 09:26.040 --> 09:26.670 Right. 157 09:27.690 --> 09:30.600 We have created this procedure in schema id dbo. 158 09:30.600 --> 09:31.500 So db. 159 09:32.670 --> 09:34.350 So schema id equal to one. 160 09:34.350 --> 09:35.190 What is the type. 161 09:35.220 --> 09:41.520 Type is equal to p p for SQL stored procedure and created date and modified date. 162 09:46.170 --> 09:49.440 Now let's see further few more information. 163 09:49.530 --> 09:54.070 If trigger is created, then it will show into the triggers table. 164 09:54.250 --> 09:59.230 But right now no trigger are available right now. 165 09:59.230 --> 10:02.110 See, this is a one more important thing. 166 10:03.610 --> 10:05.590 Six dot objects. 167 10:06.130 --> 10:07.900 Six dot objects. 168 10:07.930 --> 10:15.880 So in the database, whatever number of objects are created that will be displayed over here, that 169 10:15.880 --> 10:17.620 all will be displayed over here. 170 10:17.650 --> 10:22.030 So let me try to select data from this particular two columns. 171 10:22.060 --> 10:22.840 Only. 172 10:24.880 --> 10:26.110 Two columns only. 173 10:26.110 --> 10:27.010 So. 174 10:31.390 --> 10:33.760 I will specify distinct over here. 175 10:38.740 --> 10:46.840 And now see see these are all the distinct distinct objects are available in my current database. 176 10:47.260 --> 10:50.170 P means for SQL stored procedure. 177 10:51.040 --> 10:54.490 Then V means for view pk. 178 10:54.520 --> 11:00.820 So for primary key also one one different type is available into the system objects table. 179 11:00.910 --> 11:03.130 U means user table. 180 11:06.820 --> 11:15.310 And I am not sure what is this service q s means system table right? 181 11:15.340 --> 11:18.700 So you have to remember this type sometimes as well. 182 11:18.970 --> 11:20.050 What is a p? 183 11:20.080 --> 11:27.250 When you retrieve the information from this table where type is equal to u. 184 11:27.250 --> 11:33.970 So what do you mean by you see u means user table. 185 11:34.810 --> 11:39.310 So if you run this one, if you run this one see over here. 186 11:39.310 --> 11:40.930 If you run this one. 187 11:42.970 --> 11:45.850 Now let me run all these three query. 188 11:48.710 --> 11:49.160 Okay. 189 11:49.160 --> 11:50.180 Not this one. 190 11:50.180 --> 11:51.080 One minute. 191 11:53.240 --> 11:54.590 All these three query. 192 11:54.620 --> 11:55.400 This three query. 193 11:55.430 --> 11:56.450 Let me run. 194 12:01.730 --> 12:04.070 One is for information schema dot tables. 195 12:04.070 --> 12:08.450 One is from six dot tables and one is for six dot objects. 196 12:08.450 --> 12:10.700 So all will give me same number of rows. 197 12:10.730 --> 12:16.640 See 17, 17 and 17. 198 12:18.500 --> 12:22.310 All will give me same number of rows, right? 199 12:22.670 --> 12:31.640 But based upon your requirement, you can choose any of the three table while performing some DBA related 200 12:31.640 --> 12:33.560 activities or something else. 201 12:33.590 --> 12:34.400 Right. 202 12:35.330 --> 12:37.910 But you have to remember and. 203 12:39.950 --> 12:42.230 So now see I can. 204 12:42.350 --> 12:45.080 So this will give me all the view data. 205 12:45.110 --> 12:45.650 Right. 206 12:46.100 --> 12:47.210 All the view data. 207 12:47.240 --> 12:49.550 This will give me all the procedure data. 208 12:52.700 --> 12:54.350 This will give me all the. 209 12:54.380 --> 12:56.360 Let me take one minute. 210 12:56.390 --> 12:59.120 Let me open another SSMs. 211 12:59.120 --> 12:59.840 So. 212 13:20.270 --> 13:21.560 Let it open. 213 13:21.560 --> 13:24.110 So the trigger for type will be TR. 214 13:24.140 --> 13:26.420 Foreign key for type will be FN. 215 13:27.950 --> 13:31.520 Primary key for type will be PK like that right. 216 13:32.390 --> 13:34.010 Now let me connect. 217 13:39.020 --> 13:41.630 Now let me open the database. 218 13:41.930 --> 13:43.640 Let me close this. 219 13:46.530 --> 13:51.270 Let me connect with the our main server. 220 13:56.520 --> 14:01.440 Because right now I don't have that information. 221 14:01.440 --> 14:01.740 Right. 222 14:01.740 --> 14:04.170 I don't that information available. 223 14:04.650 --> 14:09.570 So let me now copy this one and run copy this query over here. 224 14:12.090 --> 14:15.570 Now see it will give me all the views array. 225 14:31.500 --> 14:35.100 In our database we have created so many views right. 226 14:35.100 --> 14:38.190 So it will give me see 29 views. 227 14:38.220 --> 14:40.500 All the views which we have created. 228 14:40.830 --> 14:48.930 Now this P will what P will give me, P will give me only all the stored procedure c SQL stored procedure. 229 14:48.960 --> 14:54.240 This much of stored procedure till now I have created in my database. 230 14:55.200 --> 15:00.960 Maybe many of are repeated only then maybe no, no triggers are available. 231 15:00.960 --> 15:01.650 I am not sure. 232 15:01.680 --> 15:06.300 Let me see whether a okay this much of triggers are there. 233 15:07.320 --> 15:09.750 Then this will indicate foreign key. 234 15:11.070 --> 15:12.300 Now see this. 235 15:12.300 --> 15:14.460 Foreign keys are created. 236 15:15.840 --> 15:18.060 This much of foreign keys are created. 237 15:20.460 --> 15:21.750 Now primary key. 238 15:21.780 --> 15:27.330 We can see all the primary keys which we are created all the PK right. 239 15:27.360 --> 15:28.920 Primary key constraint. 240 15:29.490 --> 15:31.080 Now f is for what? 241 15:31.110 --> 15:34.440 F is for foreign key constraint. 242 15:34.440 --> 15:36.420 And what is this fn. 243 15:37.050 --> 15:38.070 Let me see. 244 15:38.160 --> 15:40.980 FN SQL Keras function okay. 245 15:41.100 --> 15:42.690 This is a scalar function. 246 15:42.690 --> 15:44.700 And this is a foreign key constraint. 247 15:45.030 --> 15:47.490 And C is for I think check constraint. 248 15:47.520 --> 15:48.060 Let me see. 249 15:48.090 --> 15:48.660 Yeah. 250 15:48.900 --> 15:51.720 All the checks constraint are available in my. 251 15:51.840 --> 15:54.420 All the tables of the current database. 252 15:55.050 --> 15:57.570 All the tables of the current database. 253 15:57.720 --> 16:02.130 Now see one more important thing. 254 16:02.130 --> 16:03.270 Let me. 255 16:04.080 --> 16:07.080 Give me alias like this. 256 16:07.080 --> 16:18.360 Now inner join says dot objects t on o dot parent object. 257 16:18.390 --> 16:24.690 ID is equal to t dot object id. 258 16:24.720 --> 16:26.190 See what I am doing? 259 16:26.580 --> 16:30.450 Now I want to check this specific constraint. 260 16:30.480 --> 16:37.860 Over dot name is created on which table. 261 16:37.890 --> 16:42.060 On which table this specific check constraint is created. 262 16:42.090 --> 16:42.750 On which table? 263 16:42.780 --> 16:44.320 How can I check that? 264 16:44.800 --> 16:45.580 See? 265 16:50.350 --> 16:51.100 This. 266 16:51.130 --> 16:52.090 DF. 267 16:53.740 --> 16:56.380 Oh, let me take some another example. 268 17:01.630 --> 17:04.270 MP one it is showing anywhere. 269 17:04.300 --> 17:05.260 MP one. 270 17:12.820 --> 17:14.170 I think I am missing something. 271 17:14.170 --> 17:16.060 Let me take t dot name. 272 17:26.830 --> 17:29.020 I want table information. 273 17:29.020 --> 17:31.840 So let me go below. 274 17:31.870 --> 17:32.410 Below. 275 17:32.410 --> 17:32.650 Below. 276 17:32.650 --> 17:33.070 Below. 277 17:40.900 --> 17:41.830 Just a minute. 278 17:41.860 --> 17:43.960 Oh dot star. 279 17:49.690 --> 17:50.050 Okay. 280 17:50.050 --> 17:54.670 Parent object ID is this one and object ID is this one. 281 17:59.980 --> 18:03.040 Select star from system. 282 18:06.520 --> 18:10.990 Where object id equal to this one. 283 18:10.990 --> 18:13.000 Let me try to run this query. 284 18:13.000 --> 18:18.460 And what is a parent object id parent object id. 285 18:28.570 --> 18:31.510 Okay this is my user table okay. 286 18:31.540 --> 18:32.530 Object ID. 287 18:32.560 --> 18:45.560 So now let me try to take this where t dot object id equal to this one. 288 18:45.560 --> 18:47.420 Let's see for one table only. 289 18:48.290 --> 19:02.510 So see let me comment this one and let me run both of these query see on shift table on save table. 290 19:02.510 --> 19:10.910 So this is my t dot name means if you see shift is over what shift is our table name user underscore 291 19:10.910 --> 19:11.450 table. 292 19:11.450 --> 19:18.740 And in this user underscore table A22 constraint are available. 293 19:18.770 --> 19:20.180 Two constraint are available. 294 19:20.180 --> 19:24.530 One is default constraint and one is primary key constraint. 295 19:27.080 --> 19:32.030 So you you can you do not need to hover over here right. 296 19:32.060 --> 19:34.010 You do not need to hover over here. 297 19:34.040 --> 19:38.420 Or you do not need to write like this SP underscore help. 298 19:38.420 --> 19:41.360 And then table name is shift. 299 19:42.890 --> 19:44.990 So if you mention this one. 300 19:47.150 --> 19:50.990 The object's shift is not available in the database. 301 19:51.020 --> 19:52.340 I don't know why. 302 20:01.520 --> 20:02.240 Help! 303 20:03.380 --> 20:06.200 I don't know where this shift table is coming from. 304 20:12.830 --> 20:16.880 So see, now that empty table we are able to see. 305 20:17.240 --> 20:23.810 So in in the empty table, what are the different constraints are available that we can see over here. 306 20:23.840 --> 20:24.170 Right. 307 20:24.170 --> 20:25.820 Only one constraint are available. 308 20:25.820 --> 20:30.110 Primary key constraint and constraint name is this one right. 309 20:30.950 --> 20:32.690 Constraint name is this one. 310 20:32.720 --> 20:38.990 Now if I execute this query then also it will give me the same same data right. 311 20:40.230 --> 20:42.960 Then also it will give me the same data c. 312 20:45.360 --> 20:47.970 PQ MP a f for c. 313 20:48.330 --> 20:54.270 So like this also you will able to see you do not need to uh go to each and every table. 314 20:54.270 --> 20:57.240 You have to just specify the join condition. 315 20:57.450 --> 21:05.580 And now if I remove this join condition it will give me for all the tables related information in all 316 21:05.580 --> 21:09.270 the tables what are the different constraints are created. 317 21:09.420 --> 21:13.770 We can see like that one right okay. 318 21:13.770 --> 21:15.510 Now let me move forward. 319 21:18.750 --> 21:25.590 Now see there is one requirement I want to see what are the different uh different. 320 21:28.950 --> 21:33.510 Columns are available in a particular particular table. 321 21:33.690 --> 21:36.510 So I think MP one is not exist. 322 21:36.540 --> 21:39.810 What are the different columns are available in a particular table. 323 21:40.050 --> 21:43.020 So see this is my just a table. 324 21:43.050 --> 21:43.620 Right. 325 21:43.620 --> 21:46.470 So I have taken the object ID from here. 326 21:46.470 --> 21:48.990 I have taken the object ID from here. 327 21:49.290 --> 21:51.390 This is a list of tables. 328 21:51.720 --> 21:55.770 Now there is one table is there which is six columns. 329 21:57.780 --> 21:59.970 Which is six columns. 330 21:59.970 --> 22:09.330 So now I have to mention that object ID over here I can do a join or I can mention a manually. 331 22:09.330 --> 22:10.110 I can also see. 332 22:10.140 --> 22:10.470 Right. 333 22:10.470 --> 22:16.320 So in the MP one these are the different columns are available in the MP one. 334 22:16.620 --> 22:19.140 These are the different columns are available. 335 22:19.140 --> 22:24.150 You can do a join as well by some joining condition. 336 22:24.150 --> 22:25.740 And you can check manually. 337 22:25.740 --> 22:30.090 If 1 or 2 table are there then you can check manually as well right. 338 22:33.480 --> 22:36.930 Now this is one more thing we can check. 339 22:36.940 --> 22:40.390 what are the number of columns are available in particular table. 340 22:41.080 --> 22:45.100 So if you run this then it will return should be eight. 341 22:45.130 --> 22:49.510 Total number of columns are eight in the object ID this one. 342 22:49.690 --> 22:51.280 And what is this object id. 343 22:51.310 --> 22:55.060 This object id is attached with this emp emp table right. 344 22:56.410 --> 22:57.520 EMP table. 345 22:57.610 --> 22:59.920 Now same thing I have done over here. 346 22:59.950 --> 23:02.680 Join I have done over here. 347 23:02.680 --> 23:03.430 Join. 348 23:04.780 --> 23:10.120 So see in the EMP table, eight columns are there in the EMP table. 349 23:10.150 --> 23:12.400 Eight columns are there in the department table. 350 23:12.400 --> 23:16.240 Three columns are there in the bonus table five. 351 23:16.270 --> 23:17.740 Four columns are there. 352 23:17.770 --> 23:26.350 So it will give me we need to join with six dot tables and inner join six dot columns on t dot object 353 23:26.380 --> 23:29.380 id equal to c dot object id. 354 23:31.750 --> 23:36.730 And then t dot name means table name and c dot name means column name. 355 23:40.540 --> 23:41.140 Right. 356 23:41.170 --> 23:44.230 Then there is one more thing we can. 357 23:44.320 --> 23:50.890 We have to check in how many columns are available in each of the table. 358 23:51.250 --> 23:53.740 Not on a single table in each of the table. 359 23:53.770 --> 23:55.630 How many number of columns are there? 360 23:55.720 --> 23:59.410 So see in activity tables, total number of columns are five. 361 23:59.650 --> 24:03.430 In EMP test table, total number of columns are eight. 362 24:03.640 --> 24:06.610 In EMP table, total number of columns are eight. 363 24:06.790 --> 24:09.610 So it will give me data of all the tables. 364 24:09.640 --> 24:10.870 Group by right. 365 24:12.280 --> 24:16.660 Here we have to use group by because we are using aggregate function. 366 24:16.990 --> 24:17.650 Right. 367 24:17.650 --> 24:20.890 We are using aggregate function in order by. 368 24:20.920 --> 24:22.150 Also I have done. 369 24:26.320 --> 24:32.020 Now suppose this is a very interesting interview question. 370 24:33.580 --> 24:37.130 This is a very interesting interview question. 371 24:37.400 --> 24:40.880 So let me type over here where. 372 24:44.990 --> 24:51.920 T dot name is equal to emp t dot name is equal to emp. 373 24:52.730 --> 24:55.280 So um. 374 24:59.030 --> 25:02.720 Let me copy this one and copy it over here. 375 25:05.180 --> 25:09.380 See in my one table total eight columns are there. 376 25:09.680 --> 25:13.490 But now I want the I don't want the information like this. 377 25:13.490 --> 25:15.620 I want information like this. 378 25:15.710 --> 25:24.050 In EMP table, how many number of columns in one, one column and comma separated. 379 25:24.050 --> 25:26.540 So one column is EMP number. 380 25:26.780 --> 25:38.780 Then second column is E name, then then job, then mgr, then higher date then salary, then commission. 381 25:41.450 --> 25:44.870 Then commission, then dept number. 382 25:44.900 --> 25:51.860 I want information like this comma separated value in a single column. 383 25:52.010 --> 25:56.150 This is a very frequently asked interview question. 384 25:56.150 --> 26:00.710 So you can say you can use the string aggregate function. 385 26:01.370 --> 26:06.950 String aggregate function for the same as columns from sis dot table. 386 26:06.950 --> 26:08.720 Then you have to do inner join. 387 26:10.310 --> 26:12.020 Then which table you want. 388 26:12.050 --> 26:13.610 You can do group by. 389 26:16.010 --> 26:17.750 And what is a separated. 390 26:18.890 --> 26:23.270 So that comma separated I want right over here I have mentioned over here comma. 391 26:23.810 --> 26:25.160 Now let's see. 392 26:26.990 --> 26:28.430 It will give me the result. 393 26:28.430 --> 26:29.810 What I want right. 394 26:34.920 --> 26:41.610 But now the important thing is see what is the problem if I run the same query over here? 395 26:45.930 --> 26:50.670 In another SQL server I have two SQL server installed in my system, right? 396 26:51.000 --> 26:52.500 This is second SQL server. 397 26:52.530 --> 26:53.550 See what happened? 398 26:57.810 --> 26:58.620 String. 399 26:58.650 --> 27:02.670 Aggregate is not a recognized built in function name. 400 27:03.480 --> 27:07.230 So this string aggregate function is a. 401 27:07.230 --> 27:13.320 In the last two three years of edition of SQL server it is come into the picture. 402 27:13.350 --> 27:15.330 This is my 2012. 403 27:15.390 --> 27:18.180 So if you see over here about. 404 27:20.430 --> 27:24.480 I think SSMs is 19. 405 27:24.510 --> 27:29.670 It will display only but this is my database is of 2012. 406 27:29.670 --> 27:34.830 So that is why it will not give me the result after 2012. 407 27:34.860 --> 27:39.690 It is came into the picture after 2012. 408 27:39.720 --> 27:41.040 It is came into the picture. 409 27:41.040 --> 27:42.780 So over here it will work. 410 27:43.050 --> 27:45.810 But over here it will not work, right? 411 27:45.900 --> 27:48.150 Now let me try to remove this one. 412 27:48.150 --> 27:50.400 Whether it will give me the proper result or not. 413 27:50.400 --> 27:51.600 Let me see. 414 27:53.070 --> 27:54.750 See in all the tables. 415 27:54.750 --> 27:55.950 Now it will work. 416 27:56.940 --> 27:58.320 All the tables. 417 27:58.380 --> 28:00.240 It will give me all the columns. 418 28:00.240 --> 28:01.500 Comma separated. 419 28:04.140 --> 28:05.160 Comma separated. 420 28:05.160 --> 28:05.730 Right. 421 28:05.760 --> 28:07.830 You can mention specify over here. 422 28:07.860 --> 28:08.550 Dash as well. 423 28:08.550 --> 28:08.880 Right. 424 28:08.910 --> 28:11.070 Dash, let me try to mention dash. 425 28:12.840 --> 28:14.970 So it will give me dash separated. 426 28:15.000 --> 28:21.240 See the result of this particular query will be dash separated. 427 28:21.270 --> 28:21.810 See. 428 28:26.010 --> 28:35.140 This function is an aggregate function that concatenates rows of strings into strings, uh, into a 429 28:35.140 --> 28:42.430 single string separated by a specified separator, separated by a specified separator. 430 28:42.460 --> 28:45.100 Now, see, this is one more thing. 431 28:45.910 --> 28:46.900 Um, okay. 432 28:46.900 --> 28:48.850 I will give you one more example. 433 28:53.560 --> 28:56.080 I want this is my table, right? 434 28:56.110 --> 28:57.220 EMP table. 435 28:59.260 --> 29:00.820 Order by dept. 436 29:09.760 --> 29:11.050 Order by dept number. 437 29:11.050 --> 29:16.240 So in department number ten what are the different employee names are available. 438 29:16.240 --> 29:23.410 I want all the employee name comma separated like department number ten, clerk, comma, King, comma, 439 29:23.410 --> 29:27.970 Miller, clerk, comma, king comma Miller, then department number 20. 440 29:28.000 --> 29:34.270 I want all this employee comma separated so I can get it through the string aggregate function. 441 29:34.270 --> 29:34.870 Right. 442 29:35.800 --> 29:36.430 String. 443 29:36.460 --> 29:37.690 Aggregate function. 444 29:40.330 --> 29:41.560 Department number ten. 445 29:41.590 --> 29:43.000 These employees are working. 446 29:43.000 --> 29:44.680 This is an interview question. 447 29:44.920 --> 29:46.360 Department number 20. 448 29:46.390 --> 29:48.340 All employees comma separated. 449 29:58.750 --> 29:59.110 Okay. 450 29:59.140 --> 29:59.770 Over here. 451 29:59.770 --> 30:04.570 All the all the 18 employees are separated by commas. 452 30:04.600 --> 30:06.820 All the 18 or 17, whatever. 453 30:06.820 --> 30:11.860 123456789 ten 1112. 454 30:11.890 --> 30:12.700 13. 455 30:13.420 --> 30:16.300 All the employees are now available in comma separated. 456 30:16.480 --> 30:20.890 This is just one example, but this is an interview question. 457 30:20.920 --> 30:24.520 You this is a these are the interview questions. 458 30:24.520 --> 30:25.540 You can say. 459 30:28.460 --> 30:32.390 But these are the things you have to remember. 460 30:32.420 --> 30:36.320 6.6. view six dot procedure. 461 30:36.920 --> 30:39.170 Six dot trigger information schema. 462 30:39.200 --> 30:39.890 Dot table. 463 30:39.920 --> 30:41.240 Six dot tables. 464 30:42.200 --> 30:45.260 Then what are the different types are available in this? 465 30:45.290 --> 30:47.960 Six dot objects. 466 30:48.620 --> 30:50.540 You have to memorize this one. 467 30:50.540 --> 30:51.740 Or you can. 468 30:51.890 --> 30:55.100 If you know only the table name then also it will be fine. 469 30:55.100 --> 30:55.730 Right? 470 30:56.690 --> 30:58.520 Then also it will be fine. 471 31:03.020 --> 31:05.300 These are the different types are there. 472 31:08.540 --> 31:11.630 Now you can ask if you have any question. 473 31:11.630 --> 31:12.650 Let me. 474 31:25.610 --> 31:28.220 You can ask if you have a question.