f9Gkv1yB

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