220411 ~ 14 데이터베이스 SELECT 함수 4

중간에 시험이랑 시험문제 풀이 등의 시간이 있어서 수업 내용이 별로 없음

 

---

 

1. SELECT 함수 4

1-1. GROUP BY

데이터를 그룹핑해서 그 결과를 가져오는 경우 사용한다.

집계 함수와 짝을 이루어 사용할 수 있다.

DISTINCT 처럼 중복 데이터를 제거해주는 효과를 주지만, DISTINCT와 달리 실제로 데이터를 제외하고 결과를 출력하는 것은 아니다.

-- GROUP BY 사용법
SELECT 	DEPARTMENT_ID 
FROM	EMPLOYEES e  
GROUP BY DEPARTMENT_ID 
;
-- DEPARTMENT_ID 별로 그룹핑되어 DEPARTMENT_ID가 출력된다.


-- 부서별 연봉 총합
-- SELECT 	DISTINCT DEPARTMENT_ID , sum(SALARY)
-- FROM 	EMPLOYEES e 
-- ;
-- distinct는 중복 데이터를 제거했기 때문에 에러가 남

SELECT 	sum(SALARY), DEPARTMENT_ID 
FROM 	EMPLOYEES e 
GROUP BY DEPARTMENT_ID
;
-- group by는 단순히 그룹핑 해준 상태이기 때문에 데이터가 살아있으므로 결과값이 나옴


-- 부서별 사원수와 총 급여와 평균 급여를 구해보자
SELECT 	DEPARTMENT_ID , sum(SALARY) , count(SALARY) , count(employee_id) , avg(SALARY)	
FROM 	EMPLOYEES e 
GROUP BY DEPARTMENT_ID 
;


-- 부서별, 직급별 사원수와 평균 급여를 구해보자
SELECT	count(EMPLOYEE_ID), avg(SALARY), DEPARTMENT_ID, JOB_ID 
FROM	EMPLOYEES e  
GROUP BY DEPARTMENT_ID, JOB_ID 
;
-- 부서별, 직급별로 따로따로 나오는 게 아니라, 부서별 그룹 안에서 직급별로 그룹핑이 다시 되는 것!

 

1-2. HAVING절

WHERE에서는 집계함수를 사용할 수 없다.HAVING : 집계함수를 가지고 조건비교를 할 때 사용하며, GROUP BY절과 함께 사용된다.*집계함수? COUNT, SUM 등

-- DEPARTMENT_ID가 NULL이 아니고 부서별로 사원수가 10이상인 부서의 ID와 사원수 
SELECT 	DEPARTMENT_ID , count(*)
FROM	EMPLOYEES e  
WHERE 	DEPARTMENT_ID IS NOT NULL 
GROUP BY DEPARTMENT_ID 
HAVING 	COUNT(*) >= 10
;

 

2. 예제

/*
 * 문제 1.
 * employees 테이블에서 job_id가 'SA'로 시작하는 사람에 대하여 
 * 급여의 평균, 최고액, 최저액, 합계를 출력하라
 */
SELECT 	AVG(SALARY) , MAX(SALARY) , MIN(SALARY), SUM(SALARY)  
FROM 	EMPLOYEES e 
WHERE 	JOB_ID LIKE 'SA%'
;


/*
 * 문제 2.
 * employees 테이블에 등록되어 있는 사원수, commission_pct가 null이 아닌 인원수, salary 평균, 
 * 등록되어 있는 부서의 수를 출력하라.
 */
SELECT 	COUNT(*), COUNT(COMMISSION_PCT), ROUND(AVG(SALARY)), COUNT(DISTINCT DEPARTMENT_ID) 
FROM 	EMPLOYEES e
;


/*
 * 문제 3.
 * employees 테이블에서 부서별 인원수, 평균급여, 최저급여, 최고 급여, 급여의 합을 출력하라.
 */
