mysql 기본정리 3

# 현재 세션에서 `ONLY_FULL_GROUP_BY` 모드 끄기
## 영구적으로 설정되는 것은 아닙니다.
SET SESSION sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));
SET sql_mode = (SELECT REPLACE(@@sql_mode, 'ONLY_FULL_GROUP_BY', ''));

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

# 부서(홍보, 기획)

CREATE TABLE dept (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    regDate DATETIME NOT NULL,
    `name` CHAR(100) NOT NULL UNIQUE
);

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 UNSIGNED NOT NULL AUTO_INCREMENT,
    PRIMARY KEY(id),
    regDate DATETIME NOT NULL,
    NAME CHAR(100) NOT NULL,
    salary INT NOT NULL
);

ALTER TABLE emp ADD COLUMN deptId INT UNSIGNED NOT NULL;



INSERT INTO emp
SET regDate = NOW(),
NAME = "홍길동",
deptId = 1,
salary = 5000;

INSERT INTO emp
SET regDate = NOW(),
NAME = "홍길순",
deptId = 1,
salary = 6000;

INSERT INTO emp
SET regDate = NOW(),
NAME = "임꺽정",
deptId = 2,
salary = 4000;

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


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


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

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

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

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

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

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


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


# 부서별, 부서명, 사원리스트, 평균연봉, 최고연봉, 최소연봉, 사원수 평균연봉이 5000이상인 부서로 추리기

SELECT D.name AS `부서명`,
GROUP_CONCAT(E.NAME ORDER BY E.id DESC SEPARATOR ", ") AS `사원리스트`,
CONCAT(TRUNCATE(AVG(E.salary), 0), "만원") AS `평균연봉`,
CONCAT(TRUNCATE(MAX(E.salary), 0), "만원") AS `최고연봉`,
CONCAT(TRUNCATE(MIN(E.salary),0), "만원") AS `최소연봉`,
CONCAT(TRUNCATE(COUNT(*),0), "명") AS `사원수`
FROM emp AS E
INNER JOIN dept AS D
ON E.deptId = D.id
GROUP BY E.deptId
HAVING `평균연봉` >= 5000;


(HAVING 없이 서브쿼리로 수행)
### HINT, UNION을 이용한 서브쿼리
# SELECT *
# FROM (
#     SELECT 1 AS id
#     UNION
#     SELECT 2
#     UNION
#     SELECT 3
# ) AS A
#서브쿼리는 말 그대로 () 괄호 안에서 테이블을 새로 만들고 거기서  데이터를 추출하는 거라고 생각을 하면 된다.

SELECT *
FROM (
    SELECT D.name AS `부서명`,
    GROUP_CONCAT(E.NAME ORDER BY E.id DESC SEPARATOR ", ") AS `사원리스트`,
    CONCAT(TRUNCATE(AVG(E.salary), 0), "만원") AS `평균연봉`,
    CONCAT(TRUNCATE(MAX(E.salary), 0), "만원") AS `최고연봉`,
    CONCAT(TRUNCATE(MIN(E.salary),0), "만원") AS `최소연봉`,
    CONCAT(TRUNCATE(COUNT(*),0), "명") AS `사원수`
    FROM emp AS E
    INNER JOIN dept AS D
    ON E.deptId = D.id
    GROUP BY E.deptId
) AS `D`
WHERE D.평균연봉 >= 5000;