Database 6일차

[ 문자함수 ]

▶ TRANSLATE(expr, from_str, to_str) : expr에서 from_str에 해당하는 문자를 찾아

     to_str로 한 글자씩 바꾼 결과 반환
예) TRANSLATE('나는 너를 모르는데', '나는', '너를') ➔ 너를 너를 모르는데

 

SELECT REPLACE('나는 너를 모르는데 너는 나를 알겠는가?', '나는', '너를') AS rep,
            TRANSLATE('나는 너를 모르는데 너는 나를 알겠는가?', '나는', '너를') AS trn
            FROM DUAL;

SELECT employee_id, emp_name, 
            TRANSLATE(EMP_NAME,'ABCDEFGHIJKLMNOPQRSTUWXYZ', 'fjekslfjakcndjwjskpfoiwjskmdzxc')
            AS TRANS_NAME
            FROM employees;

▶ INSTR(str, substr, pos, occur) : str에서 substr과 일치하는 위치를 반환, pos는 시작위치, occur은 몇 번째 일치하는지를 명시

 

--INSTR
SELECT INSTR('내가 만약 외로울 때면, 내가 만약 괴로울 때면, 내가 만약 즐거울 때면','만약') AS INSTR1,
            INSTR('내가 만약 외로울 때면, 내가 만약 괴로울 때면, 내가 만약 즐거울 때면','만약', 5) AS INSTR2,
            INSTR('내가 만약 외로울 때면, 내가 만약 괴로울 때면, 내가 만약 즐거울 때면','만약', 5, 2) AS INSTR3
    FROM DUAL;

▶  LENGTH( chr) : 문자열의 길이 반환

예) LENGTH('대한민국') ➔  4

▶ LENGTHB( chr) : 문자열의 BYTE수 반환

예) LENGTHB('대한민국') ➔  12

--LENGTH, LENGTHB
SELECT LENGTH('대한민국'),
            LENGTHB('대한민국')
        FROM DUAL;

---

[ 날짜함수 ]

▶ SYSDATE : 현재일자
예) SYSDATE ➔ 2015-03-16

 

▶ SYSTIMESTAMP : 현재일자와 시간 반환 (TIMESTAMP)
예) SYSTIMESTAMP ➔ 2015-03-16 22:10:56.998000000 +09:00

-- SYSDATE, SYSTIMESTAMP
SELECT SYSDATE, SYSTIMESTAMP
    FROM DUAL;

 

▶ ADD_MONTHS (date, integer) : date에 integer만큼 월을 더한 날짜 반환
예) ADD_MONTHS(SYSDATE, 1) ➔ 2015-04-16 22:10:33

--ADD_MONTHS
SELECT ADD_MONTHS(SYSDATE, 1), ADD_MONTHS(SYSDATE, -1)
    FROM DUAL;

 

▶ MONTHS_BETWEEN(date1, date2) : 두 날짜 사이의 개월 수 반환
예) MONTHS_BETWEEN(ADD_MONTHS(SYSDATE, 1), SYSDATE) ➔ 1

-- MONTHS_BETWEEN
SELECT MONTHS_BETWEEN(SYSDATE, ADD_MONTHS(SYSDATE, 1)) mon1,
         MONTHS_BETWEEN(ADD_MONTHS(SYSDATE, 1), SYSDATE) mon2
    FROM DUAL;

---

[ 날짜함수 ]

▶ LAST_DAY(date) : 해당 월의 마지막 일자 반환
예) LAST_DAY(SYSDATE) ➔ 2015-03-31

 

--LAST_DAY(date)
SELECT LAST_DAY(SYSDATE)
    FROM DUAL;

▶ ROUND(date, format) : 반올림한 날짜 반환
예) ROUND(SYSDATE, 'month') ➔ 2015-04-01 (현재일자가 3월 16일인 경우)

▶ TRUNC(date, format) : 잘라낸 날짜 반환
예) TRUNC(SYSDATE, 'month') ➔ 2015-03-01 (현재일자가 3월 16일인 경우)

--ROUND, TRUNC
SELECT SYSDATE, ROUND(SYSDATE,'month'),TRUNC(SYSDATE,'month')
    FROM DUAL;

▶ NEXT_DAY(date, char) : date를 char에 명시한 요일로 다음 주중 일자를 반환
예) NEXT_DAY(SYSDATE, '금요일') ➔ 2015-03-20

--NEXT_DAY
SELECT NEXT_DAY(SYSDATE, 2)
    FROM DUAL;

]

 

---

[ 변환함수 ]

▶ TO_CHAR (숫자 혹은 날짜, format) : format에 맞게 변환 후 결과 반환
예) TO_CHAR(123456789, '999,999,999') ➔ 123,456,789
TO_CHAR(SYSDATE, 'YYYY-MM-DD') ➔ 2015-03-16

