DDL OPERATIONS:

The various DDL commands are CREATE, ALTER and DROP.

 

Let us learn about each DDL Command one by one with examples.

CREATE Statement:

 

The CREATE statement in SQL is used to create the database and the database objects like Tables, Views, Sequences, etc. Let us see how a database and table are created in SQL.

Creating a database in SQL:

 

Syntax:

CREATE DATABASE DatabaseName;

Example:

1. CREATE DATABASE Demos;   

On executing this query, a database named "Demos" is created.

 

Creating Table in SQL:

 

Database is a collection of related tables and other objects. This implies that a table is a part of the database. Now, we have created the "Demos" database. And, we need to create a "Students" table in this database.

By default, the master database is selected. Select Demos database from the dropdown list or alternatively you can use the following command:

Use Demos;

 

This command will instruct the SQL Server that you want to use the database Demos. Then we can create a table within Demos database. 

Syntax:

CREATE TABLE TableName(
   columnName1 datatype, 
   columnName2 datatype,

   .

   .

   columnNameN datatype);

Here, datatype is the type of data which you need to store in that column. For numeric values, we have "numeric" datatype, for strings we have "varchar" and so on. We will study about the datatypes in the upcoming articles.

Example:

1. Create Table Students  
2. (  
3.    StudentID numeric(3),  
4.    StudentName varchar(50)  
5. );  

This will create a table named Students with two columns, one StudentID which can contain a numeric value up to three digits and second StudentName which is varchar(50), which means that it can contain 50 characters.

We have created a table with two columns. Now, we need to add two more columns "City" and "marks" to the table Students. Then, how can we go do this?

This can be done using the ALTER Command of SQL. 

 

ALTER Statement

 

The Alter Table command helps us to modify the structure of the table. If we need to add, delete or modify the columns in our existing table then we use the Alter Table Statement.

Syntax:

To add column to the existing table:

1. ALTER TABLE ExistingTableName  
2. ADD ColumnName datatype  

To remove column from the existing table:

1. ALTER TABLE ExistingTableName  
2. DROP ColumnName  

To modify the datatype of a column in our existing table: 

1. ALTER TABLE ExistingTableName  
2. ALTER COLUMN ColumnName NewDatatype  

Example:

Let's add the City and marks columns to our table Students.

1. Alter table Students  
2. Add City varchar(25)  
3. Alter table Students  
4. Add marks numeric(3)  

DROP Statement:

 

As the name suggests, the DROP Statement is used to delete a table or a database.

Syntax:

To delete a table:

Drop Table TableName;

To delete a database:

1. Drop Database DatabaseName;  

Example:

1. Drop table Students;  

This will delete the Students table which we have created.

There may be situations when we just want to empty the table and not delete the table itself. In such situations, we use the Truncate Table statement of SQL.

Suppose we have data of 50 old students in our table. But, we don't need that data anymore. But, we need the table as we need to keep a record of our new students. In such situations, we will use the truncate table statement so that only the data gets deleted and the table is as it is.

Example:

1. Truncate table Students;