1 00:00:00,360 --> 00:00:01,240 Hello, dear friends. 2 00:00:01,560 --> 00:00:05,300 So in this section, we're going to learn about Ashwell schema objects. 3 00:00:06,450 --> 00:00:11,790 So this is a subject where we're going to learn how to create and manage schemas and. 4 00:00:13,400 --> 00:00:18,590 How to create views and how to create and use sequences. 5 00:00:19,860 --> 00:00:21,270 So let's get started with schema. 6 00:00:23,430 --> 00:00:26,640 All right, so what is a schema Enescu server? 7 00:00:27,970 --> 00:00:35,260 A schema is a collection of database objects, including tables, views, triggers, indexes, etc. 8 00:00:36,340 --> 00:00:44,110 A schema is associated with a username which is known as the schema owner, who is the owner of the 9 00:00:44,110 --> 00:00:45,700 logically related database. 10 00:00:45,700 --> 00:00:53,200 Objects to a schema, always belongs to one database, on the other hand. 11 00:00:53,530 --> 00:00:57,370 A database may have one or multiple schemas. 12 00:01:00,010 --> 00:01:03,340 I'll tell you this, for nothing schema makes work simple. 13 00:01:04,200 --> 00:01:11,010 In general, we may need to give permission to tables and we set privileges one by one, and this will 14 00:01:11,010 --> 00:01:13,040 make management really difficult. 15 00:01:13,260 --> 00:01:14,490 Well, tedious, right? 16 00:01:15,750 --> 00:01:22,770 So instead of giving permissions to a table one by one, the tables which are related are created under 17 00:01:22,770 --> 00:01:27,150 a schema and then the schema can be assigned a user. 18 00:01:28,700 --> 00:01:30,140 So how do you create a schema? 19 00:01:30,980 --> 00:01:39,320 Well, the create schema command, that's how you create a schema, then the schema name should be specified. 20 00:01:40,190 --> 00:01:46,820 It's optional that the owner name can be specified with authorization command, but you can determine 21 00:01:46,820 --> 00:01:53,330 that yourself schema and the schema owner can be specified in the same query. 22 00:01:54,960 --> 00:02:00,330 So let's go ahead and jump over to assess Hamas and get started in creating a scheme. 23 00:02:02,860 --> 00:02:09,280 So our scheme, an MLB test scheme, and we can specify the schema owner with authorization. 24 00:02:10,820 --> 00:02:18,250 And schemas are created for a database, so the users should be created in that database beforehand. 25 00:02:18,800 --> 00:02:25,970 So we will create this test schema for adventure works database and we will choose the test underscore 26 00:02:25,970 --> 00:02:27,380 user in this database. 27 00:02:27,860 --> 00:02:31,580 Now execute the query and the commands complete successfully. 28 00:02:32,120 --> 00:02:37,370 Schemas are stored in the schemas folder under security in the database. 29 00:02:38,840 --> 00:02:43,760 And here's our test schema, and you can see it has been created successfully. 30 00:02:44,630 --> 00:02:50,450 Now, it's possible that schemas can't be created graphically in order to create a new schema just right, 31 00:02:50,450 --> 00:02:57,440 click on the schema folder and select the new schema option schema properties pop up, windows going 32 00:02:57,440 --> 00:03:02,540 to be open, and the schema name and authorization user are specified in this window. 33 00:03:03,900 --> 00:03:09,270 So if the schema owner is not specified, Devaux user will be authorized as default. 34 00:03:10,370 --> 00:03:14,390 And we will select test user by clicking on the search button. 35 00:03:15,480 --> 00:03:18,180 Go ahead and click, OK, to create the schema. 36 00:03:21,540 --> 00:03:24,630 Now, how can we create a table in scheme? 37 00:03:26,220 --> 00:03:32,220 So we've learned how to create a table and have a schema specified with a table name, the table will 38 00:03:32,220 --> 00:03:33,640 be created in that schema. 39 00:03:34,410 --> 00:03:38,700 There's really not any difference about how you go about creating a table. 40 00:03:39,670 --> 00:03:42,640 So this create a table in the schema with an example. 41 00:03:44,240 --> 00:03:50,990 And the previous section, we learned how to create a table in a database, so of any schema will not 42 00:03:50,990 --> 00:03:51,950 be specified. 43 00:03:52,460 --> 00:03:54,890 The table will be created in the schema DBO. 44 00:03:56,010 --> 00:04:01,950 Devo schema is a default schema that every table will be created in as a default. 45 00:04:02,460 --> 00:04:05,820 So let's execute this query and check the schema of the table. 46 00:04:11,250 --> 00:04:15,130 We'll need to refresh the folder to get those last changes. 47 00:04:15,890 --> 00:04:17,160 Here's the department's table. 48 00:04:17,640 --> 00:04:21,270 It's been created in the DBO schema by default. 49 00:04:22,960 --> 00:04:29,590 You create a table in a specific schema, we should write this schema name before the table, name in 50 00:04:29,590 --> 00:04:31,000 the create table statement. 51 00:04:32,090 --> 00:04:38,750 If we execute again, we will see that departments is the table that's been created in the test schema. 52 00:04:40,350 --> 00:04:43,370 We will create the department's table in this scheme. 53 00:04:46,570 --> 00:04:50,070 So how can we change the schema of a table? 54 00:04:51,970 --> 00:04:57,610 Alter schema and the transfer command is used to change the schema of a table. 55 00:04:59,330 --> 00:05:02,570 Table is then transferred from one schema to another. 56 00:05:05,290 --> 00:05:11,320 So let's create a new schema with a name, new schema and the Adventure Works database. 57 00:05:26,500 --> 00:05:30,260 Now we have two custom schemas, new schema and test schema. 58 00:05:31,150 --> 00:05:38,200 So in this example, what we're going to do is transfer the department's table from test schema to new 59 00:05:38,200 --> 00:05:38,670 schema. 60 00:05:39,850 --> 00:05:45,100 Of course, it's not possible to change the schema of a table just by renaming the table. 61 00:05:46,180 --> 00:05:51,330 When we try to rename the department's table, for instance, the schema name disappears. 62 00:05:52,150 --> 00:05:56,080 So we have to build a query to transfer that table. 63 00:05:57,060 --> 00:06:03,570 And we will alter the new schema, the table name should indeed be specified with a schema name. 64 00:06:04,560 --> 00:06:06,090 And execute the query. 65 00:06:07,510 --> 00:06:11,500 And refresh the table folder to get the last changes. 66 00:06:13,330 --> 00:06:19,450 Great, the department's table has been transferred to the new schema successfully. 67 00:06:22,280 --> 00:06:25,850 And the other schema has been removed from the list as well. 68 00:06:27,440 --> 00:06:29,120 Now, what about dropping the scheme? 69 00:06:29,930 --> 00:06:34,820 There is a drop schema command and that's used to drop or delete the schema from the database. 70 00:06:36,760 --> 00:06:42,610 So in this example, we are going to drop the schemas that we have created, the new schema and the 71 00:06:42,610 --> 00:06:43,690 test schema. 72 00:06:45,730 --> 00:06:48,520 That's just drop the test scheme of first. 73 00:06:52,160 --> 00:06:54,530 Execute the query and refresh the list. 74 00:06:56,200 --> 00:06:56,920 Bubby. 75 00:06:58,850 --> 00:07:01,510 So let's drop new schema as well. 76 00:07:07,860 --> 00:07:15,150 Now we're going to get an error if we ask you the query, SQL Server will not allow users to delete 77 00:07:15,150 --> 00:07:17,310 a schema that has tables in it. 78 00:07:18,990 --> 00:07:24,540 The department's table is still in the new schema, so we will not be able to delete new schema until 79 00:07:24,540 --> 00:07:28,370 we have transferred or removed the department's table in it. 80 00:07:31,670 --> 00:07:38,720 So let's transfer department's table from new schema to human resources schema, which is created by 81 00:07:39,020 --> 00:07:42,440 the Adventure Works Database during setup of the escrow server. 82 00:07:47,350 --> 00:07:52,120 Right, so now we've learned transferring the table from a schema to another schema. 83 00:07:54,580 --> 00:07:58,040 So we will alter the human resources scheme. 84 00:07:59,140 --> 00:08:03,100 Now, don't forget to write the name of the schema before the name of the table. 85 00:08:05,140 --> 00:08:07,080 Execute it and refresh the folder. 86 00:08:14,290 --> 00:08:18,340 And here is a department's table transferred to human resources schema. 87 00:08:22,200 --> 00:08:25,590 Now, we won't be thwarted anymore. 88 00:08:26,990 --> 00:08:30,050 Let's execute the query to drop new schema. 89 00:08:33,970 --> 00:08:36,640 And that is it, it has been dropped. 90 00:08:39,630 --> 00:08:40,440 Views. 91 00:08:42,220 --> 00:08:50,020 Views are virtual tables defined by select statements that are used to simplify queries, edit access 92 00:08:50,020 --> 00:08:56,950 permissions, compare equivalent data from different servers or in some cases short and query time. 93 00:08:58,300 --> 00:09:04,120 In other words, view is a virtual table based on the results of an actual statement. 94 00:09:05,300 --> 00:09:12,410 The Create View Command is used to create a view and then a select statement follows it, views are 95 00:09:12,410 --> 00:09:13,700 not a real table. 96 00:09:13,700 --> 00:09:15,410 Of course they are virtual tables. 97 00:09:16,530 --> 00:09:22,010 So let's jump into as ASMs and create a view as an example. 98 00:09:23,030 --> 00:09:29,420 So in this example, we will work on the person table and the email address table. 99 00:09:30,810 --> 00:09:38,460 So we're going to retrieve first name, last name from the person table and the email address from the 100 00:09:38,460 --> 00:09:40,230 table called the email address. 101 00:09:47,120 --> 00:09:50,870 Now, we've learned to retrieve data for multiple tables with Joynes. 102 00:10:17,520 --> 00:10:21,670 It can be hard to do it this way every time that we need this data. 103 00:10:22,380 --> 00:10:29,550 So in other words, instead of doing it this way, we will create a view with this select statement 104 00:10:29,970 --> 00:10:30,930 to make it simpler. 105 00:10:31,920 --> 00:10:33,990 The person with mail address. 106 00:10:35,140 --> 00:10:38,460 Will be our view to retrieve this data. 107 00:10:39,780 --> 00:10:46,980 We will create a view when the query is executed and the commands are completed successfully, the view 108 00:10:46,980 --> 00:10:50,360 will be created under the views folder in the database. 109 00:10:50,610 --> 00:10:54,490 And when we expand that folder, we will see our view. 110 00:10:55,050 --> 00:10:55,800 Here it is. 111 00:10:57,230 --> 00:11:03,820 All right, so we don't need to create a new joint statement to retrieve this data anymore, view acts 112 00:11:03,820 --> 00:11:06,420 like a table and we can retrieve data from. 113 00:11:07,540 --> 00:11:11,560 So let's retrieve all data from the view that we've created. 114 00:11:12,870 --> 00:11:15,860 Execute the query perfect. 115 00:11:15,900 --> 00:11:16,970 Here's what we need. 116 00:11:17,700 --> 00:11:21,090 So as you can see, this is a huge difference of view. 117 00:11:21,120 --> 00:11:23,690 Always shows up to date data. 118 00:11:24,810 --> 00:11:31,960 The database engine recreates the data using the views ESKIL statement every time a user queries a view. 119 00:11:32,730 --> 00:11:33,510 How about that? 120 00:11:34,410 --> 00:11:37,710 And what about dropping the view to drop of you? 121 00:11:38,250 --> 00:11:45,240 The drop view command is used to drop or delete a view, and then we have to specify the name of the 122 00:11:45,240 --> 00:11:46,590 view that we want to drop. 123 00:11:47,180 --> 00:11:49,500 OK, so drop view and a view name. 124 00:11:50,280 --> 00:11:53,580 We will drop person with email address. 125 00:11:55,260 --> 00:12:01,860 Execute the query and refresh the view folder, and that'll show us our changes. 126 00:12:05,890 --> 00:12:07,840 And Verber.