D+34~36 Oracle(SQL SELECT문: Function, Group Function)

2016. 09. 28. (Wed) 서른네 번째 수업

2016. 09. 29. (Thu) 서른다섯 번째 수업

2016. 09. 30. (Fri) 서른여섯 번째 수업

 지난주부터 시작된 PC방 관리 프로그램 만들기 프로젝트로 인해 IT 수업은 일일 2시간으로 진행되어 내용이 많지 않고,

맥에서 오라클을 실행할 수 없기 때문에 수업내용을 간략히 모아서 업로드하고 있다.

 先週から始まったPCカフェの管理プログラムのプロジェクトの為、IT授業が２時間になってるから内容も少ないし

MacOSではOracleが出来ないから、授業の内容をまとめて投稿してる。

수업내용

L04 Function

1. INSTR() //특정 문자의 위치를 구하는 함수

1. SELECT INSTR(‘WELCOME TO ORACLE’,’O’) FROM DUAL;

2. SELECT INSTR(‘WELCOME TO ORACLE’,’O’,6,2) FROM DUAL;

i. 대상 문자의 6번째에서 2번째로 발견되는 ‘o’의 위치

2. 날짜 함수

1. SYSDATE //시스템에 저장된 오늘 날짜 반환

2. MONTH_BETWEEN(date1, date2) //date1과 2사이의 날짜 반환

3. ADD_MONTHS(date, n) //date + n개월 날짜 반환

4. NEXT_DAY(date, 요일) //date와 가까운 요일이 있는 날짜 반환

5. LAST_DAY(date) //date를 포함하는 달의 마지막 날을 반환

6. ROUND(date, format) //date를 format으로 반올림

7. TRUNC(date, format) //date를 format으로 내림

8. format

1. CC, SCC //4자리 연도 끝의 두 글자를 기준

2. SYYY, YYYY, YEAR ,SYEAR, YYY, YY, YY //7월 1일 기준

3. HH, HH12, HH24 시를 기준

4. Q //한 분기의 두 번째 달의 16일을 기준

5. MONTH, MON, MM, RM //한 주가 시작되는 날짜

6. MI //분을 기준

7. 예) ROUND(HIREDATE, ‘MONTH’)

3. 문제1) DUAL 테이블에 어제, 오늘, 내일 날짜를 출력하세요.

1. SYSDATE는 연산 가능

답) select sysdate-1 어제, sysdate 오늘, sysdate+1 내일 from dual;

4. 문제2) 사원 이름과 입사일, 사원의 근무 일수를 출력하세요.

1. 오늘 날짜 - 입사일

답) select ename, hiredate, sysdate-hiredate from emp;

5. 문제3) 사원 이름과 입사일 사원이 근무한 개월 수를 출력하세요.

1. 소수점은 내림하세요.

답) select ename, hiredate, trunc(sysdate-hiredate) AS 근무일수 from emp;

답) select ename, hiredate, trunc(months_between(sysdate, hiredate)) AS

    근무개월 from emp;

6. 문제4) 사원 이름과 입사일, 입사일 + 6개월을 출력하세요.

답) select ename, hiredate, add_months(hiredate, 6) from emp;

7. 문제5) 오늘을 기준으로 가장 가까운 수요일을 DUAL 테이블에 출력

1. 윈도우 운영체제가 사용 중인 언어(한글)를 사용: ‘수요일’

답) select next_day(sysdate, '수요일') from dual;

2. Oracle언어 (NLS_LANGUAGE)를 변경하려면

ALTER SESSION SET NLS_LANGUAGE = AMERICAN

‘WENDSDAY’ 사용 가능

8. 문제6) 사원의 이름과 입사일, 입사한 달의 마지막 날을 출력하세요.

답) select ename, hiredate, last_day(hiredate) from emp;

9. 형 변환 함수

1. TO_CHAR(date, format) //날짜를 format 형식 문자로 변환

2. TO_CHAR(number, format) //number를 문자로 변환

3. 숫자 format

1. 0 //자릿수를 나타내면 자릿수가 맞지 않으면 0으로 채운다.

2. 9 //자릿수를 나타내며 자릿수가 맞지 않아도 채우지 않는다.

3. L //각 지역 별 통화를 앞에 표시

4. . //소수점

5. , // 천 다윈 자리 구분

4. TO_DATE(‘date’, format) //문자를 format 형식 날짜로 변환

5. TO_DATE(date, format) //숫자를 format 형식 날짜로 변환

6. TO_NUMBER //문자를 숫자로 변환

7. 날짜 format

1. YYYY //년도 4자리

2. YY //년도 2자리

3. MM //월을 숫자로

4. MON //월을 알파벳으로

5. DAY //요일 표현

6. DY // 요일을 약어로 표현

8. 시간 format

1. AM or PM //오전(AM), 오후(PM) 시각 표현

2. A.M or P.M //오전(A.M), 오후(P.M) 시각 표현

3. HH or HH12 //12시간으로 표현 (1~12)

4. HH24 //24시간으로 표현 (0~23)

5. MI //분 표현

6. SS //초 표현

9. 도식도

1. NUMBER > CHAR > DATE

2. DATE > CHAR >NUMBER

10. 문제6) 오늘 날짜를 2016-09-28, 14:01:13로 출력하세요. (DUAL)

답) select to_char(sysdate,'YYYY-MM-DD, HH24:MI:SS') from dual;

11. 문제7) 사원의 이름, 급여 (통화 기호 + 천 단위 구분) 출력하세요.

답) select ename, to_char(sal,'L999,999') from emp;

TO_DATE(date, format) //문자 or 숫자 date를 format의 시간으로 변경

12. TO_DATE(date, format) //문자 or 숫자 date를 format의 시간으로 변경

