-
Notifications
You must be signed in to change notification settings - Fork 0
/
Copy pathExercise1.sql
193 lines (158 loc) · 6.07 KB
/
Exercise1.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
CREATE DATABASE BOOTCAMP_EXERCISE1;
USE BOOTCAMP_EXERCISE1;
CREATE TABLE JOBS (
JOB_ID VARCHAR(10) PRIMARY KEY,
JOB_TITLE VARCHAR(35),
MIN_SALARY DECIMAL(10,2),
MAX_SALARY DECIMAL(10,2)
);
CREATE TABLE REGIONS (
REGION_ID INTEGER PRIMARY KEY,
REGION_NAME VARCHAR(25)
);
CREATE TABLE COUNTRIES (
COUNTRY_ID CHAR(2) PRIMARY KEY,
COUNTRY_NAME VARCHAR(40),
REGION_ID INTEGER,
FOREIGN KEY (REGION_ID) REFERENCES REGIONS(REGION_ID)
);
CREATE TABLE LOCATIONS (
LOCATION_ID INTEGER PRIMARY KEY,
STREET_ADDRESS VARCHAR(25),
POSTAL_CODE VARCHAR(12),
CITY VARCHAR(30),
STATE_PROVINCE VARCHAR(12),
COUNTRY_ID CHAR(2),
FOREIGN KEY (COUNTRY_ID) REFERENCES COUNTRIES(COUNTRY_ID)
);
CREATE TABLE DEPARTMENTS (
DEPARTMENT_ID INTEGER PRIMARY KEY,
DEPARTMENT_NAME VARCHAR(30),
MANAGER_ID INTEGER,
LOCATION_ID INTEGER
);
CREATE TABLE EMPLOYEES (
EMPLOYEE_ID INTEGER PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(25),
EMAIL VARCHAR(25),
PHONE_NUMBER VARCHAR(20),
HIRE_DATE DATE,
SALARY DECIMAL(10,2),
COMMISSION_PCT DECIMAL(5,2),
MANAGER_ID INTEGER,
JOB_ID VARCHAR(10),
FOREIGN KEY (JOB_ID) REFERENCES JOBS(JOB_ID),
DEPARTMENT_ID INTEGER,
FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID)
);
CREATE TABLE JOB_HISTORY (
EMPLOYEE_ID INTEGER,
START_DATE DATE,
PRIMARY KEY (EMPLOYEE_ID, START_DATE),
END_DATE DATE,
JOB_ID VARCHAR(10),
FOREIGN KEY (JOB_ID) REFERENCES JOBS(JOB_ID),
DEPARTMENT_ID INTEGER,
FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID)
);
-- input values into JOBS
INSERT INTO JOBS VALUES ('1', 'teacher', 15000.0, 100000.0);
INSERT INTO JOBS VALUES ('2', 'programmer', 23000.0, 200000.0);
INSERT INTO JOBS VALUES ('3', 'banker', 13000.0, 11000.0);
INSERT INTO JOBS VALUES ('4', 'fireman', 8000.0, 26000.0);
INSERT INTO JOBS VALUES ('5', 'waiter', 9000.0, 20000.0);
INSERT INTO JOBS VALUES ('6', 'barista', 13000.0, 43000.0);
INSERT INTO JOBS VALUES ('7', 'designer', 20000.0, 99000.0);
INSERT INTO JOBS VALUES ('8', 'hacker', 40000.0, 110000.0);
-- input values into REGIONS
INSERT INTO REGIONS VALUES(1001, 'South East Asia');
INSERT INTO REGIONS VALUES(1002, 'North East Asia');
INSERT INTO REGIONS VALUES(1003, 'East Asia');
INSERT INTO REGIONS VALUES(1004, 'South Europe');
INSERT INTO REGIONS VALUES(1005, 'West Europe');
INSERT INTO REGIONS VALUES(1006, 'East Europe');
INSERT INTO REGIONS VALUES(1007, 'West Asia');
INSERT INTO REGIONS VALUES(1008, 'North West Asia');
INSERT INTO REGIONS VALUES(1009, 'South Asia');
-- input values into COUNTRIES
INSERT INTO COUNTRIES VALUES('HK', 'HONG KONG', 1001);
INSERT INTO COUNTRIES VALUES('CN', 'CHINA', 1002);
INSERT INTO COUNTRIES VALUES('JP', 'JAPAN', 1002);
INSERT INTO COUNTRIES VALUES('KR', 'KOREA', 1002);
INSERT INTO COUNTRIES VALUES('SG', 'SINGAPORE', 1009);
INSERT INTO COUNTRIES VALUES('IN', 'INDIA', 1007);
INSERT INTO COUNTRIES VALUES('TW', 'TAIWAN', 1001);
INSERT INTO COUNTRIES VALUES('AU', 'AUSTRALIA', 1009);
-- input values into LOCATIONS
INSERT INTO LOCATIONS VALUE(1, 'Bank Street', '00001', 'Singapore', 'None', 'SG');
INSERT INTO LOCATIONS VALUE(2, 'Baby Road', 'M2Y 3YS', 'Slam City', 'Victoria', 'AU');
INSERT INTO LOCATIONS VALUE(3, 'Tssey Road', 'U7G 6G', 'Wuhan', 'Huibei', 'CN');
INSERT INTO LOCATIONS VALUE(4, 'Merry Street', 'YSE 777', 'Hong Kong', 'None', 'HK');
-- input values into DEPARTMENTS
INSERT INTO DEPARTMENTS VALUE(1, 'Developing', 15, 1);
INSERT INTO DEPARTMENTS VALUE(2, 'Marketing', 34, 3);
INSERT INTO DEPARTMENTS VALUE(3, 'Sales', 45, 4);
INSERT INTO DEPARTMENTS VALUE(4, 'Accounting', 35, 1);
-- input values into EMPLOYEES
INSERT INTO EMPLOYEES VALUE(1,'JOHN','WICK','[email protected]','5656577','2020-01-01', 25000.0,5.0, 34,1,1);
INSERT INTO EMPLOYEES VALUE(2,'PETER','HO','[email protected]','56757575','2022-01-01', 22000.0,5.0, 45,1,1);
INSERT INTO EMPLOYEES VALUE(3,'DAVID','WAN','[email protected]','22334455','2020-07-01', 34000.0,5.0, 34,1,2);
-- input values into JOB_HISTORY
INSERT INTO JOB_HISTORY VALUE(1,'2020-07-01','2023-07-12',1,2);
INSERT INTO JOB_HISTORY VALUE(2,'2020-07-02','2023-07-15',2,1);
INSERT INTO JOB_HISTORY VALUE(3,'2020-07-03','2023-07-17',3,3);
-- Question 12
DROP TABLE JOB_HISTORY;
DROP TABLE EMPLOYEES;
DROP TABLE JOBS;
CREATE TABLE JOBS (
JOB_ID VARCHAR(10) PRIMARY KEY,
JOB_TITLE VARCHAR(35)
);
CREATE TABLE JOBS_GRADES (
JOB_ID VARCHAR(10),
MIN_SALARY DECIMAL(10,2),
MAX_SALARY DECIMAL(10,2),
FOREIGN KEY (JOB_ID) REFERENCES JOBS(JOB_ID)
);
CREATE TABLE EMPLOYEES(
EMPLOYEE_ID INTEGER PRIMARY KEY,
FIRST_NAME VARCHAR(20),
LAST_NAME VARCHAR(25),
EMAIL VARCHAR(25),
PHONE_NUMBER VARCHAR(20),
HIRE_DATE DATE,
SALARY DECIMAL(10,2),
COMMISSION_PCT DECIMAL(5,2),
MANAGER_ID INTEGER,
JOB_ID VARCHAR(10),
FOREIGN KEY (JOB_ID) REFERENCES JOBS(JOB_ID),
DEPARTMENT_ID INTEGER,
FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID)
);
CREATE TABLE JOB_HISTORY(
EMPLOYEE_ID INTEGER,
START_DATE DATE PRIMARY KEY,
END_DATE DATE,
JOB_ID VARCHAR(10),
DEPARTMENT_ID INTEGER,
FOREIGN KEY (EMPLOYEE_ID) REFERENCES EMPLOYEES(EMPLOYEE_ID),
FOREIGN KEY (JOB_ID) REFERENCES JOBS(JOB_ID),
FOREIGN KEY (DEPARTMENT_ID) REFERENCES DEPARTMENTS(DEPARTMENT_ID)
);
INSERT INTO JOBS VALUES ('1', 'teacher');
INSERT INTO JOBS VALUES ('2', 'programmer');
INSERT INTO JOBS VALUES ('3', 'banker');
INSERT INTO JOBS_GRADES VALUES ('1', 15000.0, 100000.0);
INSERT INTO JOBS_GRADES VALUES ('2', 23000.0, 200000.0);
INSERT INTO JOBS_GRADES VALUES ('3', 13000.0, 11000.0);
INSERT INTO EMPLOYEES VALUES
(1, 'John', 'Wong', '[email protected]', '57485475', '2020-01-01', 22000.00, 0.00, 15, 1, 1);
INSERT INTO EMPLOYEES VALUES
(2, 'Peter', 'Kwan', '[email protected]', '5676868', '2022-01-01', 13000.00, 1.00, 34, 2, 2);
INSERT INTO EMPLOYEES VALUES
(3, 'Sally', 'Ip', '[email protected]', '354566565', '2009-11-11', 27000.00, 2.00, 45, 3, 3);
INSERT INTO JOB_HISTORY VALUES (1, '1999-08-08', '2001-07-24', '1', 3);
INSERT INTO JOB_HISTORY VALUES (2, '2020-01-01', '2020-12-01', '2', 2);
INSERT INTO JOB_HISTORY VALUES (3, '2020-11-01', '2023-01-16', '3', 1);