PostgreSQL/집합 연산자와 서브쿼리
PostgreSQL 집합 연산자 실습 테이블 만들기
DOGvelopers
2019. 11. 10. 11:18
반응형
실습준비1
SALES2007_1 테이블 만들기
CREATE TABLE SALES2007_1
(
NAME VARCHAR(50),
AMOUNT NUMERIC(15,2)
);
SALES2007_1 데이터 저장
INSERT INTO SALES2007_1
VALUES
('MIKE',15000.25),
('JON',132000.75),
('MARY',10000)
commit;
SALES2007_2 테이블 만들기
CREATE TABLE SALES2007_2
(
NAME VARCHAR(50),
AMOUNT NUMERIC(15,2)
);
SALES2007_2 데이터 저장
INSERT INTO SALES2007_2
VALUES
('MIKE',120000.25),
('JON',142000.75),
('MARY',10000)
commit;
실습준비2
EMPLOYEES_2 테이블 생성
CREATE TABLE EMPLOYEES_2(
EMPLOYEE_ID SERIAL PRIMARY KEY,
EMPLOYEE_NAME VARCHAR(255) NOT NULL
);
EMPLOYEES_2 데이터 저장
INSERT INTO EMPLOYEES_2 (EMPLOYEE_NAME)
VALUES
('Joyce Edwards'),
('Diane Collins'),
('Alice Stewart'),
('Julie Sanchez'),
('Heather Morris'),
('Teresa Rogers'),
('Doris Reed'),
('Gloria Cook'),
('Evelyn Morgan'),
('Jean Bell');
commit;
KEYS 테이블 생성
CREATE TABLE KEYS(
EMPLOYEE_ID INT PRIMARY KEY,
EFFECTIVE_DATE DATE NOT NULL,
FOREIGN KEY (EMPLOYEE_ID)
REFERENCES EMPLOYEES_2 (EMPLOYEE_ID)
);
KEYS 데이터 저장
INSERT INTO KEYS
VALUES
(1,'2000-02-01'),
(2,'2001-06-01'),
(5,'2002-01-01'),
(7,'2005-06-01');
commit;
HIPOS 테이블 생성
CREATE TABLE HIPOS(
EMPLOYEE_ID INT PRIMARY KEY,
EFFECTIVE_DATE DATE NOT NULL,
FOREIGN KEY (EMPLOYEE_ID)
REFERENCES EMPLOYEES_2 (EMPLOYEE_ID)
);
HIPOS 데이터 저장
INSERT INTO HIPOS
VALUES
(9,'2000-01-01'),
(2,'2002-06-01'),
(5,'2006-06-01'),
(10,'2005-06-01');
commit;
반응형