13. 문제8) TO_DATE를 이용해 입사 일이 19810220인 사원을 검색하세요.

답) select * from emp where hiredate=to_date(19810220,'YYYYMMDD');

14. 문제9) DUAL 테이블에서 오늘 날짜와 ‘2009/01/01’을 뺀 날을 출력하세요

답) select sysdate - to_date('2009/01/01','YYYY/MM/DD') from dual;

15. 문제10) 문자열 ‘20,000’과 ‘10,000’을 마이너스 연산 하세요.

답) select to_number('20,000','99,999')-to_number('10,000','99,999') from dual;

L05 Gruop Function

1. NVL() // NULL을 다른 값으로 바꾸는 함수

2. DECODE (칼럼, 조건1, 결과1, 조건2, 결과2, DEFAULT) AS NAME

NAME을

1. 칼럼의 조건이 조건1일 경우 결과 1을 출력하고

2. 칼럼의 조건이 조건2일 경우 결과 2를 출력하고

3. 조건에 맞는 것이 없으면 DEFAULT를 출력한다.

1. 문제1) 사원의 부서 번호가 10이면 ‘A’를 출력하고 20이면 ‘B’를 조건에 맞는 것이 없다면 ‘DEFAULT’를 출력하세요.

답) select ename, deptno, decode(deptno, 10, 'A', 20, 'B', 30, 'C','DEFAULT') AS

     DENAME from emp;

4. 문제2) 사원의 이름, 부서 번호를 출력하고 부서 이름(DENAME)을 부서 번호가 10이면 ‘ACCOUNTING’, 20이면 ‘RESEARCH’, 30이면 ‘SALES’, 40이면 ‘OPERATIONS’으로 출력하세요.

답) select ename, deptno, decode(deptno, 10, 'ACCOUNTING', 20, 'RESEARCH',

30, 'SALES', 40, 'OPERATIONS') AS DENAME from emp;

5. 문제3) 직급에 따라 급여를 인상할 계획이다. 직급이 ‘ANALYST’이면 5%, ‘SALESMAN’은 10%, ‘MANAGER’는 15%, ‘CLERK’는 20% 인상한 결과를 출력하세요.

답) select ename, job, sal, decode(job, 'ANALYST', SAL*1.05, 'SALESMAN',

SAL*1.1, 'MANAGER', SAL*1.15, 'CLERK', SAL*1.2 ) AS INCREASE FROM EMP;

6. CASE WHEN 조건1 THEN 결과1

조건 2 THEN 결과2

ELSE 결과3 AS NAME

NAME을

1. 조건1 (칼럼=?)이 만족하면 칼럼을 결과1로 출력

2. 조건2 (칼럼=?)이 만족하면 칼럼을 결과2로 출력

7. 문제4) 문제 2를 CASE로 바꾸세요.

답) select ename, deptno, case when deptno = 10 then 'ACCOUNTING'

  when deptno = 20 then 'RESEARCH'

  when deptno = 30 then 'SALES'

  when deptno = 40 then 'OPERATIONS'

  END AS DENAME FROM EMP;

8. 문제5) 문제 3을 CASE로 바꾸세요.

답) select ename, job, sal, case when job = 'ANALYST' then SAL*1.05

  when job = 'SALESMAN' then SAL*1.1

  when job = 'MANAGER' THEN SAL*1.15

  when job = 'CLERK' THEN SAL*1.2

  ELSE SAL*1

  END AS SALX FROM EMP;

L06 Group Function

• 그룹 함수란?

그룹 함수는 하나 이상의 행을 그룹으로 묶어 연산하여 총합, 평균 등을 하나의 결과로

나타낸다.

그룹 함수는 단일 행 함수 또는 단일 칼럼을 사용할 수 없다.

예) FUNCTION(칼럼) // 칼럼을 한 개의 그룹으로 보고 단일 결과를 출력하세요.

1. 그룹 함수의 종류

1. SUM() //그룹의 누적 합계

2. AVG() //그룹의 평균

3. COUNT(A) //그룹의 칼럼 A중 NULL을 제외한 총 개수

4. COUNT(DISTINCT A) //그룹의 칼럼 A중 중복된 값과 NULL을 제외한 총 개수. DISTINT는 GROUP BY와 동일

5. MAX() //그룹의 최대값

6. MIN() //그룹의 최소값

7. STDDEV() //그룹의 표준편차

8. VARIANCE //그룹의 분산

2. 문제1) 사원의 급여 총액을 구하세요.

답) select sum(sal) from emp;

3. 문제2) 사원의 커미션 총액을 구하세요.

답) select sum(comm) from emp;

4. 문제3) 부서가 30인 사원의 급여 총액을 구하세요.

답) select sum(sal) from emp where deptno=30;

5. 문제4) 사원의 평균 급여를 구하세요.

답) select avg(sal) from emp;

6. 문제5) 부서가 10인 사원의 평균 급여를 구하세요.

답) select avg(sal) from emp where deptno=10;

7. 문제6) 사원의 가장 높은 급여와 가장 낮은 급여를 구하세요.

답) select max(sal), min(sal) from emp;

8. 문제7) 커미션의 총 개수를 출력하세요.

답) select count(comm) from emp;

9. 문제8) 사원의 직업 개수를 출력하세요.

답) select count(distinct job) from emp;

10. 문제9) 부서가 10인 사원의 수는

답) select count(*) from emp where deptno =10;

11. 문제10) 가장 최근에 입사한 사원과 가장 오래된 사원을 출력하세요.

답) select max(hiredate), min(hiredate) from emp;

12. 문제11) 부서가 30인 사원 중 커미션을 받는 사원의 수는?

1. 결과가 3이 나와야 한다.

답) select count(comm) from emp where deptno=30 and comm>0 ;