SQL - Dia 9

General Functions

case
decode

case

its for condition.
Relation and logical operators will be used when implementing CONDITIONS.
It needs to have END word while finishing.

-- select first_name , salary , salary*10/100 from hr.employees; 10 % increment but we need only > 10000 ? how to do ?
-- select first_name , salary , CASE WHEN SALARY > 10000 THEN salary*10/100 END from hr.employees;
select first_name , salary , CASE WHEN SALARY > 10000 AND FIRST_NAME LIKE 'A%' THEN salary*10/100 END from hr.employees;

decode

NO Relation or logical operators will be used when implementing CONDITIONS.
decode ( input , p1 , p1 , p2 , p2 )
Read two two.

select decode (1,2,4,1,0) from dual;

என்னுடைய input வந்து 1 , now read two by two , the next inputs. ‘1’க்கு அடுத்தது ரெண்டு ரெண்டா படிங்க . இதுல input வந்து ஒன்று , என்னுடைய input ‘2’டா இருந்ததுன்னா 4 என்று print பண்ணனும் but our input is ‘1’.
என்னுடைய input ‘1’ இருந்ததுன்னா 0 என்று print பண்ணனும் but our input is ‘1’
So answer is 0.

select decode (1,2,4,8,0,5) from dual; --> here 5 is else part.

Now you will get data for all , because you have given else part in the query.

-- select first_name , salary , CASE WHEN SALARY > 10000 AND FIRST_NAME LIKE 'A%' THEN salary*10/100 END from hr.employees;
select first_name , salary , decode ( salary , 24000 , salary*10/100 , 17000 , salary*10/100 ) from hr.employees;

select first_name , salary , decode ( salary , 24000 , salary*10/100 , 17000 , salary*10/100 , salary*5/100 ) from hr.employees; --> Else is implemented.

select first_name , salary , CASE WHEN SALARY > 10000 THEN salary*10/100 
    WHEN SALARY < 10000 THEN salary*9/100 
    ELSE salary*5/100
    END AS BONUS from hr.employees;

-- select first_name , salary , COMMISSION_PCT , CASE WHEN COMMISSION_PCT IS NULL THEN 1.5 END from hr.employees;
select first_name , salary , CASE WHEN COMMISSION_PCT IS NULL THEN 1.5 ELSE COMMISSION_PCT END AS COMMISSION_PCT from hr.employees;

select FIRST_NAME , Hire_date , to_char (Hire_date,'day') from hr.employees;