ORACLE 오라클 SQL9 - CASE, 그룹함수

CASE문의 경우 개발을 하게 되면 간혹 사용하게 된다. 나도 종종 사용해서 특정 값에 대해 CASE문을 통해 값을 나타내게끔 했다.

오라클에서는 특이하게 DECODE라는 함수를 제공한다.

 

알클 오라클 유튜브 강의

https://www.youtube.com/watch?v=3Uo3O19pWoE&list=PL4C2AmBC9jOYAQrvie3y5c8GN01KGq5WR&index=22

 

CASE
    WHEN 조건1 THEN 결과1
    WHEN 조건2 THEN 결과2
    WHEN 조건3 THEN 결과3
    .....
    ELSE 결과A ----- 위 모든 조건 만족하지 않을 때, 결과 A를 반환
END 

 

---

46. 테이블 EMPLOYEES의 FIRST, DEPARTMENT_ID 속성을 이용하여 <예시>와 같이 SQL문을 작성하시오.

DECODE함수 사용

-- DECODE함수를 이용하시오.
-- 부서명은 HR계정의 DEPARTMENTS 테이블을 참조하시오
SELECT
    first_name AS "이름",
    department_id AS "부서번호",
    DECODE(department_id, 
                        10,'Administration',
                        20,'Marketing',
                        30,'Purchasing',
                        40,'Human Resources',
                        50,'Shipping',
                        60,'IT',
                        70,'Public Relations',
                        80,'Sales',
                        90,'Executive',
                        100,'Finance',
                        110,'Accounting',
                        120,'Treasury',
                        130,'Corporate Tax',
                        140,'Control And Credit',
                        150,'Shareholder Services',
                        160,'Benefits',
                        170,'Manufacturing',
                        180,'Construction',
                        190,'Contracting',
                        200,'Operations',
                        210,'IT Support',
                        220,'NOC',
                        230,'IT Helpdesk',
                        240,'Government Sales',
                        250,'Retail Sales',
                        260,'Recruiting',
                        270,'Payroll'
    ) AS "부서"
FROM employees;

SELECT * FROM departments;

---

 

47. 테이블 EMPLOYEES의 FIRST_NAME, DEPARTMENT_ID 속성을 이용하여 <예시>와 같이 SQL문을 작성하시오.

(HR계정 샘플 데이터)

SELECT
    first_name AS "이름",
    department_id AS "부서번호",
    CASE
        WHEN department_id = 10 THEN 'Administration'
        WHEN department_id = 20 THEN 'Marketing'
        WHEN department_id = 30 THEN 'Purchasing'
        WHEN department_id = 40 THEN 'Human Resources'
        WHEN department_id = 50 THEN 'Shipping'
        WHEN department_id = 60 THEN 'IT'
        WHEN department_id = 70 THEN 'Public Relations'
        WHEN department_id = 80 THEN 'Sales'
        WHEN department_id = 90 THEN 'Executive'
        WHEN department_id = 100 THEN 'Finance'
        WHEN department_id = 110 THEN 'Accounting'
        WHEN department_id = 120 THEN 'Treasury'
        WHEN department_id = 130 THEN 'Corporate Tax'
        WHEN department_id = 140 THEN 'Control And Credit'
        WHEN department_id = 150 THEN 'Shareholder Services'
        WHEN department_id = 160 THEN 'Benefits'
        WHEN department_id = 170 THEN 'Manufacturing'
        WHEN department_id = 180 THEN 'Construction'
        WHEN department_id = 190 THEN 'Contracting'
        WHEN department_id = 200 THEN 'Operations'
        WHEN department_id = 210 THEN 'IT Support'
        WHEN department_id = 220 THEN 'NOC'
        WHEN department_id = 230 THEN 'IT Helpdesk'
        WHEN department_id = 240 THEN 'Government Sales'
        WHEN department_id = 250 THEN 'Retail Sales'
        WHEN department_id = 260 THEN 'Recruiting'
        WHEN department_id = 270 THEN 'Payroll'
    END AS "부서명"
FROM EMPLOYEES;

 

 

위의 문장에서는 department테이블의 부서명을 참조하여 위와 같이 case문을 통해 부서명을 표기해 주었다.

보통 실무에서는 department테이블과 employees의 테이블을 JOIN하여 부서명을 표기할 수 있다.

 

---

48. 다음 <예시>와 같이 테이블 EMPLOYEES의 사원 수를 구하는 SQL문을 작성하시오. (HR계정 샘플 데이터)

SELECT
    COUNT(FIRST_NAME) AS "사원수"
FROM EMPLOYEES;

SELECT COUNT(*) AS "사원 수"
FROM EMPLOYEES;

---

49.다음 <예시>와 같이 테이블 EMPLOYEES의 최고급여, 최저급여를 구하는 SQL문을 작성하시오. (HR의 샘플 데이터)

SELECT
    MAX(salary) AS "최고급여",
    MIN(salary) AS "최저급여"
FROM EMPLOYEES;

---

50. 다음 <예시>와 같이 테이블 EMPLOYEES의 급여합계, 급여평균을 구하는 SQL을 구하시오.(HR의 샘플 데이터)

SELECT
    SUM(salary) AS "급여합계",
    ROUND(AVG(salary),2) AS "급여평균"
FROM EMPLOYEES;

---

51. 다음 <예시>와 같이 테이블 EMPLOYEES의 급여표준편차와 급여분산을 구하시오.(HR계정 샘플 데이터)

SELECT
    ROUND( STDDEV(salary),2 ) AS "급여표준편차",
    ROUND( VARIANCE(salary), 2 ) AS "급여분산"
FROM EMPLOYEES;

 

 

 

 

*블로그의 내용은 알클님 강의를 기반으로 작성되었습니다!