연산자와 단일행 함수

연산자 개념

select last_name, department_id, hire_date
from employees
order by 2 desc;

select last_name, department_id, hire_date 
from employees
order by 2 desc, hire_date desc

select last_name "이 름", salary*12 "연 봉"
from employees
order by 2 desc

단일행 함수 개념

select employee_id, last_name, department_id
from employees
where lower(last_name) = 'higgins';

select mod(10, 3)
from dual; -- dual 가상 테이블, 하나의 레코드를 리턴

select round(35765.357, 2) from dual;   --35765.36
select round(35765.357, 0) from dual;    --35765
select round(35765.357, -3) from dual; -- 36000

select trunc(35765.357, 2) from dual;  --35765.35
select trunc(35765.357, 0) from dual;  -- 35765
select trunc(35765.357, -3) from dual; -- 35000
-
select ceil(35765.357) from dual;

select concat('hello', ' world') from dual;

create table text (str1 char(20),str2 varchar2(20));
insert into text(str1,str2) values('엘리트','엘리트');
insert into text(str1,str2) values('elite','elite');
commit;
select * from tab;
select * from text;
select length(str1),length(str2) from text where str1='엘리트';     --14   3
select lengthb(str1),lengthb(str2) from text where str1='엘리트';    --20   9
-
select length(str1),length(str2) from text where str1='elite';     --20   5
select lengthb(str1),lengthb(str2) from text where str1='elite';   --20   5

select length('hello') from dual;   -- 5
select length('헬로우') from dual;    -- 3
select lengthb('hello') from dual;   -- 5
select lengthb('헬로우') from dual;  -- 9 

select instr('HelloWorld', 'W') from dual; -- 6
select instr('HelloToWorld', 'o', -5) from dual; -- 7 !! 

select substr('I am very happy', 6, 4) from dual; -- 6번째 부터 4글자 출력
select substr('I am very happy', 6) from dual; --지정한 위치부터 끝까지

select employee_id, concat(first_name, last_name) name,
		length(concat(first_name, last_name)) "length"
from employees
where substr(last_name, -1, 1) = 'n';

select width_bucket(74, 0, 100, 10) -- 8번째 구간에 위치하고 있다.
from dual; -- 가상 테이블		0~10, 11~20, 21~30, 31~40, 41~50
						--	51~60, 61~70, 71~80, 81~90, 91~100

select rtrim('test        ') || 'exam' from dual;
select ltrim('            test') || 'exam' from dual;
select trim('       test      ') || 'exam' from dual;

select sysdate  -- 2023-01-18 09:26:13
from dual;
-
select to_char(sysdate) -- 23/01/18
from dual;
-
select to_char(sysdate, 'YYYY"년" MM"월" DD"일"') -- 홑따옴표랑 겹따옴표랑 구분하기
from dual;							-- 2023년 01월 18일
-
select to_char(to_date('13/1/5'), 'YYYY"년" MM"월" DD"일"') -- 문자열을 날짜형식으로 변환
from dual;					-- 2013년 01월 05일
-
select to_char(to_date('13/1/5'), 'YYYY"년" fmMM"월" DD"일"') -- 문자열을 날짜형식으로 변환
from dual;					-- 2013년 1월 5일
-
select to_char(to_date('13/1/5'), 'YYYY"년" fmMM"월" fmDD"일"') -- 문자열을 날짜형식으로 변환
from dual;					-- 2013년 1월 05일, fm이 들어가면 서식이 변환

select add_months(sysdate, 6) from dual;

select last_day(sysdate) from dual;
select last_day('2004-02-01') from dual;
select last_day('2005-02-01') from dual;

select last_day(sysdate) - sysdate || '일' "남은 날수" from dual;

select months_between(sysdate, add_months(sysdate, 6)) from dual;	-- -6
select months_between(add_months(sysdate, 6), sysdate) from dual;	-- 6
select round(months_between('2014-02-10', '2013-10-21')) from dual;  -- 4
select trunc(months_between('2014-02-10', '2013-10-21')) from dual; -- 3

