CASE()| Multiple inserts| MERGE| How to create user defined datatype & Table
CASE Function Ø It can be used to perform if then else logic in SQL. Ø These are two types 1) Simple case expression 2) Search case expression Ø 1. Simple case expression Select empno, ename, CASE deptno When 10 then ‘ACCOUNTING’ When 20 then ‘RESEARCH’ When 30 then ‘SALES’ When 40 then ‘OPERATION’ Else ‘others’ End Department_name from emp; Ø 2. Search case expression Select empno, ename, job, sal, Case When job=’MANAGER’ then ‘MGR’ When sal=3000 then ‘hipay’ When job=’SALESMAN’ then ‘sman’ Else ‘not specified’ End Employee_name from emp; Multiple inserts Ø Insert all When deptno<20 then into d1 values (deptno, dname, loc) When deptno>20 and deptno<=30 then into d2 values (deptno, dname, loc) Else into d3 values (deptno, dname, loc) Select * from dept d; Commit; DML MERGE statement à It is used to select rows from one o...