JUST DO, 데이터베이스! 🕺 - (3) dual, 다양한 함수

방대한 데이터의 세계로, JUST DO DBMS!

---

📁 dual

• Oracle 자체에서 기본으로 제공하는 dummy table
• 간단하게 함수를 이용해서 계산 결과값을 확인 할 때 사용할 수 있어요.
• dual테이블은 사용자가 함수(계산)를 실행할 때 임의로 사용하는데 적합해요,
• 함수에 대한 쓰임을 알고 싶을 때 특정 테이블을 생성할 필요없이 dual 테이블을 사용하여 함수의 값을 리턴받을 수 있어요.

---

📁 다양한 QUERY(2)

⚫절대값(abs)

    SELECT 	ABS(-23)
    FROM	dual 
    ;
    
    /*
    출력겨로가)
    23
    */

⚫ 반올림 : round()

    SELECT	ROUND(0.123), ROUND(0.543)
    FROM	dual
    ;

	/*
    출력결과)
    0
    1
    */

⚫ 절사 : trunc()

    SELECT	TRUNC(1234.37273)
    FROM	dual
    ;

    SELECT	TRUNC(1234.37273, 2) -- 소숫점 둘째자리까지
    FROM	dual
    ;
    
    /*
   	출력결과)
    1234
    1234.37
    */

⚫ concat

