DEV Community

technonotes-hacker
technonotes-hacker

Posted on

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.

Image description

Image description

Image description

-- 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;
Enter fullscreen mode Exit fullscreen mode

decode

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

Image description

  • 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.

Image description

Image description

Image description

  • 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.
Enter fullscreen mode Exit fullscreen mode

Image description

Image description

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;
Enter fullscreen mode Exit fullscreen mode

Image description

Image description

-- 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;
Enter fullscreen mode Exit fullscreen mode

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

Image description

  • To_char used to convert.

Notes:

  1. 19c and 23c --> Oracle
  2. what is normalisation ?
  3. Performance tuning ?
  4. how to delete the duplicate ?
  5. case is faster than decode , both are general functions .
  6. D --> Day

Top comments (0)