gbS2RTTc

1create database hr;
2use hr;
3
4CREATE TABLE IF NOT EXISTS countries (
5 COUNTRY_ID varchar(2) NOT NULL,
6 COUNTRY_NAME varchar(40) DEFAULT NULL,
7 REGION_ID decimal(10,0) DEFAULT NULL,
8 PRIMARY KEY (COUNTRY_ID),
9 KEY COUNTR_REG_FK (REGION_ID)
10);
11
12INSERT INTO countries (COUNTRY_ID, COUNTRY_NAME, REGION_ID) VALUES
13('AR', 'Argentina', '2'),
14('AU', 'Australia', '3'),
15('BE', 'Belgium', '1'),
16('BR', 'Brazil', '2'),
17('CA', 'Canada', '2'),
18('CH', 'Switzerland', '1'),
19('CN', 'China', '3'),
20('DE', 'Germany', '1'),
21('DK', 'Denmark', '1'),
22('EG', 'Egypt', '4'),
23('FR', 'France', '1'),
24('HK', 'HongKong', '3'),
25('IL', 'Israel', '4'),
26('IN', 'India', '3'),
27('IT', 'Italy', '1'),
28('JP', 'Japan', '3'),
29('KW', 'Kuwait', '4'),
30('MX', 'Mexico', '2'),
31('NG', 'Nigeria', '4'),
32('NL', 'Netherlands', '1'),
33('SG', 'Singapore', '3'),
34('UK', 'United Kingdom', '1'),
35('US', 'United States of America', '2'),
36('ZM', 'Zambia', '4'),
37('ZW', 'Zimbabwe', '4');
38
39CREATE TABLE IF NOT EXISTS departments (
40 DEPARTMENT_ID decimal(4,0) NOT NULL DEFAULT '0',
41 DEPARTMENT_NAME varchar(30) NOT NULL,
42 MANAGER_ID decimal(6,0) DEFAULT NULL,
43 LOCATION_ID decimal(4,0) DEFAULT NULL,
44 PRIMARY KEY (DEPARTMENT_ID),
45 KEY DEPT_MGR_FK (MANAGER_ID),
46 KEY DEPT_LOCATION_IX (LOCATION_ID)
47);
48
49INSERT INTO departments (DEPARTMENT_ID, DEPARTMENT_NAME, MANAGER_ID,LOCATION_ID) VALUES
50('10', 'Administration', '200', '1700'),
51('20', 'Marketing', '201', '1800'),
52('30', 'Purchasing', '114', '1700'),
53('40', 'Human Resources', '203', '2400'),
54('50', 'Shipping', '121', '1500'),
55('60', 'IT', '103', '1400'),
56('70', 'Public Relations', '204', '2700'),
57('80', 'Sales', '145', '2500'),
58('90', 'Executive', '100', '1700'),
59('100', 'Finance', '108', '1700'),
60('110', 'Accounting', '205', '1700'),
61('120', 'Treasury', '0', '1700'),
62('130', 'Corporate Tax', '0', '1700'),
63('140', 'Control And Credit', '0', '1700'),
64('150', 'Shareholder Services', '0', '1700'),
65('160', 'Benefits', '0', '1700'),
66('170', 'Manufacturing', '0', '1700'),
67('180', 'Construction', '0', '1700'),
68('190', 'Contracting', '0', '1700'),
69('200', 'Operations', '0', '1700'),
70('210', 'IT Support', '0', '1700'),
71('220', 'NOC', '0', '1700'),
72('230', 'IT Helpdesk', '0', '1700'),
73('240', 'Government Sales', '0', '1700'),
74('250', 'Retail Sales', '0', '1700'),
75('260', 'Recruiting', '0', '1700'),
76('270', 'Payroll', '0', '1700');
77
78CREATE TABLE IF NOT EXISTS employees (
79 EMPLOYEE_ID decimal(6,0) NOT NULL DEFAULT '0',
80 FIRST_NAME varchar(20) DEFAULT NULL,
81 LAST_NAME varchar(25) NOT NULL,
82 EMAIL varchar(25) NOT NULL,
83 PHONE_NUMBER varchar(20) DEFAULT NULL,
84 HIRE_DATE date NOT NULL,
85 JOB_ID varchar(10) NOT NULL,
86 SALARY decimal(8,2) DEFAULT NULL,
87 COMMISSION_PCT decimal(2,2) DEFAULT NULL,
88 MANAGER_ID decimal(6,0) DEFAULT NULL,
89 DEPARTMENT_ID decimal(4,0) DEFAULT NULL,
90 PRIMARY KEY (EMPLOYEE_ID),
91 UNIQUE KEY EMP_EMAIL_UK (EMAIL),
92 KEY EMP_DEPARTMENT_IX (DEPARTMENT_ID),
93 KEY EMP_JOB_IX (JOB_ID),
94 KEY EMP_MANAGER_IX (MANAGER_ID),
95 KEY EMP_NAME_IX (LAST_NAME,FIRST_NAME)
96);
97 
98 INSERT INTO employees (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL,
99PHONE_NUMBER, HIRE_DATE, JOB_ID, SALARY, COMMISSION_PCT, MANAGER_ID,
100DEPARTMENT_ID) VALUES('100', 'Steven', 'King', 'SKING', '515.123.4567', '1987-06-17',
101'AD_PRES', '24000.00', '0.00', '0', '90'),('101', 'Neena', 'Kochhar', 'NKOCHHAR',
102'515.123.4568', '1987-06-18', 'AD_VP', '17000.00', '0.00', '100', '90'),('102', 'Lex', 'De Haan',
103'LDEHAAN', '515.123.4569', '1987-06-19', 'AD_VP', '17000.00', '0.00', '100', '90'),('103',
104'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', '1987-06-20', 'IT_PROG', '9000.00', '0.00',
105'102', '60'),('104', 'Bruce', 'Ernst', 'BERNST', '590.423.4568', '1987-06-21', 'IT_PROG',
106'6000.00', '0.00', '103', '60'),('105', 'David', 'Austin', 'DAUSTIN', '590.423.4569', '1987-06-22',
107'IT_PROG', '4800.00', '0.00', '103', '60'),('106', 'Valli', 'Pataballa', 'VPATABAL', '590.423.4560',
108'1987-06-23', 'IT_PROG', '4800.00', '0.00', '103', '60'),('107', 'Diana', 'Lorentz', 'DLORENTZ',
109'590.423.5567', '1987-06-24', 'IT_PROG', '4200.00', '0.00', '103', '60'),('108', 'Nancy',
110'Greenberg', 'NGREENBE', '515.124.4569', '1987-06-25', 'FI_MGR', '12000.00', '0.00', '101',
111'100'),('109', 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', '1987-06-26', 'FI_ACCOUNT',
112'9000.00', '0.00', '108', '100'),('110', 'John', 'Chen', 'JCHEN', '515.124.4269', '1987-06-27',
113'FI_ACCOUNT', '8200.00', '0.00', '108', '100'),('111', 'Ismael', 'Sciarra', 'ISCIARRA',
114'515.124.4369', '1987-06-28', 'FI_ACCOUNT', '7700.00', '0.00', '108', '100'),('112', 'Jose
115Manuel', 'Urman', 'JMURMAN', '515.124.4469', '1987-06-29', 'FI_ACCOUNT', '7800.00',
116'0.00', '108', '100'),('113', 'Luis', 'Popp', 'LPOPP', '515.124.4567', '1987-06-30', 'FI_ACCOUNT',
117'6900.00', '0.00', '108', '100'),
118('114', 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', '1987-07-01', 'PU_MAN', '11000.00', '0.00', '100', '30'),
119('115', 'Alexander', 'Khoo', 'AKHOO',
120'515.127.4562', '1987-07-02', 'PU_CLERK', '3100.00', '0.00', '114', '30'),('116', 'Shelli', 'Baida',
121'SBAIDA', '515.127.4563', '1987-07-03', 'PU_CLERK', '2900.00', '0.00', '114', '30'),('117',
122'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', '1987-07-04', 'PU_CLERK', '2800.00', '0.00', '114',
123'30'),('118', 'Guy', 'Himuro', 'GHIMURO', '515.127.4565', '1987-07-05', 'PU_CLERK', '2600.00',
124'0.00', '114', '30'),('119', 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', '1987-07-06',
125'PU_CLERK', '2500.00', '0.00', '114', '30'),('120', 'Matthew', 'Weiss', 'MWEISS', 
126'650.123.1234', '1987-07-07', 'ST_MAN', '8000.00', '0.00', '100', '50'),('121', 'Adam', 'Fripp',
127'AFRIPP', '650.123.2234', '1987-07-08', 'ST_MAN', '8200.00', '0.00', '100', '50'),('122',
128'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', '1987-07-09', 'ST_MAN', '7900.00', '0.00',
129'100', '50'),('123', 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', '1987-07-10', 'ST_MAN',
130'6500.00', '0.00', '100', '50'),('124', 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', '1987-07-11', 'ST_MAN', '5800.00', '0.00', '100', '50'),('125', 'Julia', 'Nayer', 'JNAYER',
131'650.124.1214', '1987-07-12', 'ST_CLERK', '3200.00', '0.00', '120', '50'),('126', 'Irene',
132'Mikkilineni', 'IMIKKILI', '650.124.1224', '1987-07-13', 'ST_CLERK', '2700.00', '0.00', '120',
133'50'),('127', 'James', 'Landry', 'JLANDRY', '650.124.1334', '1987-07-14', 'ST_CLERK', '2400.00',
134'0.00', '120', '50'),('128', 'Steven', 'Markle', 'SMARKLE', '650.124.1434', '1987-07-15',
135'ST_CLERK', '2200.00', '0.00', '120', '50'),('129', 'Laura', 'Bissot', 'LBISSOT', '650.124.5234',
136'1987-07-16', 'ST_CLERK', '3300.00', '0.00', '121', '50'),('130', 'Mozhe', 'Atkinson',
137'MATKINSO', '650.124.6234', '1987-07-17', 'ST_CLERK', '2800.00', '0.00', '121', '50'),('131',
138'James', 'Marlow', 'JAMRLOW', '650.124.7234', '1987-07-18', 'ST_CLERK', '2500.00', '0.00',
139'121', '50'),('132', 'TJ', 'Olson', 'TJOLSON', '650.124.8234', '1987-07-19', 'ST_CLERK',
140'2100.00', '0.00', '121', '50'),('133', 'Jason', 'Mallin', 'JMALLIN', '650.127.1934', '1987-07-20',
141'ST_CLERK', '3300.00', '0.00', '122', '50'),('134', 'Michael', 'Rogers', 'MROGERS',
142'650.127.1834', '1987-07-21', 'ST_CLERK', '2900.00', '0.00', '122', '50'),('135', 'Ki', 'Gee',
143'KGEE', '650.127.1734', '1987-07-22', 'ST_CLERK', '2400.00', '0.00', '122', '50'),('136', 'Hazel',
144'Philtanker', 'HPHILTAN', '650.127.1634', '1987-07-23', 'ST_CLERK', '2200.00', '0.00', '122',
145'50'),('137', 'Renske', 'Ladwig', 'RLADWIG', '650.121.1234', '1987-07-24', 'ST_CLERK',
146'3600.00', '0.00', '123', '50'),('138', 'Stephen', 'Stiles', 'SSTILES', '650.121.2034', '1987-07-25',
147'ST_CLERK', '3200.00', '0.00', '123', '50'),('139', 'John', 'Seo', 'JSEO', '650.121.2019', '1987-07-26', 'ST_CLERK', '2700.00', '0.00', '123', '50'),('140', 'Joshua', 'Patel', 'JPATEL', '650.121.1834',
148'1987-07-27', 'ST_CLERK', '2500.00', '0.00', '123', '50'),('141', 'Trenna', 'Rajs', 'TRAJS',
149'650.121.8009', '1987-07-28', 'ST_CLERK', '3500.00', '0.00', '124', '50'),('142', 'Curtis',
150'Davies', 'CDAVIES', '650.121.2994', '1987-07-29', 'ST_CLERK', '3100.00', '0.00', '124',
151'50'),('143', 'Randall', 'Matos', 'RMATOS', '650.121.2874', '1987-07-30', 'ST_CLERK', '2600.00',
152'0.00', '124', '50'),('144', 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', '1987-07-31',
153'ST_CLERK', '2500.00', '0.00', '124', '50'),('145', 'John', 'Russell', 'JRUSSEL',
154'011.44.1344.429268', '1987-08-01', 'SA_MAN', '14000.00', '0.40', '100', '80'),('146', 'Karen',
155'Partners', 'KPARTNER', '011.44.1344.467268', '1987-08-02', 'SA_MAN', '13500.00', '0.30',
156'100', '80'),('147', 'Alberto', 'Errazuriz', 'AERRAZUR', '011.44.1344.429278', '1987-08-03',
157'SA_MAN', '12000.00', '0.30', '100', '80'),('148', 'Gerald', 'Cambrault', 'GCAMBRAU',
158'011.44.1344.619268', '1987-08-04', 'SA_MAN', '11000.00', '0.30', '100', '80'),('149', 'Eleni',
159'Zlotkey', 'EZLOTKEY', '011.44.1344.429018', '1987-08-05', 'SA_MAN', '10500.00', '0.20',
160'100', '80'),('150', 'Peter', 'Tucker', 'PTUCKER', '011.44.1344.129268', '1987-08-06', 'SA_REP',
161'10000.00', '0.30', '145', '80'),('151', 'David', 'Bernstein', 'DBERNSTE', '011.44.1344.345268',
162'1987-08-07', 'SA_REP', '9500.00', '0.25', '145', '80'),('152', 'Peter', 'Hall', 'PHALL',
163'011.44.1344.478968', '1987-08-08', 'SA_REP', '9000.00', '0.25', '145', '80'),('153',
164'Christopher', 'Olsen', 'COLSEN', '011.44.1344.498718', '1987-08-09', 'SA_REP', '8000.00',
165'0.20', '145', '80'),('154', 'Nanette', 'Cambrault', 'NCAMBRAU', '011.44.1344.987668', '1987-08-10', 'SA_REP', '7500.00', '0.20', '145', '80'),('155', 'Oliver', 'Tuvault', 'OTUVAULT',
166'011.44.1344.486508', '1987-08-11', 'SA_REP', '7000.00', '0.15', '145', '80'),('156', 'Janette', 
167'King', 'JKING', '011.44.1345.429268', '1987-08-12', 'SA_REP', '10000.00', '0.35', '146',
168'80'),('157', 'Patrick', 'Sully', 'PSULLY', '011.44.1345.929268', '1987-08-13', 'SA_REP',
169'9500.00', '0.35', '146', '80'),('158', 'Allan', 'McEwen', 'AMCEWEN', '011.44.1345.829268',
170'1987-08-14', 'SA_REP', '9000.00', '0.35', '146', '80'),('159', 'Lindsey', 'Smith', 'LSMITH',
171'011.44.1345.729268', '1987-08-15', 'SA_REP', '8000.00', '0.30', '146', '80'),('160', 'Louise',
172'Doran', 'LDORAN', '011.44.1345.629268', '1987-08-16', 'SA_REP', '7500.00', '0.30', '146',
173'80'),('161', 'Sarath', 'Sewall', 'SSEWALL', '011.44.1345.529268', '1987-08-17', 'SA_REP',
174'7000.00', '0.25', '146', '80'),('162', 'Clara', 'Vishney', 'CVISHNEY', '011.44.1346.129268',
175'1987-08-18', 'SA_REP', '10500.00', '0.25', '147', '80'),('163', 'Danielle', 'Greene', 'DGREENE',
176'011.44.1346.229268', '1987-08-19', 'SA_REP', '9500.00', '0.15', '147', '80'),('164', 'Mattea',
177'Marvins', 'MMARVINS', '011.44.1346.329268', '1987-08-20', 'SA_REP', '7200.00', '0.10',
178'147', '80'),('165', 'David', 'Lee', 'DLEE', '011.44.1346.529268', '1987-08-21', 'SA_REP',
179'6800.00', '0.10', '147', '80'),('166', 'Sundar', 'Ande', 'SANDE', '011.44.1346.629268', '1987-08-22', 'SA_REP', '6400.00', '0.10', '147', '80'),('167', 'Amit', 'Banda', 'ABANDA',
180'011.44.1346.729268', '1987-08-23', 'SA_REP', '6200.00', '0.10', '147', '80'),('168', 'Lisa',
181'Ozer', 'LOZER', '011.44.1343.929268', '1987-08-24', 'SA_REP', '11500.00', '0.25', '148',
182'80'),('169', 'Harrison', 'Bloom', 'HBLOOM', '011.44.1343.829268', '1987-08-25', 'SA_REP',
183'10000.00', '0.20', '148', '80'),('170', 'Tayler', 'Fox', 'TFOX', '011.44.1343.729268', '1987-08-26', 'SA_REP', '9600.00', '0.20', '148', '80'),('171', 'William', 'Smith', 'WSMITH',
184'011.44.1343.629268', '1987-08-27', 'SA_REP', '7400.00', '0.15', '148', '80'),('172', 'Elizabeth',
185'Bates', 'EBATES', '011.44.1343.529268', '1987-08-28', 'SA_REP', '7300.00', '0.15', '148',
186'80'),('173', 'Sundita', 'Kumar', 'SKUMAR', '011.44.1343.329268', '1987-08-29', 'SA_REP',
187'6100.00', '0.10', '148', '80'),('174', 'Ellen', 'Abel', 'EABEL', '011.44.1644.429267', '1987-08-30', 'SA_REP', '11000.00', '0.30', '149', '80'),('175', 'Alyssa', 'Hutton', 'AHUTTON',
188'011.44.1644.429266', '1987-08-31', 'SA_REP', '8800.00', '0.25', '149', '80'),('176', 'Jonathon',
189'Taylor', 'JTAYLOR', '011.44.1644.429265', '1987-09-01', 'SA_REP', '8600.00', '0.20', '149',
190'80'),('177', 'Jack', 'Livingston', 'JLIVINGS', '011.44.1644.429264', '1987-09-02', 'SA_REP',
191'8400.00', '0.20', '149', '80'),('178', 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.429263',
192'1987-09-03', 'SA_REP', '7000.00', '0.15', '149', '0'),('179', 'Charles', 'Johnson', 'CJOHNSON',
193'011.44.1644.429262', '1987-09-04', 'SA_REP', '6200.00', '0.10', '149', '80'),('180', 'Winston',
194'Taylor', 'WTAYLOR', '650.507.9876', '1987-09-05', 'SH_CLERK', '3200.00', '0.00', '120',
195'50'),('181', 'Jean', 'Fleaur', 'JFLEAUR', '650.507.9877', '1987-09-06', 'SH_CLERK', '3100.00',
196'0.00', '120', '50'),('182', 'Martha', 'Sullivan', 'MSULLIVA', '650.507.9878', '1987-09-07',
197'SH_CLERK', '2500.00', '0.00', '120', '50'),('183', 'Girard', 'Geoni', 'GGEONI', '650.507.9879',
198'1987-09-08', 'SH_CLERK', '2800.00', '0.00', '120', '50'),('184', 'Nandita', 'Sarchand',
199'NSARCHAN', '650.509.1876', '1987-09-09', 'SH_CLERK', '4200.00', '0.00', '121', '50'),('185',
200'Alexis', 'Bull', 'ABULL', '650.509.2876', '1987-09-10', 'SH_CLERK', '4100.00', '0.00', '121',
201'50'),('186', 'Julia', 'Dellinger', 'JDELLING', '650.509.3876', '1987-09-11', 'SH_CLERK',
202'3400.00', '0.00', '121', '50'),('187', 'Anthony', 'Cabrio', 'ACABRIO', '650.509.4876', '1987-09-12', 'SH_CLERK', '3000.00', '0.00', '121', '50'),('188', 'Kelly', 'Chung', 'KCHUNG',
203'650.505.1876', '1987-09-13', 'SH_CLERK', '3800.00', '0.00', '122', '50'),('189', 'Jennifer',
204'Dilly', 'JDILLY', '650.505.2876', '1987-09-14', 'SH_CLERK', '3600.00', '0.00', '122', '50'),('190',
205'Timothy', 'Gates', 'TGATES', '650.505.3876', '1987-09-15', 'SH_CLERK', '2900.00', '0.00',
206'122', '50'),('191', 'Randall', 'Perkins', 'RPERKINS', '650.505.4876', '1987-09-16', 'SH_CLERK', 
207'2500.00', '0.00', '122', '50'),('192', 'Sarah', 'Bell', 'SBELL', '650.501.1876', '1987-09-17',
208'SH_CLERK', '4000.00', '0.00', '123', '50'),('193', 'Britney', 'Everett', 'BEVERETT',
209'650.501.2876', '1987-09-18', 'SH_CLERK', '3900.00', '0.00', '123', '50'),('194', 'Samuel',
210'McCain', 'SMCCAIN', '650.501.3876', '1987-09-19', 'SH_CLERK', '3200.00', '0.00', '123',
211'50'),('195', 'Vance', 'Jones', 'VJONES', '650.501.4876', '1987-09-20', 'SH_CLERK', '2800.00',
212'0.00', '123', '50'),('196', 'Alana', 'Walsh', 'AWALSH', '650.507.9811', '1987-09-21',
213'SH_CLERK', '3100.00', '0.00', '124', '50'),('197', 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822',
214'1987-09-22', 'SH_CLERK', '3000.00', '0.00', '124', '50'),('198', 'Donald', 'OConnell',
215'DOCONNEL', '650.507.9833', '1987-09-23', 'SH_CLERK', '2600.00', '0.00', '124', '50'),('199',
216'Douglas', 'Grant', 'DGRANT', '650.507.9844', '1987-09-24', 'SH_CLERK', '2600.00', '0.00',
217'124', '50'),('200', 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', '1987-09-25', 'AD_ASST',
218'4400.00', '0.00', '101', '10'),('201', 'Michael', 'Hartstein', 'MHARTSTE', '515.123.5555', '1987-09-26', 'MK_MAN', '13000.00', '0.00', '100', '20'),('202', 'Pat', 'Fay', 'PFAY', '603.123.6666',
219'1987-09-27', 'MK_REP', '6000.00', '0.00', '201', '20'),('203', 'Susan', 'Mavris', 'SMAVRIS',
220'515.123.7777', '1987-09-28', 'HR_REP', '6500.00', '0.00', '101', '40'),('204', 'Hermann', 'Baer',
221'HBAER', '515.123.8888', '1987-09-29', 'PR_REP', '10000.00', '0.00', '101', '70'),('205',
222'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', '1987-09-30', 'AC_MGR', '12000.00', '0.00',
223'101', '110'),('206', 'William', 'Gietz', 'WGIETZ', '515.123.8181', '1987-10-01', 'AC_ACCOUNT',
224'8300.00', '0.00', '205', '110');
225
226CREATE TABLE IF NOT EXISTS job_history (
227 EMPLOYEE_ID decimal(6,0) NOT NULL,
228 START_DATE date NOT NULL,
229 END_DATE date NOT NULL,
230 JOB_ID varchar(10) NOT NULL,
231DEPARTMENT_ID decimal(4,0) DEFAULT NULL,
232 PRIMARY KEY (EMPLOYEE_ID,START_DATE),
233 KEY JHIST_DEPARTMENT_IX (DEPARTMENT_ID),
234 KEY JHIST_EMPLOYEE_IX (EMPLOYEE_ID),
235 KEY JHIST_JOB_IX (JOB_ID)
236);
237
238INSERT INTO job_history (EMPLOYEE_ID, START_DATE, END_DATE, JOB_ID,DEPARTMENT_ID) VALUES
239('102', '1993-01-13', '1998-07-24', 'IT_PROG', '60'),
240('101', '1989-09-21', '1993-10-27', 'AC_ACCOUNT', '110'),
241('101', '1993-10-28', '1997-03-15', 'AC_MGR', '110'),
242('201', '1996-02-17', '1999-12-19', 'MK_REP', '20'),
243('114', '1998-03-24', '1999-12-31', 'ST_CLERK', '50'),
244('122', '1999-01-01', '1999-12-31', 'ST_CLERK', '50'),
245('200', '1987-09-17', '1993-06-17', 'AD_ASST', '90'),
246('176', '1998-03-24', '1998-12-31', 'SA_REP', '80'),
247('176', '1999-01-01', '1999-12-31', 'SA_MAN', '80'),
248('200', '1994-07-01', '1998-12-31', 'AC_ACCOUNT', '90'),
249('0', '0000-00-00', '0000-00-00', '', '0');
250
251CREATE TABLE IF NOT EXISTS jobs (
252 JOB_ID varchar(10) NOT NULL DEFAULT '',
253 JOB_TITLE varchar(35) NOT NULL,
254 MIN_SALARY decimal(6,0) DEFAULT NULL,
255 MAX_SALARY decimal(6,0) DEFAULT NULL,
256 PRIMARY KEY (JOB_ID)
257);
258
259INSERT INTO jobs (JOB_ID, JOB_TITLE, MIN_SALARY,MAX_SALARY) VALUES
260('AD_PRES', 'President', '20000', '40000'),
261('AD_VP', 'Administration Vice President', '15000', '30000'),
262('AD_ASST', 'Administration Assistant', '3000', '6000'),
263('FI_MGR', 'Finance Manager', '8200', '16000'),
264('FI_ACCOUNT', 'Accountant', '4200', '9000'),
265('AC_MGR', 'Accounting Manager', '8200', '16000'),
266('AC_ACCOUNT', 'Public Accountant', '4200', '9000'),
267('SA_MAN', 'Sales Manager', '10000', '20000'),
268('SA_REP', 'Sales Representative', '6000', '12000'),
269('PU_MAN', 'Purchasing Manager', '8000', '15000'),
270('PU_CLERK', 'Purchasing Clerk', '2500', '5500'),
271('ST_MAN', 'Stock Manager', '5500', '8500'),
272('ST_CLERK', 'Stock Clerk', '2000', '5000'),
273('SH_CLERK', 'Shipping Clerk', '2500', '5500'),
274('IT_PROG', 'Programmer', '4000', '10000'),
275('MK_MAN', 'Marketing Manager', '9000', '15000'),
276('MK_REP', 'Marketing Representative', '4000', '9000'),
277('HR_REP', 'Human Resources Representative', '4000', '9000'),
278('PR_REP', 'Public Relations Representative', '4500', '10500');
279
280CREATE TABLE IF NOT EXISTS locations (
281 LOCATION_ID decimal(4,0) NOT NULL DEFAULT '0',
282 STREET_ADDRESS varchar(40) DEFAULT NULL,
283 POSTAL_CODE varchar(12) DEFAULT NULL,
284 CITY varchar(30) NOT NULL,
285 STATE_PROVINCE varchar(25) DEFAULT NULL,
286 COUNTRY_ID varchar(2) DEFAULT NULL,
287 PRIMARY KEY (LOCATION_ID),
288 KEY `LOC_CITY_IX` (CITY),
289 KEY `LOC_COUNTRY_IX` (COUNTRY_ID),
290 KEY `LOC_STATE_PROVINCE_IX` (STATE_PROVINCE)
291);
292
293INSERT INTO locations (LOCATION_ID, STREET_ADDRESS, POSTAL_CODE, CITY,
294STATE_PROVINCE, COUNTRY_ID) VALUES
295('1000', '1297 Via Cola di Rie', '989', 'Roma', '', 'IT'),
296('1100', '93091 Calle della Testa', '10934', 'Venice', '', 'IT'),
297('1200', '2017 Shinjuku-ku', '1689', 'Tokyo', 'Tokyo Prefecture', 'JP'),
298('1300', '9450 Kamiya-cho', '6823', 'Hiroshima', '', 'JP'),
299('1400', '2014 Jabberwocky Rd', '26192', 'Southlake', 'Texas', 'US'),
300('1500', '2011 Interiors Blvd', '99236', 'South San Francisco', 'California', 'US'),
301('1600', '2007 Zagora St', '50090', 'South Brunswick', 'New Jersey', 'US'),
302('1700', '2004 Charade Rd', '98199', 'Seattle', 'Washington', 'US'),
303('1800', '147 Spadina Ave', 'M5V 2L7', 'Toronto', 'Ontario', 'CA'),
304('1900', '6092 Boxwood St', 'YSW 9T2', 'Whitehorse', 'Yukon', 'CA'),
305('2000', '40-5-12 Laogianggen', '190518', 'Beijing', '', 'CN'),
306('2100', '1298 Vileparle (E)', '490231', 'Bombay', 'Maharashtra', 'IN'),
307('2200', '12-98 Victoria Street', '2901', 'Sydney', 'New South Wales', 'AU'),
308('2300', '198 Clementi North', '540198', 'Singapore', '', 'SG'),
309('2400', '8204 Arthur St', '', 'London', '', 'UK'),
310('2500', '"Magdalen Centre', ' The Oxford ', 'OX9 9ZB', 'Oxford', 'Ox'),
311('2600', '9702 Chester Road', '9629850293', 'Stretford', 'Manchester', 'UK'),
312('2700', 'Schwanthalerstr. 7031', '80925', 'Munich', 'Bavaria', 'DE'),
313('2800', 'Rua Frei Caneca 1360', '01307-002', 'Sao Paulo', 'Sao Paulo', 'BR'),
314('2900', '20 Rue des Corps-Saints', '1730', 'Geneva', 'Geneve', 'CH'),
315('3000', 'Murtenstrasse 921', '3095', 'Bern', 'BE', 'CH'),
316('3100', 'Pieter Breughelstraat 837', '3029SK', 'Utrecht', 'Utrecht', 'NL'),
317('3200', 'Mariano Escobedo 9991', '11932', 'Mexico City', '"Distrito Federal', '"');
318
319CREATE TABLE IF NOT EXISTS regions ( 
320REGION_ID decimal(5,0) NOT NULL,
321REGION_NAME varchar(25) DEFAULT NULL,
322 PRIMARY KEY (REGION_ID),
323 UNIQUE KEY sss (REGION_NAME));
324 
325 INSERT INTO regions (REGION_ID, REGION_NAME) VALUES
326('1', 'Europe\r'),
327('2', 'Americas\r'),
328('3', 'Asia\r'),
329('4', 'Middle East and Africa\r');