• concat 은 인자를 2개 밖에 받을 수 없다.
• concat(인자 1, concat(인자1, 인자2) 와 같이 받을 수 있다.

    SELECT 	CONCAT('Hello', 'bye') , 
            CONCAT('good', 'bad') 
    FROM	dual
    ;

    SELECT 	CONCAT('Hello', 'bye') concats ,
            'good' || 'bad'
    FROM 	dual
    ;
    
    /*
    출력결과)
    Hellobye
    goodbad
    
    Hellobye
    goodbad
    */

⚫ INITCAP

    -- 첫 글자를 대문자로
    SELECT 	INITCAP('good morning')
    FROM 	dual
    ;
    
    /*
    출력결과)
    Good morning
    */

⚫ LOWER, UPPER

    -- 대/소문자 처리lower(char), upper(char)
    SELECT LOWER('GOOD'), UPPER('good') 
    FROM 	dual
    ;
    
    /*
    출력결과)
    good
    GOOD
    */

⚫ LPAD, RPAD

• lapd : lpad('값', '총문자길이', '채움문자') 왼쪽에 채워지는
• rpad : rpad('값', '총문자길이', '채움문자') 오른쪽에 채워지는
• 채움문자를 따로 적어주지 않으면 공백이 들어가요.

    SELECT	LPAD('good', 6) LPAD1,
            LPAD('good', 7, '#') LPAD2,
            LPAD('good', 8, 'L') LPAD3,
            RPAD('good', 7, '#') RPAD1,
            RPAD('good', 8, 'L') RPAD2
    FROM 	dual
    ;
    
    /*
    출력결과)
    good   //채움문자를 따로 입력하지 않으면 공백이 들어가요.
    good###
    goodLLLL
    ###good
    LLLLgood
    */
    

⚫ SUBSTR

    -- SUBSTR('문자열', '시작위치', '길이)
    SELECT 	SUBSTR('good morning joy', 1, 4) substr1,
            SUBSTR('good morning joy', 8, 2) substr2,
            SUBSTR('good morning joy', 8) substr3,
            -- 맨 마지막 파라미터를 주지 않으면 끝까지 출력
            SUBSTR('good morning joy', -2) substr4
            -- -는 뒤에서부터
    FROM	dual
    ;
    
    /*
    출력결과)
    good
    rn
    rning joy
    oy
    */

⚫ REPLACE()

    SELECT 	REPLACE ('good morning joy', 
                    'morning', 'evnning')
    FROM 	dual
    ;
    
    /*
    출력결과)
    good evnning joy
    */

⚫ SYSDATE

    -- 작성하는 현재 시간을 출력
    SELECT	SYSDATE 
    FROM 	dual
    ;

⚫ ADD_MONTHS()

    SELECT 	ADD_MONTHS(SYSDATE, 7) -- 현재 날짜로부터 7개월을 더함
    FROM	dual
    ;

⚫ LAST_DAY

    -- 현재달의 마지막 날짜
    SELECT	LAST_DAY(SYSDATE)
    FROM	dual
    ;

⚫ INTERVAL

    -- 년, 달, 일, 시간, 분, 초 뒤에
    SELECT	SYSDATE + (INTERVAL '1' YEAR) yearLater,
            SYSDATE + (INTERVAL '1' MONTH) monthLater,
            SYSDATE + (INTERVAL '1' DAY) dayLater,
            SYSDATE + (INTERVAL '1' HOUR) hourLater,
            SYSDATE + (INTERVAL '1' minute) minuteLater,
            SYSDATE + (INTERVAL '1' second) secondLater
    FROM	dual
    ;

⚫ TO_CAHR() : 문자열 반환

    SELECT 	TO_CHAR(SYSDATE, 'yyyy-mm-dd') ,
            TO_CHAR(SYSDATE, 'yyyy/mm/dd'),
            SYSDATE 
    FROM 	EMPLOYEES e 
    ;

    SELECT	TO_CHAR(SYSDATE, 'yyyymmdd'),
            TO_CHAR(SYSDATE, 'yyyy/mm/dd'),
            TO_CHAR(SYSDATE, 'yyyy-mm-dd'),
            TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss')
    FROM dual
    ;

⚫ TO_DATE()

    -- 날짜형으로 변환 
    SELECT	TO_DATE('2015/03/04', 'yyyy/mm/dd')
    FROM 	dual
    ;

⚫ NVL() :

• Null VaLue

    -- null 값을 다른 데이터로 변경하는 함수
    SELECT 	FIRST_NAME , LAST_NAME ,
            NVL(COMMISSION_PCT, 0) 
    FROM 	EMPLOYEES e 
    ;

⚫ DECOTE()

• switch 문의 역할을 하는 함수

    SELECT	*
    FROM 	DEPARTMENTS d 
    ;

    SELECT 	*
    FROM 	EMPLOYEES e 
    ;

    SELECT	DEPARTMENT_ID ,
            DECODE(DEPARTMENT_ID, 20, 'MA', 
                    60, 'IT', 90, 'EX', 'ETC') 
    FROM 	EMPLOYEES e 
    ORDER BY DEPARTMENT_ID 
    ;

⚫ CASE()

• if ~ else if문과 같은 역할을 하는 함수

    SELECT 	FIRST_NAME , DEPARTMENT_ID , 
        CASE WHEN DEPARTMENT_ID = 20 THEN 'MA'
             WHEN DEPARTMENT_ID = 60 THEN 'IT'
             WHEN DEPARTMENT_ID = 90 THEN 'EX'
             ELSE ''
        END department
    FROM 	EMPLOYEES e 
    ORDER BY DEPARTMENT_ID 
    ;    

⚫ GROUP BY

• GROUP BY : 데이터를 그룹핑해서 그 결과를 가져오는 경우에 사용해요.

    -- 집계함수와 짝을 이루어 사용할 수 있어요.
    SELECT 	DEPARTMENT_ID 
    FROM 	EMPLOYEES e 
    GROUP BY DEPARTMENT_ID 
    ;
    
    -- 부서별 사원수, 평균 급여, 급여의 합을 구해오기
    SELECT  DEPARTMENT_ID,
            COUNT(DEPARTMENT_ID) ,
            SUM(SALARY),
            AVG(SALARY)
    FROM 	EMPLOYEES e
    GROUP BY DEPARTMENT_ID  
    ;

    -- 부서별 직급별 사원수와 평균 급여를 구하기

    SELECT 	DEPARTMENT_ID,
            JOB_ID,
            COUNT(EMPLOYEE_ID),
            AVG(SALARY) 
    FROM 	EMPLOYEES e 
    GROUP BY DEPARTMENT_ID , JOB_ID 
    ORDER BY DEPARTMENT_ID , JOB_ID 
    ;

• GROUP BY와 조건절

    /*
     * WHERE절에서는 집계 함수를 사용할 수 없다.
     * HAVING절 : 집계함수를 가지고 조건 비교를 할 때, 사용한다.
     * 				HAVING 절은 GROUP BY 절과 함께 사용된다.
     */
    SELECT 	DEPARTMENT_ID , COUNT(*) 
    FROM 	EMPLOYEES e
    WHERE 	DEPARTMENT_ID IS NOT NULL
    -- AND		COUNT(*) >= 10 
    GROUP BY DEPARTMENT_ID
    -- 그룹핑 된 후 조건을 줄 때 HAVING 을 사용한다.
    HAVING 	COUNT(*) >= 10
    ;

---

📁 활용 예제

⚫ (1)

• 연봉이 12000 이상되는 직원들의 last_name, salary 를 조회

  SELECT	LAST_NAME,
          SALARY 
  FROM	EMPLOYEES e
  WHERE 	SALARY >= 12000
  ;

⚫ (2)

• 사원번호가 176인 사람의 last_name, 부서번호를 조회

    SELECT 	LAST_NAME ,
            DEPARTMENT_ID 
    FROM 	EMPLOYEES e 
    WHERE 	EMPLOYEE_ID = 176
    ;

⚫ (3)

• 연봉이 5000이상, 12000이하의 범위 이외인 사람들의 last_name, 연봉 조회

    SELECT 	LAST_NAME ,
            SALARY 
    FROM 	EMPLOYEES e 
    WHERE 	NOT SALARY BETWEEN 5000 AND 12000
    ;

⚫ (4)

• 20번 및 50번 부서에서 근무하는 모든 사원들의 last_name, 부서번호를 last_name의 알파벳 순으로 조회

    SELECT 	LAST_NAME,
            DEPARTMENT_ID 
    FROM 	EMPLOYEES e 
    WHERE 	DEPARTMENT_ID = 20
    OR		DEPARTMENT_ID = 50
    -- WHERE DEPARTMENT_ID IN(20, 50)
    ORDER BY LAST_NAME ASC
    ;

⚫ (5)

20번 및 50번 부서에 근무하며, 연봉이 5000~12000 사이인 사원들의 last_name 및 연봉 조회

    SELECT 	LAST_NAME ,
            SALARY,
            DEPARTMENT_ID 
    FROM 	EMPLOYEES e 
    WHERE SALARY BETWEEN 5000 AND 12000
    AND (DEPARTMENT_ID = 20 OR DEPARTMENT_ID = 50)
    ;

⚫ (6)

• last_name의 첫 글자가 A인 사원들의 last_name을 조회

    SELECT	LAST_NAME 
    FROM 	EMPLOYEES e 
    WHERE 	LAST_NAME LIKE 'A%'
    ;

⚫ (7)

• 매니저가 없는 사람들의 last_name 및 job_id를 조회

SELECT	LAST_NAME ,
		JOB_ID 
FROM 	EMPLOYEES e 
WHERE	MANAGER_ID IS NULL
;

⚫ (8)

• 커미션을 버는 모든 사원들의 last_name, 연봉, 커미션 조회 단, 연봉 역순 정렬

    SELECT 	LAST_NAME ,
            SALARY ,
            COMMISSION_PCT 
    FROM	EMPLOYEES e 
    WHERE 	COMMISSION_PCT IS NOT NULL
    -- WHERE	COMMISSION_PCT = 0
    -- 회사에 따라 커미션이 0 OR NULL 일 수 있다.
    ORDER BY SALARY DESC
    ;

⚫ (9)

• EMPLOYEES 테이블에서 King의 정보를 소문자로 검색하고 사원번호, 성명, 담당업무(job_id), 부서번호(department_id)를 출력 단, 담당업무(job_id)는 소문자 출력

    SELECT 	EMPLOYEE_ID ,
            LAST_NAME ,
            LOWER(JOB_ID) ,
            DEPARTMENT_ID
    FROM 	EMPLOYEES e 
    WHERE	LOWER(LAST_NAME) = 'king'
    ;

⚫ (10)

• EMPLOYEES 테이블에서 King의 정보를 대문자로 검색하고 사원번호, 성명, 담당업무(job_id), 부서번호(department_id)를 출력 단, 담당업무(job_id)는 대문자 출력

    SELECT 	EMPLOYEE_ID ,
            LAST_NAME  ,
            UPPER(JOB_ID) ,
            DEPARTMENT_ID
    FROM 	EMPLOYEES e 
    WHERE	UPPER(LAST_NAME) = 'KING'
    ;

⚫ (11)

• department 테이블에서 부서번호, 부서이름, 위치번호를 합하여 출력하도록 하라 (|| 사용)
  SELECT DEPARTMENT_ID || ' ' ||
  DEPARTMENT_NAME || ' ' ||
  LOCATION_ID
  FROM DEPARTMENTS d
  ;

⚫ (12)

• employees 테이블에서 30번 부서 중 사원번호, 이름과 담당아이디를 (manager id) 연결하여 출력하여라 (concat())

    SELECT  CONCAT(EMPLOYEE_ID, CONCAT(FIRST_NAME, MANAGER_ID))
    FROM 	EMPLOYEES e 
    WHERE 	DEPARTMENT_ID = 30
    ;

⚫ (13)

• employees 테이블에서 job_id가 'SA'로 시작하는 사람에 대하여급여의 평균, 최고급여, 최저급여, 급여 합계를 출력하라

  SELECT	AVG(SALARY),
          MAX(SALARY),
          MIN(SALARY),
          SUM(SALARY) 
  FROM 	EMPLOYEES e 
  WHERE 	JOB_ID LIKE 'SA%'
  ;

⚫ (14)

• EMPLOYEES 테이블에서 등록되어있는 인원 수, 커미션이 NULL이 아닌 인원수, 연봉 평균, 등록되어 있는(현재 존재하는) 부서의 수를 출력

    SELECT  COUNT(*),
            COUNT(COMMISSION_PCT),
            -- NULL은 카운팅이 되지 않는다.
            AVG(SALARY),
            COUNT (DISTINCT DEPARTMENT_ID)
    FROM	EMPLOYEES e 
    ;

⚫ (15)

• EMPLOYEES 테이블에서 부서별로 인원수, 평균급여, 최저급여, 최고급여, 급여의 합을 출력

SELECT 	COUNT(EMPLOYEE_ID),
		TRUNC(AVG(SALARY)) ,
		MIN(SALARY),
		MAX(SALARY),
		SUM(SALARY) 
FROM 	EMPLOYEES e 
GROUP BY DEPARTMENT_ID 
;

⚫ (16)

• EMPLOYEES 테이블에서 각 부서별, 업무별(JOB_ID) 그룹화하여 결과를 부서번호, 업무, 인원수, 급여의 평균, 급여의 합을 출력

    SELECT 	DEPARTMENT_ID ,
            JOB_ID ,
            COUNT(EMPLOYEE_ID), 
            TRUNC(AVG(SALARY)),
            SUM(SALARY) 
    FROM 	EMPLOYEES e 
    GROUP BY DEPARTMENT_ID , JOB_ID 
    ORDER BY DEPARTMENT_ID 
    ;

⚫ (17)

• EMPLOYEES 테이블에서 부서 인원이 4명보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력

    SELECT 	DEPARTMENT_ID ,
            COUNT(*),
            SUM(SALARY) 
    FROM  	EMPLOYEES e 
    HAVING 	COUNT(*) > 4
    GROUP BY DEPARTMENT_ID 
    ;

⚫ (18)

• EMPLOYEES 테이블에서 급여가 최대 10,000 이상인 부서에 대해서 부서번호, 평균 급여, 급여의 합을 출력

    SELECT 	DEPARTMENT_ID ,
            TRUNC(AVG(SALARY)) ,
            SUM(SALARY) ,
            MAX(SALARY)
    FROM 	EMPLOYEES e 
    HAVING	MAX(SALARY) >= 10000
    GROUP BY DEPARTMENT_ID 
    ;

⚫ (19)

• EMPLOYEES 테이블에서 업무별(JOB_ID) 급여의 평균이 10,000 이상인 업무에 대해서 업무명, 평균급여, 급여의 합 출력

    SELECT 	JOB_ID ,
            AVG(SALARY) ,
            SUM(SALARY) 
    FROM 	EMPLOYEES e
    HAVING	AVG(SALARY) >= 10000 
    GROUP BY	JOB_ID 
    ;

⚫ (20)

• EMPLOYEES 테이블에서 업무별 전체 월급이(SUM) 10,000을 초과하는 각 업무에 대해서 업무와, 급여의 합계를 출력. 단, 판매원(JOBID 가 SA로 시작하는..) 은 제외하고, 월 급여의 합계로 내림차순 정렬

    SELECT 	JOB_ID ,
            SUM(SALARY) 
    FROM 	EMPLOYEES e 
    WHERE	NOT JOB_ID LIKE 'SA_%'
    HAVING	SUM(SALARY) > 10000
    GROUP BY JOB_ID 
    ORDER BY SUM(SALARY) DESC
    ;

⚫ (21)

• 각 JOB_ID 별 최대 연봉, 최소 연봉, 연봉 총합 및 평균연봉을 JOB_ID 내림차순으로 조회

    SELECT 	MAX(SALARY),
            MIN(SALARY),
            SUM(SALARY),
            AVG(SALARY) 
    FROM 	EMPLOYEES e 
    GROUP BY JOB_ID 
    ORDER BY JOB_ID DESC
    ;

⚫ (22)

• EMPLOYEES 테이블에서 salary + salarycommition_pct 이
  10000이상이면 'good', 5000이상이면 'average', 1이상 5000미만이면 'bad', 0이면 'no good'으로 평가하고, EMPLOYEE_id, first_name, salary, commission_pct, salary + salarycommition_pct 을 출력

    SELECT 	EMPLOYEE_ID ,
            FIRST_NAME ,
            SALARY ,
            NVL(COMMISSION_PCT, 0) COMMISSION_PCT,
            SALARY  + (SALARY * NVL(COMMISSION_PCT, 0)) monthlyPay,
        CASE WHEN SALARY  + (SALARY * NVL(COMMISSION_PCT, 0)) >= 10000 THEN 'good'
             WHEN SALARY  + (SALARY * NVL(COMMISSION_PCT, 0)) >= 5000 THEN 'average'
             WHEN 1 < SALARY  + (SALARY * NVL(COMMISSION_PCT, 0)) AND SALARY  + (SALARY * NVL(COMMISSION_PCT, 0)) < 5000 THEN 'bad'
             ELSE 'no good'
        END MonthlyComment
    FROM	EMPLOYEES e 
    ORDER BY MonthlyComment 
    ;

---

포기하지 말고 JUST DO! ✔️

---

출처
https://media.giphy.com/media/1hVi7JFFzplHW/giphy.gif
https://media.giphy.com/media/jUwpNzg9IcyrK/giphy.gif