SQL For Beginners - DDL Statements

In this article, we are going to learn about the DDL Language Statements of SQL. We have seen a brief overview regarding the SQL DDL Statements in the introductory article SQL For Beginners - Introduction. Please go through it if you are not aware of the various types of SQL Commands.

 

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:

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:

Create Table Students
(
   StudentID numeric(3),
   StudentName varchar(50)
);

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:

ALTER TABLE ExistingTableName
ADD ColumnName datatype

To remove column from the existing table:

ALTER TABLE ExistingTableName
DROP ColumnName

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

ALTER TABLE ExistingTableName
ALTER COLUMN ColumnName NewDatatype

Example:

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

Alter table Students
Add City varchar(25)
Alter table Students
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:

Drop Database DatabaseName;

Example:

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:

Truncate table Students;

This was a brief overview of the various DDL Statements in SQL. I hope that this article helped you. We have worked with the table and its structure over here. In the upcoming articles, we will learn to insert, delete, modify the data in our table.

Stay tuned. Happy learning!