SQL 부서별 인원수 - SQL buseobyeol in-wonsu

like Amazon Web Services

목차 Show

• 문자 출력
• 날짜 출력
• 말일 구하기
• 월 반올림
• 날짜-> 문자
• update시 select 먼저!!!
• 퀴즈 ) 박구곤이라는 사람의 mailid를 null로 변경하시오
• count(*) vs count(컬럼)시 - null값 고려
• GROUP BY - 부서별로 그룹화
• 직책이 사원인 사람들의 평균연봉을 부서별로 구하고 연봉이 높은 순으로 정렬합니다!!!!!!!
• Q. 각 부서별 평균 급여를 계산해서 보여주시오
• Q. 각 부서별로 직책이 사원인 직원들의 평균 급여를 계산해서 보여주시오
• 그룹화 한 경우에는 그룹함수와 group by 에 지정된 컬럼만 사용가능
• Q. 각 지역 별(region_id)로 몇개의 부서가 있는지를 나타내시오
• Q. 각 부서별(dept_id)로 평균 급여(avg(salary))를 구하되 평균 급여가 2000 이상(avg(salary)>=2000)인 부서만 나타내시오
• 직책을 두개 이상 가지고 있는 그룹 평균 연봉을 보여줌
• Q. 각 직책별(title)로 급여의 총합(sum(salary))을 구하되 직책이 부장인 사람은 제외하시오,
• Q. 각 부서별로 직책이 사원인 직원들에 대해서만 평균 급여를 구하시오
• Q. 각 부서내에서(dept_id) 각 직책별(title)로 몇명의 인원(count(*))이 있는지 나타내시오
• Q. 각 부서내(dept_id)에서 몇명(count(*))의 직원이 근무하는지를 나타내시오
• Q. 각 부서별(dept_id)로 급여의 최소값(min(salary))과 최대값(max(salary))을 나타내시오

• 한글자 '_'

select * from s_emp
where name like '박_'; // '박근수' 검색 불가, '박__'으로 해야 가능

• 여러글자 '*'

select * from s_emp
where name like '박*'; // '박근수' 검색가능

Note DUAL 은 가상 테이블

문자 출력

select chr(65) from dual; // 영어 'A'
select nchr(44032) from dual; // 한글 '가'

날짜 출력

SELECT SYSDATE FROM DUAL; // 오늘 날짜

말일 구하기

SELECT TO_DATE('19/05/01')-1 FROM DUAL; // 익월에서 하루 빼면

월 반올림

SELECT ROUND(SYSDATE,'MONTH') FROM DUAL;

(오늘 4/16일 때)

SELECT ADD_MONTHS(TRUNC(SYSDATE, 'MONTH'),1)-1 FROM DUAL;

TRUNC - 버림 4/1
ADD_MONTHS - 한달 더하기 5/1
-1 하면 - 4월 마지막날 4/30

결과 4/30

날짜-> 문자

SELECT TO_CHAR(SYSDATE, 'yyyy/MM/dd') FROM DUAL;

update시 select 먼저!!!

퀴즈 ) 박구곤이라는 사람의 mailid를 null로 변경하시오

--update s_emp set mailid = null
select * from s_emp;
where name='박구곤';

count(*) vs count(컬럼)시 - null값 고려

select count(*) from s_emp; --e_emp 테이블의 행의 개수 : 25
select count(mailid) from s_emp; --s_emp 테이블의 mailid컬럼의 값의 개수 : 24 // null 값은 카운팅 되지 않음

GROUP BY - 부서별로 그룹화

직책이 사원인 사람들의 평균연봉을 부서별로 구하고 연봉이 높은 순으로 정렬합니다!!!!!!!

select dept_id, AVG(salary) -- 1. 평균연봉을 구한다
from s_emp              
where title = '사원'      -- 2. 사원인 사람들만
group by dept_id         -- 3. 부서별로 구한다(dept_id)
order by 2 desc;         -- 4. 두번째 컬럼AVG(salary)로 내림차순 정렬!

