SQL Data Definition Language (DDL)

DDL stands for Data Definition Language.
It is a language used for defining and modifying the data and its structure.
It is used to build and modify the structure of your tables and other objects in the database.

DDL commands are as follows,

These commands can be used to add, remove or modify tables within a database.
DDL has pre-defined syntax for describing the data.

1. CREATE COMMAND

CREATE command is used for creating objects in the database.
It creates a new table.

Syntax:
CREATE TABLE <table_name>
(    column_name1 datatype,
     column_name2 datatype,
     .
     .
     .
     column_name_n datatype
);

Example : CREATE command

CREATE TABLE employee
(
     empid INT,
     ename CHAR,
     age INT,
     city CHAR(25),
     phone_no VARCHAR(20)
);

2. DROP COMMAND

DROP command allows to remove entire database objects from the database.
It removes entire data structure from the database.
It deletes a table, index or view.

Syntax:

Example : DROP Command

DROP TABLE employee;
OR
DROP DATABASE employees;

If you want to remove individual records, then use DELETE command of the DML statement.

3. ALTER COMMAND

An ALTER command allows to alter or modify the structure of the database.
It modifies an existing database object.
Using this command, you can add additional column, drop existing column and even change the data type of columns.

Syntax:
ALTER TABLE <table_name>
ADD <column_name datatype>;

OR

ALTER TABLE <table_name>
CHANGE <old_column_name> <new_column_name>;

OR

ALTER TABLE <table_name>
DROP COLUMN <column_name>;

Example : ALTER Command

ALTER TABLE employee
ADD (address varchar2(50));

OR

ALTER TABLE employee
CHANGE (phone_no) (contact_no);

OR

ALTER TABLE employee
DROP COLUMN age;

To view the changed structure of table, use 'DESCRIBE' command.
For example:
DESCRIBE TABLE employee;

4. RENAME COMMAND

RENAME command is used to rename an object.
It renames a database table.

Syntax:
RENAME TABLE <old_name> TO <new_name>;

Example:
RENAME TABLE emp TO employee;

5. TRUNCATE COMMAND

TRUNCATE command is used to delete all the rows from the table permanently.
It removes all the records from a table, including all spaces allocated for the records.
This command is same as DELETE command, but TRUNCATE command does not generate any rollback data.

Syntax:
TRUNCATE TABLE <table_name>;

Example:
TRUNCATE TABLE employee;

« Previous