sCR55zsn

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#Q1
324SELECT * FROM employees where EMPLOYEE_ID<=105;
325#Q2
326update employees set SALARY =8000 where EMPLOYEE_ID<=105 and SALARY<=5000;
327SELECT * FROM employees where EMPLOYEE_ID=105;
328#Q3 
329update employees set JOB_ID=' SH_CLERK ' where  EMPLOYEE_ID=118 and DEPARTMENT_ID=30 AND JOB_ID NOT LIKE  'SH%';
330SELECT * FROM employees where EMPLOYEE_ID=118;
331#Q4
332update employees set SALARY = SALARY*(1.25) where   DEPARTMENT_ID=40;
333update employees set SALARY = SALARY*(1.15) where   DEPARTMENT_ID=90;
334update employees set SALARY = SALARY*(1.10) where   DEPARTMENT_ID=110;
335SELECT * FROM employees ;
336#Q5
337SELECT FIRST_NAME"first name",LAST_NAME"last name" from employees;
338#Q6
339SElECT distinct DEPARTMENT_ID FROM employees;
340#Q7
341SELECT * 	FROM employees 	ORDER BY FIRST_NAME DESC;
342#Q8
343SELECT first_name, last_name, salary, salary*.15 PF FROM employees;
344#Q9
345SELECT employee_id, first_name, last_name, salary FROM employees ORDER BY salary ASC;
346#Q10
347SELECT SUM(salary) FROM employees;
348#Q11
349SELECT MAX(salary), MIN(salary) FROM employees;
350#Q12
351SELECT AVG(salary), COUNT(*) FROM employees;
352#Q13
353SELECT COUNT(*) FROM employees;
354#Q14
355SELECT COUNT(distinct job_id) FROM employees;
356#Q15
357SELECT UPPER(first_name) FROM employees;
358#Q16
359SELECT SUBSTRING(first_name,1,3) FROM employees;
360#Q17
361SELECT 171*214+625 Result;
362#Q18
363SELECT  CONCAT(first_name,' ', last_name) 'Employee Name' FROM employees;
364#Q19
365select TRIM(first_name) from employees;
366#Q20
367select  first_name, last_name ,length(first_name)+length(last_name) 'Length of name' from employees;
368#Q21
369SELECT* FROM employees where  first_name REGEXP '[0-9]';
370#Q22
371SELECT employee_id, first_name FROM employees  LIMIT 10;
372#Q23
373SELECT first_name, last_name, round(salary/12,2) as 'Monthly Salary' FROM employees;
374#Q24
375SELECT first_name, last_name, salary FROM employees WHERE salary NOT BETWEEN 10000 AND 15000;
376#Q25
377SELECT first_name, last_name, department_id
378FROM employees
379WHERE department_id IN (30, 100)
380ORDER BY  department_id  ASC;
381#Q26
382SELECT first_name
383FROM employees
384WHERE first_name LIKE '%b%'
385AND first_name LIKE '%c%';
386#Q27
387SELECT * 
388FROM employees 
389WHERE last_name IN('JONES', 'BLAKE', 'SCOTT', 'KING', 'FORD');
390#Q28
391SELECT location_id, street_address, city, state_province, country_name
392FROM locations
393NATURAL JOIN countries;
394#Q29
395SELECT location_id, street_address, city, state_province, country_name
396FROM locations
397NATURAL JOIN countries;
398#Q30
399SELECT first_name, last_name, department_id, department_name 
400FROM employees 
401JOIN departments USING (department_id);
402#Q31
403SELECT first_name, last_name, hire_date, salary, 
404(DATEDIFF(now(), hire_date))/365 Experience 
405FROM departments d JOIN employees e 
406ON (d.manager_id = e.employee_id) 
407WHERE (DATEDIFF(now(), hire_date))/365>15;
408#Q32
409SELECT job_title, AVG(salary) 
410FROM employees 
411NATURAL JOIN jobs 
412GROUP BY job_title;