Scenario: (Uses Oracle-SQL)
HR asks to increment the salary
1. <= 3000, increase by 1000;
2. <= 4000, increase by 500;
3. <= 5000 increase by 250;
Example:
Current Salary: 3000 then New Salary should be 3000 + 1000 + 500 + 250 = 4750
Solution:
create table emp_salary
(
id number,
name varchar2(20),
current_sal number(20,2),
new_sal number(20,2)
);
insert into EMP_SALARY (id, name, current_sal)
values (1, 'Mani', 3000);
insert into EMP_SALARY (id, name, current_sal)
values (2, 'abhishek', 4000);
insert into EMP_SALARY (id, name, current_sal)
values (3, 'ajay', 5000);
insert into EMP_SALARY (id, name, current_sal)
values (4, 'arun', 2000);
commit;
1. Not efficient:
update emp_salary
set new_sal = current_sal + 1000 + 500 + 250
where current_sal > 0 and current_sal <= 3000;
update emp_salary
set new_sal = current_sal + 500 + 250
where current_sal >= 3001 and current_sal <= 4000;
update emp_salary
set new_sal = current_sal + 250
where current_sal >= 4001 and current_sal <= 5000;
commit;
2. Efficient:
update test_emp_sal
set current_sal =
case
when current_sal > 0 AND current_sal <= 3000 then current_sal + 1000 + 500 + 250
when current_sal > 3001 AND current_sal <= 4000 then current_sal + 500 + 250
when current_sal > 4001 AND current_sal <= 5000 then current_sal + 250
end
;
commit;
==================================================================================
update orders
set order_mode_num =
decode(order_mode,
'direct',100,
'online',200,
'walmart',300,
'amazon',400,
0)
;
==================================================================================
update orders
set order_mode_num =
case
when order_mode = 'direct' and
customer_id < 102 then 500
when order_mode = 'direct' then 100
when order_mode = 'online' then 200
when order_mode = 'walmart' then 300
when order_mode = 'amazon' then 400
else 0
end
;
=================================================================================
