SQL DDL Statements

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

 

CREATE TABLE

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

CREATE TABLE AS

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




ALTER TABLE

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;

DROP TABLE

To drop a table from a database

Syntax: DROP TABLE table_name;

Example: DROP TABLE booklist;

COMMENTS

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

RENAME TABLE

To rename a table in the database

Syntax: ALTER TABLE table_name RENAME TO new_table_name;

Example: ALTER TABLE Players RENAME TO Sportsperson;