DDL EXTENSION

. DDL EXTENSION .

******TWO TABLE USED ARE IN THESE EXAMPLE***********

create table stud(name varchar2(10),roll number(5),DOB date);

CREATE TABLE JOB_STUD(NAME VARCHAR2(30),MANAGER NUMBER(10),DUTY VARCHAR2(30));

-------------------------

INSERT ALL (UNCONDITIONAL)

INSERT ALL

INTO STUD VALUES(NAME,ROLL,DOB)

INTO JOB_STUD VALUES(NAME,MANAGER,DUTY)

SELECT ENAME NAME,EMPNO ROLL,HIREDATE DOB,MGR MANAGER,JOB DUTY FROM EMP

WHERE SAL>2000;

-------------------------

SELECT * FROM STUD;

SELECT * FROM JOB_STUD;

-------------------------

TRUNCATE TABLE STUD;

TRUNCATE TABLE JOB_STUD;

-------------------------

INSERT ALL (NCONDITIONAL)

INSERT ALL

WHEN DEPTNO=10 THEN

INTO STUD VALUES(NAME,ROLL,DOB)

WHEN DEPTNO =20 THEN

INTO JOB_STUD VALUES(NAME,MANAGER,DUTY)

SELECT ENAME NAME,EMPNO ROLL,HIREDATE DOB,MGR MANAGER,JOB DUTY,DEPTNO FROM EMP

WHERE SAL>2000;

-------------------------

SELECT * FROM STUD;

SELECT * FROM JOB_STUD;

-------------------------

TRUNCATE TABLE STUD;

TRUNCATE TABLE JOB_STUD;

-------------------------

INSERT FIRST (ONLY THE FIRST VALID EXPRESSION UNDER WHEN CLAUSE WILL WORK)

INSERT FIRST

WHEN MANAGER IS NULL THEN

INTO STUD VALUES(NAME,ROLL,DOB)

WHEN DUTY ='PRESIDENT' THEN

INTO JOB_STUD VALUES(NAME,MANAGER,DUTY)

SELECT ENAME NAME,EMPNO ROLL,HIREDATE DOB,MGR MANAGER,JOB DUTY FROM EMP

WHERE SAL>2000;

-------------------------

SELECT * FROM STUD;

SELECT * FROM JOB_STUD;

-------------------------

TRUNCATE TABLE STUD;

TRUNCATE TABLE JOB_STUD;

-------------------------