SQL Data Definition Language (DDL)

Introduction to 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,
    1. CREATE
    2. DROP
    3. ALTER
    4. RENAME
    5. TRUNCATE
  • 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:
    DROP TABLE <table_name>;
    OR
    DROP DATABASE <database_name>;

    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;
▲