21. 03. 09. 문자 조작 함수 이어서, 여러 함수들, GROUP BY, HAVING, JOIN

문자조작 함수 INSTR 함수

- 자바 indexof같이 문자열중에서 사용자가 지정한 특정 문자가 포함된 위치를 반환하는 함수

SELECT 'CORPORATE FLOOR',
    INSTR('CORPORATE FLOOR', 'OR'),
    INSTR('CORPORATE FLOOR', 'OR', 3, 2),
    INSTR('CORPORATE FLOOR', 'OR', -3, 2)
FROM DUAL;

 

-- 부서 테이블의 부서 이름 갈럼에서 '과' 글자의 위치를 출력.

SELECT DNAME, INSTR(DNAME, '과')
FROM DEPARTMENT;

 

문자조작함수 LPAD, RPAD 함수

- 문자열이 일정한 크기가 되도록 왼쪽 또는 오른쪽에 지정할 문자를 삽입하는 함수

 

-- 교수테이블에서 직급칼럼의 왼쪽에 *문자를 삽입하여 10바이트로 출력하고
-- 교수 아이디칼럼은 오른쪽에+문자를 삽입하여 12바이트로 출력하여라

SELECT LPAD(POSITION, 10, '*'), RPAD(USERID, 12, '+')
FROM PROFESSOR;

 

 

문자조작함수LTRIM, RTRIM 함수

- 문자열에서 특정 문자를 삭제하기 위해 사용
- 함수의 인수에서 삭제할 문자를 지정하지 않으면 문자열의 앞 뒤 부분에 있는 공백 문자를 삭제

 

-- 부서테이블에서 부서 이름의 마지막 글자인 '과'를 삭제하여 출력하여라

SELECT DNAME, RTRIM(DNAME,'과')
FROM DEPARTMENT;

 

숫자함수

- ROUND(지정한 소수점 자리로 반올림), TRUNC(지정한 소수점 자리까지 남기고 버림), MOD(나머지), CEIL(올림), FLOOR(버림)

 

-- 교수테이블에서 101학과교수의 일급을 계산(월근무일은22일)하여
-- 소수점 첫째자리와 셋째자리에서 반올림한 값과 소숫점 왼쪽 첫째자리에서 반올림한 값을 출력

SELECT SAL, SAL/22, ROUND(SAL/22), ROUND(SAL/22, 2)
    , ROUND(SAL/22, -1)
FROM PROFESSOR
WHERE DEPTNO = 101;

- NULL값은 MOD를 하여도 NULL

 

 

날짜함수

 

-- 교수번호가 9908인 교수의 입사일을 기준으로 입사 30일후와 60일후의 날짜를 출력

SELECT NAME, HIREDATE, HIREDATE + 30, HIREDATE + 60
FROM PROFESSOR
WHERE PROFNO = 9908;

- SYSDATE : 시스템 현재 날짜. 결과: 날짜

- MONTHS_BETWEEN : 날짜와 날짜 사이의 개월 계산. 결과: 숫자

- ADD_MONTHS : 날짜에 개월을 더한 날짜 계산. 결과: 날짜

- NEXT_DAY : 날짜 후의 첫 요일의 날짜 계산. 결과: 날짜

- LAST_DAY : 월의 마지막 날짜를 계산. 결과: 날짜 (2월 윤년때문에 존재.)

- ROUND : 날짜를 반올림. 결과: 날짜 (정오를 넘으면 다음날을 출력)

- TRUNC : 날짜를 절삭. 결과: 날짜 (시간정보와 상관없이 당일날을 출력)

 

-- 교수의 교수번호, 입사일, 입사일로부터 현재일까지의 개월수,
-- 입사일에서 6개월 후의 날짜를 출력하여라

SELECT PROFNO, HIREDATE
    , MONTHS_BETWEEN(SYSDATE, HIREDATE)
    , ADD_MONTHS(HIREDATE, 6) 
FROM PROFESSOR;

 

-- 오늘이 속한 달의 마지막 날짜와 다가오는 일요일의 날짜를 출력하여라