desc employees;
select employee_id, last_name
from employees
where department_id='90'; -- 문자와 숫자간의 자료형 변환
-
select months_between(add_months(sysdate, 6), sysdate)
from dual; -- 날짜와 숫자간의 자료형 변환

select last_name, to_char(salary, 'L99,999.00')
from employees
where last_name='King';

select last_name, hire_date
from employees
where hire_date='05/06/25';
-
select last_name, hire_date
from employees
where hire_date='05/6/25';

select to_char(to_date('97/9/30', 'yy-mm-dd'), 'yyyy-mm-dd') 
from dual;	--2097
-
select to_char(to_date('97/9/30', 'rr-mm-dd'), 'rrrr-mm-dd') 
from dual;	--1997
-
select to_char(to_date('57/9/30', 'yy-mm-dd'), 'yyyy-mm-dd') 
from dual; -- 2057
-
select to_char(to_date('57/9/30', 'rr-mm-dd'), 'rrrr-mm-dd') 
from dual;	--1957

select last_name, to_char(hire_date, 'DD-MON-YYYY') hire_date
from employees
where hire_date >= '2005/01/01';
-    
select last_name, to_char(hire_date, 'DD-MON-YYYY') hire_date
from employees
where hire_date >= to_char(to_date('2005/01/01', 'yy-mm-dd'), 'yy-mm-dd');

select count(salary), max(salary), min(salary), trunc(avg(salary), 0), to_char(sum(salary), 'L999,999')
from employees;

select count(*)
from employees
where commission_pct is null;

select count(distinct nvl(department_id,0))   -- count함수는 null값을 처리를 하지 않음
from employees
-
select distinct department_id from employees;
select distinct nvl(department_id, 0) from employees; -- null값을 0으로 바꿈

select last_name 사원이름, salary 급여, nvl(commission_pct,0) 커미션, salary*12 + (salary*12)*nvl(commission_pct,0) 연봉
from employees;

select job_id as 직무,
	decode(job_id, 'SA_MAN', 'Sales Dept', 'SA_REP', 'Sales Dept', 'Another') 분류
from employees
order by 2;
-
select job_id,
	case job_id when 'SA_MAN' then 'Sales Dept' -- SA_MAN 일때 Sales Dept로 바꿈
				when 'SA_REP' then 'Sales Dept'
				else 'Another'
			end
from employees
-
select job_id,
	case  when job_id = 'SA_MAN' then 'Sales Dept' -- SA_MAN 일때 Sales Dept로 바꿈
		  when job_id = 'SA_REP' then 'Sales Dept'
				else 'Another'
			end
from employees
order by 2;

select employee_id 사원번호, last_name 사원명, case
				when salary < 10000 then '초급'
				when salary < 20000 then '중급'
				else '고급'
				end "구  분"
from employees
order by 3, 2;

select rank(3000) within group(order by salary desc) "rank"
from employees;		-- 전체 107명 중 82등

select employee_id, last_name, salary, rank() over(order by salary desc)
from employees;
--first_value함수 : 정렬된 값 중에서 첫번째값 반환
--first_value(표현식) over(쿼리파티션)

select employee_id, salary,
		first_value(salary) 
		over(partition by department_id order by salary desc) 
		as "부서별 최고급여",department_id
from employees;

select employee_id 사원번호, last_name 이름, salary 급여, nvl(commission_pct, 0) 커미션, 
		to_char(salary*12+ salary*12*nvl(commission_pct,0), '$999,999') "연봉"
from employees;

select employee_id 사원번호, last_name 이름, nvl(manager_id, 1000) 매니저ID
from employees;

select last_name, salary, case
		when salary>=7000 then '고급'
		when salary>=3000 then '중급'
		else '초급'
		end "grade"
from employees
order by 3 

익숙하지 않은 코드

• order by에 1, 2, 3 인덱스 지정 가능(select 문 컬럼순)
• 날짜 형식 YYYY, MM, DD
• 숫자 형식 9, 0, $, L, . , ,
• 기타 함수 nvl, decode, case
• 가상 테이블 from dual
• 지정한 문자열 찾기 instr()
• case when then
• rank, first_value, over 함수
• trunc() 내림, mod(10, 3) 나머지