Some findings from Oracle : Kanchan K Bakshi

Different Languages in SQL - Oracle

a) Data Definition Language i.e DDL

b) Data Manipulation language i.e DML

c) Data Control Language i.e DCL

Question : What is DDL?

a) DDL stands for Data definition Language.

b) It is use to create or modify the definition or structure of an object, schema or a database.

c) DDL commands can not be undone. As you fire them their effect will get saved permanently i.e. DDL commands are auto commit.

Different type of commands comes under DDL:

a) Create : create is use to create an object (table,view,synonym,index) ,schema or a database .

b) Alter : alter is use to modify the structure of an object or database.

c) Drop : drop is use to drop an object ,schema or database.

d)Truncate: Truncate is use to delete all the data from the table. It will not affect the structure of table. You can not use condition with truncate command.

Create

Syntax to create a table:

create table table_name(col1 datatype,col2 datatype…..);

For e.g.: My requirement is:

a) Create a new table students to store students records

b) table should have following columns:

stu_id,stu_name,stu_age

Then the command would be:

Create table students(stu_id number,stu_name varchar2(10),stu_age number);

Create table is like Building a House.

Alter

Syntax to alter a table :

alter table table_name add|modify column column_name datatype;

Now, I want to add one more column to my students table.As table already exist I can not use create command.

To modify the existing structure I will use alter command.

Eg: I want to add a column stu_ph_no then the command will be:

Alter table students add column stu_ph_no number;

Alter is like adding a new room to existing house.

Drop

Syntax to drop a table:

Drop table table_name.

Now ,I want to drop students tables then the command will be:

Drop table students;

Drop is like demolishing a house.

Ps: drop will remove all the data from the table and delete the structure of a table.

Truncate

Syntax to truncate a table:

truncate table table_name;

Now,I want to remove all the data from students table the command will be:

Truncate table students;

Truncate is like removing all the things from house and making it empty.

P.S: truncate will not drop the structure of a table. It will only remove data. You can not use "where" Clause with truncate command.