SELECT LAST_DAY(SYSDATE), NEXT_DAY(SYSDATE, '일')
FROM DUAL;

- NEXT_DAY(SYSDATE, 1), NEXT_DAY(SYSDATE, '일요일') 도 됨.

 

-- 101번학과 교수들의 입사일을 일, 월, 년을 기준으로 반올림하여 출력하여라.

SELECT TO_CHAR(HIREDATE, 'YY/MM/DD HH24:MI:SS') HIREDATE
    , TO_CHAR(ROUND(HIREDATE, 'DD'), 'YY/MM/DD') ROUND_DD
    , TO_CHAR(ROUND(HIREDATE, 'MM'), 'YY/MM/DD') ROUND_MM
    , TO_CHAR(ROUND(HIREDATE, 'YY'), 'YY/MM/DD') ROUND_YY
FROM PROFESSOR
WHERE DEPTNO = 101;

 

데이터 타입의 변환

• 묵시적 데이터 타입 변환
•  
• 명시적 제이터 타입 변환
• - TO_CHAR(숫자/날짜 타입을 문자로 변환), TO__NUMBER(숫자로 구성된 문자열을 숫자로 변환), TO_DATE

-- 학생테이블에서 전인하 학생의 학번과 생년월일중에서 년월만 출력.

SELECT STUDNO, BIRTHDATE
    , TO_CHAR(BIRTHDATE, 'YY/MM') YY_MM
FROM STUDENT
WHERE NAME = '전인하';

 

-- 학생테이블에서 102번학과 학생의 이름, 학년, 생년월일을 출력하여라

SELECT NAME, GRADE, TO_CHAR(BIRTHDATE, 'DAY MONTH DD,YYYY') BIRTHDATE
FROM STUDENT
WHERE DEPTNO = 102;

 

기타 날짜 표현 형식

- "text", TH(서수), SP(기수, 영문으로 표시), SPTH or THSP(서수를 영문으로 표시)

SELECT NAME, POSITION, TO_CHAR(HIREDATE, 'MON "THE" DDTH "OF" YYYY') HIREDATE
FROM PROFESSOR
WHERE DEPTNO = 101;

 

TO_CHAR 함수를 이용한 숫자 출력 형식 변환

 

-- 보직수당을 받는 교수들의 이름, 급여, 보직수당,
-- 그리고 급여와 보직수당을 더한 값에 12를 곱한 결과를 연봉으로 출력하여라.

SELECT NAME, SAL, COMM, TO_CHAR((SAL+COMM)*12, '9,999') ANUAL_SAL
FROM PROFESSOR
WHERE COMM IS NOT NULL;

 

-- 교수테이블에서 입사일이 '01/06/01'인 교수의 이름과 입사일을 출력하여라.

SELECT NAME, HIREDATE
FROM PROFESSOR
WHERE HIREDATE = TO_DATE('01/06/01', 'YY/MM/DD');

 

중첩 함수

F3(F2(F1(column, arg1), arg2), arg3)

- F1함수의 결과값은 F2의 인수로, F2의 결과값은 F3의 인수로 사용됨.

 

-- 주민등록번호에서 생년월일을 추출하여 YY/MM/DD 형태로 출력하여라.

SELECT IDNUM, TO_CHAR(TO_DATE(SUBSTR(IDNUM,1,6),'YYMMDD'),'YY/MM/DD') BIRTHDATE
FROM STUDENT;

- SUBSTR부터 작성.

 

일반함수 NVL 함수 

NVL(expression1, expression2)

- exp1 : NULL을 포함하는 칼럼 또는 표현식

- exp2 : NULL을 대체하는 값

 

-- 201번학과 교수의 이름, 직급, 급여, 보직수당, 급여와 보직수당의 합계를 출력하여라.
-- 단, 보직수당이 NULL인 경우에는 보직수당을 0으로 계산한다.

SELECT NAME, POSITION, SAL, NVL(COMM,0), NVL(SAL+COMM, SAL) 
FROM PROFESSOR;