sql 할때 꼭 한두개 정도 평균 확인 해 볼 필요가 있다!
계산기 ㅎㅎㅎ 해보기 크으으으으
쿼리 결과랑 비교해서 값이 맞는지 확인!

Q. 각 부서별 평균 급여를 계산해서 보여주시오

select dept_id, trunc(AVG(salary),2) --1. 평균급여를 구한다.
from s_emp
group by dept_id  --2. 부서별로 구한다
order by 2 desc
;

Q. 각 부서별로 직책이 사원인 직원들의 평균 급여를 계산해서 보여주시오

select dept_id, avg(salary) from s_emp
where title = '사원'
group by dept_id
order by 1;

그룹화 한 경우에는 그룹함수와 group by 에 지정된 컬럼만 사용가능

select dept_id, avg(salary) from s_emp
group by dept_id;

Q. 각 지역 별(region_id)로 몇개의 부서가 있는지를 나타내시오

select region_id, count(name) from s_dept  -- 1. 몇개부서인지count(name) 
group by region_id
order by region_id;  -- 2. 지역별 그룹화

Q. 각 부서별(dept_id)로 평균 급여(avg(salary))를 구하되 평균 급여가 2000 이상(avg(salary)>=2000)인 부서만 나타내시오

select dept_id,avg(salary) from s_emp
group by dept_id
having avg(salary) >= 2000 -- 그룹함수 조건은 where 절이 아닌 having에 넣는다
;

직책을 두개 이상 가지고 있는 그룹 평균 연봉을 보여줌

select title, avg(salary), count(*) -- 직책 / 평균연봉 / 직책 수 를
from s_emp                              
group by title                      -- 직책별로
having count(*) > 2;                -- 직책의 개수가 2 초과 하면 출력

Q. 각 직책별(title)로 급여의 총합(sum(salary))을 구하되 직책이 부장인 사람은 제외하시오,

-- 단, 급여 총합이 8000만원 이상인 직책만 나타내며, 급여 총합에 대한 오름차순으로 정렬하시오

select title, sum(salary) from s_emp--1. 급여의 총합 - (sum(salary))
where title not like '%부장' -- 부장인 사람은 제외
group by title--2. 직책별로 - (title)
having sum(salary) >= 8000--3. 급여 총합이 8000만원 이상
order by 2 asc;--4. 오름차순

Q. 각 부서별로 직책이 사원인 직원들에 대해서만 평균 급여를 구하시오

select dept_id, avg(salary) from s_emp--1. 평균급여 출력
where title='사원' --3. 직책이 사원
group by dept_id--2. 부서별
;

Q. 각 부서내에서(dept_id) 각 직책별(title)로 몇명의 인원(count(*))이 있는지 나타내시오

select title, count(*) from s_emp--1. 몇명있는지 출력
group by title--3. 직책별로 그룹화
order by title
;

Q. 각 부서내(dept_id)에서 몇명(count(*))의 직원이 근무하는지를 나타내시오

select dept_id, count(*) from s_emp--1. 몇명있는지 출력
group by dept_id--2. 부서별로
;

Q. 각 부서별(dept_id)로 급여의 최소값(min(salary))과 최대값(max(salary))을 나타내시오

