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>
