Transcript for:
SQL Server Tutorial - Part 16

Hello friends, welcome to my channel. This is part 16 of SQL server tutorial. In today's session we are going to discuss about data definition language under SQL statements. Before continuing with this session, I would request you all please subscribe to my channel and click on bell icon for notification alerts.

Under data definition language, we have multiple statements like create, alter, drop, truncate and comment. We are going to discuss about each statement with the example. First we will see what is the data definition language.

It consists of the SQL commands that can be used to define the database schema. So whenever we are creating any schema we will use the data definition language. It simply deals with the description of the database schema and is used to create and modify the structure of the database objects in the database. So this will be used to create or modify the structure of the database objects in the SQL database.

We have some important SQL statements like create, alter, drop, truncate and comment. We will see one by one. First create.

It is used to create the database or its objects. Like we can create tables, index, functions, views, store procedures and triggers. whenever we are creating any object we will use the create statement this is the syntax for create statement create here we are going to create one database we have to provide the database here and database name we have to provide if you are creating any table we will pause the table keyword here and table name and whatever the columns you want we can provide here so this is the example for create statement create database Here database name, we are going to create one test DB for that I am using create statement and here This is the keyword of database and we are giving database name While creating table, we will use the create statement like this create table here we have to provide the table name and we are going to provide column names with the data types For demo purpose, I am using Microsoft SQL Server 2012 and I am going to use Windows authentication if you want you can use the sql server authentication by providing login id and password i am going to use windows authentication click on connect it has been connected to my database first we will create one database for that click on new query create is the statement i want to create database so here we have to provide the database name this is my database name testdb click on execute you can check it in the explorer so our database has been created here so like this you can use the create statement we will create one more table here under testdb create is the statement table we are going to create one table here we have to provide the table name i am giving table name as test table and here i have to provide column names i am giving as column name id and integer this is the data type click on execute your table is created you can expand the database refresh it here under tables our table has been created like this by using create statement we can create database and we can create table stored procedures triggers index so based on our requirement we will use the create statement next is alter statement it is used to add modify drop or rename a column or rename a table if you want to rename any object if you want to alter any object that time we will use the alter statement this is the syntax for alter statement alter table table name here we have to pause and we are going to add one new column for this existing table here we have to pause column name with the data type this is the example alter table this is the table name test table i am going to add one address column with the data type of webcar Switch to SQL Server and we will see the auto statement example.

We have the table called test table where it contains one column with the ID. For this table, I want to add one more column called address. for that I will use alter statement here I have to pause the table keyword and we have to pause the table name and going to add one column called address here and here we have to provide the data type and size I'm giving us 500 characters click on execute so our column is added now check the select statement so our address column has been added to the existing table like this we can use the alter statement next is drop statement this is used to remove database tables indexes triggers and constraints it's opposite to create statement in create we will use to create these objects whereas drop will be used to remove the objects like database table indexes triggers and constraints. This is the syntax for drop statement.

Drop, you have to pass the table keyword here and we have to pass the table name. We are going to drop one table here. This is the example. Drop table, test table is our table.

We'll see the example for drop statement. Suppose we have table called test table. We will drop this one. drop table you have to pass the table name that's it click on execute it's executed successfully if you come to tables refresh here our table has been dropped here next is truncated it is used to remove all records from a table including all spaces allocated for the recursor remote this truncate statement we will use to delete all the records from the table also it will remove all the spaces allocated for these records this is the syntax for the truncate statement truncate table we are going to pass table name here this is the example truncate table we have to pass the table name we will see the example for truncate statement first we will insert some records into this table I am inserting some records If you see the table now We have two records here. Now we will use the truncate statement Table we have to pass the table name here execute the query Executed successfully Now you check this select statement whether any records available So it's all are truncated from this query Like this we can use the truncate statement.

Next statement is comment. It is used to add comments to the data dictionary Whenever you want to add any comments to your SQL queries, we will use the comment We have different types of comments here that is single line comments multi line comments and inline comment. We will see one by one Single line comments. Comments starting and ending in a single line are considered as single line comments.

Single line comments we can't use more than a line. Line starting with hyphen. It is double hyphen is a comment and will not be executed. Line starting with double hyphen is a comment and will not be executed.

Whenever we use double hyphen it will not be executed. SQL server will treat this as a single line comment. and it will not be executed this is the syntax for single line comment whenever you want to use the single line comment you have to use the hyphen two times that is double hyphen and then you can start the single line comment this is the example for single line comment we'll see the example in sequence over click on new query and using one select statement to check the records from my table this is my table where it contains id and address for this i want to add single line comment so execute again still our query is able to execute because SQL will think that this is a single line comment we should not consider to compile this line okay suppose if I am removing one hyphen here execute no it throws the error because it's not able to identify it is a comment always we have to use the double hyphen then only it will treat it as comment like this we can use the single line comments multi-line comments comments starting in one line and ending in different lines are considered as mighty then comments you want to use more than a single line then you have to use the multi-line comments. Line starting with forward slash store is considered as starting point of the comment and are terminated when store forward slash is encountered.

So whenever we want to use the multi-line comments it starts with the forward slash store and then closes with the store forward slash. This is the syntax for multi-line comments. so whenever you want to use the multi-lane command you have to start with the forward slash store then you have to put all the comments then again you have to close with the star forward slash so here you can pass your queries this is the example we are going to pass the multi-lane comment here then finally we are going to pass our select query we will switch to sql server and we'll see the example for multi-lane comments so this is our query here we want to add the multi-line comments for that I am using forward slash star and again I'm closing with the here here here we can pass multiple comments suppose I am creating like this and I want to pause some data here so these are all comments I have added like this you can add multiple comments here so we are able to see the data also here like this we can pass multiple comments to your select query execute now it's executing fine so whenever we use this forward slash star and we are closing with the stop forward slash sequence server will not consider these block to execute okay it will treat it as comment like this we can use the multi-line comments next is inline comments inline comments are an extension of multi-line comments comments can be stated in between the statements so this is the syntax for inline comments select sour form table we are calling one query here where you can use inline comment like this It's like almost similar to multi-line comments whereas here you are using within the statement. This is the example for inline comments. Here we are going to pause inline comments.

We'll switch to SQL server and we'll see the example for inline comments. So this is our select query. Here I want to pause one inline comment for that I will use like this. so this is the inline comment try to execute this it's executing fine and these blocks are not considered as a query okay like this we can use inline comments in SQL server in today's session we have discussed about data definition language and also what are all the statements available inside the data definition language like create alter draw truncate and comment also we had seen example for each statement In next session we are going to discuss about data query language and select statement inside the data query language That's it for today.

Thanks for watching