M43LYXqs

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