상황에 맞는 SQL작성하기, INNER JOIN, 사원, 부서

# a5 데이터베이스 삭제/생성/선택
drop database if exists a5;

create database a5;

use a5;

# 부서(dept) 테이블 생성 및 홍보부서 기획부서 추가
create table dept (
    id INT(10) 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;

# 사원(emp) 테이블 생성 및 홍길동사원(홍보부서), 홍길순사원(홍보부서), 임꺽정사원(기획부서) 추가
create table emp (
    id int(10) unsigned not null auto_increment,
    primary key(id),
    regDate datetime not null,
    `name` char(100) not null,
    deptName char(100) not null
);

insert into emp
set regDate = NOW(),
`name` = '홍길동',
deptName = '홍보';

INSERT INTO emp
SET regDate = NOW(),
`name` = '홍길순',
deptName = '홍보';

INSERT INTO emp
SET regDate = NOW(),
`name` = '임꺽정',
deptName = '기획';

# 홍보를 마케팅으로 변경
select *
from dept;

update dept
set `name` = '마케팅'
where `name` = '홍보';

select *
from emp;

update emp
set deptName = '마케팅'
where deptName = '홍보';

SELECT *
FROM emp;

# 마케팅을 홍보로 변경

UPDATE dept
SET `name` = '홍보'
WHERE `name` = '마케팅';

SELECT *
FROM dept;

UPDATE emp
SET deptName = '홍보'
WHERE deptName = '마케팅';

SELECT *
FROM emp;

# 홍보를 마케팅으로 변경
# 구조를 변경하기로 결정(사원 테이블에서, 이제는 부서를 이름이 아닌 번호로 기억)
ALTER TABLE emp add COLUMN deptId int(10) unsigned not null;

select *
from emp;

update emp
set deptId = 1
where deptName = '홍보';

UPDATE emp
SET deptId = 2
WHERE deptName = '기획';

SELECT *
FROM emp;

alter table emp drop column deptName;

select *
from dept;

UPDATE dept
SET `name` = '마케팅'
WHERE `name` = '홍보';

select *
from dept;

select *
from emp;

# 사장님께 드릴 인명록을 생성
select *
from emp;

# 사장님께서 부서번호가 아니라 부서명을 알고 싶어하신다.
# 그래서 dept 테이블 조회법을 알려드리고 혼이 났다.
SELECT *
FROM dept
where id = 1;

# 사장님께 드릴 인명록을 생성(v2, 부서명 포함, ON 없이)
# 이상한 데이터가 생성되어서 혼남
SELECT emp.*, dept.name AS `부서명`
FROM emp
inner join dept;

# 사장님께 드릴 인명록을 생성(v3, 부서명 포함, 올바른 조인 룰(ON) 적용)
# 보고용으로 좀 더 편하게 보여지도록 고쳐야 한다고 지적받음
SELECT emp.*, dept.id, dept.name AS `부서명`
FROM emp
inner join dept
ON emp.deptId = dept.id;

# 사장님께 드릴 인명록을 생성(v4, 사장님께서 보시기에 편한 칼럼명(AS))
SELECT emp.id AS `사원번호`,
emp.name AS `사원명`,
DATE(emp.regDate) AS `입사일`,
dept.name AS `부서명`
FROM emp
INNER JOIN dept
ON emp.deptId = dept.id
order by `부서명`, `사원번호`;