Data Definition Language(DDL) Statements are used to perform
- Create, Alter, Drop Schema Objects
- Analyse information on an index, table, object
- Setting up auditing option at object level
- Adding comments to the data dictionary
The CREATE TABLE Statement is used to create a TABLE in a Database.
Syntax:
CREATE TABLE table_name
(
Column_name1 data_type(size);
Column_name2 data_type(size);
Column_name3 data_type(size);
);
Here data_type specifies the type of data (int, char, varchar…etc) than can be stored in the column.
Example:
CREATE TABLE EMPLOYEES (EMPLOYEE_ID int, FIRST_NAME varchar (20), LAST_NAME varchar (20), SALARY int);
The CREATE TABLE AS Statement is used to create a TABLE from the columns of an existing table.
Syntax:
CREATE TABLE new_table AS (SELECT * FROM old_table);
Example:
CREATE TABLE voter_list AS (SELECT * FROM HEALTH_DEPT where age > 18);
CREATE TABLE by copying selected columns from old table
Syntax:
CREATE TABLE new_table AS (SELECT col_1, col_2, col_3 from old_table);
Example:
CREATE TABLE voter_list AS (SELECT FIRST_NAME, LAST_NAME, GENDER from HEALTH_DEPT where age >18);
The ALTER TABLE statement is used to add, delete, drop, rename a column or rename a table.
Syntax:
To add a column in a table: ALTER TABLE table_name ADD (column_name column-definition);
Example:
ALTER TABLE voter_list ADD (EMPLOYED char (20));
To add multiple column in a table: ALTER TABLE table_name ADD (col1 col-definition, col_2 col-definition, col_3 col-definition);
Example:
ALTER TABLE voter_list ADD (EMPLOYED CHAR (20), ADDRESS VARCHAR (30), PINCODE INT);
DROP COLUMN
This is used to drop a column from a table
Syntax: ALTER TABLE table_name DROP column_name;
Example: ALTER TABLE voter_list DROP address;
RENAME COLUMN
This statement is used to rename a column in a table
Syntax: ALTER TABLE table_name RENAME COLUMN old_name TO new_name;
Example: ALTER TABLE voter_list RENAME COLUMN address TO location;
To drop a table from a database
Syntax: DROP TABLE table_name;
Example: DROP TABLE booklist;
To add a descriptive comment about any column
Syntax: COMMENT ON COLUMN table_name.column_name is ‘This column talks of name’;
Example: COMMENT ON COLUMN BOARD.Director_Name IS ‘This gives the list of Board of Directors’;
To rename a table in the database
Syntax: ALTER TABLE table_name RENAME TO new_table_name;
Example: ALTER TABLE Players RENAME TO Sportsperson;