-- Employees 테이블의 사원들 정보를 아래의 두 테이블에 나눠 저장하세요.
--1. emp_personal_info 테이블에는 employee_id, first_name, last_name,
-- email, phone_number가 저장되도록 하세요.
--2. emp_office_info 테이블에는 employee_id, hire_date,
-- salary, commission_pct, manager_id, department_id가 저장되도록 하세요.
CREATE TABLE emp_personal_info AS
SELECT employee_id, first_name, last_name, email, phone_number
FROM employees
WHERE 1=2;
CREATE TABLE emp_office_info AS
SELECT employee_id, hire_date, salary, commission_pct,
manager_id, department_id
FROM employees
WHERE 1=2;
INSERT ALL
INTO emp_personal_info
VALUES (employee_id, first_name, last_name, email, phone_number)
INTO emp_office_info
VALUES (employee_id, hire_date, salary, commission_pct,
manager_id, department_id)
SELECT * FROM employees;
SELECT * FROM emp_personal_info;
SELECT * FROM emp_office_info;
--- Employees 테이블의 사원들 정보를 아래의 두 테이블에 나눠 저장하세요.
--1. 보너스가 있는 사원들의 정보는 emp_comm 테이블에 저장하세요.
--2. 보너스가 없는 사원들의 정보는 emp_nocomm 테이블에 저장하세요.
CREATE TABLE emp_comm AS SELECT * FROM employees WHERE 1=2;
CREATE TABLE emp_nocomm AS SELECT * FROM employees WHERE 1=2;
INSERT ALL
WHEN commission_pct IS NULL THEN
INTO emp_comm
VALUES (employee_id, first_name, last_name, email,
phone_number, hire_date, job_id, salary,
commission_pct, manager_id, department_id)
WHEN commission_pct IS NOT NULL THEN
INTO emp_comm
VALUES (employee_id, first_name, last_name, email,
phone_number, hire_date, job_id, salary,
commission_pct, manager_id, department_id)
SELECT * FROM employees;