SELECT NAME, POSITION, SAL, COMM, SAL+NVL(COMM, 0)
FROM PROFESSOR
WHERE DEPTNO = 201;

COMM = NVL(COMM,0)이니깐, SAL + NVL(COMM,0)으로 합칠 수 있음.

 

일반함수NVL 확장함수:  NVL2 함수

NVL2(expression1, expression2, expresion3)

- 첫번째 인수값이 NULL이 아니면 두번째 인수값을 출력하고, 첫번째 인수값이 NULL이면 세번째 인수값을출력하는 함수 (자바 삼항연산자같은 느낌)

 

-- 102번학과 교수중에서 보직수당을 받는 사람은 급여와 보직수당을 더한 값을 급여총액으로 출력.
-- 단, 보직수당을 받지 않는 교수는 급여만 급여총액으로 출력.

SELECT NAME, SAL, COMM
    , NVL2(COMM, SAL+COMM, SAL) TOTAL
FROM PROFESSOR
WHERE DEPTNO = 102;

 

일반함수NVL 확장함수: NULLIF 함수

- 두개의 표현식을 비교하여 값이 동일하면 NULL을 반환하고, 일치하지 않으면 첫번째 표현식의 값을 반환

 

-- 교수테이블에서 이름의 바이트 수와 사용자 아이디의 바이트 수를 비교해서
-- 같으면 NULL을 반환하고 같지 않으면 이름의 바이트 수를 반환하여라

SELECT NAME, USERID, LENGTHB(NAME)-3, LENGTHB(USERID)
    , NULLIF(LENGTHB(NAME)-3, LENGTHB(USERID)) NULLIF_RESULT
FROM PROFESSOR;

일반함수NVL 확장함수: COALESCE 함수

- 인수중에서 NULL이 아닌 첫번째 인수를 반환하는 함수

COALESCE(expression-1, expression-2, … ,expression-n)

- expression-1 : expression-1이 NULL이 아니면 expression-1을 반환
– expression-2 : expression-1이 NULL이고 expression-2가 NULL이 아니면 expression-2를 반환
– expression-n : expression-1부터 expression-n-1까지의 값이 NULL이고 expression-n이 NULL이 아니면 expression-n을 반환

 

일반함수 DECODE 함수

- IF문이나 CASE문으로 표현되는 복잡한 알고리즘을 하나의 SQL 명령문으로 간단하게 표현할 수 있는 유용한 기능
- DECODE 함수에서 비교연산자는 =만 가능

DECODE(expression | column, SEARCH1, RESULT1

           [, SEARCH2, RESULT2, …, ][, DEFAULT])

 

-- 교수테이블에서 교수의 소속학과 번호를 학과 이름으로 변환하여 출력하여라.
-- 학과번호가 101이면 컴퓨터공학과, 102이면 멀티미디어학과, 201이면 전자공학과,
-- 나머지 학과번호는 기계공학과(default)로 변환한다.

SELECT NAME, DEPTNO
    , DECODE(DEPTNO
        , 101, '컴퓨터공학과'
        , 102, '멀티미디어학과'
        , 201, '전자공학과'
        ,'기계공학과') DECODE
FROM PROFESSOR;

 

CASE

- DECODE 함수의 기능(문법) 확장. 문장 구조

- DECODE는 표현식 또는 칼럼값이 = 비교를 통해 조건과 일치하는 경우에 막다른 값으로 대치할 수 있지만, CASE에서는 산술연산, 관계연산, 논리연산과 같은 다양한 비교가 가능
–또한 WHEN절에서 표현식을 다양하게 정의

 

-- 교수테이블에서 소속학과에 따라 보너스를 다르게 계산하여 출력하여라.
-- 학과번호별로 보너스는 다음과 같이 계산한다. 
-- 학과번호가 101이면 보너스는 급여의 10%, 102이면20%, 201이면30%, 나머지학과는0%이다.

