DDL Commands in SQL/Oracle ( Step by step Explanation with examples)

 Hi All,

As we discussed in our previous post, Today we will discuss In-Detailed about SQL commands.

1. DDL Commands

2. DML Commands

3. DQL Commands

4. TCL Commands

5. DCL Commands.

We will discuss one by one. First we will discuss about DDL Commands.

1. DDL Commands (Data definition Language):-

DDL commands are used to define the structure of the table/Database objects.

DDL Commands are further divided into 5 types. If you search about DDL commands, Normally You will get only 4 types But actually these are 5 types i.e 5th one is RENAME (introduced in oracle 9i).

A) Create

B) Alter

C) Drop

D) Truncate

E) Rename

NOTE : DDL commands are by default auto commit. No need to use commit command . 

              Commit means just like save option.

A)CREATE: 

This command is used to create database objects like tables, views, synonyms, index ..etc.

First we will see table creation. Below is the standard syntax of table creation

Syntax:-  

CREATE TABLE  tablename(columnname1 datatype(size), columnname2 datatype(size));

Example: 

CREATE TABLE Mytable(sno number(10), name varchar2(20));

So we have created a table. Now if we can to see the structure of the table then below is the command.

Syntax:-      

            Desc tablename;

Example : 

            Desc Mytable;

Output :  Name   type

                --------  ------

                sno        number(10)

                name      varchar2(20)

So the DESC command is used to describe/know about the table structure. This command won't return any data, it just shows structure.

B) ALTER:

Alter command is used to change the structure of the existing table/Database object.

Further ALTER also dived into 3 type

• ADD
• MODIFY
• DROP

>ALTER With ADD is used to add a column to the existing table.

Syntax: 

ALTER TABLE tablename ADD(columnname3 datatype(size));

Example : 

Alter table Mytable add(salary number(10));

>ALTER with MODIFY is used change the existing column data type (or) data type size.

syntax: 

ALTER TABLE tablename MODIFY(columnname1 datatype(size));

Example: 

Alter table Mytable modify salary number(8);     (Size changed from 10 to 8)

>ALTER with DROP is used to drop the columns from table.

syntax: 

ALTER TABLE tablename DROP COLUMN columnname;

Example :  

Alter table Mytable drop column salary;

NOTE: In Oracle, System won't allow to drop all the columns in a table.

C) DROP :-

Drop command is used to drop the database objects from database. Simple way , this is like delete

option in windows.

Syntax: 

DROP OBJECTTYPE  Objectname;

ObjectType means tables, views, Index, Procedure...etc.

Example : 

Drop table Mytable;

Note : Before oracle 10G version, we don't have the option to get the dropped table/object. In Oracle 10g version, Oracle introduced the "Flashback" command to get the dropped object( Same like Recycle bin in windows).

If we want to drop the table/database object permanently, We have to use PURGE command with DROP.

Syntax:-  

Drop table Mytable PURGE;

We have to be sure before using DROP with PURGE command. Because we can't reverse the operation.

D) TRUNCATE :-

Truncate command is used to delete/remove the all rows(data) from table permanently. In this case we won't have flashback option to restore. Normally these kind of operations performs by Database Admin.

Syntax: 

TRUNCATE TABLE tablename;

Example : 

Truncate table Mytable;

E) RENAME:-

Rename command is used to renaming a table/Database object and same way, we can rename a table column also.

rename table:  

RENAME existingTablename  to NewTableName;

Rename Column: 

ALTER table tablename RENAME column oldColumnName to NewColumnName;

                                                    Thank You