hi guys this is Mina. Today we are going to create
objects in sql server management studio using T-SQL
codes. Objects such as databases and tables. At the end of this video, you
will know what is a database, a schema, and a table. You'd also learn
how to use t sql codes to create databases schemas
and tables this is the SQL server management studio
interface and one thing you have to know is that
tables and schemas reside in databases. so for instance, this is my databases
folder if I open it all of these are databases
i click on the plus sign you'd see tables. The tables that we will create
will reside under tables and schemas should reside under security.
So in order to write a t-sql code you have to first go to
new query and in this canvas we are going to type out
our t-sql code for creating database first because remember schemas and tables
reside in database. Database helps us to collect and structure our data it helps
us to store it in such a way that we can easily retrieve and manipulate it for
analysis. Here in sql server management studio we
have the object explorer and that's where we can find our databases
and it organizes our data so that we can easily retrieve and manipulate it
for analysis. so the syntax for creating
any object in sql server management studio
is create. so if it's a database, it's create database and then you give the
database a name so i'm just going to go ahead and type
that. Create database
and the name of the database is student records
that's just a name i came up with and then you execute.
you can click on this execute or you can click on F5. command completed
successfully it means that our database has been
created so we can refresh our main database folder and then we
should see our student records created. so there it is student records,
so under student record database we are going to create
our schema and then create our table. A database schema helps us to logically
group all our tables under one qualifying name so if all our
tables are grouped under one name then we can easily apply security
permissions for protecting our tables. So we have to
create our schema under the database instead of creating
it under the main Master database. I'm going to
use our newly created
database and execute. if you don't want to use
code to switch from one database to another, you can just click
on this drop down arrow and select the appropriate
database. Like i said at first, if you want to create anything at all
in sql server management studio use CREATE
and this time we are going to create a schema
and the name of the schema i'm going to call it mega Class
and execute. Now you can see that my code has red underlining, if
the underlying annoys you just separate your
codes from one another using Go. Now we are going to create our table.
Table is a database object structured in the form of
rows and columns. so you have rows as the horizontal and columns would be
the vertical axis, if you will
columns in a table can also be referred to as fields
and rows in a table can also be referred to as
records. Code for writing a table is create
table and then the table name, i'm going to call this table
students. Mega class is the schema and then student is the actual table
name so you have the schema dot the table name. And then the columns
for the table, you need to put them into grounded brackets. The first column is
going to be student id and you specify the data type
which is an integer and then if you wish to make it an identity
key, you type identity and it's gonna have seed value of one
means it's going to start from one and it's going to increment by one so you're
gonna have one two three as our student id
and as a student id it's also a primary key
so you just type primary key separate by a comma and then you go to the next
column which will be first name and the data
type is nvarchar. Nvarchar
is more like a string of variable length and it accepts unicode as well. You need
to specify the string length, i'm going to give it 50 and
go on to the next column name which is last name it's also an nvarchar
of length 50. Students need to have age which is going
to be an integer you don't need to specify
length for an integer. i am going to have email
address that's an nvarchar of length 50. I just want to be
consistent in my string lengths, so no special reason and what about
gender nvarchar or varchar and the length
is 50. so that's it for our first table. we can go ahead and press F5
or execute to create our first table. I'm going to go ahead and
create two more tables for our student record database.
so i have my table and this is the schema name
dot the table name instructor and then these are all columns. so the first column
is the instructor id, the data type is an integer
and it's an identity okay and this time i didn't specify
primary key here because i want to actually do it in a different way.
Primary key is a constraint, so the code for explicitly creating a
primary key constraint is constraint and then you give the constraint and name.
so i have constraint constraint name and then it's a primary key and
what column is the primary key? instructor id.
this is also another way for creating a primary key so you can go ahead and
execute and it should be there as a primary key.
Last table i'm going to create is the courses table
so as usual i have my table name and the columns and then i want to
link the student table and instructor table to
the courses table which means that the primary keys for
student and instructor tables should be listed here
also as foreign keys. so I already have instructor id
which is a primary key in the instructor table. Here i'm just going to make it a
foreign key by typing in foreign key and then which table does it
reference, so it references the instructor table and then which
column in the instructor table? it's the
instructor id and that's the code for creating a
foreign key in another table. i'm going to create another
foreign key constraint for the student id linking the student table
to the courses table. This time i'm going to use
the constraint method and it's going to be,
constraint and the name of the constraint
i'm going to preface it with foreign key (FK) and then the schema name, the foreign key
table name and the primary key table name and that
is the foreign key what column in the courses table is the
foreign key? it's student id and it references
the students table which column in the student table? it's
the student id.
okay so this is how you can use constraint to also create foreign keys
for columns so we can go ahead and execute this.
okay so if you want to see these tables you can refresh the
database and go to tables
there we have our tables. You can also go to database diagrams,
It is the relational database schema for our
student record database. If you want to see the primary and
foreign keys associated with each table we can click
on the plus sign beside each table, go to columns and then we have the keys
that's a primary key course id and pk represents a primary
key and also here instructor id and
student id are foreign keys. if you do not
explicitly state that your columns do not allow nulls, then the system will
automatically state that it should allow nulls. So you have null
beside each column. But primary key cannot contain null so
that's also because we still have the primary key
it says not null. okay so for instance if we do not want any
um of these columns to be null, we can just type
not null
so under security, we go to schemas and we can find our mega class schema.
thank you so much for staying with me on this tutorial,
and see you in the next one. Have a good day.