SELECT NAME, SAL,
    CASE
        WHEN DEPTNO = 101 THEN SAL * 0.1
        WHEN DEPTNO = 102 THEN SAL * 0.2
        WHEN DEPTNO = 201 THEN SAL * 0.3
        ELSE 0
    END BONUS
FROM PROFESSOR;

 

그룹 함수

- 테이블의 전체 행을 하나 이상의 컬럼을 기준으로 그룹화하여 그룹별로 결과를 출력하는 함수
- 그룹함수는 통계적인 결과를 출력하는데 자주 사용

 

그룹 함수의 종류

- COUNT, MAX, MIN, SUM, AVG ★

- STDDEV, VARIANCE, GROUPING GROUPING SETS

 

COUNT 함수

- 테이블에서 조건을 만족하는 행의 개수를 반환.

- *은 NULL을 포함한 모든 행의 개수
- DISTINCT는 중복되는 값을 제외한 행의 개수
- ALL은 중복되는 값을 포함한 행의 개수, 기본값은ALL
- expr 인수에서 사용 가능한 데이터타입은 CHAR, VARCHAR2, NUMBER, DATE 타입

 

-- 101번학과 교수중에서 보직수당을 받는 교수의 수를 출력하여라.

SELECT COUNT(COMM)
FROM PROFESSOR
WHERE DEPTNO = 101;

SELECT COUNT(*)
FROM PROFESSOR
WHERE COMM IS NOT NULL;

 

AVG, SUM 함수

- expr의 데이터 타입은 NUMBER 타입만 가능.

 

-- 101번학과 학생들의 몸무게 평균과 합계를 출력하여라.

SELECT AVG(WEIGHT), SUM(WEIGHT)
FROM STUDENT
WHERE DEPTNO = 101;

 

데이터 그룹 생성

• GROUP BY 절

- 특정 칼럼 값을 기준으로 테이블의 전체 행을 그룹별로 나누기 위한 절
- GROUP BY 절에 명시되지 않은 칼럼은 그룹함수와 함께 사용할 수 없음

 

• GROUP BY 절 사용할 때 적용되는 규칙

- 그룹핑 절에 WHERE 절을 사용하여 그룹 대상 집합을 먼저 선택
- GROUP BY 절에는 반드시 칼럼 이름을 포함해야 하며 칼럼 별명은 사용할 수 없음

- SELECT 절에서 나열된 칼럼 이름이나 표현식은 GROUP BY 절에서 반드시 명시

 

 

-- 교수테이블에서 학과별로 교수 수와 보직수당을 받는 교수 수를 출력하여라

SELECT COUNT(*), COUNT(COMM)
FROM PROFESSOR
GROUP BY DEPTNO;

- 행 개수의 예측을 해야 함.

 

 

다중칼럼을 이용한 그룹핑

- 하나 이상의 칼럼을 사용하여 그룹을 나누고, 그룹별로 다시 서브그룹을 나눔.
- 전체 교수를 학과별로 먼저 그룹핑한 다음, 학과별 교수를 직급별로 다시 그룹핑 하는 경우

 

-- 학과별로 소속 교수들의 평균급여, 최소급여, 최대급여를 출력하여라.

SELECT AVG(SAL), MIN(SAL), MAX(SAL)
FROM PROFESSOR
GROUP BY DEPTNO;

 

다중 칼럼을 이용한 그룹별 검색

-- 전체 학생을 소속학과별로 나누고, 같은학과 학생은 다시 학년별로 그룹핑하여,
-- 학과와 학년별 인원수, 평균 몸무게를 출력하여라.
-- 단, 평균 몸무게는 소수점 이하 첫번째 자리에서 반올림한다.

SELECT DEPTNO, GRADE,
    COUNT(*), ROUND(AVG(WEIGHT)) AVG_WEIGHT
FROM STUDENT
GROUP BY DEPTNO, GRADE
ORDER BY 1, 2;

 

HAVING 절

- GROUP BY 절에 의해 생성된 그룹을 대상으로 조건을 적용

 

