Wednesday, July 16, 2014

MERGE Statement in Oracle SQL

Following MERGE statement is used to search the record in the file, If the record exist then update statement will run and if the record is not found then insert statement will run.


In below example Employee number is the key, Both files are join on employee number. If the employee exist then update the name , job and salary of the employee and if the employee is not exist then insert all fields from EMP table into COPY_EMP table.


SELECT * FROM COPY_EMP;







STATEMENT:













RESULT:




















Saturday, July 12, 2014

CASE Statement in SQL

Calculation of Bonus by using CASE statement.

Select statement with CASE is used to insert the logical conditions in the statement. Oracle provide us the facility to insert logical condition while selection from the database. Below statement is also used to insert the record in the table.

In following example, Logical condition is applied in JOB field while calculation of bonus with CASE statement.

In case of manager bonus will be 10% and in case of salesman bonus will be 8% and in case of clerk bonus will be 6%.

SELECT EMPNO,ENAME,JOB,
  CASE JOB WHEN   'MANAGER'   THEN SAL*10/100
                     WHEN   'SALESMAN'  THEN SAL*8/100
                     WHEN   'CLERK'           THEN SAL*6/100
       ELSE SAL* 2/100  END  BONUS
       FROM EMP;