PostgreSQL 집합 연산자 실습 테이블 만들기

 

실습준비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;