Database/실습문제
[Database] 추가 내용
8기_이지정
2024. 5. 27. 12:37
실습준비
# 실습 준비1
-- DDL for Table DEPARTMENT
CREATE TABLE department (
dept_id CHAR(2) PRIMARY KEY,
dept_title VARCHAR(35) NOT NULL
);
INSERT INTO department(dept_id,dept_title) VALUES ('D1','인사관리부');
INSERT INTO department(dept_id,dept_title) VALUES ('D2','회계관리부');
INSERT INTO department(dept_id,dept_title) VALUES ('D3','마케팅부');
INSERT INTO department(dept_id,dept_title) VALUES ('D4','국내영업부');
INSERT INTO department(dept_id,dept_title) VALUES ('D5','해외영업1부');
INSERT INTO department(dept_id,dept_title) VALUES ('D6','해외영업2부');
INSERT INTO department(dept_id,dept_title) VALUES ('D7','해외영업3부');
INSERT INTO department(dept_id,dept_title) VALUES ('D8','기술지원부');
INSERT INTO department(dept_id,dept_title) VALUES ('D9','총무부');
-- DDL for Table JOB
CREATE TABLE job (
job_code CHAR(2) PRIMARY KEY,
job_name VARCHAR(35) NOT NULL
);
INSERT INTO job(job_code,job_name) VALUES ('J1','대표');
INSERT INTO job(job_code,job_name) VALUES ('J2','부사장');
INSERT INTO job(job_code,job_name) VALUES ('J3','부장');
INSERT INTO job(job_code,job_name) VALUES ('J4','차장');
INSERT INTO job(job_code,job_name) VALUES ('J5','과장');
INSERT INTO job(job_code,job_name) VALUES ('J6','대리');
INSERT INTO job(job_code,job_name) VALUES ('J7','사원');
-- DDL for Table EMPLOYEE
CREATE TABLE employee (
emp_id CHAR(3) PRIMARY KEY,
emp_name VARCHAR(20) NOT NULL,
emp_no CHAR(14) NOT NULL,
email VARCHAR(25),
phone VARCHAR(12),
dept_code CHAR(2),
job_code CHAR(2),
salary INT,
bonus DOUBLE,
manager_id VARCHAR(3),
hire_date DATE,
ent_date DATE,
ent_yn CHAR(1) DEFAULT 'N'
) ;
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('200','선동일','621235-1985634','sun_di@kh.or.kr','01099546325','D9','J1',8000000,0.3,null,'90/02/06',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('201','송종기','631156-1548654','song_jk@kh.or.kr','01045686656','D9','J2',6000000,null,'200','01/09/01',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('202','노옹철','861015-1356452','no_hc@kh.or.kr','01066656263','D9','J2',3700000,null,'201','01/01/01',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('203','송은희','631010-2653546','song_eh@kh.or.kr','01077607879','D6','J4',2800000,null,'204','96/05/03',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('204','유재식','660508-1342154','yoo_js@kh.or.kr','01099999129','D6','J3',3400000,0.2,'200','00/12/29',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('205','정중하','770102-1357951','jung_jh@kh.or.kr','01036654875','D6','J3',3900000,null,'204','99/09/09',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('206','박나라','630709-2054321','pack_nr@kh.or.kr','01096935222','D5','J7',1800000,null,'207','08/04/02',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('207','하이유','690402-2040612','ha_iy@kh.or.kr','01036654488','D5','J5',2200000,0.1,'200','94/07/07',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('208','김해술','870927-1313564','kim_hs@kh.or.kr','01078634444','D5','J5',2500000,null,'207','04/04/30',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('209','심봉선','750206-1325546','sim_bs@kh.or.kr','0113654485','D5','J3',3500000,0.15,'207','11/11/11',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('210','윤은해','650505-2356985','youn_eh@kh.or.kr','0179964233','D5','J7',2000000,null,'207','01/02/03',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('211','전형돈','830807-1121321','jun_hd@kh.or.kr','01044432222','D8','J6',2000000,null,'200','12/12/12',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('212','장쯔위','780923-2234542','jang_zw@kh.or.kr','01066682224','D8','J6',2550000,0.25,'211','15/06/17',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('213','하동운','621111-1785463','ha_dh@kh.or.kr','01158456632',null,'J6',2320000,0.1,null,'99/12/31',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('214','방명수','856795-1313513','bang_ms@kh.or.kr','01074127545','D1','J7',1380000,null,'200','10/04/04',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('215','대북혼','881130-1050911','dae_bh@kh.or.kr','01088808584','D5','J5',3760000,null,null,'17/06/19',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('216','차태연','770808-1364897','cha_ty@kh.or.kr','01064643212','D1','J6',2780000,0.2,'214','13/03/01',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('217','전지연','770808-2665412','jun_jy@kh.or.kr','01033624442','D1','J6',3660000,0.3,'214','07/03/20',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('218','이오리','870427-2232123','loo_or@kh.or.kr','01022306545',null,'J7',2890000,null,null,'16/11/28',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('219','임시환','660712-1212123','im_sw@kh.or.kr',null,'D2','J4',1550000,null,null,'99/09/09',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('220','이중석','770823-1113111','lee_js@kh.or.kr',null,'D2','J4',2490000,null,null,'14/09/18',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('221','유하진','800808-1123341','yoo_hj@kh.or.kr',null,'D2','J4',2480000,null,null,'94/01/20',null,'N');
INSERT INTO employee(emp_id,emp_name,emp_no,email,phone,dept_code,job_code,salary,bonus,manager_id,hire_date,ent_date,ent_yn) VALUES ('222','이태림','760918-2854697','lee_tr@kh.or.kr','01033000002','D8','J6',2436240,0.35,'100','97/09/12','17/09/12','Y');
COMMIT;
# 실습 준비2
CREATE TABLE location(
local_code CHAR(2) PRIMARY KEY,
national_code CHAR(2),
local_name VARCHAR(40) NOT NULL
);
INSERT INTO location VALUES
('L1', 'KO', 'ASIA1'),
('L2', 'JP', 'ASIA2'),
('L3', 'CH', 'ASIA3'),
('L4', 'US', 'AMERICA'),
('L5', 'RU', 'EU');
SELECT * FROM location;
# 실습 준비3
CREATE TABLE `national`(
national_code CHAR(2) PRIMARY KEY,
national_name VARCHAR(35) NOT NULL
);
INSERT INTO national VALUES
('KO', '한국'),
('JP', '일본'),
('CH', '중국'),
('US', '미국'),
('RU', '러시아');
SELECT * FROM national;
# 실습준비4
CREATE TABLE sal_grade(
sal_level CHAR(2) PRIMARY KEY,
min_sal INT,
max_sal INT
);
INSERT INTO sal_grade VALUES
('S1', 6000000, 10000000),
('S2', 5000000, 5999999),
('S3', 4000000, 4999999),
('S4', 3000000, 3999999),
('S5', 2000000, 2999999),
('S6', 1000000, 1999999);
NON EQUAL JOIN
1. NON EQUAL JOIN (비등가 조인)
- 다른 JOIN은 같은 지를 비교하지만 NON EQUAL JOIN은 조인 조건에 등호(=)를 사용하지 않는 조인
-- NON EQUAL JOIN 실습
-- 조인 조건에 등호(=)를 사용하지 않는 조인문을 비등가 조인이라고 한다.
-- employee 테이블과 sal_grade 테이블을 비등가 조인하여 직원명, 급여, 급여 등급 조회
-- INNER JOIN이기 때문에 조인 조건을 만족하는 경우가 아니면 출력이 안됨
SELECT e.emp_name,
e.salary,
s.sal_level
FROM employee e
INNER JOIN sal_grade s ON e.salary >= s.min_sal AND e.salary <= s.max_sal
# INNER JOIN sal_grade s ON e.salary between s.min_sal AND s.mas_sal과 결과 동일
;