[Oracle] decode()
DECODE() decode( 컬럼명, 조건1, 값1, 조건2, 값2, 그 외값) EX1 -- 문제. hr.employees 활용하기 -- 1) 30번과 50번 부서 소속 직원에서 금여 10% 인상 select first_name, DEPARTMENT_ID, SALARY, decode(DEPARTMENT_ID,30,SALARY*1.1,50,SALARY*1.1,0) as increased_pay, decode(DEPARTMENT_ID,30,'10%+',50,'10%+','--') as stat from employees; EX2 -- 2)인상된 직원 set 만 출력 select first_name, DEPARTMENT_ID, SALARY, decode(DEPARTMENT_ID,30,SALARY*1.1,50..
2022. 12. 6.
[Oracle] NVL, NVL2 함수
NVL() nvl(검사할 값, (Null)일 때 값) NVL2() nvl(검사할 값, 'NOT Null'일 때 값, (Null)일 때 값) comm 값이 (null) 이면 0으로 출력. (null이 아닐 시, nvl은 본래의 값 출력. nvl2는 1로 출력) select salary as sal, commission_pct as comm, nvl(commission_pct,0) as comm_n, salary * nvl(commission_pct,1) as s_comm_n, nvl2(commission_pct,1,0) as s2_comm_n from employees;
2022. 12. 6.
연봉 높은 순으로 정렬하기
employees 테이블 이용 select rownum, name as Full_name , substr(name,1,(instr(name,' ')-1)) as Fmaily_name, substr(name,(instr(name,' ')),20) as Given_name, HIRE_DATE, to_char(SALARY*12, '$999,999,999') as Total_sal from (select name, substr(name,1,(instr(name,' ')-1)), substr(name,(instr(name,' ')),20), HIRE_DATE, SALARY from employees) order by Total_sal desc /
2022. 12. 4.
rownum() 은 1번을 건너뛰고 출력할 수 없음
연봉(Total_sal)이 가장 높은 1등, 2등, 3등을 출력하는 코드다. select name as Full_name, substr(name,1,(instr(name,' ')-1)) as Fmaily_name, substr(name,(instr(name,' ')),20) as Given_name, HIRE_DATE, to_char(SALARY*12, '$999,999,999') as Total_sal from employees order by Total_sal desc where rownum = 4) 이러면 no rows selected 뜨는 이유는? rownum() 요건 데..
2022. 12. 2.