-- 학생수가 4명 이상인 학년에 대해서 학년, 학생수, 평균키, 평균몸무게를 출력하여라.
-- 단, 평균키와 평균몸무게는 소수점 첫번째 자리에서 반올림하고,
-- 출력순서는 평균키가 높은순부터 내림차순으로 출력하여라.

SELECT GRADE, COUNT(*), ROUND(AVG(HEIGHT)), ROUND(AVG(WEIGHT))
FROM STUDENT
GROUP BY GRADE
--WHERE 1=1 실행순서 보려고
HAVING COUNT(*) >= 4
ORDER BY 3 DESC;

- COUNT(*) >= 4는 WHERE 절에서 처리 못 함.

- 쿼리 실행 순서: FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER

 

-- 학과별 학생수가 최대 또는 최소인 학과의 학생수를 출력하여라

SELECT MAX(COUNT(*)), MIN(COUNT(*))
FROM STUDENT
GROUP BY DEPTNO;

 

조인(JOIN)

- ANSI JOIN 형태 (표준 문법) 연습하기. JOIN 키워드가 들어가면 다 ANSI JOIN임.

 

카티션 곱

- 두 개 이상의 테이블에 대해 연결 가능한 행을 모두 결합

- 개발자가 시뮬레이션을 위한 대용량의 실험용 데이터를 생성하기 위해 의도적으로 사용 가능

-- 학생테이블과 부서테이블을 카티션 곱을 한 결과를 출력

SELECT *
FROM STUDENT
CROSS JOIN DEPARTMENT;

 

EQUI JOIN

- 조인 대상 테이블에서 공통 칼럼을 =(equal) 비교를 통해 같은 값을 가지는 행을 연결하여 결과를 생성하는 방법

 

-- 전인하 학생의 학번, 이름, 학과 이름 그리고 학과 위치를 출력하여라.

SELECT STUDNO, NAME, DNAME, LOC, DEPTNO
FROM STUDENT
NATURAL JOIN DEPARTMENT
WHERE NAME = '전인하';

-  NATURAL JOIN : 동등 조인의 한 분야. 컬럼명이 일치하면 그 컬럼을 동등값으로 비교. (겹치는 컬럼이 여러 개면 안 됨.) 조인 속성에 테이블 별명을 사용하면 오류 발생.

 

EQUI JOIN - JOIN ~ USING

- USING 절에 조인 대상 칼럼을 지정
- 칼럼 이름은 조인 대상 테이블에서 동일한 이름으로 정의되어 있어야 함.

 

-- JOIN ~ USING 절을 이용하여 학번, 이름, 학과번호, 학과이름, 학과위치를 출력하여라.

SELECT STUDNO, NAME, DEPTNO, DNAME, LOC
FROM STUDENT
JOIN DEPARTMENT
USING (DEPTNO);

NON-EQUI JOIN

- <, BETWEEN a AND b 와같이 '=' 조건이 아닌 연산자 사용

 

-- 교수테이블과 급여등급테이블을 NON-EQUI JOIN하여 교수별로 급여등급을 출력하여라.

SELECT PROFNO, NAME, SAL, GRADE
FROM PROFESSOR, SALGRADE
WHERE SAL BETWEEN LOSAL AND HISAL;

SELECT PROFNO, NAME, SAL, GRADE
FROM PROFESSOR
JOIN SALGRADE
ON SAL BETWEEN LOSAL AND HISAL;

 

OUTER JOIN

- EQUI JOIN에서 양측 칼럼 값 중의 하나가 NULL 이지만 조인 결과로 출력할 필요가 있는 경우 OUTER JOIN 사용

 

-- 학생테이블과 교수테이블을 조인하여 이름, 학년, 지도교수이름, 직급을 출력하여라.

-- 단, 지도학생을 배정받지 않은 교수 이름도 함께 출력하여라.

SELECT S.NAME, GRADE, P.NAME, POSITION
FROM STUDENT S
RIGHT JOIN PROFESSOR P
USING (PROFNO);

- 안시 조인 형태

LEFT JOIN, RIGHT JOIN, FULL JOIN