Data Definition Language or Data Description Language(DDL)

Data Definition Language:

A data definition language or data description language (DDL) is a syntax similar to a computer  programming language for defining  data structures, especially  database schemas. Many data description languages use a declarative syntax to define fields and data types. SQL, however, uses a collection of imperative verbs whose effect is to modify the schema of the database by adding, changing, or deleting definitions of tables or other objects. These statements can be freely mixed with other SQL statements, so the DDL is not truly a separate language.

CREATE statements

Create - To make a new database, table, index, or  stored procedure.

A CREATE statement in  SQL creates an object in a  relational database management system (RDBMS). In the SQL 1992 specification, the types of objects that can be created are schemas,  tables,  views, domains, character sets, collations, translations, and assertions. Many implementations extend the syntax to allow creation of additional objects, such as  indexes and user profiles. Some systems (such as  PostgreSQL) allow CREATE, and other DDL commands, inside a  transaction and thus they may be  rolled back.

CREATE TABLE statement

A commonly used CREATE command is the CREATE TABLE command. The typical usage is:

CREATE TABLE [table name] ( [column definitions] ) [table parameters].

column definitions: A comma-separated list consisting of any of the following

Column definition: [column name] [data type] {NULL | NOT NULL} {column options} 

Primary key definition: PRIMARY KEY ( [comma separated column list] )

Constraints: {CONSTRAINT} [constraint definition]

RDBMS specific functionality

For example, the command to create a table named employees with a few sample columns  would be:

CREATE TABLE employees (

 id INTEGER PRIMARY KEY,

 first_name VARCHAR(50) NULL,

 last_name VARCHAR(75) NOT NULL,

 fname VARCHAR(50) NOT NULL,

 dateofbirth DATE NULL

);

DROP statements

Drop - To destroy an existing database, table, index, or view.

A DROP statement in  SQL removes an object from a  relational database management system (RDBMS). The types of objects that can be dropped depends on which RDBMS is being used, but most support the dropping of  tables, users, and  databases. Some systems (such as  PostgreSQL) allow DROP and other DDL commands to occur inside of a  transaction and thus be  rolled back.

DROP objecttype objectname.

For example, the command to drop a table named employees would be:

DROP employees;

The DROP statement is distinct from the  DELETE and  TRUNCATE statements, in that DELETE and TRUNCATE do not remove the table itself. For example, a DELETE statement might delete some (or all) data from a table while leaving the table itself in the database, whereas a DROP statement would remove the entire table from the database.

ALTER statements

Alter - To modify an existing database object.

An ALTER statement in  SQL changes the properties of an object inside of a  relational database  management system (RDBMS). The types of objects that can be altered depends on which RDBMS is being used. The typical usage is:

ALTER objecttype objectname parameters.

For example, the command to add (then remove) a column named bubbles for an existing table named sink would be:

ALTER TABLE sink ADD bubbles INTEGER;

ALTER TABLE sink DROP COLUMN bubbles;

Rename statement

Rename - to rename the table. for example

RENAME TABLE old_name TO new_name;

Referential integrity statements

Finally, another kind of DDL sentence in SQL is one used to define referential integrity relationships, usually implemented as primary key and foreign key tags in some columns of the tables. These two statements can be included inside a CREATE TABLE or an ALTER TABLE sentence.