[ORACLE] 단일행과 복수행 함수 예제

문1) employees 테이블에서 입사일(hire_date) 가 가장 최근인 사원부터 출력.

☞ 최근이므로 내림차순을 나타내는 DESC로 출력

SELECT
    *
FROM
    employees
ORDER BY
    hire_date DESC;

 

 

문2) employees 테이블에서 salary가 많은순으로 정렬하고, 값이 같다면 hire_date 가 빠른 순으로 정렬하여 출력.

☞ 금액이 많은 순이면 내림차순, 날짜가 빠른순이면 오름차순 정렬이므로 각각 DESC, ASC로 출력함

SELECT
    *
FROM
    employees
ORDER BY
    salary DESC,
    hire_date ASC;

 

 

문3) employees 테이블에서 email 컬럼의 데이터를 첫글자만 대문자로 출력.

☞ 첫글자만 대문자로 출력하는 함수 : initcap()

SELECT
    initcap(email)
FROM
    employees;

 

 

문4) employees 테이블에서 사원들의 근무 년수를 출력.

SELECT
    employee_id,
    last_name,
    floor((months_between(sysdate, hire_date) / 12)) 근속년수
FROM
    employees;


-- 방법2 (참고)
SELECT
    hire_date,
    to_char(sysdate, 'YYYY') - to_char(hire_date, 'YYYY') 근속년수
FROM
    employees;

 

 

문5) 입사 날짜가 9월인 사원들의 정보 출력

☞ substr() 은 엑셀의 mid 함수와 비슷함. 몇 번째 열에서 몇 개를 출력할건지를 지정하게 됨

☞ to_char() 함수로 날짜에서 month를 문자형으로 변환하여 비교할 수도 있음

SELECT
    *
FROM
    employees
WHERE
    09 = substr(hire_date, 4, 2);


-- 방법2 (참고)
SELECT
    *
FROM
    employees
WHERE
    '09' = to_char(hire_date, 'MM');

 

 

문6) employees 테이블에서 월급이 24000 이상이면 30%, 17000 이상이면 20%, 나머지는 10% 보너스를 차등 지급하도록 출력.

SELECT
    salary,
    CASE
        WHEN salary = 24000 THEN
            0.3 * salary
        WHEN salary = 17000 THEN
            0.2 * salary
        ELSE
            0.1 * salary
    END commission
FROM
    employees;


-- 방법2 (참고)
SELECT
    salary,
    decode(salary, 24000, salary * 0.3, 17000, salary * 0.2,
           salary * 0.1) COMMISSION
FROM
    employees;

 

 

문7) employees 테이블에서 입사 년도별 사원수 출력

SELECT
    to_char(hire_date, 'YYYY') 입사년도,
    COUNT(*)
FROM
    employees
GROUP BY
    to_char(hire_date, 'YYYY');

 

 

문8) employees 테이블에서 salary 가 20001~25000 이면 상, 10001~20000 이면 중, 나머지는 하로 표시.

☞ SQL의 CASE문은 JAVA의 if-else문과 비슷한 구조를 갖고 있음

SELECT
    salary,
    CASE
        WHEN salary BETWEEN 20001 AND 25000 THEN
            '상'
        WHEN salary BETWEEN 10001 AND 20000 THEN
            '중'
        ELSE
            '하'
    END AS GRADE
FROM
    employees;

 

 

문9) employees 테이블에서 24000, 17000, 14000 이면 상, 13500, 13000 이면 중, 나머지는 하로 표시.

☞ 범위지정이 아닌 특정 값을 조건으로 잡을 때는 in 함수를 사용할 수 있음(or과 같은 효과)

SELECT
    salary,
    CASE
        WHEN salary IN ( 24000, 17000, 14000 ) THEN
            '상'
        WHEN salary IN ( 13500, 13000 ) THEN
            '중'
        ELSE
            '하'
    END AS GRADE
FROM
    employees;

 

 

 

문10) employees 테이블에서 커미션을 받는 사원들의 수 출력

☞ count() 는 null을 무시하고 숫자를 셈

WHERE commission_pct in not null 을 추가해도 O

SELECT
    COUNT(commission_pct)
FROM
    employees;

 

 

문11) employees 테이블에서 부서별 평균 월급을 출력.

☞ 부서별 함수를 사용하려면 GROUP BY 를 이용

null값은 평균을 구하는 범위에 포함하지 않으려면 아래와 같이 is not null 사용

SELECT
    department_id,
    AVG(salary)
FROM
    employees
WHERE
    department_id IS NOT NULL
GROUP BY
    department_id;

 

 

문12) employees 테이블에서 부서별 월급 총액이 90000 이상인 부서만 출력.

☞ 복수행 함수에서 조건을 추가하려면 HAVING 이용

SELECT
    department_id,
    SUM(salary)
FROM
    employees
GROUP BY
    department_id
HAVING
    SUM(salary) >= 90000;

 

 

 

문13) employees 테이블에서 부서별 인원수가 6명 이상인 부서명만 출력.

SELECT
    department_id,
    COUNT(employee_id) AS count
FROM
    employees
GROUP BY
    department_id
HAVING
    COUNT(employee_id) >= 6;

 

 

문14) employees 테이블에서 salary 가 3000 이상인 사원들에 대해서만 부서별 월급 총액이 90000 이상인 부서를 조회.

☞ GROUP으로 묶기 전에 조건을 수행하려면 사이에 WHERE로 조건 추가

SELECT
    department_id,
    SUM(salary) 급여합계
FROM
    employees
WHERE
    salary >= 3000
GROUP BY
    department_id
HAVING
    SUM(salary) >= 90000;