[210524월] 단일함수, 그룹함수(group by, having)

[주말과제 풀이]

create table guestbook
(
    bookseqno number primary key,
    useid varchar2(20) references userinfo(userid) on delete cascade,
    text varchar2(1000),
    regdate date default sysdate    
);

 

지정된 제약조건이 없는 경우에는 constraint_name을 비워둬도 되나? 아님 null로 이름을 줘야하나? 

→ 비워둬도 됩니다. 

 

 

[1교시]

▶ 단일함수

숫자함수 : round(), ceil(), floor(),...

문자열함수 : Lower(), Upper(), Length(), Substr()

날짜함수 :  add_month(), month_between()

변환함수 : to_char(), to_date(), nvl()

             decode(컬럼, 값1, 작업1, 값2, 작업2,...작업(default))

 

nvl() 함수

: nvl(컬럼명, 값), null이 들어있는 값을 다른 값으로 변환해주는 함수, null value를 처리해주는 함수 

 

▶ 그룹함수: 특정 row를 기준으로 그룹화시켜서 적용하는 함수(~별, 부서별, 지급별 등등)

- 문자열보다는 숫자 데이터로 집계를 내야할 때 자주 사용한다. 

- select절, group by, having 

- 주의:  where절에 조건을 줄 때는 단일의 row에 조건을 줄 때 사용하는 문장이지만 그룹화된 데이터에 조건을 줄 때는 having이라는 명령문을 사용한다. where(단일 row), having(그룹화된 데이터)

- count(컬럼명), sum(컬럼명), avg(컬럼명), max(컬럼명), min(컬럼명), stdenv(컬럼명), variance(컬럼명)

- 결과가 한 줄로 나온다. 하나의 열에 출력결과를 담는 다중행 함수이다.        

 

[count() 함수]

: 전체 조직이 인원 수를 구할 때 사용할 수 있다. 

count() 함수는 null은 제외해 세지 않는다. 

사장인 'King'은 manager가 없어 null을 가지고 있기 때문에 106이 나온다. 

employee_id와 manager_id가 의미하는 것 이해하기

manager_id의 번호가 뜻하는 것은 해당 직원의 상사의 employee_id를 의미한다. 

 

 

[avg(), sum() 함수]

: 연산이 가능한 숫자데이터가 들어있는 컬럼이 와야한다. 

 

[min(), max() 함수]

 

그룹함수 

DISTINCT로 중복 제거 

order by절의 1은 화면에 출력되는 첫번째 컬럼을 기준으로 정렬해라는 의미이다. 

 

급여평균

 

부서별 급여 평균 

order by 1은 첫번째 컬럼을 기준을 정렬하겠다를 의미한다. 

 

직급별 급여 총액

★ gruoup by를 써줘야 하는 이유 

오류 ORA-00937 

데이터가 여러개가 나오는 컬럼과 한 줄로 나오는 칼럼을 함께 쓸 수 없다. 오류를 없애기 위해 group by를 사용해줘야 한다.  전체 row가 나오는 함수, 1개만 나오는 함수를 알아봐야 한다. 

여러 개가 나오는 데이터로 group by해야한다.

 

부서별 급여 평균(급여 평균이 8000을 초과하는 부서만)
- where절은 단일 row에 조건을 걸고자 할 때 사용 
- 그룹함수에서는 조건을 줄 때 WHERE절 대신 HAVING이라는 절을 사용한다.

 

 

 

[교재 P177]

COUNT 함수와 DISTINCT, ALL 사용 

 

[그룹함수 연습문제]

1번) 80번 부서의 급여 평균, 최고, 최저, 인원수 조회

 

2번) 각 부서별 급여의 평균, 최고, 최저, 인원수 조회 

 

2-1번) 부서번호가 NULL인 데이터는 출력되지 않도록 조건을 부여

 

3번) 각 부서별 같은 업무를 하는 사람의 인원수를 구하여 부서번호, 업무명, 인원수 조회 

부서번호와 업무명과 같이 여러 줄의 데이터가 나오는 데이터가 나온다. 이럴 경우에는?

SELECT DEPARTMENT_ID, JOB_ID, COUNT(*)
DEPARTMENT_ID 여러개, JOB_ID 여러 개, COUNT(*) 한 줄 

=> 그룹화되지 않은 데이터와 그룹화된 데이터를 같이 쓰면 오류가 나기 때문에 GROUP화해야 한다.

같은 부서에서 또 그룹화된 데이터 

 

 

4번) 같은 업무를 하는 사람의 수가 4명 이상인 업무와 인원수 조회 

count(job_id)

SELECT JOB_ID, COUNT(JOB_ID)
FROM EMPLOYEES
GROUP BY JOB_ID
HAVING COUNT(JOB_ID) >= 4
ORDER BY 1; 

 

 

[교재 잊기 전에 한 번 더! P212-213]

Q3. 사원들의 입사연도를 기준으로 부서별 몇 명이 입사했는지 출력하라. (부서번호, 입사연도, 인원수)

 

Q4. 커미션을 받는 사람과 받지 않는 사람의 인원수를 출력 (O, X)

 

Q5. 각 부서의 입사 연도별 사원수, 최고급여, 급여합, 평균급여 (ROLLUP함수 사용)

 

ROLLUP함수

: 명시한 열을 소그룹부터 대그룹의 순서로 각 그룹별 결과를 출력하고 마지막 총 데이터의 결과를 출력합니다. 

 

select department_id,
        to_char(hire_date, 'YYYY') AS HIRE_YEAR,
        COUNT(*) AS CNT,
        MAX(SALARY) AS MAX_SAL,
        SUM(SALARY) AS SUM_SAL,
        AVG(SALARY) AS AVG_SAL
        FROM EMPLOYEES
GROUP BY ROLLUP(DEPARTMENT_ID, TO_CHAR(HIRE_DATE, 'YYYY'));