DDL Command Operation in SQL

DDL Commands (Create, Alter, Drop, Truncate, Comment and Rename) and DDL Operations (Create a Database, Drop Database, Create a Table, Truncate a Table and Drop a Table etc,)
Post Reply
AyubHussain
Posts: 138
Joined: Fri Nov 23, 2018 2:48 am

DDL Command Operation in SQL

Post by AyubHussain » Tue Dec 04, 2018 12:19 pm

Explain DDL Command Operation in SQL with Example?

manoj
Posts: 87
Joined: Fri Nov 23, 2018 5:29 am

Re: DDL Command Operation in SQL

Post by manoj » Tue Dec 04, 2018 12:21 pm

The Create Table Command

The create table command defines each column of the table uniquely. Each column has minimum of three attributes.
Name
Data type
Size(column width).

Each table column definition is a single clause in the create table syntax. Each table column definition is separated from the other by a comma. Finally, the SQL statement is terminated with a semicolon.

Example:

CREATE TABLE Student
(Reg_no varchar2(10),
Name char(30),
DOB date,
Address varchar2(50));

The DROP Command

Syntax:
DROP TABLE <table_name>

Example:
DROP TABLE Student;
It will destroy the table and all data which will be recorded in it.

The TRUNCATE Command

Syntax:
TRUNCATE TABLE <Table_name>

Example:
TRUNCATE TABLE Student;

The RENAME Command

Syntax:
RENAME <OldTableName> TO <NewTableName>

Example:
RENAME <Student> TO <Stu>

The old name table was Student now new name is the Stu.

The ALTER Table Command

By The use of ALTER TABLE Command we can modify our exiting table.

Adding New Columns

Syntax:
ALTER TABLE <table_name>
ADD (<NewColumnName> <Data_Type>(<size>),......n)

Example:
ALTER TABLE Student ADD (Age number(2), Marks number(3));

The Student table is already exist and then we added two more columns Age and Marks respectively, by the use of above command.

Dropping a Column from the Table

Syntax:
ALTER TABLE <table_name> DROP COLUMN <column_name>

Example:
ALTER TABLE Student DROP COLUMN Age;

This command will drop particular column

Modifying Existing Table

Syntax:
ALTER TABLE <table_name> MODIFY (<column_name> <NewDataType>(<NewSize>))

Example:
ALTER TABLE Student MODIFY (Name Varchar2(40));

The Name column already exist in Student table, it was char and size 30, now it is modified by Varchar2 and size 40.

Restriction on the ALTER TABLE

Using the ALTER TABLE clause the following tasks cannot be performed.
Change the name of the table
Change the name of the column
Decrease the size of a column if table data exists

Post Reply

Return to “SQL - Data Definition Language”