-- TO_CHAR
SELECT TO_CHAR(12345679, '999,999,999')
    FROM DUAL;
    
SELECT TO_CHAR(SYSDATE, 'YYYY-MM-DD')
    FROM DUAL;

▶ TO_NUMBER(expr, format) : 문자나 다른 유형의 숫자를 NUMBER 형으로 변환
예) TO_NUMBER('123456') ➔ 123456

--TO_NUMBER    
SELECT TO_NUMBER('12356')
    FROM DUAL;

▶ TO_DATE (char, format) : DATE 형으로 변환
예) TO_DATE('20140101', 'YYYY-MM-DD') ➔ 2014/01/01 00:00:00

--TO_DATE  
SELECT TO_DATE('20140101', 'YYYY-MM-DD')
    FROM DUAL;

▶ TO_TIMESTAMP(char, format) : TIMESTAMP 형으로 변환
예) TO_TIMESTAMP('20140101', 'YYYY-MM-DD') ➔ 2014-01-01 00:00:00.000000000

SELECT TO_TIMESTAMP('20140101 13:44:50', 'YYYY-MM-DD HH24:MI:SS')
    FROM DUAL;

---

[ NULL 관련 함수 ]

① NVL(expr1, expr2), NVL2(expr1, expr2, expr3)
NVL 함수는 expr1이 NULL일 때 expr2를 반환한다.

-- NVL
SELECT NVL(manager_id, employee_id)
     FROM employees
    WHERE manager_id IS NULL;

SELECT employee_id,
        NVL2(commission_pct, salary + (salary * commission_pct),salary) AS salary2
    FROM employees;

 

② COALESCE(expr1, expr2, ...)
COALESCE 함수는 매개변수로 들어오는 표현식에서 NULL이 아닌 첫 번째 표현식을 반환하는 함수다.

 

-- COALESCE
SELECT employee_id,
           COALESCE(salary * commission_pct, salary) AS salary2
    FROM employees;

 

③ LNNVL(조건식)
LNNVL은 컬럼이 NULL인 경우 TRUE, 매개변수로 들어오는 조건식의 결과가 FALSE나 UNKNOWN이면 TRUE를,

TRUE이면 FALSE를 반환

--LNNVL(조건식)
SELECT employee_id, commission_pct
        FROM employees
        WHERE commission_pct < 0.2;
        
SELECT employee_id, commission_pct
        FROM employees
        WHERE NVL(commission_pct, 0) < 0.2;
 
SELECT COUNT(*)
        FROM employees
        WHERE NVL(commission_pct, 0) < 0.2;
        
SELECT COUNT(*)
        FROM employees
        WHERE LNNVL (commission_pct >= 0.2);

 

 

④ NULLIF(expr1, expr2)
NULLIF 함수는 expr1과 expr2를 비교해 같으면 NULL을, 같지 않으면 expr1을 반환한다.

-- NULLIF
SELECT employee_id,
    TO_CHAR(start_date, 'YYYY')start_year,
    TO_CHAR(end_date, 'YYYY') end_year,
    NULLIF(TO_CHAR(end_date, 'YYYY'), TO_CHAR(start_date, 'YYYY')) nullif_year
FROM job_history;

---

[ 기타함수 ]

▶ GREATEST(expr1, expr2, ...) : 매개변수로 들어오는 표현식에서 가장 큰 값을 반환

▶ LEAST(expr1, expr2, ...) : 매개변수로 들어오는 표현식에서 가장 작은 값을 반환

-- GREATEST
SELECT GREATEST('이순신', '장영실', '세종대왕'),
    LEAST('이순신', '장영실', '세종대왕')
FROM DUAL;

▶ DECODE(expr, search1, result1, search2, result2, ..., default) :
expr과 search1을 비교해 두 값이 같으면 result1을 반환.
같지 않으면 다시 search2와 비교해 값이 같으면 result2를 반환,
이런 식으로 계속 비교한 뒤 최종적으로 같은 값이 없으면 default 값을 반환한다.

-- DECODE
SELECT prod_id,
    DECODE(channel_id, 3, 'DIrect',
                9, 'Direct',
                5, 'Indirect',
                4, 'Indirect',
                    'Others') decodes
    FROM sales
WHERE rownum < 10;

---

-----------------------------------------------------------------
-----------------------------------------------------------------
--DB04 chap04_ex2
-----------------------------------------------------------------
--1. SUBSTR 함수를 사용하여 사원들의 입사한 년도와 입사한 달만 출력하세요.
SELECT 
  eno, 
  SUBSTR(hiredate, 1, 4) AS "입사년도", 
  SUBSTR(hiredate, 6, 2) AS "입사월" 
FROM EMPLOYEE;

--2. SUBSTR 함수를 사용하여 4월에 입사한 사원을출력하세요
SELECT
  eno,
  ename,
  hiredate
