210909(목) DB 3일차 - Select 여러함수 이어서
<Select - 날짜, 최대, 최소, 평균, 합>
ex15) months_between(date1, date2) : 두 날짜 사이의 달 수
select round(months_between('95-10-21', '94-10-20'), 0) from dual; ← 자동 형변환
명시적인 변환(강제)
select last_name, to_char(salary, 'L99,999.00')
from employees
where last_name='King';
cf) #이나오면 자릿수가 부족하구나 생각하면됨 오라클은 딱맞게 해줘야함!
ex16)
select to_char(to_date('97/9/30', 'YY-MM-DD') , 'YYYY-MON-DD') from dual; ← 2097
=> MOM : 6월 이런식으로 나오게 해줌~~~!!!
select to_char(to_date('97/9/30', 'RR-MM-DD') , 'RRRR-MON-DD') from dual; ← 1997
select to_char(to_date('17/9/30', 'YY-MM-DD') , 'YYYY-MON-DD') from dual; ← 2017 Y는 시스템연도 따라감!
select to_char(to_date('17/9/30', 'RR-MM-DD') , 'RRRR-MON-DD') from dual; ← 2017
뒤의 두자리만 기억 19 99 YYYY: 앞의 두자리는 시스템을 따라감
19 90
세기가 바뀔 때는 RRRR공식을 쓴다??! => 현재 기준으로 더 가까운 시점으로 선택하면 굳이 공식을 외우지 않아도 된다! 현재기준과 더 가까울 때:YYYY/ 1900년대를 원할때는 더 머니까 RRRR
문 4 답: select last_name, to_char(hire_date, 'DD-MON-YYYY') as hire_date from employees
where hire_date < '2005-01-01';
ex17) fm형식 : 형식과 데이터가 반드시 일치해야함(fm - fm사이값만 일치)
fm를 표시하면 숫자 앞의 0을 나타나지 않는다.
select last_name, hire_date from employees where hire_date='05/09/30';
select last_name, hire_date from employees where hire_date='05/9/30';
select to_char(sysdate, 'YYYY-MM-DD') from dual;
select to_char(sysdate, 'YYYY-fmMM-DD') from dual;
select to_char(to_date('2011-03-01','YYYY-MM-DD'), 'YYYY-MM-DD') from dual;
← 2011-03-01
select to_char(to_date('2011-03-01','YYYY-MM-DD'), 'YYYY-fmMM-DD') from dual;
← 2011-3-1 => 한번만 붙이면 둘다 사라지는 것 주의~!!!
select to_char(to_date('2011-03-01','YYYY-MM-DD'), 'YYYY-fmMM-fmDD') from dual;
← 2011-3-01 => 두번붙이면 오히려 0이 살아난다.
ex18) count(컬럼명), max(컬럼명), min(컬럼명), avg(컬럼명), sum(컬럼명) 함수
employees테이블에서 급여의 최대, 최소, 평균, 합을 구하시오
조건) 평균은 소수이하절삭, 합은 세자리마다 콤마찍고 ￦표시
select max(salary),
min(salary),
trunc(avg(salary),0),
to_char(sum(salary), 'L9,999,999') from employees;
[문제5] 커미션(commission_pct)을 받지 않은 사원의 인원수를 구하시오
문 5 답: select count(*) from employees
where commission_pct is null;
<Select - count, decode, case, rank, first value, 파티션 조금>
ex19) employees테이블에서 없는 부서 포함해서 총 부서의 수를 구하시오
select department_id from employees; ← 107
select count(department_id) from employees; ← 106 : count(컬럼)은 null을 계산하지 않는다! => *로하면된다
select count(*) from employees;
select count(distinct department_id) from employees; ← 11
select count(distinct nvl(department_id, 0)) from employees; ← 12 : nvl : null을 0으로 채워라
select distinct nvl(department_id, 0) from employees; ← nvl은 null값을 0으로 대치
ex20) 둘다 다중 if문 / case를 더 많이 씀 => 컬럼을 새로 만들 수 있다!!!
① decode(표현식, 검색1,결과1, 검색2,결과2....[default])
: 표현식과 검색을 비교하여 결과 값을 반환 다르면 default
② case value when 표현식 then 구문1
when 표현식 then 구문2
else 구문3
end case
업무 id가 'SA_MAN' 또는 ‘SA_REP'이면 'Sales Dept' 그 외 부서이면 'Another'로 표시
조건) 분류별로 오름차순 정렬
select job_id, decode(job_id,
'SA_MAN', 'Sales Dept',
'SA_REP', 'Sales Dept',
'Another') "분류"(컬럼명: as 생략)
from employees
order by 2;
-------------------------------------------
select job_id, case job_id
when 'SA_MAN' then 'Sales Dept'
when 'SA_REP' then 'Sales Dept'
else 'Another'
end "분류"
from employees
order by 2;
-------------------------------------------
select job_id, case
when job_id='SA_MAN' then 'Sales Dept'
when job_id='SA_REP' then 'Sales Dept'
else 'Another'
end "분류"
from employees
order by 2;
[문제6] 급여가 10000 미만이면 초급, 20000 미만이면 중급 그 외면 고급을 출력하시오
조건1) 제목은 사원번호, 사원명, 구분으로 표시하시오
조건2) 구분 컬럼으로 오름차순 정렬하고, 같으면 사원명 컬럼으로 오름차순 하시오
조건3) case 사용하시오
문 6 답: select employee_id as 사원번호, last_name as 사원명,
case
when salary<10000 then '초급'
when salary<20000 then '중급'
else '고급'
end "구분"
from employees
order by 3 , 2;
ex21) rank함수 : 전체 값을 대상으로 순위를 구함
rank(표현식) within group(order by 표현식)
rank() over(쿼리파티션) → 전체순위를 표시
급여가 3000인 사람의 상위 급여순위를 구하시오
select rank(3000) within group(order by salary desc) "rank" from employees;
전체사원의 급여순위를 구하시오
select employee_id, salary, rank() over(order by salary desc)"rank" from employees;
ex22) first_value함수 : 정렬된 값 중에서 첫 번째 값 반환
first_value(표현식) over(쿼리파티션)
전체사원의 급여와 함께 각부서의 최고급여를 나타내고 비교하시오
select employee_id,
salary,
department_id,
first_value(salary) over(partition by department_id order by salary desc) "highsal_deptID"
from employees;
| ★ PARTITION BY 절은 GROUP BY 절과 동일한 역할을 진행 합니다. : 파티션- 그룹화 : ex) 1학년->1반,2반,3반 cf) 근데 이렇게하면 시간이 많이 걸려서 잘못하면 뻗는다 단, GROUP BY 절을 사용하지 않고 필요한 집합으로 행들을 그룹화 시킴 Partition by 절을 사용 함으로 GROUP BY 절 없이 다양한 GROUPING 집합의 집계 결과들을 함께 출력 할 수 있습니다. ORDER BY 절은 Partition by 로 정의된 WINDOW 내에서의 행들의 정렬 순서를 정의 한다. |
select employee_id,
last_name,
salary,
department_id,
row_number( ) over ( PARTITION BY department_id ORDER BY salary DESC ) rnum
from employees ;
부서별 급여를 내림차순으로 정렬 했을 경우 Row Number
부서 번호가 바뀔 때 Row Number 는 새로 시작 되는 것을 확인 할 수 있습니다.
NULL 값은 정렬 시 가장 큰 값으로 인식 (기본설정)
[문제7] 사원테이블에서 사원번호, 이름, 급여, 커미션, 연봉을 출력하시오
조건1) 연봉은 $ 표시와 세자리마다 콤마를 사용하시오
조건2) 연봉 = 급여 * 12 + (급여 * 12 * 커미션)
조건3) 커미션을 받지 않는 사원도 포함해서 출력하시오
문 7 답: select employee_id as 사원번호,
last_name as 이름,
salary as 급여,
commission_pct as 커미션,
to_char(salary*12 +(salary*12*nvl(commission_pct,0)), '$999,999') as 연봉
from employees;
[문제8] 매니저가 없는 사원의 MANAGER_ID를 1000번으로 표시
조건1) 제목은 사원번호, 이름, 매니저ID
조건2) 모든 사원을 표시하시오
문 8 답: select employee_id as 사원번호, last_name as 이름,
case
when manager_id is null then nvl(manager_id , 1000)
else manager_id
end "매니저ID"
from employees;
select employee_id as 사원번호,
last_name as 이름,
nvl(manager_id ,1000) as 매니저ID
from employees;
[추가문제]
답 : select sum(price) as 판매액
from sellings
where to_char(created_at, 'MM')='11';
select sum(price) as 판매액
from sellings
where created_at like '%2016-11%';