SQL DATA DEFINATION STATEMENTS

SQL STATEMENTS.

DATA DEFINITION  STATEMENTS 

DDL- Data Definition Language 

DDL command for defining relation schema. The DDL commands are : CREATE, ALTER, DROP, RENAME, TRUNCATE

a) CREATE 

It Define the name of tables and columns , as well as specifies the type of data allowed in each columns.

Syntax:

 CREATE TABLE <table_name>
( co1_1 datatype<Size>,
  col_2 datatype<Size>,
.
.
  con_n  datatype<size)
 );


Example:

 Create emp table having eno, ename and salary columns

CREATE TABLE EMP
(
 EMPNO NUMBER(3),
ENAME VARCHAR2(20),
SAL NUMBER(8)
);


b) ALTER  Statements

Alter operations is used for chaining the definition of tables.
It can be used to add columns, drop columns or modify definition of existing column.

i)  ADD Column 

Syntax:
ALTER TABLE <tablename>  ADD <col_name> DATATYPE.


Example:
ALTER TABLE EMP ADD EMAILID VARCHAR2(20);

ii) DROP Column
Syntax:
ALTER TABLE <tablename>  DROP COLUMN  <col_name> DATATYPE.

Example:
ALTER TABLE EMP DROP COLUMN  EMAILID VARCHAR2(20);

iii) Change the Definition of Existing Column.
  Syntax:
ALTER TABLE <tablename>  MODIFY  <col_name> DATATYPE.

Example:

If you want to resize EMAILID column size VARCHAR2(20) TO VARCHAR2(30)
  ALTER TABLE EMP MODIFY  EMAILID VARCHAR2(30);


c) DROP Statements

DROP TABLE command is used for deleting tables from the schema. It can be used to delete all rows currently in the named table and to remove the entire definition of the table from the schema. 

Syntax:

DROP TABLE<table_name>;

Example:

DROP TABLE EMP;

This command drop EMP table from the schema.

d) RENAME Statement

RENAME  COMMAND is used to change the existing name of the table to new name.


Syntax:

RENAME  <old_table_name> TO <new_table_name>

Example:

RENAME EMP TO EMPLOYEES;
This command change table name EMP TO EMPLOYEES. 

e) TRUNCATE Statements

TRUNCATE TABLE is used for deleting data from tables but not the table itself. 

Difference between TRUNCATE and DELETE is,
DELETE is the DML and TRUNCATE is DDL command.
Data delete by using DELETE command  can be recover by ROLLBACK statement. 
But if Data delete by using TRUNCATE command it can't be recover, it lost permanently.

Difference between TRUNCATE is used for deleting data from tables not the table itself. 
DROP command delete data as well as table itself.

Syntax:

TRUNCATE TABLE <table_name>;

Example: 

TRUNCATE TABLE EMP;

For more query Ask Here......

 

SHARE

Milan Tomic

Hi. I’m Designer of Blog Magic. I’m CEO/Founder of ThemeXpose. I’m Creative Art Director, Web Designer, UI/UX Designer, Interaction Designer, Industrial Designer, Web Developer, Business Enthusiast, StartUp Enthusiast, Speaker, Writer and Photographer. Inspired to make things looks better.

• 
• 
• 
• 
•