-- 단, 최소값과 최대값이 같은 부서((min(salary))=(max(salary))는 출력하지 마시오.

select dept_id, min(salary), max(salary) from s_emp --1. 급여의 최소값과 최대값
group by dept_id--2. 부서별로
--having min(salary) not like max(salary)--3. 부서별로 최소값과 최대값이 같으면 출력 안함
having min(salary) <> max(salary)--3. 부서별로 최소값과 최대값이 같으면 출력 안함
order by dept_id;

GROUP BY & HAVING

​

※ 작성 순서(숫자: 실행순서)

5: SELECT 컬럼명, AS 별칭, 계산식, 함수식 - 원하는 컬럼 값만 가져옴

1: FROM 참조할 테이블명

2: WHERE 컬럼명 | 함수식 비교 연산자 비교값

3: GROUP BY 그룹을 묶을 컬럼명

4: HAVING 그룹 함수식 비교 연산자 비교값

5: ORDER BY 컬럼명 | 별칭 | 컬럼순법 정렬방식 [NULLS FIRSH | LAST];

​

[GROUP BY 컬럼명 | 함수식]

여러개의 값을 묶어서 하나로 처리할 목적으로 사용함

그룹으로 묶은 값에 대해서 SELECT절에서 그룹 함수를 사용함

​

SELECT DEPT_CODE, SUM(SALARY)

FROM EMPLOYEE; >> 에러 발생

그룹 함수는 단 한개의 결과 값만 산출하므로 그룹이 여러개일 경우 오류 발생

​

- 여러개의 결과 값을 산출하기 위해 그룹 함수가 적용된 그룹의 기준을 GROUP BY절에 기술하여 사용

SELECT DEPT_CODE, SUM(SALARY)
FROM EMPLOYEE
GROUP BY DEPT_CODE;

부서코드, 그룹별 급여 합계, 그룹별 급여 평균(정수처리), 인원 수 조회 후 부서코드 순으로 정렬

SELECT DEPT_CODE 부서코드, SUM(SALARY) 합계, FLOOR(AVG(SALARY)) 평균, COUNT(*) 인원 수

GROUP BY DEPT_CODE >> 그룹처리

ORDER BY DEPT_CODE; >> 부서코드 순으로 정렬

​

- 부서코드와 부서별 보너스 받는 사원 수 조회 후 부서코드 순으로 정렬

SELECT DEPT_CODE 부서코드, COUNT(BONUS) 인원수
FROM EMPLOYEE
GROUP BY DEPT_CODE
ORDER BY DEPT_CODE;

​

- 직원 테이블에서 직급 코드별, 보너스를 받는 사원의 수를 조회하여 직급 코드순 오름차순 정렬

단, 보너스 받는 사원이 해당 직급코드에 없다면 조회하지 X

SELECT JOB_CODE 부서코드, COUNT(BONUS) 인원수
FROM EMPLOYEE
WHERE BONUS IS NOT NULL >> 사원이 0인 직급 코드 삭제
GROUP BY JOB_CODE
ORDER BY JOB_CODE;
>> 원하는 조건을 충족하는 ROW를 WHERE절에서 선행적으로 거름

- 성별과 성별별 급여 평균(정수 처리), 급여 합계, 인원 수 조회 후 인원 수 내림차순 정렬

SELECT DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여') 성별, FLOOR(AVG(SALARY) 평균, 
       SUM(SALARY) 합계, COUNT(*) 인원수 
FROM EMPLOYEE GROUP BY DECODE(SUBSTR(EMP_NO, 8, 1), 1, '남', 2, '여') 
ORDER BY COUNT(*) DESC; 

>> 함수식으로 GROUP BY 가능
* DECODE: 비교하고자 하는 값 또는 컬럼이 조건와 같으면 결과 값 반환
* FLOOR: 내림처리

​

- 부서별로 같은 직급인 사원의 급여 합계를 조회하고 부서 코드 순으로 정렬

SELECT DEPT_CODE, JOB_CODE, SUM(SALARY) 
FROM EMPLOYEE GROUP BY DEPT_CODE, JOB_CODE 
ORDER BY 1; 
>> 여러 컬럼으로 GROUP BY 가능

​

[HAVING]

HAVING절: 그룹 함수로 구해올 그룹에 대해 조건을 설정할 때 사용

HAVING 컬럼명 | 함수식 비교 연산자 비교값

​

- 급여 3,000,000 이상인 직원들의 부서 그룹별 급여 평균 조회(부서 코드 순 정렬)

SELECT DEPT_CODE, FLOOR(AVG(SALARY)) 평균 
FROM EMPLOYEEM WHERE SALARY >= 3000000 
GROUP BY DEPT_CODE 
ORDER BY 1;

​

- 부서 그룹별 급여 평균이 3,000,000 이상인 그룹 조회(부서 코드순 정렬)

;

SELECT DEPT_CODE, FLOOR(AVG(SALARY)) 평균
FROM EMPLOYEE HAVING FLOOR(AVG(SALARY)) >= 3000000
GROUP BY DEPT_CODE
ORDER BY 1

​

- 부서별 그룹의 급여 합계가 9백만원을 초과하는 부서코드와 급여 합계 조회(부서 코드순 정렬)

SELECT DEPT_CODE, SUM(SALARY) 
FROM EMPLOYEE 
HAVING SUM(SALARY) > 9000000 
GROUP BY DEPT_CODE 
ORDER BY 1;

​

※참고

- 급여 합계가 가장 많은 부서의 부서 코드와 부서 합계를 구하세요. (서브쿼리 이용)

SELECT DEPT_CODE, SUM(SALARY) 
FROM EMPLOYEE 
GROUP BY DEPT_CODE 
HAVING SUM(SALARY) = (SELECT MAX(SUM(SALARY)) 
FROM EMPLOYEE 
GROUP BY DEPT_CODE); 
>> 메인 쿼리를 보조하는 서브쿼리 구문

​

[집계함수 (ROLLUP, CUBE]

그룹별 산출한 결과 값의 집계를 계산하는 함수

​

- 직급 코드별 급여 합계와 마지막 행에 전체 급여 총합 조회 (직급 코드순 정렬)

SELECT JOB_CODE, SUM(SALARY) 
FROM EMPLOYEE 
GROUP BY JOB_CODE 
ORDER BY 1; 

>> 각 직급별 급여의 합만 출력 

SELECT JOB_CODE, SUM(SALARY) 
FROM EMPLOYEE
GROUP BY ROLLUP(JOB_CODE) 
ORDER BY 1;

>> 마지막 행에 직급별 급여 합을 모두 더한 총합이 출력됨 

SELECT JOB_CODE, SUM(SALARY) 
FROM EMPLOYEE 
GROUP BY CUBE(JOB_CODE) 
ORDER BY 1;

ROLLUP과 CUBE를 사용했을 때 같은 결과가 출력된다.

ROLLUP과 CUBE의 차이점은?

​

ROLLUP 함수​

- 그룹별로 중간 집계 처리를 하는 함수

- GROUP BY절에서만 사용하는 함수

- 그룹별로 묶여진 값에 대한 중간 집계와 총 집계를 구할 때 사용

- 그룹 함수로 계산된 결과 값들에 대한 총 집계가 자동으로 추가

* 인자로 전달받은 그룹 중 '가장 먼저' 지정한 그룹별 합계와 총 합계를 구하는 함수

​

- 부서 코드마다 직급 코드별 급여의 합, 부서별 급여의 합, 총합 조회(부서 코드순 정렬)

SELECT DEPT_CODE, JOB_CODE, SUM(SALARY) 
FROM EMPLOYEE
GROUP BY ROLLUP(DEPT_CODE, JOB_CODE) << 첫 번째 행에 대한 그룹별 합계 
ORDER BY 1;

​

CUBE 함수

* 그룹으로 지정된 모든 그룹에 대한 집계와 총 합계를 구하는 함수

SELECT DEPT_CODE, JOB_CODE, SUM(SALARY) 
FROM EMPLOYEE 
GROUP CUBE (DEPT_CODE, JOB_CODE) << 모든 행에 대한 그룹별 합계가 출력됨 
ORDER BY 1;

GROUPING 함수

- ROLLUP이나 CUBE에 의한 산출물이 인자로 전달 받은 컬럼 집합의 산출물이면 0을 반환하고 아니면 1을 반환하는 함수

SELECT DEPT_CODE, JOB_CODE, SUM(SALARY), 
       GROUPING(DEPT_CODE) "부서별그룹묶인상태", 
       GROUPING(JOB_CODE) "직급별그룹묶인상태" 
FROM EMPLOYEE 
GROUP BY ROLLUP(DEPT_CODE, JOB_CODE) 
ORDER BY DEPT_CODE; 

SELECT DEPT_CODE, JOB_CODE, SUM(SALARY), 
       CASE WHEN GROUPING(DEPT_CODE) = 0 AND GROUPING(JOB_CODE) = 1 THEN '부서별합계'
            WHEN GROUPING(DEPT_CODE) = 1 AND GROUPING(JOB_CODE) = 0 THEN '직급별합계' 
            WHEN GROUPING(DEPT_CODE) = 0 AND GROUPING(JOB_CODE) = 0 THEN '그룹별합계' 
            ELSE '총합계' 
       END 구분 
FROM EMPLOYEE 
GROUP BY CUBE(DEPT_CODE, JOB_CODE) 
ORDER BY 1;

​

SET OPERATION

- 여러가지 조건이 있을 때 그에 해당하는 여러개의 결과 값을 결합시키고 싶을 때 사용

​

UNION은 OR 같은 개념(합집합)

INTERSECT는 AND 같은 개념(교집합)

UNION ALL은 OR 결과 값에서 AND 결과값이 더해진 것 (합집함 + 교집함)

MINUS는 차집함

​

UNION

- 여러개의 쿼리 결과를 하나로 합치는 연산자. 중복된 영역을 제외하여 하나로 합친다.

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY 
FROM EMPLOYEE 
WHERE DEPT_CODE = 'D5' 

UNION 

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY 
FROM EMPLOYEE 
WHERE SALARY > 3000000; 

>> 위 쿼리문의 결과는 아래처럼 WHERE절에 OR을 쓴 것과 같다. 

SELECT EMP_ID, EMP_NAME, DPET_CODE, SALARY 
FROM EMPLOYEE 
WHERE DEPT_CODE = 'D5' OR SALARY > 3000000;

INTERSECT

- 여러개의 SELECT한 결과에서 공통 부분만 결과로 추출(교집합)

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY 
FROM EMPLOYEE 
WHERE DEPT_CODE = 'D5' 

INTERSECT 

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY 
FROM EMPLOYEE 
WHERE SALARY > 3000000;

>> 위 쿼리문의 결과는 아래처럼 WHERE절에 AND를 쓴 것과 같다. 

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY 
FROM EMPLOYEE 
WHERE DEPT_CODE = 'D5' AND SALARY > 3000000;

​

UNION ALL

- 여러개의 쿼리 결과를 하나로 합치는 연산자

- UINON과의 차이점은 중복 영역을 모두 포함시키는 것(합집함 + 교집함)

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY 
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' 

UNION

ALL SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY 
FROM EMPLOYEE 
WHERE SALARY > 3000000;

​

MINUS

- 선행 SELECT 결과에서 다음 SELECT 결과와 겹치는 부분을 제외한 나머지 부분만 추출(차집합)

- 부서 코드 D5 중 급여가 300만을 초과하는 직원 제외

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY 
FROM EMPLOYEE
WHERE DEPT_CODE = 'D5' 

MINUS

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY 
FROM EMPLOYEE 
WHERE SALARY > 3000000; 

>> 위 쿼리문의 결과는 다음과 같다. 
>> 부서코드 D5 중 급여가 300만 이하인 직원 조회 

SELECT EMP_ID, EMP_NAME, DEPT_CODE, SALARY 
FROM EMPLOYEE
WHERE DPET_CODE = 'D5' AND SALARY <= 3000000;

​

GROUPING SETS

- 그룹별로 처리된 여러개 SELECT문을 하나로 합칠 때 사용

- SET OPERATION를 사용한 결과와 동일

SELECT DEPT_CODE, JOB_CODE, MANAGER_ID, COUNT(*), FLOOR(AVG(SALARY)) 
FROM EMPLOYEE
GROUP BY GROUPING SETS((DEPT_CODE, MANAGER_ID),  << 1부터 14행까지 
                      (JOB_CODE, MANAGER)ID))    << 15행부터 
ORDER BY 1;