INTRODUCTION TO SQL

Prepared by Ravichandiran

Senior Oracle Trainer

WHY MAZENET?

• Oracle Certified Partner & more than 10+ years experienced Trainers.

• Oracle unique id for students• E-kits from Oracle University• Course Completion Certificate from Oracle University.• Flexible timing for working professionals.• Preparing candidates to face interview and free resume

preparation.

COMMANDS

DDL - DATA DEFINITION LANUGAGE

DML - DATA MANIPULATION LANGUAGE

TCL - TRANSACTION CONTROL LANGUAGE

DCL - DATA CONTROL LANGUAGE

DRL - DATA RETRIEVAL LANGUAGE

DDL

• Used to create or modify the structure of the table.

• Auto commit occurs. Changes cannot be rolled back.

• Session independent commands.

• Commands are :

CREATE, ALTER DROP, TRUNCATE, RENAME

DDL - CREATE

SYNTAXcreate table <tab_name>(col1 datatype(size),col2 datatype(size),........................coln datatype(size));

EXAMPLE

Create table emp

(

empno number(5),

ename varchar2(20),

job varchar2(20)

);

DDL - ALTERADD• ALTER TABLE EMP ADD

DOJ DATE;

• ALTER TABLE EMP ADD ( SALARY NUMBER(5), COMM NUMBER(4), DEPTNO NUMBER(3));

MODIFY• ALTER TABLE EMP ADD

DOJ DATE;

• ALTER TABLE EMP ADD ( SALARY NUMBER(5), COMM NUMBER(4), DEPTNO NUMBER(3));

DDL - ALTER

DROP COLUMN

• ALTER TABLE EMP DROP COLUMN DEPTNO;

• ALTER TABLE EMP DROP(DOJ,COMM);

RENAME COLUMN

• ALTER TABLE EMP RENAME COLUMN EMPNO TO ENO;

DDL - DROP

TABLE DROPPED

• DROP TABLE EMP;• DROP TABLE EMP

PURGE;• PURGE RECYCLEBIN;• PURGE TABLE EMP;

FLASHBACK COMMAND

• Flashback table emp to before drop;

• Flashback table emp to before drop rename to employees;

DDL - TRUNCATE

• Used to remove records from table.• Cannot delete single row alone• Changes are permanent. So deleted record cannot

be rolled back.

TRUNCATE TABLE EMP;

DDL - RENAME

• Used to rename the table.

RENAME <OLD_TABLE_NAME> TO <NEW_TAB_NAME>;RENAME EMP TO EMPLOYEES;