Hello, dear friends.
So in this section, we're going to learn about Ashwell schema objects.
So this is a subject where we're going to learn how to create and manage schemas and.
How to create views and how to create and use sequences.
So let's get started with schema.
All right, so what is a schema Enescu server?
A schema is a collection of database objects, including tables, views, triggers, indexes, etc.
A schema is associated with a username which is known as the schema owner, who is the owner of the
logically related database.
Objects to a schema, always belongs to one database, on the other hand.
A database may have one or multiple schemas.
I'll tell you this, for nothing schema makes work simple.
In general, we may need to give permission to tables and we set privileges one by one, and this will
make management really difficult.
Well, tedious, right?
So instead of giving permissions to a table one by one, the tables which are related are created under
a schema and then the schema can be assigned a user.
So how do you create a schema?
Well, the create schema command, that's how you create a schema, then the schema name should be specified.
It's optional that the owner name can be specified with authorization command, but you can determine
that yourself schema and the schema owner can be specified in the same query.
So let's go ahead and jump over to assess Hamas and get started in creating a scheme.
So our scheme, an MLB test scheme, and we can specify the schema owner with authorization.
And schemas are created for a database, so the users should be created in that database beforehand.
So we will create this test schema for adventure works database and we will choose the test underscore
user in this database.
Now execute the query and the commands complete successfully.
Schemas are stored in the schemas folder under security in the database.
And here's our test schema, and you can see it has been created successfully.
Now, it's possible that schemas can't be created graphically in order to create a new schema just right,
click on the schema folder and select the new schema option schema properties pop up, windows going
to be open, and the schema name and authorization user are specified in this window.
So if the schema owner is not specified, Devaux user will be authorized as default.
And we will select test user by clicking on the search button.
Go ahead and click, OK, to create the schema.
Now, how can we create a table in scheme?
So we've learned how to create a table and have a schema specified with a table name, the table will
be created in that schema.
There's really not any difference about how you go about creating a table.
So this create a table in the schema with an example.
And the previous section, we learned how to create a table in a database, so of any schema will not
be specified.
The table will be created in the schema DBO.
Devo schema is a default schema that every table will be created in as a default.
So let's execute this query and check the schema of the table.
We'll need to refresh the folder to get those last changes.
Here's the department's table.
It's been created in the DBO schema by default.
You create a table in a specific schema, we should write this schema name before the table, name in
the create table statement.
If we execute again, we will see that departments is the table that's been created in the test schema.
We will create the department's table in this scheme.
So how can we change the schema of a table?
Alter schema and the transfer command is used to change the schema of a table.
Table is then transferred from one schema to another.
So let's create a new schema with a name, new schema and the Adventure Works database.
Now we have two custom schemas, new schema and test schema.
So in this example, what we're going to do is transfer the department's table from test schema to new
schema.
Of course, it's not possible to change the schema of a table just by renaming the table.
When we try to rename the department's table, for instance, the schema name disappears.
So we have to build a query to transfer that table.
And we will alter the new schema, the table name should indeed be specified with a schema name.
And execute the query.
And refresh the table folder to get the last changes.
Great, the department's table has been transferred to the new schema successfully.
And the other schema has been removed from the list as well.
Now, what about dropping the scheme?
There is a drop schema command and that's used to drop or delete the schema from the database.
So in this example, we are going to drop the schemas that we have created, the new schema and the
test schema.
That's just drop the test scheme of first.
Execute the query and refresh the list.
Bubby.
So let's drop new schema as well.
Now we're going to get an error if we ask you the query, SQL Server will not allow users to delete
a schema that has tables in it.
The department's table is still in the new schema, so we will not be able to delete new schema until
we have transferred or removed the department's table in it.
So let's transfer department's table from new schema to human resources schema, which is created by
the Adventure Works Database during setup of the escrow server.
Right, so now we've learned transferring the table from a schema to another schema.
So we will alter the human resources scheme.
Now, don't forget to write the name of the schema before the name of the table.
Execute it and refresh the folder.
And here is a department's table transferred to human resources schema.
Now, we won't be thwarted anymore.
Let's execute the query to drop new schema.
And that is it, it has been dropped.
Views.
Views are virtual tables defined by select statements that are used to simplify queries, edit access
permissions, compare equivalent data from different servers or in some cases short and query time.
In other words, view is a virtual table based on the results of an actual statement.
The Create View Command is used to create a view and then a select statement follows it, views are
not a real table.
Of course they are virtual tables.
So let's jump into as ASMs and create a view as an example.
So in this example, we will work on the person table and the email address table.
So we're going to retrieve first name, last name from the person table and the email address from the
table called the email address.
Now, we've learned to retrieve data for multiple tables with Joynes.
It can be hard to do it this way every time that we need this data.
So in other words, instead of doing it this way, we will create a view with this select statement
to make it simpler.
The person with mail address.
Will be our view to retrieve this data.
We will create a view when the query is executed and the commands are completed successfully, the view
will be created under the views folder in the database.
And when we expand that folder, we will see our view.
Here it is.
All right, so we don't need to create a new joint statement to retrieve this data anymore, view acts
like a table and we can retrieve data from.
So let's retrieve all data from the view that we've created.
Execute the query perfect.
Here's what we need.
So as you can see, this is a huge difference of view.
Always shows up to date data.
The database engine recreates the data using the views ESKIL statement every time a user queries a view.
How about that?
And what about dropping the view to drop of you?
The drop view command is used to drop or delete a view, and then we have to specify the name of the
view that we want to drop.
OK, so drop view and a view name.
We will drop person with email address.
Execute the query and refresh the view folder, and that'll show us our changes.
And Verber.