Data Definition Language (DDL) Statements

CREATE Statement : Create statement used to create data base objects like tables, views, indexes, triggers and synonyms etc..

CREATE TABLE : Syntax is create table table_name(column1 data type,column2 data type,…….,cloumnn data type);

SQL> CREATE TABLE PROFILE (ID INT,FNAME VARCHAR2(50),LNAME VARCHAR2(50),FULL_NAME VARCHAR2(100),
EMAIL VARCHAR2(100),MOBILE_NO NUMBER(10),CREATED_DATE DATE,CREATEDBY VARCHAR2(100));

Table created.

SQL>
SQL> DESC PROFILE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 FNAME                                              VARCHAR2(50)
 LNAME                                              VARCHAR2(50)
 FULL_NAME                                          VARCHAR2(100)
 EMAIL                                              VARCHAR2(100)
 MOBILE_NO                                          NUMBER(10)
 CREATED_DATE                                       DATE
 CREATEDBY                                          VARCHAR2(100)

SQL>

CREATE VIEW : Syntax : create view view_name as select column1,column2,…,columnn from table_name [ where conditions];

SQL> CREATE VIEW ID_EMAIL AS SELECT ID,EMAIL FROM PROFILE;

View created.

SQL> desc ID_EMAIL;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 EMAIL                                              VARCHAR2(100)

SQL>

CREATE INDEX : Syntax is create index index_name on table_name(column);

SQL> CREATE INDEX ID_IDX ON PROFILE(ID);

Index created.

SQL>

CREATE TRIGGER : Syntax is  create trigger trigger_name [before or after]  [delete or insert  or update] on table pl/sql block

SQL> create trigger update_date before insert on PROFILE
 2 for each row
 3 declare
 4 username varchar2(20)
 5 ;
 6 begin
 7 select user into username from dual;
 8 :new.CREATED_DATE:=sysdate;
 9 :new.CREATEDBY:=username;
 10
 11 end;
 12 /

Trigger created.

SQL>

CREATE SYNONYM : Syntax is create [or replace] [public] synonym synonym_name for object_name;

SQL>  CREATE OR REPLACE PUBLIC SYNONYM LIST_TAB FOR sree.DETAILS_LIST;

Synonym created.

SQL>

You will learn more about index, view, triggers and synonyms in next chapters.

ALTER Statement : ALTER statement used to modify the definition of objects like rename, adding or removing columns or constraints etc..  

ALTER TABLE: Syntax is alter table table_name [add or modify or drop column] column datatype;

SQL> ALTER TABLE PROFILE ADD STATUS CHAR(1);

Table altered.

SQL> DESC PROFILE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 FNAME                                              VARCHAR2(50)
 LNAME                                              VARCHAR2(50)
 FULL_NAME                                          VARCHAR2(100)
 EMAIL                                              VARCHAR2(100)
 MOBILE_NO                                          NUMBER(10)
 CREATED_DATE                                       DATE
 CREATEDBY                                          VARCHAR2(100)
 STATUS                                             CHAR(1)

SQL>
SQL>
SQL> ALTER TABLE PROFILE MODIFY LNAME VARCHAR2(40);

Table altered.

SQL>
SQL>
SQL> DESC PROFILE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 FNAME                                              VARCHAR2(50)
 LNAME                                              VARCHAR2(40)
 FULL_NAME                                          VARCHAR2(100)
 EMAIL                                              VARCHAR2(100)
 MOBILE_NO                                          NUMBER(10)
 CREATED_DATE                                       DATE
 CREATEDBY                                          VARCHAR2(100)
 STATUS                                             CHAR(1)

SQL>
SQL>
SQL> ALTER TABLE PROFILE DROP COLUMN CREATEDBY;

Table altered.

SQL> DESC PROFILE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 FNAME                                              VARCHAR2(50)
 LNAME                                              VARCHAR2(40)
 FULL_NAME                                          VARCHAR2(100)
 EMAIL                                              VARCHAR2(100)
 MOBILE_NO                                          NUMBER(10)
 CREATED_DATE                                       DATE
 STATUS                                             CHAR(1)

SQL>

ALTER INDEX : Syntax is alter index index_name [RENAME | REBUILD] [ TO NEW_NAME | COMPUTE STATISTICS ]

SQL> ALTER INDEX ID_IDX RENAME TO NEW_ID_IDEX;

Index altered.

SQL>
SQL> ALTER INDEX NEW_ID_IDEX REBUILD COMPUTE STATISTICS;

Index altered.

SQL>

ALTER TRIGGER : Use the ALTER TRIGGER statement to enable, disable, or compile a database trigger.

SQL> ALTER TRIGGER update_date DISABLE;

Trigger altered.

SQL>
SQL> ALTER TRIGGER update_date ENABLE;

Trigger altered.

SQL> ALTER TRIGGER update_date RENAME TO update_date_profile;

Trigger altered.

SQL>

ALTER SYNONYM : Used to modify an existing synonym.

SQL>
SQL> ALTER SYNONYM LIST_TAB COMPILE;

Synonym altered.

SQL> ALTER SYNONYM LIST_TAB NONEDITIONABLE;

Synonym altered.

SQL>

TRUNCATE Statement : TRUNCATE statement used delete all records in the table and deallocates all space used by the removed records.

Syntax : truncate table table_name;

SQL> TRUNCATE TABLE PROFILE;

Table truncated.

SQL>

Caution : You cannot roll back a TRUNCATE TABLE statement.

DROP Statement : DROP statement used remove a table or object from the database entirely.

SQL> DROP TABLE PROFILE;

Table dropped.

SQL>


SQL> DROP VIEW ID_EMAIL;

View dropped.

SQL>
SQL> DROP TRIGGER update_date_profile;

Trigger dropped.

SQL>
SQL>
SQL> DROP INDEX NEW_ID_IDEX;

Index dropped.

SQL>

RENAME Statement :

RENAME TABLE allows you to rename an existing table.

SQL> RENAME EMP TO EMPLOYEES;

Table renamed.

SQL>
Advertisements