The Data Definition Language (DDL)

The Data Definition Language (DDL) is used to create and destroy databases and database objects. These commands will primarily be used by database administrators during the setup and removal phases of a database project. Let's take a look at the structure and usage of four basic DDL commands:

DDL Cover following part of SQL:

·                     CREATE DATABASE - creates a new database

·                     ALTER DATABASE - modifies a database

·                     CREATE TABLE - creates a new table

·                     ALTER TABLE - modifies a table

·                     DROP TABLE - deletes a table

·                     CREATE INDEX - creates an index (search key)

·                     DROP INDEX - deletes an index

---

SQL CREATE DATABASE

The CREATE DATABASE statement is used to create a database.

Syntax:

CREATE DATABASE database_name

Example:

Now we want to create a database called "my_Database", We use the following CREATE DATABASE statement:

CREATE DATABASE my_Database

---

SQL ALTER DATABASE

For More Details follow these links:

http://msdn.microsoft.com/en-us/library/ms174269.aspx

http://msdn.microsoft.com/en-us/library/aa275464(v=sql.80).aspx

---

SQL DROP DATABASE

The DROP DATABASE statement is used to delete a database.

Example:

DROP DATABASE database_name

---

SQL CREATE TABLE

The CREATE TABLE statement is used to create a table in a database.

Syntax:

CREATE TABLE table_name ( column_name1 data_type, column_name2 data_type, column_name3 data_type, .... )

* data_type : Specify Column Datatype (ie, int,varchar(n) etc.)

SQL CREATE TABLE Example

Now we want to create a table called "Persons" that contains five columns: P_Id, LastName, FirstName, Address, and City.
We use the following CREATE TABLE statement:

CREATE TABLE Persons ( P_Id int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) )

The P_Id column is of type int and will hold a number. The LastName, FirstName, Address, and City columns are of type varchar with a maximum length of 255 characters.
Note: You can see the table created by select * from Persons Statement.
The empty "Persons" table will now look like this:

P_Id	LastName	FirstName	Address	City

---

SQL ALTER TABLE STATEMENT

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

Syntax:

• To add a column in a table, use the following syntax:

ALTER TABLE table_name ADD column_name datatype

• To delete a column in a table, use the following syntax (notice that some database systems don't allow deleting a column):

ALTER TABLE table_name DROP COLUMN column_name

• To change the data type of a column in a table, use the following syntax:

ALTER TABLE table_name ALTER COLUMN column_name datatype

Example:

Suppose we have a table

ID	Name	City
1	Ram	Agra

Now I want to add new column with name DOB Than,

ALTER TABLE tblPersons ADD DateOfBirth date

Result will look like this

ID	Name	City	DOB
1	Ram	Agra

---

SQL Drop Table Statement

The DROP TABLE statement is used to delete a table.

Syntax:

DROP TABLE table_name

---

SQL CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEX index_name ON table_name (column_name1, column_name2, column_name3….)

* index_name is the name of the INDEX.

* table_name is the name of the table to which the indexed column belongs.

* column_name1, column_name2.. is the list of columns which make up the INDEX.

Example:

CREATE INDEX PIndex ON Persons (LastName)     ** For Single Column

CREATE INDEX PIndex ON Persons (LastName, FirstName)     ** For Single Column

---

SQL Drop Index

Indexes, tables, and databases can easily be deleted/removed with the DROP statement.

Syntex:

DROP INDEX table_name.index_name