공부 내용 정리 mySQL :: 예제 정리 4

# a6 DB 삭제/생성/선택
DROP DATABASE IF EXISTS `a6`;
CREATE DATABASE `a6`;
USE `a6`;

# 부서(홍보, 기획)
CREATE TABLE `dept` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    `name` CHAR(5) NOT NULL,
    regDate DATETIME NOT NULL
);

INSERT INTO dept
SET `name` = '홍보',
regDate = NOW();

INSERT INTO dept
SET `name` = '기획',
regDate = NOW();

INSERT INTO dept
SET `name` = 'IT',
regDate = NOW();

# 사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원)

CREATE TABLE emp (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    `name` CHAR(5) NOT NULL,
    `deptId` INT(10) NOT NULL,
    `salary` INT(10) NOT NULL,
    regDate DATETIME NOT NULL
);

INSERT INTO emp
SET `name` = '홍길동',
`deptId` = 1,
`salary` = 5000,
regDate = NOW();

INSERT INTO emp
SET `name` = '홍길순',
`deptId` = 1,
`salary` = 6000,
regDate = NOW();

INSERT INTO emp
SET `name` = '임꺽정',
`deptId` = 2,
`salary` = 4000,
regDate = NOW();

# 사원 수 출력
SELECT COUNT(*) AS `사원 수`
FROM emp

# 가장 큰 사원 번호 출력
SELECT MAX(id) AS `가장 큰 사원 번호`
FROM emp


# 가장 고액 연봉
SELECT MAX(salary) AS `고액연봉`
FROM emp

# 가장 저액 연봉
SELECT MIN(salary) AS `저액연봉`
FROM emp

# 회사에서 1년 고정 지출(인건비)
SELECT SUM(salary) AS `1년 고정 지출(인건비)`
FROM emp

# 부서별, 1년 고정 지출(인건비)
SELECT deptId, SUM(salary) AS `부서별 1년 고정 지출(인건비)`
FROM emp
GROUP BY deptId

# 부서별, 최고연봉
SELECT deptId, MAX(salary) AS `부서별 최고연봉`
FROM emp
GROUP BY deptId

# 부서별, 최저연봉
SELECT deptId, MIN(salary) AS `부서별 최저연봉`
FROM emp
GROUP BY deptId

# 부서별, 평균연봉
SELECT deptId, AVG(salary) AS `부서별 평균연봉`
FROM emp
GROUP BY deptId

# 부서별, 사원리스트, 평균연봉, 최고연봉, 최소연봉, 사원수 
## V1(조인 안한 버전)
SELECT deptId AS `부서별`,
GROUP_CONCAT(`name`) AS `사원리스트`,
TRUNCATE(AVG(`salary`),0) AS `평균연봉`,
MAX(`salary`) AS `최고연봉`,
MIN(`salary`) AS `최저연봉`,
COUNT(`id`) AS `사원 수`
FROM emp
GROUP BY deptId


## V2(조인해서 부서명까지 나오는 버전) 부서명
SELECT dept.name AS `부서명`,
GROUP_CONCAT(emp.name) AS `사원 리스트`,
TRUNCATE(AVG(emp.salary),0) AS `평균연봉`,
MAX(emp.salary) AS `최고연봉`,
MIN(emp.salary) AS `최저연봉`,
COUNT(emp.id) AS `사원 수`
FROM emp
INNER JOIN dept
ON emp.deptId = dept.id
GROUP BY emp.deptId

## V3(V2에서 평균연봉이 5000이상인 부서로 추리기)
SELECT dept.name AS `부서명`,
GROUP_CONCAT(emp.name) AS `사원 리스트`,
TRUNCATE(AVG(emp.salary),0) AS `평균 연봉`,
MAX(emp.salary) AS `최고 연봉`,
MIN(emp.salary) AS `최저 연봉`,
COUNT(emp.id) AS `사원 수`
FROM emp
INNER JOIN dept
ON dept.id = emp.deptId
GROUP BY emp.deptId
HAVING `평균 연봉` >= 5000;


## V4(V3에서 HAVING 없이 서브쿼리로 수행)
### HINT, UNION을 이용한 서브쿼리
# SELECT *
# FROM (
#     select 1 AS id
#     union
#     select 2
#     UNION
#     select 3
# ) AS A