SQL DDL commands

DDL:

• Data Definition Language or DDL changes the structure of the table like creating a table, deleting a table, altering a table, etc.
• All the command of DDL are auto-committed that means it permanently save all the changes in the database.
•  Here are some commands that come under DDL: 
• CREATE 
• ALTER 
• DROP 
• TRUNCATE

 CREATE

 CREATE It is used to create a new table in the database.

 Syntax:

 CREATE TABLE table_name (

    column1 datatype,
    column2 datatype,
    column3 datatype,
   ....
);

 Example:

CREATE TABLE EMPLOYEE(

Name VARCHAR2(20), 

Email VARCHA R2(100), 

DOB DATE);

Create Table Using Another Table:

A copy of an existing table can also be created using CREATE TABLE.

The new table gets the same column definitions. All columns or specific columns can be selected.

If you create a new table using an existing table, the new table will be filled with the existing values from the old table.

Syntax:

CREATE TABLE new_table_name AS
    SELECT column1, column2,...
    FROM existing_table_name
    WHERE ....;

Example:

CREATE TABLE TestTable AS
SELECT customername, contactname
FROM customers;

Drop

It is used to delete both the structure and record stored in the table.

 Syntax: 

DROP TABLE  table_name;

 Example:

 DROP TABLE EMPLOYEE;

 ALTER

 It is used to alter the structure of the database. This change could be either to modify the characteristics of an existing attribute or probably to add a new attribute.

 Syntax:

Add columm:

 ALTER TABLE table_name

ADD column_name datatype;

Example:

ALTER TABLE Customers
ADD Email varchar(255);

Drop column:

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

ALTER TABLE Customers
DROP COLUMN Email;

Rename column:

ALTER TABLE table_name
RENAME COLUMN old_name to new_name;

Modify column data type:

ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

 Example:

ALTER TABLE STU_DETAILS 

MODIFY column NAME VARCHAR2(20);

TRUNCATE

 It is used to delete all the rows from the table and free the space containing the table.

 Syntax:

 TRUNCATE TABLE table_name;

 Example:

 TRUNCATE TABLE EMPLOYEE;