DDL (Data Definition Language) Commands in SQL

 Data Definition Language (DDL):

DDL is abbreviation of Data Definition Language. It is used to create and modify the structure of database objects in database.

CREATE – Creates objects in the database. Create Query is used to Create database and to create objects in the database. We can create Database, Tables, Procedures and Views etc... Using CREATE.

Create a Database

Syntax:

CREATE DATABASE <DB_NAME>

Example:

CREATE DATABASE VEDAZ

Create a table

Syntax:

CREATE TABLE <Table_NAME>

(

COLUMN_NAME1 DATATYPE,

COLUMN_NAME2 DATATYPE

       Etc...

)

Example:

CREATE TABLE tbl_Employees(

EmpID INT,

EmpName VARCHAR(100),

EmpDept VARCHAR(100)

)

ALTER – Alters objects of the database

Alter command is used to make the structural changes to a table or database.

We can change the data type of a particular column.

We can drop existing column or can add new columns to the existing table.

Now we will how to use Alter command programmatically.

Let us suppose we have a databse VEDAZ

                        It is having a table tbl_Employees

We have 3 columns in that table as below.

EmpID INT,

EmpName VARCHAR(100),

EmpDept VARCHAR(100)

While creating EmpDept column having data type VARCHAR(100).

Now we want to change the size of the data type to VARCHAR(50).

Then we have to write query as below.

Syntax:

ALTER TABLE <tbl_Name> ALTER COLUMN <Column_Name> <New_Data_Type>

Example:

ALTER TABLE tbl_Employees ALTER COLUMN EmpDept VARCHAR(100)

Then the data type will set to VARCHAR(50).

In the same way we can change the data type to INT,FLOAT etc….

Now we will see how to add/drop a column in the table.

ADD:

Syntax:

ALTER TABLE <tbl_Name> ADD <Column_Name> <Data_Type>

Example:

ALTER TABLE tbl_Employees ADD EmpAddress VARCHAR(200)

Drop:

Syntax:

ALTER TABLE <tbl_Name> DROP COLUMN <Column_Name>

Example:

ALTER TABLE tbl_Employees DROP COLUMN EmpAddress

In the same way we can add/remove Constraints (we will discuss on it in the upcoming classes), Identity etc…

DROP – Deletes objects of a database

Using drop we can drop a table, or column (Please check alter command above).

Here drop means delete permanently.

Syntax:

DROP TABLE <tbl_Name>

Example:

DROP TABLE tbl_Employees

TRUNCATE – TRUNCATE Command is used to remove all records from a table and reset the table identity to initial value.

                                   

Syntax:

TRUNCATE TABLE <tbl_Name>

    Example:

TRUNCATE TABLE tbl_Employees