231030 (Oracle)

date 자료에서 특정 요소 추출하기

• extract(요소 from 자료)

선택을 위한 함수

• decode(열, 조건, 이름, 조건, 이름, …) : 열에서 각 조건에 해당하는 값을 가진 레코드에 이름을 출력한다. 마지막 에 조건, 이름 완료 후 이름을 작성하면 그 외의 값의 default 값을 설정할 수 있다.

조건에 따라 서로 다른 처리가 가능한 함수

• case when 조건 then 결과 …… end : 스위치문과 같은 방식으로 동작하게 된다. default 값에 대한 결과는 else를 사용하여 나타낸다.

그룹함수

• 하나 이상의 행을 그룹으로 묶어 연산하여, 하나의 결과를 나타내는 함수이다.
• 그룹함수와 그룹함수가 아닌 함수를 같이 출력하는 경우 레코드의 수가 달라 오류가 발생한다.
• sum(열) : 열의 값을 모두 합하여 하나의 레코드로 출력한다.
• avg(열) : 열의 값의 평균을 반환한다.
• max/min(열) : 열의 값 중 최대값과 최소값을 반환한다.
• count(열) : 열 중 값을 가진 레코드의 수를 반환한다.
• group by : 특정 열을 기준으로 그룹을 묶고, 그에 해당하는 값을 반환한다. 출력하고자 하는 값은 그룹으로 묶인 값, 혹은 그룹으로 묶은 열만을 나타낼 수 있다.
• having절 : 그룹함수에 대해 조건을 설정하는 방법. 조건 역시 그룹 함수로 설정해야 한다.

 

그룹 함수의 연습 문제

 

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

select last_name from employees order by hire_date;

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

select last_name from employees order by salary ,hire_date;

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

select upper(substr(email, 1, 1)) from employees;

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

select floor((sysdate - hire_date-1)/365)+1 from employees;

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

select last_name from employees where EXTRACT(month from hire_date)=9;

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

select last_name, case when salary>=24000 then salary*1.3 when salary>= 17000 then salary*1.2 else salary*2 end as withbonus from employees;

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

select count(hire_date) from employees group by EXTRACT(year from hire_date);

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

SELECT
    CASE
        WHEN salary BETWEEN 20000 AND 25000 THEN
            '상'
        WHEN salary BETWEEN 10000 AND 20000 THEN
            '중'
        ELSE
            '하'
    END AS 등급
FROM
    employees;

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

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

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

SELECT count(commission_pct) FROM employees;

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

SELECT department_id, avg(salary) FROM employees group by department_id;

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

SELECT department_id, sum(salary) FROM employees group by department_id having sum(salary) >= 90000;

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

SELECT department_id, count(department_id) FROM employees group by department_id having count(department_id) >= 6;

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

SELECT department_id, sum(salary) FROM employees where salary>=3000 group by department_id having sum(salary) >= 90000;

15. 사원테이블에서 똑같은 이름(first_name)이 둘 이상 있는 이름과 그 이름이 모두 몇명인지를 조회하는 쿼리문을 작성

SELECT first_name, count(first_name) FROM employees group by first_name having count(first_name) >= 2;

16. 부서번호, 각 부서별 급여총액, 평균급여를 조회하는 쿼리문을 작성(단, 부서 평균 급여가 8000 이상인 부서만 조회되도록)

SELECT department_id, sum(salary), avg(salary) FROM employees group by department_id having avg(salary) >= 8000;

17. 년도, 년도별로 입사한 사원의 수를 조회하는 쿼리를 작성 (단, 연도는 2014의 형태로 표기)

SELECT EXTRACT(year from hire_date), count(EXTRACT(year from hire_date)) FROM employees group by EXTRACT(year from hire_date);