FROM EMPLOYEE
WHERE SUBSTR(hiredate, 4, 2) = '04';

--3. MOD 함수를 사용하여 사원번호가 짝수인 사람만 출력하세요
SELECT *
FROM EMPLOYEE
WHERE MOD(eno, 2) = 0;

--4. 입사일을 연도는 2자리(YY), 월은 숫자(MON), 
--일은 2자리(DD)로 표시하고 요일은 *요일(DAY)로 지정하여 출력하세요.
SELECT hiredate,
  TO_CHAR(hiredate, 'YY-MON-DD DAY')
FROM EMPLOYEE;

--5. 사원들의 매니저 사번을 출력하되 manager가
--없는 사원에 대해서는 NULL 대신에 0을 출력하세요.
SELECT  eno ,  ename,
  NVL2(manager, manager, 0) AS manager
FROM EMPLOYEE;

--6. DECODE 함수로 직급에 따라 급여를 인상하도록 하세요. 직급이 'ANALYST'인 사원은 200,
--'SALESMAN'인 사원은 180, 'MANAGER'인 사원은150,'CLERK'인 사원은 100을 인상하세요.
SELECT eno, ename,  job,  salary,
  DECODE(job,
         'ANALYST', salary + 200,
         'SALESMAN', salary + 180,
         'MANAGER', salary + 150,
         'CLERK', salary + 100,
         salary) AS "인상급여"
FROM EMPLOYEE;

---

[ 기본 집계함수 ]

집계 함수도 SQL 함수의 일종이다. 집계함수란 대상 데이 터를 특정 그룹으로 묶은 다음

이 그룹에 대해 총합, 평균, 최댓값, 최솟값 등을 구하는 함수를 말한다

 

[ 기본 집계함수 ]

▶ COUNT : 쿼리 결과건 수, 로우 수 반환
DISTINCT를 붙이면 뒤따라 나오는 컬럼에 있는 유일한 값만 조회

 

 

 

▶ SUM(expr) : expr의 전체 합계

▶ AVG(expr) : expr의 평균

▶ MIN(expr) : expr의 최소값

▶ MAX(expr) : expr의 최대값

▶ VARIANCE(expr) : expr의 분산

▶ STDDEV(expr) : expr의 표준편차

---

[ GROUP BY와 HAVING 절 ]

▶ GROUP BY 절
● 특정 그룹으로 묶어 데이터 집계 시 사용
● WHERE와 ORDER BY절 사이에 위치
● 집계함수와 함께 사용
● SELECT 리스트에서 집계함수를 제외한 모든 컬럼과 표현식은 GROUP BY 절에 명시해야 함

-- 2013년 지역별 가계대출 총 잔액

↑ 대출 내역 조회

--2013년 11월 지역별 가계대출 총 잔액

SELECT period, region, SUM(loan_jan_amt) totl_jan
    FROM kor_loan_status
WHERE period = '201311'
GROUP BY region, period
HAVING SUM(loan_jan_amt) > 100000
ORDER BY region;

-- 2013년도 대출 종류별 총 잔액

SELECT period, gubun, SUM(loan_jan_amt) totl_jan
    FROM kor_loan_status
WHERE period LIKE '2013%'
GROUP BY period, gubun
ORDER BY period;

▶ HAVING 절
● GROUP BY 절 다음에 위치하여, 그룹화된 결과를 대상으로 다시 필터링할 때 사용
● 집계 함수 조건을 지정할 수 있으며, SELECT 리스트에 사용된 집계함수를 활용해 조건을 명시

 

-- 2013년 11월 지역별 가계대출 중 100000을 초과하는 대출의 총 잔액

SELECT period, region, SUM(loan_jan_amt) totl_jan
    FROM kor_loan_status
WHERE period = '201311'
GROUP BY region, period
HAVING SUM(loan_jan_amt) > 100000
ORDER BY region;

 

 

EX_USER

문제

--• 예제 : 부서별 최고 급여가 3000 이상인 부서의 번호와 부서별 최고 급여 구하기

SELECT DNO, MAX(SALARY)
    FROM employee
    GROUP BY DNO
    HAVING MAX(SALARY) >= 3000
    ORDER BY DNO;

--• 예제: 매니저를 제외하고 급여 총액이 5000 이상인 담당 업무( job)별 급여 총액과 해당 인원을 구하기

SELECT JOB, COUNT(*), SUM(SALARY)
    FROM employee
    WHERE job NOT LIKE '%MANAGER%'
    GROUP BY JOB
    HAVING SUM(SALARY) >= 5000
    ORDER BY SUM(SALARY);

--• 예제: 부서별 평균 급여중 최고 평균 급여를 조회하기

SELECT MAX(AVG(salary))
    FROM employee
    GROUP BY dno;