· 6 years ago · Apr 07, 2019, 02:50 PM
1DROP DATABASE IF EXISTS hr;
2CREATE DATABASE hr;
3USE hr;
4
5CREATE TABLE REGIONS
6(
7 REGION_ID NUMERIC(1),
8 REGION_NAME VARCHAR(25),
9 CONSTRAINT REG_ID_PK
10 PRIMARY KEY (REGION_ID)
11);
12
13CREATE TABLE COUNTRIES
14(
15 COUNTRY_ID CHAR(2),
16 COUNTRY_NAME VARCHAR(40),
17 REGION_ID NUMERIC(2),
18 CONSTRAINT COUNTR_REG_FK
19 FOREIGN KEY (REGION_ID)
20 REFERENCES REGIONS (REGION_ID),
21 CONSTRAINT COUNTRY_C_ID_PK
22 PRIMARY KEY (COUNTRY_ID)
23);
24
25CREATE TABLE LOCATIONS
26(
27 LOCATION_ID NUMERIC(4,0),
28 STREET_ADDRESS VARCHAR(40),
29 POSTAL_CODE VARCHAR(12),
30 CITY VARCHAR(30) NOT NULL,
31 STATE_PROVINCE VARCHAR(25),
32 COUNTRY_ID CHAR(2),
33 CONSTRAINT LOC_C_ID_FK
34 FOREIGN KEY (COUNTRY_ID)
35 REFERENCES COUNTRIES (COUNTRY_ID),
36 CONSTRAINT LOC_ID_PK
37 PRIMARY KEY (LOCATION_ID)
38);
39
40CREATE TABLE JOBS
41(
42 JOB_ID VARCHAR(10),
43 JOB_TITLE VARCHAR(35) NOT NULL,
44 MIN_SALARY NUMERIC(6,0),
45 MAX_SALARY NUMERIC(6,0),
46 CONSTRAINT JOB_ID_PK
47 PRIMARY KEY (JOB_ID)
48);
49
50CREATE TABLE DEPARTMENTS
51(
52 DEPARTMENT_ID NUMERIC(4,0),
53 DEPARTMENT_NAME VARCHAR(30) NOT NULL,
54 MANAGER_ID NUMERIC(6,0),
55 LOCATION_ID NUMERIC(4,0),
56 CONSTRAINT DEPT_ID_PK
57 PRIMARY KEY (DEPARTMENT_ID),
58 CONSTRAINT DEPT_LOC_FK
59 FOREIGN KEY (LOCATION_ID)
60 REFERENCES LOCATIONS (LOCATION_ID)
61);
62
63CREATE TABLE EMPLOYEES
64(
65 EMPLOYEE_ID NUMERIC(6,0),
66 FIRST_NAME VARCHAR(20),
67 LAST_NAME VARCHAR(25) NOT NULL,
68 EMAIL VARCHAR(25) NOT NULL,
69 PHONE_NUMBER VARCHAR(20),
70 HIRE_DATE DATE NOT NULL,
71 JOB_ID VARCHAR(10) NOT NULL,
72 SALARY NUMERIC(8,2),
73 COMMISSION_PCT NUMERIC(2,2),
74 MANAGER_ID NUMERIC(6,0),
75 DEPARTMENT_ID NUMERIC(4,0),
76 CONSTRAINT EMP_EMP_ID_PK
77 PRIMARY KEY (EMPLOYEE_ID),
78 CONSTRAINT EMP_DEPT_FK
79 FOREIGN KEY (DEPARTMENT_ID)
80 REFERENCES DEPARTMENTS (DEPARTMENT_ID),
81 CONSTRAINT EMP_JOB_FK
82 FOREIGN KEY (JOB_ID)
83 REFERENCES JOBS (JOB_ID)
84);
85
86ALTER TABLE DEPARTMENTS
87ADD (
88 CONSTRAINT EMP_MANAGER_FK
89 FOREIGN KEY (MANAGER_ID)
90 REFERENCES EMPLOYEES (EMPLOYEE_ID)
91 );
92
93ALTER TABLE EMPLOYEES
94ADD (
95 CONSTRAINT EMP_MANAGER2_FK
96 FOREIGN KEY (MANAGER_ID)
97 REFERENCES EMPLOYEES (EMPLOYEE_ID)
98 );
99
100
101CREATE TABLE JOB_HISTORY
102(
103 EMPLOYEE_ID NUMERIC(6,0),
104 START_DATE DATE,
105 END_DATE DATE NOT NULL,
106 JOB_ID VARCHAR(10) NOT NULL,
107 DEPARTMENT_ID NUMERIC(4,0),
108 CONSTRAINT JHIST_DEPT_FK
109 FOREIGN KEY (DEPARTMENT_ID)
110 REFERENCES DEPARTMENTS (DEPARTMENT_ID),
111 CONSTRAINT JHIST_EMP_FK
112 FOREIGN KEY (EMPLOYEE_ID)
113 REFERENCES EMPLOYEES (EMPLOYEE_ID),
114 CONSTRAINT JHIST_EMP_ID_ST_DATE_PK
115 PRIMARY KEY (EMPLOYEE_ID,START_DATE),
116 CONSTRAINT JHIST_JOB_FK
117 FOREIGN KEY (JOB_ID)
118 REFERENCES JOBS (JOB_ID)
119);
120
121
122/* Data for the 'REGIONS' table (Records 1 - 4) */
123
124INSERT INTO REGIONS
125 (REGION_ID, REGION_NAME)
126VALUES
127 (1, 'Europe'),
128 (2, 'Americas'),
129 (3, 'Asia'),
130 (4, 'Middle East and Africa');
131
132/* Data for the 'COUNTRIES' table (Records 1 - 25) */
133
134INSERT INTO COUNTRIES
135 (COUNTRY_ID, COUNTRY_NAME, REGION_ID)
136VALUES
137 ('AR', 'Argentina', 2),
138 ('AU', 'Australia', 3),
139 ('BE', 'Belgium', 1),
140 ('BR', 'Brazil', 2),
141 ('CA', 'Canada', 2),
142 ('CH', 'Switzerland', 1),
143 ('CN', 'China', 3),
144 ('DE', 'Germany', 1),
145 ('DK', 'Denmark', 1),
146 ('EG', 'Egypt', 4),
147 ('FR', 'France', 1),
148 ('IL', 'Israel', 4),
149 ('IN', 'India', 3),
150 ('IT', 'Italy', 1),
151 ('JP', 'Japan', 3),
152 ('KW', 'Kuwait', 4),
153 ('ML', 'Malaysia', 3),
154 ('MX', 'Mexico', 2),
155 ('NG', 'Nigeria', 4),
156 ('NL', 'Netherlands', 1),
157 ('SG', 'Singapore', 3),
158 ('UK', 'United Kingdom', 1),
159 ('US', 'United States of America', 2),
160 ('ZM', 'Zambia', 4),
161 ('ZW', 'Zimbabwe', 4);
162
163/* Data for the 'LOCATIONS' table (Records 1 - 23) */
164
165INSERT INTO LOCATIONS
166 (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY, STATE_PROVINCE, COUNTRY_ID)
167VALUES
168 (1000, '1297 Via Cola di Rie', '00989', 'Roma', NULL, 'IT'),
169 (1100, '93091 Calle della Testa', '10934', 'Venice', NULL, 'IT'),
170 (1200, '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP'),
171 (1300, '9450 Kamiya-cho', '6823', 'Hiroshima', NULL, 'JP'),
172 (1400, '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US'),
173 (1500, '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US'),
174 (1600, '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US'),
175 (1700, '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US'),
176 (1800, '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA'),
177 (1900, '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA'),
178 (2000, '40-5-12 Laogianggen', '190518', 'Beijing', NULL, 'CN'),
179 (2100, '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN'),
180 (2200, '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU'),
181 (2300, '198 Clementi North', '540198', 'Singapore', NULL, 'SG'),
182 (2400, '8204 Arthur St', NULL, 'London', NULL, 'UK'),
183 (2500, 'Magdalen Centre, The Oxford Science Park', 'OX9 9ZB', 'Oxford', 'Oxford', 'UK'),
184 (2600, '9702 Chester Road', '09629850293', 'Stretford', 'Manchester', 'UK'),
185 (2700, 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE'),
186 (2800, 'Rua Frei Caneca 1360', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR'),
187 (2900, '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH'),
188 (3000, 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH'),
189 (3100, 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL'),
190 (3200, 'Mariano Escobedo 9991', '11932', 'Mexico City', 'Distrito Federal,', 'MX');
191
192/* Data for the 'JOBS' table (Records 1 - 19) */
193
194INSERT INTO JOBS
195 (JOB_ID, JOB_TITLE, MIN_SALARY, MAX_SALARY)
196VALUES
197 ('AD_PRES', 'President', 20080, 40000),
198 ('AD_VP', 'Administration Vice President', 15000, 30000),
199 ('AD_ASST', 'Administration Assistant', 3000, 6000),
200 ('FI_MGR', 'Finance Manager', 8200, 16000),
201 ('FI_ACCOUNT', 'Accountant', 4200, 9000),
202 ('AC_MGR', 'Accounting Manager', 8200, 16000),
203 ('AC_ACCOUNT', 'Public Accountant', 4200, 9000),
204 ('SA_MAN', 'Sales Manager', 10000, 20080),
205 ('SA_REP', 'Sales Representative', 6000, 12008),
206 ('PU_MAN', 'Purchasing Manager', 8000, 15000),
207 ('PU_CLERK', 'Purchasing Clerk', 2500, 5500),
208 ('ST_MAN', 'Stock Manager', 5500, 8500),
209 ('ST_CLERK', 'Stock Clerk', 2008, 5000),
210 ('SH_CLERK', 'Shipping Clerk', 2500, 5500),
211 ('IT_PROG', 'Programmer', 4000, 10000),
212 ('MK_MAN', 'Marketing Manager', 9000, 15000),
213 ('MK_REP', 'Marketing Representative', 4000, 9000),
214 ('HR_REP', 'Human Resources Representative', 4000, 9000),
215 ('PR_REP', 'Public Relations Representative', 4500, 10500);
216
217
218
219/* Data for the 'EMPLOYEES' table (Records 1 - 107) */
220
221INSERT INTO EMPLOYEES
222 (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID, DEPARTMENT_ID)
223VALUES
224 (100, 'Steven', 'King', 'SKING', '515.123.4567', '2003-06-17', 'AD_PRES', 24000, NULL, NULL, NULL),
225 (101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '2005-09-21', 'AD_VP', 17000, NULL, 100, NULL),
226 (102, 'Lex', 'De Haan', 'LDEHAAN', '515.123.4569', '2001-01-13', 'AD_VP', 17000, NULL, 100, NULL),
227 (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', '2006-01-03', 'IT_PROG', 9000, NULL, 102, NULL),
228 (104, 'Bruce', 'Ernst', 'BERNST', '590.423.4568', '2007-05-21', 'IT_PROG', 6000, NULL, 103, NULL),
229 (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', '2005-06-25', 'IT_PROG', 4800, NULL, 103, NULL),
230 (106, 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560', '2006-02-05', 'IT_PROG', 4800, NULL, 103, NULL),
231 (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', '2007-02-07', 'IT_PROG', 4200, NULL, 103, NULL),
232 (108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569', '2002-08-17', 'FI_MGR', 12008, NULL, 101, NULL),
233 (109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', '2002-08-16', 'FI_ACCOUNT', 9000, NULL, 108, NULL),
234 (110, 'John', 'Chen', 'JCHEN', '515.124.4269', '2005-09-28', 'FI_ACCOUNT', 8200, NULL, 108, NULL),
235 (111, 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', '2005-09-30', 'FI_ACCOUNT', 7700, NULL, 108, NULL),
236 (112, 'Jose Manuel', 'Urman', 'JMURMAN', '515.124.4469', '2006-03-07', 'FI_ACCOUNT', 7800, NULL, 108, NULL),
237 (113, 'Luis', 'Popp', 'LPOPP', '515.124.4567', '2007-12-07', 'FI_ACCOUNT', 6900, NULL, 108, NULL),
238 (114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', '2002-12-07', 'PU_MAN', 11000, NULL, 100, NULL),
239 (115, 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', '2003-05-18', 'PU_CLERK', 3100, NULL, 114, NULL),
240 (116, 'Shelli', 'Baida', 'SBAIDA', '515.127.4563', '2005-12-24', 'PU_CLERK', 2900, NULL, 114, NULL),
241 (117, 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', '2005-07-24', 'PU_CLERK', 2800, NULL, 114, NULL),
242 (118, 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', '2006-11-15', 'PU_CLERK', 2600, NULL, 114, NULL),
243 (119, 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', '2007-08-10', 'PU_CLERK', 2500, NULL, 114, NULL),
244 (120, 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', '2004-07-18', 'ST_MAN', 8000, NULL, 100, NULL),
245 (121, 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', '2005-04-10', 'ST_MAN', 8200, NULL, 100, NULL),
246 (122, 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', '2003-05-01', 'ST_MAN', 7900, NULL, 100, NULL),
247 (123, 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', '2005-10-10', 'ST_MAN', 6500, NULL, 100, NULL),
248 (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', '2007-11-16', 'ST_MAN', 5800, NULL, 100, NULL),
249 (125, 'Julia', 'Nayer', 'JNAYER', '650.124.1214', '2005-07-16', 'ST_CLERK', 3200, NULL, 120, NULL),
250 (126, 'Irene', 'Mikkilineni', 'IMIKKILI', '650.124.1224', '2006-09-28', 'ST_CLERK', 2700, NULL, 120, NULL),
251 (127, 'James', 'Landry', 'JLANDRY', '650.124.1334', '2007-01-14', 'ST_CLERK', 2400, NULL, 120, NULL),
252 (128, 'Steven', 'Markle', 'SMARKLE', '650.124.1434', '2008-03-08', 'ST_CLERK', 2200, NULL, 120, NULL),
253 (129, 'Laura', 'Bissot', 'LBISSOT', '650.124.5234', '2005-08-20', 'ST_CLERK', 3300, NULL, 121, NULL),
254 (130, 'Mozhe', 'Atkinson', 'MATKINSO', '650.124.6234', '2005-10-30', 'ST_CLERK', 2800, NULL, 121, NULL),
255 (131, 'James', 'Marlow', 'JAMRLOW', '650.124.7234', '2005-02-16', 'ST_CLERK', 2500, NULL, 121, NULL),
256 (132, 'TJ', 'Olson', 'TJOLSON', '650.124.8234', '2007-04-10', 'ST_CLERK', 2100, NULL, 121, NULL),
257 (133, 'Jason', 'Mallin', 'JMALLIN', '650.127.1934', '2004-06-14', 'ST_CLERK', 3300, NULL, 122, NULL),
258 (134, 'Michael', 'Rogers', 'MROGERS', '650.127.1834', '2006-08-26', 'ST_CLERK', 2900, NULL, 122, NULL),
259 (135, 'Ki', 'Gee', 'KGEE', '650.127.1734', '2007-12-12', 'ST_CLERK', 2400, NULL, 122, NULL),
260 (136, 'Hazel', 'Philtanker', 'HPHILTAN', '650.127.1634', '2008-02-06', 'ST_CLERK', 2200, NULL, 122, NULL),
261 (137, 'Renske', 'Ladwig', 'RLADWIG', '650.121.1234', '2003-07-14', 'ST_CLERK', 3600, NULL, 123, NULL),
262 (138, 'Stephen', 'Stiles', 'SSTILES', '650.121.2034', '2005-10-26', 'ST_CLERK', 3200, NULL, 123, NULL),
263 (139, 'John', 'Seo', 'JSEO', '650.121.2019', '2006-02-12', 'ST_CLERK', 2700, NULL, 123, NULL),
264 (140, 'Joshua', 'Patel', 'JPATEL', '650.121.1834', '2006-04-06', 'ST_CLERK', 2500, NULL, 123, NULL),
265 (141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', '2003-10-17', 'ST_CLERK', 3500, NULL, 124, NULL),
266 (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', '2005-01-29', 'ST_CLERK', 3100, NULL, 124, NULL),
267 (143, 'Randall', 'Matos', 'RMATOS', '650.121.2874', '2006-03-15', 'ST_CLERK', 2600, NULL, 124, NULL),
268 (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', '2006-07-09', 'ST_CLERK', 2500, NULL, 124, NULL),
269 (145, 'John', 'Russell', 'JRUSSEL', '011.44.1344.429268', '1994-10-01', 'SA_MAN', 14000, 0.4, 100, NULL),
270 (146, 'Karen', 'Partners', 'KPARTNER', '011.44.1344.467268', '1994-01-05', 'SA_MAN', 13500, 0.3, 100, NULL),
271 (147, 'Alberto', 'Errazuriz', 'AERRAZUR', '011.44.1344.429278', '1994-03-10', 'SA_MAN', 12000, 0.3, 100, NULL),
272 (148, 'Gerald', 'Cambrault', 'GCAMBRAU', '011.44.1344.619268', '1994-10-15', 'SA_MAN', 11000, 0.3, 100, NULL),
273 (149, 'Eleni', 'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', '1994-01-29', 'SA_MAN', 10500, 0.2, 100, NULL),
274 (150, 'Peter', 'Tucker', 'PTUCKER', '011.44.1344.129268', '2005-01-30', 'SA_REP', 10000, 0.3, 145, NULL),
275 (151, 'David', 'Bernstein', 'DBERNSTE', '011.44.1344.345268', '2005-03-24', 'SA_REP', 9500, 0.25, 145, NULL),
276 (152, 'Peter', 'Hall', 'PHALL', '011.44.1344.478968', '2005-08-20', 'SA_REP', 9000, 0.25, 145, NULL),
277 (153, 'Christopher', 'Olsen', 'COLSEN', '011.44.1344.498718', '2006-03-30', 'SA_REP', 8000, 0.2, 145, NULL),
278 (154, 'Nanette', 'Cambrault', 'NCAMBRAU', '011.44.1344.987668', '2006-12-09', 'SA_REP', 7500, 0.2, 145, NULL),
279 (155, 'Oliver', 'Tuvault', 'OTUVAULT', '011.44.1344.486508', '2007-11-23', 'SA_REP', 7000, 0.15, 145, NULL),
280 (156, 'Janette', 'King', 'JKING', '011.44.1345.429268', '2004-01-30', 'SA_REP', 10000, 0.35, 146, NULL),
281 (157, 'Patrick', 'Sully', 'PSULLY', '011.44.1345.929268', '2004-03-04', 'SA_REP', 9500, 0.35, 146, NULL),
282 (158, 'Allan', 'McEwen', 'AMCEWEN', '011.44.1345.829268', '2004-08-01', 'SA_REP', 9000, 0.35, 146, NULL),
283 (159, 'Lindsey', 'Smith', 'LSMITH', '011.44.1345.729268', '2005-03-10', 'SA_REP', 8000, 0.3, 146, NULL),
284 (160, 'Louise', 'Doran', 'LDORAN', '011.44.1345.629268', '2005-12-15', 'SA_REP', 7500, 0.3, 146, NULL),
285 (161, 'Sarath', 'Sewall', 'SSEWALL', '011.44.1345.529268', '2006-11-03', 'SA_REP', 7000, 0.25, 146, NULL),
286 (162, 'Clara', 'Vishney', 'CVISHNEY', '011.44.1346.129268', '2005-11-11', 'SA_REP', 10500, 0.25, 147, NULL),
287 (163, 'Danielle', 'Greene', 'DGREENE', '011.44.1346.229268', '2007-03-19', 'SA_REP', 9500, 0.15, 147, NULL),
288 (164, 'Mattea', 'Marvins', 'MMARVINS', '011.44.1346.329268', '2008-01-24', 'SA_REP', 7200, 0.1, 147, NULL),
289 (165, 'David', 'Lee', 'DLEE', '011.44.1346.529268', '2008-02-23', 'SA_REP', 6800, 0.1, 147, NULL),
290 (166, 'Sundar', 'Ande', 'SANDE', '011.44.1346.629268', '2008-03-24', 'SA_REP', 6400, 0.1, 147, NULL),
291 (167, 'Amit', 'Banda', 'ABANDA', '011.44.1346.729268', '2008-04-21', 'SA_REP', 6200, 0.1, 147, NULL),
292 (168, 'Lisa', 'Ozer', 'LOZER', '011.44.1343.929268', '2005-03-11', 'SA_REP', 11500, 0.25, 148, NULL),
293 (169, 'Harrison', 'Bloom', 'HBLOOM', '011.44.1343.829268', '2006-03-23', 'SA_REP', 10000, 0.2, 148, NULL),
294 (170, 'Tayler', 'Fox', 'TFOX', '011.44.1343.729268', '2006-01-24', 'SA_REP', 9600, 0.2, 148, NULL),
295 (171, 'William', 'Smith', 'WSMITH', '011.44.1343.629268', '2007-02-23', 'SA_REP', 7400, 0.15, 148, NULL),
296 (172, 'Elizabeth', 'Bates', 'EBATES', '011.44.1343.529268', '2007-03-24', 'SA_REP', 7300, 0.15, 148, NULL),
297 (173, 'Sundita', 'Kumar', 'SKUMAR', '011.44.1343.329268', '2008-04-21', 'SA_REP', 6100, 0.1, 148, NULL),
298 (174, 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', '2004-05-11', 'SA_REP', 11000, 0.3, 149, NULL),
299 (175, 'Alyssa', 'Hutton', 'AHUTTON', '011.44.1644.429266', '2005-03-19', 'SA_REP', 8800, 0.25, 149, NULL),
300 (176, 'Jonathon', 'Taylor', 'JTAYLOR', '011.44.1644.429265', '2006-03-24', 'SA_REP', 8600, 0.2, 149, NULL),
301 (177, 'Jack', 'Livingston', 'JLIVINGS', '011.44.1644.429264', '2006-04-23', 'SA_REP', 8400, 0.2, 149, NULL),
302 (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263', '2007-05-24', 'SA_REP', 7000, 0.15, 149, NULL),
303 (179, 'Charles', 'Johnson', 'CJOHNSON', '011.44.1644.429262', '2008-01-04', 'SA_REP', 6200, 0.1, 149, NULL),
304 (180, 'Winston', 'Taylor', 'WTAYLOR', '650.507.9876', '2006-01-24', 'SH_CLERK', 3200, NULL, 120, NULL),
305 (181, 'Jean', 'Fleaur', 'JFLEAUR', '650.507.9877', '2006-02-23', 'SH_CLERK', 3100, NULL, 120, NULL),
306 (182, 'Martha', 'Sullivan', 'MSULLIVA', '650.507.9878', '2007-06-21', 'SH_CLERK', 2500, NULL, 120, NULL),
307 (183, 'Girard', 'Geoni', 'GGEONI', '650.507.9879', '2008-02-03', 'SH_CLERK', 2800, NULL, 120, NULL),
308 (184, 'Nandita', 'Sarchand', 'NSARCHAN', '650.509.1876', '2004-01-27', 'SH_CLERK', 4200, NULL, 121, NULL),
309 (185, 'Alexis', 'Bull', 'ABULL', '650.509.2876', '2005-02-20', 'SH_CLERK', 4100, NULL, 121, NULL),
310 (186, 'Julia', 'Dellinger', 'JDELLING', '650.509.3876', '2006-06-24', 'SH_CLERK', 3400, NULL, 121, NULL),
311 (187, 'Anthony', 'Cabrio', 'ACABRIO', '650.509.4876', '2007-02-07', 'SH_CLERK', 3000, NULL, 121, NULL),
312 (188, 'Kelly', 'Chung', 'KCHUNG', '650.505.1876', '2005-06-14', 'SH_CLERK', 3800, NULL, 122, NULL),
313 (189, 'Jennifer', 'Dilly', 'JDILLY', '650.505.2876', '2005-08-13', 'SH_CLERK', 3600, NULL, 122, NULL),
314 (190, 'Timothy', 'Gates', 'TGATES', '650.505.3876', '2006-07-11', 'SH_CLERK', 2900, NULL, 122, NULL),
315 (191, 'Randall', 'Perkins', 'RPERKINS', '650.505.4876', '2007-12-19', 'SH_CLERK', 2500, NULL, 122, NULL),
316 (192, 'Sarah', 'Bell', 'SBELL', '650.501.1876', '2004-02-04', 'SH_CLERK', 4000, NULL, 123, NULL),
317 (193, 'Britney', 'Everett', 'BEVERETT', '650.501.2876', '2005-03-03', 'SH_CLERK', 3900, NULL, 123, NULL),
318 (194, 'Samuel', 'McCain', 'SMCCAIN', '650.501.3876', '2006-07-01', 'SH_CLERK', 3200, NULL, 123, NULL),
319 (195, 'Vance', 'Jones', 'VJONES', '650.501.4876', '2007-03-17', 'SH_CLERK', 2800, NULL, 123, NULL),
320 (196, 'Alana', 'Walsh', 'AWALSH', '650.507.9811', '2006-04-24', 'SH_CLERK', 3100, NULL, 124, NULL),
321 (197, 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822', '2006-05-23', 'SH_CLERK', 3000, NULL, 124, NULL),
322 (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', '2007-06-21', 'SH_CLERK', 2600, NULL, 124, NULL),
323 (199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', '2008-01-13', 'SH_CLERK', 2600, NULL, 124, NULL),
324 (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', '2003-09-17', 'AD_ASST', 4400, NULL, 101, NULL),
325 (201, 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', '2004-02-17', 'MK_MAN', 13000, NULL, 100, NULL),
326 (202, 'Pat', 'Fay', 'PFAY', '603.123.6666', '2005-08-17', 'MK_REP', 6000, NULL, 201, NULL),
327 (203, 'Susan', 'Mavris', 'SMAVRIS', '515.123.7777', '2002-06-07', 'HR_REP', 6500, NULL, 101, NULL),
328 (204, 'Hermann', 'Baer', 'HBAER', '515.123.8888', '2002-06-07', 'PR_REP', 10000, NULL, 101, NULL),
329 (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', '2002-06-07', 'AC_MGR', 12008, NULL, 101, NULL),
330 (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', '2002-06-07', 'AC_ACCOUNT', 8300, NULL, 205, NULL),
331 (207, 'Kurt', 'Cobain', 'KCOBAIN', '989.123.112.5435', '1998-02-20', 'FI_MGR', 11000, NULL, 101, NULL),
332 (208, 'Roger', 'Waters', 'RWATERS', '533.578.225.2841', '1998-03-03', 'ST_MAN', 7300, NULL, 100, NULL),
333 (209, 'Mick', 'Jagger', 'MJAGGER', '745.584.736.1524', '1998-04-24', 'PR_REP', 10000, NULL, 101, NULL),
334 (210, 'Robert', 'Plant', 'RPLANT', '546.918.643.9149', '1998-05-01', 'AD_ASST', 6000, NULL, 101, NULL),
335 (211, 'Ozzy', 'Osbourne', 'OOSBOURNE', '537.629.826.5913', '1998-03-07', 'MK_REP', 9000, NULL, 201, NULL),
336 (212, 'Freddie', 'Mercury', 'FMERCURY', '791.197.962.9419', '1998-04-30', 'HR_REP', 8000, NULL, 101, NULL),
337 (213, 'Steven', 'Tyler', 'STYLER', '541.398.572.6926', '1998-04-22', 'IT_PROG', 10000, NULL, 103, NULL),
338 (214, 'Jimi', 'Hendrix', 'JHENDRIX', '626.729.649.7295', '1998-03-11', 'PU_CLERK', 5500, NULL, 114, NULL),
339 (215, 'Axl', 'Rose', 'AROSE', '376.962.616.3952', '1998-02-20', 'SH_CLERK', 5500, NULL, 124, NULL),
340 (216, 'Anthony', 'Kiedis', 'AKIEDIS', '897.782.825.1635', '1998-03-01', 'FI_ACCOUNT', 9000, NULL, 108, NULL),
341 (217, 'Elvis', 'Presley', 'EPRESLEY', '156.915.193.6591', '1994-12-21', 'PR_REP', 9500, NULL, 101, NULL),
342 (218, 'John', 'Lennon', 'JLENNON', '555.146.164.6951', '1994-05-22', 'SH_CLERK', 5000, NULL, 124, NULL),
343 (219, 'Jim', 'Morrison', 'JMORRISON', '681.375.165.9619', '1994-11-08', 'ST_CLERK', 4500, NULL, 124, NULL),
344 (220, 'Raul', 'Seixas', 'RSEIXAS', '624.157.017.2894', '1994-08-07', 'IT_PROG', 9000, NULL, 103, NULL),
345 (221, 'Marilyn', 'Manson', 'MMANSON', '698.272.941.7940', '1994-03-01', 'SA_REP', 11000, 0.3, 145, NULL),
346 (222, 'Nick', 'Cave', 'NCAVE', '601.377.195.5174', '1994-09-24', 'SA_REP', 10000, 0.4, 149, NULL),
347 (223, 'Wendy', 'Willians', 'WWILLIANS', '091.571.964.2919', '1994-10-10', 'SA_REP', 8000, 0.15, 145, NULL),
348 (224, 'James', 'Hetfield', 'JHETFIELD', '901.375.986.5911', '1994-07-03', 'SA_REP', 7500, 0.25, 145, NULL);
349
350
351/* Data for the 'DEPARTMENTS' table (Records 1 - 27) */
352
353INSERT INTO DEPARTMENTS
354 (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID, LOCATION_ID)
355VALUES
356 (10, 'Administration', 200, 1700),
357 (20, 'Marketing', 201, 1800),
358 (30, 'Purchasing', 114, 1700),
359 (40, 'Human Resources', 203, 2400),
360 (50, 'Shipping', 121, 1500),
361 (60, 'IT', 103, 1400),
362 (70, 'Public Relations', 204, 2700),
363 (80, 'Sales', 145, 2500),
364 (90, 'Executive', 100, 1700),
365 (100, 'Finance', 108, 1700),
366 (110, 'Accounting', 205, 1700),
367 (120, 'Treasury', NULL, 1700),
368 (130, 'Corporate Tax', NULL, 1700),
369 (140, 'Control And Credit', NULL, 1700),
370 (150, 'Shareholder Services', NULL, 1700),
371 (160, 'Benefits', NULL, 1700),
372 (170, 'Manufacturing', NULL, 1700),
373 (180, 'Construction', NULL, 1700),
374 (190, 'Contracting', NULL, 1700),
375 (200, 'Operations', NULL, 1700),
376 (210, 'IT Support', NULL, 1700),
377 (220, 'NOC', NULL, 1700),
378 (230, 'IT Helpdesk', NULL, 1700),
379 (240, 'Government Sales', NULL, 1700),
380 (250, 'Retail Sales', NULL, 1700),
381 (260, 'Recruiting', NULL, 1700),
382 (270, 'Payroll', NULL, 1700);
383
384
385
386/* Data for the 'JOB_HISTORY' table (Records 1 - 10) */
387
388INSERT INTO JOB_HISTORY
389 (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID, DEPARTMENT_ID)
390VALUES
391 (102, '2001-01-13', '2006-07-24', 'IT_PROG', 60),
392 (101, '1997-09-21', '2001-10-27', 'AC_ACCOUNT', 110),
393 (101, '2001-10-28', '2005-03-15', 'AC_MGR', 110),
394 (201, '2004-02-17', '2007-12-19', 'MK_REP', 20),
395 (114, '2006-03-24', '2007-12-31', 'ST_CLERK', 50),
396 (122, '2007-01-01', '2007-12-31', 'ST_CLERK', 50),
397 (200, '1995-09-17', '2001-06-17', 'AD_ASST', 90),
398 (176, '2006-03-24', '2006-12-31', 'SA_REP', 80),
399 (176, '2007-01-01', '2007-12-31', 'SA_MAN', 80),
400 (200, '2002-07-01', '2006-12-31', 'AC_ACCOUNT', 90);
401
402/* Atualizações dos dados dos empregados */
403
404UPDATE EMPLOYEES
405SET DEPARTMENT_ID = '90'
406WHERE EMPLOYEE_ID = '100' OR EMPLOYEE_ID = '101' OR EMPLOYEE_ID = '102';
407
408UPDATE EMPLOYEES
409SET DEPARTMENT_ID = '60'
410WHERE EMPLOYEE_ID = '103' OR EMPLOYEE_ID = '104' OR EMPLOYEE_ID = '105' OR EMPLOYEE_ID = '106' OR EMPLOYEE_ID = '107';
411
412UPDATE EMPLOYEES
413SET DEPARTMENT_ID = '100'
414WHERE EMPLOYEE_ID = '108' OR EMPLOYEE_ID = '109' OR EMPLOYEE_ID = '110' OR EMPLOYEE_ID = '111' OR EMPLOYEE_ID = '112' OR EMPLOYEE_ID = '113';
415
416UPDATE EMPLOYEES
417SET DEPARTMENT_ID = '30'
418WHERE EMPLOYEE_ID = '114' OR EMPLOYEE_ID = '115' OR EMPLOYEE_ID = '116' OR EMPLOYEE_ID = '117' OR EMPLOYEE_ID = '118' OR EMPLOYEE_ID = '119';
419
420UPDATE EMPLOYEES
421SET DEPARTMENT_ID = '50'
422WHERE EMPLOYEE_ID = '120' OR EMPLOYEE_ID = '121' OR EMPLOYEE_ID = '122' OR EMPLOYEE_ID = '123' OR EMPLOYEE_ID = '124' OR EMPLOYEE_ID = '125'
423OR EMPLOYEE_ID = '126' OR EMPLOYEE_ID = '127' OR EMPLOYEE_ID = '128' OR EMPLOYEE_ID = '129' OR EMPLOYEE_ID = '130'
424OR EMPLOYEE_ID = '131' OR EMPLOYEE_ID = '132' OR EMPLOYEE_ID = '133' OR EMPLOYEE_ID = '134' OR EMPLOYEE_ID = '135'
425OR EMPLOYEE_ID = '136' OR EMPLOYEE_ID = '137' OR EMPLOYEE_ID = '138' OR EMPLOYEE_ID = '139' OR EMPLOYEE_ID = '140'
426OR EMPLOYEE_ID = '141' OR EMPLOYEE_ID = '142' OR EMPLOYEE_ID = '143' OR EMPLOYEE_ID = '144'
427OR EMPLOYEE_ID = '180' OR EMPLOYEE_ID = '181' OR EMPLOYEE_ID = '182' OR EMPLOYEE_ID = '183'
428OR EMPLOYEE_ID = '184' OR EMPLOYEE_ID = '185' OR EMPLOYEE_ID = '186' OR EMPLOYEE_ID = '187'
429OR EMPLOYEE_ID = '188' OR EMPLOYEE_ID = '189' OR EMPLOYEE_ID = '190' OR EMPLOYEE_ID = '191'
430OR EMPLOYEE_ID = '192' OR EMPLOYEE_ID = '193' OR EMPLOYEE_ID = '194' OR EMPLOYEE_ID = '195'
431OR EMPLOYEE_ID = '196' OR EMPLOYEE_ID = '197' OR EMPLOYEE_ID = '198' OR EMPLOYEE_ID = '199';
432
433UPDATE EMPLOYEES
434SET DEPARTMENT_ID = '80'
435WHERE EMPLOYEE_ID = '145' OR EMPLOYEE_ID = '146' OR EMPLOYEE_ID = '147' OR EMPLOYEE_ID = '148' OR EMPLOYEE_ID = '149' OR EMPLOYEE_ID = '150'
436OR EMPLOYEE_ID = '151' OR EMPLOYEE_ID = '152' OR EMPLOYEE_ID = '153' OR EMPLOYEE_ID = '154' OR EMPLOYEE_ID = '155'
437OR EMPLOYEE_ID = '156' OR EMPLOYEE_ID = '157' OR EMPLOYEE_ID = '158' OR EMPLOYEE_ID = '159' OR EMPLOYEE_ID = '160'
438OR EMPLOYEE_ID = '161' OR EMPLOYEE_ID = '162' OR EMPLOYEE_ID = '163' OR EMPLOYEE_ID = '164' OR EMPLOYEE_ID = '165'
439OR EMPLOYEE_ID = '166' OR EMPLOYEE_ID = '167' OR EMPLOYEE_ID = '168' OR EMPLOYEE_ID = '169'
440OR EMPLOYEE_ID = '170' OR EMPLOYEE_ID = '171' OR EMPLOYEE_ID = '172' OR EMPLOYEE_ID = '173'
441OR EMPLOYEE_ID = '174' OR EMPLOYEE_ID = '175' OR EMPLOYEE_ID = '176' OR EMPLOYEE_ID = '177' OR EMPLOYEE_ID = '179';
442
443UPDATE EMPLOYEES
444SET DEPARTMENT_ID = '10'
445WHERE EMPLOYEE_ID = '200';
446
447UPDATE EMPLOYEES
448SET DEPARTMENT_ID = '20'
449WHERE EMPLOYEE_ID = '201' OR EMPLOYEE_ID = '202';
450
451UPDATE EMPLOYEES
452SET DEPARTMENT_ID = '40'
453WHERE EMPLOYEE_ID = '203';
454
455UPDATE EMPLOYEES
456SET DEPARTMENT_ID = '70'
457WHERE EMPLOYEE_ID = '204';
458
459
460UPDATE EMPLOYEES
461SET DEPARTMENT_ID = '110'
462WHERE EMPLOYEE_ID = '205' OR EMPLOYEE_ID = '206';