DATABASE 3일차 (변환함수)
형변환
두개의 값을 일치하도록 변환 하는 것
ex) '1993/07/09' => 1993/07/09
' ' ==> 문자타입
형변환의 종류로는 암시적 형변환 / 명시적 형변환 두가지가 있다.
- 암시적 형변환 : 시스템이 자동으로 형변환을 하는것
- 명시적 형변환 : 함수를 사용하여 형변환을 하는것
TO_~~~함수
TO_CHAR(date,format)
-> to_char(hire_date,'yyyymmdd') => 입사일(숫자타입) 을 문자타입으로 변환
TO_NUMBER(char,format)
-> to_number('20,000','999,999') - to_number('10,000','999,999')
TO_DATE(char,format)
NULL 함수
null 값은 정의할 수 없는 함수이다 .즉 0도 아니고 뭐 계산을 할 수 없는 값인 것을 임의로 바꾸게 되는 것이다.
- NVL (인자값1, 인자값2)
- NVL2(인자값1,인자값2,인자값3)
- NULLIF(인자값1,인자값2)
- COALESCE(인자값1,인자값2,인자값3.......인자값N)
NVL(date,원하는 값)
ex) nvl(commission_pct,99999) commission_pct 값에 null값이 있으면 그것을 99999로 바꾸겠다는 것이다.
NVL2(date,null이 아니면,null이면)
NULLIF(인자값1,인자값2) => 두개의 인자값이 모두 참(동일)일 경우 NULL / 다를경우 인자값1을 반환
COALESCE(인자값1......인자값N) => 인자값중 NULL값이 아닌것을 반환한다.
CASE 함수
IF / ELSE 와 같으며 사용법은 다음과 같다
case 비교할 대상 when 조건 then 조건에 대한 실행할 결과값
when 조건1 then 조건1에 대한 실행할 결과값
when 조건2 then 조건2에 대한 실행할 결과값
else 위의조건이 아닐 경우 실행할 값 end
DECODE 함수
case와 비슷하지만 when과 then / end를 사용하지 않아 가독성이 떨어진다.
문제
1. 각 사원에 대해 다음과 같이 출력하는 보고서를 작성합니다.
<employee last name> earns <salary> monthly but wants <3 times salary.>
열 레이블을 Dream Salaries로 지정합니다.
2. 각 사원의 성, 채용 날짜 및 근무 6개월 후 첫번째 월요일에 해당하는 급여 심의 날짜를 표시합니다.
열 레이블을 REVIEW로 지정합니다. 날짜 형식을 "Monday, the Thirty-First of July, 2000"과 유사한 형식으로 지정합니다.
select last_name,hire_date,next_day((add_months(hire_date,06)),'월요일')"review"
from employees
3. 사원의 성 및 커미션 금액을 표시하는 query를 작성합니다. 사원이 커미션을 받지 않으면 "No Commission"을 표시합니다. 열 레이블을 COMM으로 지정합니다.
4. 다음 데이터를 사용하여 CASE 함수를 통해 JOB_ID 열의 값을 기준으로 모든 사원의 등급을 표시하는 query를 작성합니다.
직무 등급
AD_PRES A
ST_MAN B
IT_PROG C
SA_REP D
ST_CLERK E
해당 사항 없음 0
select job_id,
case job_id when 'AD_PRES' THEN 'A'
WHEN 'ST_MAN' THEN 'B'
WHEN 'IT_PROG' THEN 'C'
WHEN 'SA_REP' THEN 'D'
WHEN 'ST_CLERK' THEN 'E'
else '0' end
from employees
5. 검색된 DECODE 구문을 사용하여 앞의 연습에 나오는 명령문을 재작성합니다.
select job_id,
decode (job_id, 'AD_PRES', 'A',
'ST_MAN', 'B',
'IT_PROG','C',
'SA_REP', 'D',
'ST_CLERK', 'E',
'0')
from employees
그룹함수
- AVG : 그룹에서 값의 평균
- COUNT : 그룹에서 COUNT한다
- MAX : 그룹에서 최대값을 추려낸다.
- MIN : 그룹에서 최소값을 추려낸다.
- SUM : 그룹에서 값의 합을 구한다.
- LISTAGG :
- STDDEV :
- VARIANCE : 분산
GROUP BY
SELECT LIST에 있는 열은 모두 GROUP BY에 속해야한다.
반대로 GROUP BY 열에 있는 것은 SELECT LIST에 없어도 된다.
최소 2개의 인자값이 있을때 A,B
ROLLUP : A,B 소계를 계산 해준다. 마지막에는 총합을 계산한다.
CUBE : A,B값 출력 A값 출력 B값 출력 총합 출력
아래 예제 확인
SELECT department_id, salary
FROM employees
ORDER BY 1;
SELECT department_id, salary, count(department_id)
FROM employees
GROUP BY department_id,salary
ORDER BY 1;
SELECT department_id,job_id,sum(salary)
FROM employees
GROUP BY department_id,job_id
ORDER BY 1;
SELECT department_id,job_id,sum(salary)
FROM employees
GROUP BY rollup(department_id,job_id)
ORDER BY 1;
SELECT department_id,job_id,sum(salary)
FROM employees
GROUP BY cube(department_id,job_id)
ORDER BY 1;
SQL의 순서
SELECT
FROM
WHERE
GROUP BY
HAVING
ORDER BY
※그룹을 묶기 전에 WHERE로 그룹에 대한 제한을 걸 수 없다.
[문제]
1. 그룹 함수는 다수 행에 대해 실행되어 그룹당 하나의 결과를 산출합니다.
맞음/틀림
2. 그룹 함수는 계산에 null을 포함합니다.
맞음/틀림
3. WHERE 절은 그룹 계산에 포함시키기 전에 행을 제한합니다.
맞음/틀림
4. 모든 사원의 최고, 최저, 합계 및 평균 급여를 찾습니다. 열 레이블을 각각 Maximum, Minimum, Sum 및 Average로 지정합니다. 결과를 가장 가까운 정수로 반올림합니다.
select round(max(salary),0)"MAX"
,round(min(salary),0)"min"
,round(sum(salary),0)"sum"
,round(avg(salary),0)"avg"
from employees
5. 각 직무 유형에 대해 최소, 최대, 합계 및 평균 급여를 표시하도록 하세요
select job_id,count(job_id),round(max(salary),0)"MAX"
,round(min(salary),0)"min"
,round(sum(salary),0)"sum"
,round(avg(salary),0)"avg"
from employees
group by job_id
/
6. 동일한 직무를 수행하는 사람 수를 표시하기 위한 query를 작성합니다.
select count(*)job_id
from employees
7.관리자를 나열하지 않는 채로 관리자 수를 확인합니다. 열 레이블을 Number of Managers로 지정합니다.
힌트: MANAGER_ID 열을 사용하여 관리자 수를 확인합니다.
select count(distinct manager_id)"Number of Managers"
from employees
8. 최고 급여와 최저 급여의 차이를 알아냅니다. 열 레이블을 DIFFERENCE로 지정합니다.
select max(salary)-min(salary)"DIFFERENCE"
from employees
9. 관리자 번호 및 해당 관리자의 부하 사원 중 최저 급여를 받는 사원의 급여를 표시하는 보고서를 작성합니다. 관리자를 알 수 없는 모든 사원을 제외합니다. 최소 급여가 $6,000 이하인 그룹을 제외합니다. 급여의 내림차순으로 출력을 정렬합니다.
select manager_id, min(salary)
from employees
where manager_id is not null
group by manager_id
having min(salary) > 6000
order by min(salary);
10. 사원의 총 수와 2005년, 2006년, 2007년, 2008년에 채용된 사원의 수를 표시하는 queredy를 작성합니다. 적절한 열 머리글을 지정하십시오.
11. 부서 20, 50, 80 및 90에 대해 직무, 부서 번호별 해당 직무에 대한 급여 및 해당 직무에 대한 총 급여를 표시하고 각 열에 적절한 머리글을 지정하기 위한 Matrix query를 작성합니다.