0105 - sql 기본 문제 풀이 (group by와 join)

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

# 부서(홍보, 기획) `DEPT` 테이블 생성
CREATE TABLE dept(
	id INT PRIMARY KEY AUTO_INCREMENT
	,regDate DATETIME
	,`name` VARCHAR(10)
);

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

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

SELECT * FROM dept;

# 사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원) `EMP` 테이블 생성
CREATE TABLE emp(
	id INT PRIMARY KEY AUTO_INCREMENT
	,regDate DATETIME
	,`name` VARCHAR(10)
	,deptId INT
	,sal INT
);

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

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

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

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

# 가장 큰 사원 번호 출력
# 방법 1
# 사원번호만 출력
SELECT MAX(id) AS '사원번호' FROM emp;

# 방법 2
# 전체 테이블에서 가장 큰 사원 번호 출력
SELECT *
FROM emp
ORDER BY id DESC
LIMIT 1;

# 방법 3
# 이런 방법도 있음! => 연산이 두번 필요
SELECT *
FROM emp
WHERE id = (
	SELECT MAX(id)
	FROM emp);

# 가장 고액 연봉
SELECT MAX(sal) AS '고액 연봉'
FROM emp;

# 가장 저액 연봉
# 방법 1
SELECT MIN(sal) AS '저액 연봉'
FROM emp;

# 방법 2
SELECT `name`, sal
FROM emp
ORDER BY sal
LIMIT 1;

# 회사에서 1년 고정 지출(인건비)
SELECT AVG(sal) AS '1년 고정 지출(인건비)'
FROM emp; 

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

# 부서별, 최고연봉
SELECT deptId,MAX(sal)
FROM emp
GROUP BY deptId;

# 부서별, 최저연봉
SELECT deptId, MIN(sal)
FROM emp
GROUP BY deptId;

# 부서별, 평균연봉
SELECT deptId, TRUNCATE(AVG(sal),1) AS 평균연봉
FROM emp
GROUP BY deptId;

SELECT d.`name`, TRUNCATE(AVG(sal),1) AS 평균연봉
FROM `emp` e
JOIN `dept` d
ON e.deptId = d.id
GROUP BY `deptId`;

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

## V2(조인해서 부서명까지 나오는 버전)
SELECT d.`name`, GROUP_CONCAT(e.`name`) AS '사원리스트',
AVG(sal) AS '평균연봉', MAX(sal) AS '최고연봉',
MIN(sal) AS '최소연봉', COUNT(*) AS '사원수'
FROM `emp` e
JOIN `dept` d
ON e.deptId = d.id
GROUP BY `deptId`
HAVING `평균연봉` >= 5000;

SELECT * FROM dept;
## V3(V2에서 평균연봉이 5000이상인 부서로 추리기)