CgzqnzyS

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