SELECT 	DEPARTMENT_ID , COUNT(*) , ROUND(AVG(SALARY)) , MIN(SALARY) , MAX(SALARY) 
	, SUM(SALARY)  
FROM 	EMPLOYEES e 
GROUP BY DEPARTMENT_ID 
;


/*
 * 문제 4.
 * employees 테이블에서 부서별, 업무별로 인원수, 평균급여, 급여의 합, 부서, 업무를 출력하라.
 */
SELECT 	COUNT(*) , ROUND(AVG(SALARY)) , SUM(SALARY), DEPARTMENT_ID , JOB_ID 
FROM 	EMPLOYEES e 
GROUP BY DEPARTMENT_ID , JOB_ID 
;


/*
 * 문제 5.
 * employees 테이블에서 부서 인원이 4명 보다 많은 부서의 부서번호, 인원수, 급여의 합을 출력하라.
 */
SELECT 	DEPARTMENT_ID , COUNT(*) , SUM(SALARY)
FROM 	EMPLOYEES e 
GROUP BY DEPARTMENT_ID 
HAVING 	COUNT(*) > 4;
;


/*
 * 문제 6.
 * employees 테이블에서 급여가 최대 10000 이상인 부서에 대해서, 
 * 부서번호, 평균급여, 급여의 합을 출력하라.
 */
SELECT 	DEPARTMENT_ID , ROUND(AVG(SALARY)) , SUM(SALARY) 
FROM 	EMPLOYEES e
GROUP BY DEPARTMENT_ID
HAVING 	MAX(SALARY) >= 10000 
;


/*
 * 문제 7.
 * employees 테이블에서 업무별 급여의 평균이 10000 이상인 업무에 대해서,
 * 업무명, 평균급여, 급여의 합을 구하여 출력하라.
 */
SELECT 	JOB_ID , AVG(SALARY) , SUM(SALARY) 
FROM 	EMPLOYEES e 
GROUP BY JOB_ID 
HAVING 	AVG(SALARY) >= 10000
;


/*
 * 문제 8.
 * employees 테이블에서 급여의 합(SALARY)이 10000을 초과하는 각 업무에 대해서
 * 업무와 월급의 합계를 출력하라.
 * 단, 업무가 'SA_'로 시작하는 업무(job_id)는 제외하고 월 급여 합계로 내림차순으로 정렬하라. 
 */
SELECT	JOB_ID , SUM(SALARY)  
FROM	EMPLOYEES e 
WHERE	JOB_ID NOT LIKE 'SA_%'
GROUP BY JOB_ID
HAVING	SUM(SALARY) > 10000
ORDER BY SUM(SALARY) DESC 
;


/*
 * 문제 9.
 * 각, job_id별 최대 급여, 최소 급여, 급여 총 합 및 평균 급여를 job_id 내림차순으로 조회
 */
SELECT 	JOB_ID , MAX(SALARY) , MIN(SALARY) , SUM(SALARY) , ROUND(AVG(SALARY)) 
FROM 	EMPLOYEES e
GROUP BY JOB_ID
ORDER BY JOB_ID DESC 
;


/*
 * 문제 10.
 * employees 테이블에서 동일한 직업(job_id)를 가진 사원들의 총 수를 조회하라.
 */
SELECT 	JOB_ID , COUNT(*)
FROM 	EMPLOYEES e 
GROUP BY JOB_ID 
;
-- count(*)와 count(특정컬럼) > 값은 동일하지만 count(*)가 속도가 더 빠르다.


/*
 * 문제 11.
 * 매니저의 사번 및 그 매니저가 관리하는 직원들 중 최소 연봉을 받는 사원의 연봉을 조회
 * 매니저가 없는 사람은 제외
 * 최소 연봉 기준 역순으로 조회
 */
SELECT 	MANAGER_ID , MIN(SALARY)
FROM 	EMPLOYEES e 
WHERE	MANAGER_ID IS NOT NULL 
GROUP BY MANAGER_ID 
ORDER BY MIN(SALARY) DESC
;