mysql 기본정리 2

# a1 DB 삭제/생성/선택

DROP DATABASE IF EXISTS a1;
CREATE DATABASE a1;
USE a1;


# 부서(홍보, 기획, IT)

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

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

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

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

# 사원(홍길동/홍보/5000만원, 홍길순/홍보/6000만원, 임꺽정/기획/4000만원)
## IT부서는 아직 사원이 없음
CREATE TABLE emp (
    id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
    regDate DATETIME NOT NULL,
    NAME CHAR(100) NOT NULL,
    deptId INT UNSIGNED NOT NULL,
    salary 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 * 
FROM dept;

SELECT *
FROM emp;

# 전 사원에 대하여, [부서명, 사원번호, 사원명] 양식으로 출력(IT 부서는 안나옴)

SELECT dept.name AS `부서명`,
emp.id AS `사원번호`,
emp.name AS `사원명`
FROM emp
INNER JOIN dept
ON dept.id = emp.deptId;


# 전 사원에 대하여, [부서명, 사원번호, 사원명] 양식으로 출력(IT 부서가 아직 사원이 없더라도, 1줄이라도 나오도록 해주세요, LEFT JOIN 필요)
## IT부서는 [IT, NULL, NULL] 으로 출력 (Left outer join은 게시판에서 게시글과 댓글 수를 보여줄 때 사용함)

SELECT dept.name AS `부서명`,
emp.id AS `사원번호`,
emp.name AS `사원명`
FROM dept
LEFT OUTER JOIN emp
ON dept.id = emp.deptId;



# 전 사원에 대하여, [부서명, 사원번호, 사원명] 양식으로 출력
## IT부서는 [IT, 0, -] 으로 출력

SELECT dept.name AS `부서명`,
IFNULL(emp.id, 0) AS `사원번호`,
IFNULL(emp.name,"-") AS `사원명`
FROM dept
LEFT OUTER JOIN emp
ON dept.id = emp.deptId;

# 모든 부서별, 최고연봉, IT부서는 0원으로 표시

SELECT dept.name AS `부서명`,
MAX(IFNULL(emp.salary, 0)) AS `최고연봉`
FROM dept
LEFT OUTER JOIN emp
ON dept.id = emp.deptId
GROUP BY dept.id;


# 모든 부서별, 최저연봉, IT부서는 0원으로 표시

SELECT dept.name AS `부서명`,
MIN(IFNULL(emp.salary, 0)) AS `최저연봉`
FROM dept
LEFT OUTER JOIN emp
ON dept.id = emp.deptId
GROUP BY dept.id;


# 모든 부서별, 평균연봉, IT부서는 0원으로 표시

SELECT dept.name AS `부서명`,
TRUNCATE(AVG(IFNULL(emp.salary, 0)),0) AS `평균연봉`
FROM dept
LEFT OUTER JOIN emp
ON dept.id = emp.deptId
GROUP BY dept.id;


# 하나의 쿼리로 최고연봉자와 최저연봉자의 이름과 연봉

(
    SELECT emp.name AS `이름`,
    emp.salary AS `연봉`,
    "최고연봉자" AS `타입`
    FROM emp
    ORDER BY emp.salary DESC
    LIMIT 1
)
UNION
(
    SELECT emp.name AS `이름`,
    emp.salary AS `연봉`,
    "최저연봉자" AS `타입`
    FROM emp
    ORDER BY emp.salary ASC
    LIMIT 1
)