· 8 years ago · Jan 18, 2018, 11:56 AM
1/*
2Course: INFANL02-2 , Introduction to Database , 1718
3Assignment 2
4Student1: [Tim],[Hoeneveld] ; [0927242] , [Class INF1E]
5Student2: [Bart],[Allertz] ; [0951018] , [Class INF1E]
6*/
7
8
9
10
11
12-- Question 1 : Give the list of teachers (name) and the name of the courses that they are teaching and the name of the courses that they are reviewing.
13-- Hint: There will be a list of teacher names (first and last), teaching courses, reviewing courses.
14-- write the query here
15
16/*
17select first_name, surname, name, review.coursecode
18from teacher, teaches, course, review
19Where teacher.bsn = teaches.teacherbsn and teaches.coursecode = course.code and teacher.bsn = review.teacherbsn
20Group by teacher.first_name, surname,name ,review.coursecode
21order by surname;
22*/
23
24
25-- Question 2: Give the list of students that completed their study programs together with their course points (sum of all the course points for each student).
26-- Hint: There will be a list with first name and last name of students and total points of courses that each student followed.
27-- write the query here
28
29/*
30select first_name, surname, SUM(points)
31from has_completed, student, teaches, course
32Where has_completed.student_id = student.id and teaches.studentid = has_completed.student_id and teaches.coursecode = course.code
33
34Group by first_name, surname, points
35order by surname
36*/
37
38
39-- Question 3: Give the list of courses (names) that teachers with maximum scale are teaching.
40-- write the query here
41
42
43
44-- Question 4: Give the list of students (names) and their assignments together with the number of prerequisites for each assignment.
45-- Hint: There will be a list of students names, code of the assignment, code of the course of the assignment, total number assignments that are required to do the assignment.
46-- write the query here
47
48
49
50-- Question 5: Give a list of teachers (names) that are teaching courses, but do not work on assignments
51-- write the query here
52
53
54
55
56
57
58
59
60
61
62-- SQL commands to create tables
63-- write the commands here
64
65
66drop table if exists teacher Cascade;
67Create table teacher (bsn char(11) Primary key,
68 first_name varchar(12),
69 surname varchar (30),
70 scale int,
71 salary real,
72 check (scale >= 9 and scale <= 13),
73 check (salary >= 20*scale and salary <= 35*scale));
74
75drop table if exists course Cascade;
76Create table course (code char(5) Primary key,
77 name varchar(30),
78 points int,
79 Check (points >= 1 and points <= 12));
80
81Drop table if exists student Cascade;
82create table student (id char(7)Primary key,
83 first_name varchar(12),
84 surname varchar(30),
85 bsn char(11),
86 start_date date);
87
88
89Drop Table if exists designs Cascade;
90Create Table designs (teacherbsn char(11) References teacher(bsn),
91 coursecode char(5) References course(code),
92 Primary key (teacherbsn, coursecode));
93
94Drop Table if exists review Cascade;
95Create Table review (teacherbsn char(11) References teacher(bsn),
96 coursecode char(5) References course(code),
97 Primary key (teacherbsn, coursecode));
98
99Drop Table if exists teaches Cascade;
100Create Table teaches (teacherbsn char(11) References teacher(bsn),
101 coursecode char(5) References course(code),
102 studentid char(7) references student(id),
103 Primary key (teacherbsn, coursecode, studentid));
104
105drop table if exists assignment Cascade;
106Create table assignment (coursecode char(5) references course(code),
107 assignmentcode char(5),
108 week int,
109 year int,
110 final boolean,
111 Primary key (coursecode, assignmentcode));
112
113drop table if exists requires Cascade;
114Create table requires (cc1 char(5),
115 ac1 char(5),
116 cc2 char(5),
117 ac2 char(5),
118 Primary key (cc1, ac1, cc2, ac2),
119 Foreign key (cc1, ac1) references assignment(coursecode, assignmentcode),
120 Foreign key (cc2, ac2) references assignment(coursecode, assignmentcode));
121
122
123
124
125Drop type teacherrole Cascade;
126Create type teacherrole as enum ('designer', 'reviewer', 'solver');
127Drop table if exists workson cascade;
128Create table workson (teacherbsn char(11) references teacher(bsn),
129 coursecode char(5),
130 assignmentcode char(5),
131 role_of_teacher teacherrole,
132 primary key (teacherbsn, coursecode, assignmentcode),
133 Foreign key (coursecode, assignmentcode) references assignment(coursecode, assignmentcode));
134
135
136drop table if exists study_program cascade;
137Create table study_program (level char(3),
138 study_name varchar(45),
139 duration int,
140 Primary key (level, study_name));
141
142
143
144drop table if exists has_completed Cascade;
145Create table has_completed (student_id char(7),
146 study_level char(3),
147 study_name varchar(45),
148
149 primary key (student_id, study_level, study_name),
150 Foreign key (study_level, study_name) references study_program(level, study_name),
151 Foreign key (student_id) references student(id)
152 );
153
154drop table if exists aregiven;
155Create table aregiven (student_id char(7),
156 coursecode char(5),
157 assignmentcode char(5),
158
159 primary key(student_id, coursecode, assignmentcode),
160 Foreign key (coursecode, assignmentcode) references assignment(coursecode, assignmentcode),
161 Foreign key (student_id) references student(id));
162
163-- SQL commands to insert values
164-- write the commands here
165
166Insert Into teacher Values
167 ('52269-69987', 'Claudius', 'Streather', '10', '200'),
168 ('59614-58753', 'Dona', 'Milbank', '12', '380'),
169 ('25856-40101', 'Riley', 'Pugsley', '12', '272'),
170 ('98208-93540', 'Garwood', 'Hattrick', '11', '220'),
171 ('64756-68937', 'Juieta', 'Kunzelmann', '11', '354'),
172 ('61092-02471', 'Sallyanne', 'Delort', '13', '350'),
173 ('94203-26749', 'Leann', 'Kleinhaus', '11', '220'),
174 ('42374-12317', 'Leigha', 'Cianelli', '11', '220'),
175 ('83265-24378', 'Karel', 'Eisak', '11', '246'),
176 ('78910-58311', 'Tomi', 'Lowis', '10', '350');
177
178
179Insert Into student Values
180 ('0817347', 'Adelaide', 'Kemp', '55540-32016', '2012-02-29'),
181 ('0889300', 'Addison', 'Lewis', '62874-60266', '2012-11-14'),
182 ('0838756', 'Chandler', 'Mcgowan', '31573-29574', '2011-10-07'),
183 ('0832660', 'Averi', 'Horn', '57137-29593', '2013-01-28'),
184 ('0820356', 'Paola', 'Montoya', '92971-17968', '2010-11-29'),
185 ('0874162', 'Joslyn', 'Mann', '28097-88031', '2011-10-17'),
186 ('0855759', 'Mckenna', 'Hodge', '10062-01495', '2011-06-03'),
187 ('0861973', 'Ayanna', 'Everett', '72133-12008', '2013-01-07'),
188 ('0891964', 'Ruby', 'Snow', '29773-16346', '2011-12-18'),
189 ('0827171', 'Arya', 'Campos', '18120-95884', '2013-01-21'),
190 ('0886733', 'Yaretzi', 'Joseph', '88344-15559', '2010-10-21'),
191 ('0885275', 'Leah', 'Roman', '89744-83342', '2010-01-03'),
192 ('0837712', 'Corinne', 'Huffman', '66587-05136', '2011-04-03'),
193 ('0803756', 'Enrique', 'Tyson', '41401-89947', '2012-12-30'),
194 ('0832106', 'Holly', 'Kinney', '96961-48255', '2011-11-04'),
195 ('0871899', 'Jaxton', 'Huffman', '58680-81239', '2010-12-24'),
196 ('0834667', 'Leland', 'Mullen', '49088-99950', '2013-08-23'),
197 ('0848042', 'Quincy', 'Merritt', '13619-35297', '2010-11-07'),
198 ('0882325', 'Zara', 'Alford', '40831-15016', '2010-10-15'),
199 ('0891031', 'Samantha', 'Jackson', '24874-47775', '2012-09-13'),
200 ('0846211', 'Jaxen', 'James', '98978-28953', '2012-11-13'),
201 ('0853161', 'Jonas', 'Rivas', '47853-96551', '2012-03-10'),
202 ('0819175', 'Jaidyn', 'Clemons', '76470-06519', '2012-08-27'),
203 ('0839441', 'Ayleen', 'Lee', '29551-94776', '2010-12-19'),
204 ('0857631', 'Gauge', 'Stewart', '98473-70192', '2011-11-23'),
205 ('0857382', 'Bruce', 'Carey', '70786-61873', '2010-01-01'),
206 ('0816380', 'Amani', 'Love', '25672-23781', '2010-12-09'),
207 ('0808692', 'Taraji', 'Mills', '80141-75446', '2012-02-03'),
208 ('0875869', 'Keira', 'Byrd', '33465-41147', '2013-03-27'),
209 ('0800811', 'Deegan', 'Strong', '57118-37389', '2011-07-05'),
210 ('0838774', 'Marvin', 'Mcmahon', '39361-84625', '2010-09-14'),
211 ('0848415', 'Lindsay', 'Walker', '90980-43462', '2011-06-28'),
212 ('0840712', 'Alden', 'Ware', '11362-49381', '2013-03-19'),
213 ('0889517', 'Heaven', 'Brady', '04490-83342', '2010-05-13'),
214 ('0804162', 'Arabella', 'Villarreal', '19814-98358', '2011-02-26'),
215 ('0870247', 'Stephen', 'Roy', '74814-12661', '2011-10-11'),
216 ('0823855', 'Lukas', 'Ortega', '53365-11487', '2010-10-14'),
217 ('0885385', 'Rylie', 'Gill', '59235-16274', '2013-01-21'),
218 ('0852696', 'Keira', 'Owens', '40390-46701', '2011-04-29'),
219 ('0882301', 'Steven', 'Thompson', '50374-80378', '2012-09-15'),
220 ('0835161', 'Shelby', 'Buck', '71238-52210', '2011-04-14'),
221 ('0822564', 'Collin', 'Santos', '53223-20765', '2012-01-06'),
222 ('0821986', 'Zain', 'Nelson', '36805-56788', '2013-10-28'),
223 ('0812982', 'Juliana', 'Ewing', '40267-88898', '2010-11-14'),
224 ('0843262', 'Madison', 'Malone', '39761-44632', '2013-11-25'),
225 ('0805577', 'Aria', 'Serrano', '90238-09850', '2010-03-24'),
226 ('0845288', 'Willa', 'Vance', '02587-13960', '2013-03-23'),
227 ('0859163', 'Nathanael', 'Hood', '63909-93284', '2013-08-06'),
228 ('0810022', 'Jaidyn', 'Bond', '99102-95164', '2012-03-03'),
229 ('0857370', 'Milo', 'Pollard', '80725-49081', '2010-10-12');
230
231
232Insert Into course Values
233 ('CRS01', 'Programming Basics', '3'),
234 ('CRS02', 'OO Programming', '2'),
235 ('CRS03', 'Database Design', '3'),
236 ('CRS04', 'Software Testing', '3'),
237 ('CRS05', 'Modelling', '3'),
238 ('CRS06', 'Web Programming', '3'),
239 ('CRS07', 'Requirement Engineering', '4'),
240 ('CRS08', 'Operating Systems', '2'),
241 ('CRS09', 'Hardware Design', '3'),
242 ('PRJ01', 'Game Development', '4'),
243 ('PRJ02', 'Data Analysis Applications', '4'),
244 ('PRJ03', 'Simulators', '2'),
245 ('PRJ04', 'Mobile Apps', '3'),
246 ('PRJ05', 'Parallel Programming', '3'),
247 ('PRJ06', 'Advanced Technologies', '3');
248
249
250Insert Into assignment Values
251 ('CRS01', 'HMW37', '28', '2010', 'FALSE'),
252 ('CRS01', 'HMW54', '5', '2011', 'FALSE'),
253 ('CRS01', 'HMW41', '30', '2010', 'TRUE'),
254 ('CRS01', 'HMW44', '3', '2011', 'TRUE'),
255 ('CRS05', 'HMW39', '31', '2013', 'FALSE'),
256 ('CRS05', 'HMW03', '17', '2010', 'TRUE'),
257 ('CRS05', 'HMW66', '38', '2011', 'FALSE'),
258 ('CRS05', 'HMW99', '13', '2013', 'FALSE'),
259 ('PRJ01', 'HMW59', '36', '2012', 'TRUE'),
260 ('PRJ02', 'HMW59', '36', '2013', 'FALSE'),
261 ('PRJ01', 'HMW23', '13', '2012', 'FALSE'),
262 ('PRJ01', 'HMW04', '20', '2013', 'FALSE'),
263 ('PRJ04', 'HMW74', '18', '2012', 'FALSE'),
264 ('PRJ04', 'HMW13', '27', '2010', 'FALSE'),
265 ('PRJ04', 'HMW62', '14', '2010', 'FALSE'),
266 ('PRJ04', 'HMW78', '31', '2012', 'TRUE'),
267 ('PRJ04', 'HMW34', '24', '2010', 'TRUE'),
268 ('PRJ04', 'HMW55', '5', '2010', 'FALSE'),
269 ('PRJ04', 'HMW16', '26', '2012', 'FALSE'),
270 ('PRJ04', 'HMW77', '36', '2011', 'TRUE');
271
272
273Insert Into study_program Values
274 ('BSC', 'Informatica', '4'),
275 ('MSC', 'Informatica', '2');
276
277
278Insert Into teaches Values
279 ('52269-69987', 'CRS01', '0817347'),
280 ('59614-58753', 'CRS02', '0889300'),
281 ('25856-40101', 'CRS03', '0838756'),
282 ('98208-93540', 'CRS04', '0832660'),
283 ('64756-68937', 'CRS05', '0820356'),
284 ('61092-02471', 'CRS06', '0874162'),
285 ('94203-26749', 'CRS07', '0855759'),
286 ('42374-12317', 'CRS08', '0861973'),
287 ('83265-24378', 'CRS09', '0891964'),
288 ('78910-58311', 'PRJ01', '0827171'),
289 ('52269-69987', 'PRJ02', '0886733'),
290 ('59614-58753', 'PRJ03', '0885275'),
291 ('25856-40101', 'PRJ04', '0837712'),
292 ('98208-93540', 'PRJ05', '0803756'),
293 ('64756-68937', 'PRJ06', '0832106'),
294 ('52269-69987', 'CRS01', '0871899'),
295 ('59614-58753', 'CRS02', '0834667'),
296 ('25856-40101', 'CRS03', '0848042'),
297 ('98208-93540', 'CRS04', '0882325'),
298 ('64756-68937', 'CRS05', '0882325'),
299 ('64756-68937', 'CRS05', '0891031'),
300 ('61092-02471', 'CRS06', '0846211'),
301 ('94203-26749', 'CRS07', '0853161'),
302 ('42374-12317', 'CRS08', '0819175'),
303 ('83265-24378', 'CRS09', '0839441'),
304 ('78910-58311', 'PRJ01', '0857631'),
305 ('52269-69987', 'PRJ02', '0857382'),
306 ('59614-58753', 'PRJ03', '0816380'),
307 ('25856-40101', 'PRJ04', '0808692'),
308 ('98208-93540', 'PRJ05', '0875869'),
309 ('64756-68937', 'PRJ06', '0800811'),
310 ('52269-69987', 'CRS01', '0838774'),
311 ('59614-58753', 'CRS02', '0848415'),
312 ('25856-40101', 'CRS03', '0840712'),
313 ('98208-93540', 'CRS04', '0889517'),
314 ('64756-68937', 'CRS05', '0804162'),
315 ('61092-02471', 'CRS06', '0870247'),
316 ('94203-26749', 'CRS07', '0823855'),
317 ('42374-12317', 'CRS08', '0885385'),
318 ('83265-24378', 'CRS09', '0852696'),
319 ('78910-58311', 'PRJ01', '0882301'),
320 ('52269-69987', 'PRJ02', '0835161'),
321 ('59614-58753', 'PRJ03', '0822564'),
322 ('25856-40101', 'PRJ04', '0821986'),
323 ('98208-93540', 'PRJ05', '0812982'),
324 ('64756-68937', 'PRJ06', '0843262'),
325 ('52269-69987', 'CRS01', '0805577'),
326 ('59614-58753', 'CRS02', '0845288'),
327 ('25856-40101', 'CRS03', '0859163'),
328 ('98208-93540', 'CRS04', '0810022'),
329 ('64756-68937', 'CRS05', '0857370');
330
331
332Insert Into has_completed Values
333 ('0871899', 'BSC', 'Informatica'),
334 ('0834667', 'BSC', 'Informatica'),
335 ('0848042', 'BSC', 'Informatica'),
336 ('0882325', 'BSC', 'Informatica'),
337 ('0891031', 'BSC', 'Informatica'),
338 ('0846211', 'MSC', 'Informatica'),
339 ('0819175', 'MSC', 'Informatica'),
340 ('0839441', 'MSC', 'Informatica'),
341 ('0857631', 'MSC', 'Informatica'),
342 ('0857382', 'MSC', 'Informatica'),
343 ('0816380', 'MSC', 'Informatica'),
344 ('0808692', 'MSC', 'Informatica'),
345 ('0875869', 'MSC', 'Informatica'),
346 ('0800811', 'BSC', 'Informatica'),
347 ('0838774', 'BSC', 'Informatica'),
348 ('0848415', 'BSC', 'Informatica'),
349 ('0840712', 'MSC', 'Informatica'),
350 ('0889517', 'MSC', 'Informatica'),
351 ('0804162', 'MSC', 'Informatica');
352
353
354Insert Into review Values
355 ('94203-26749', 'CRS01'),
356 ('78910-58311', 'CRS02'),
357 ('61092-02471', 'CRS03'),
358 ('52269-69987', 'CRS04'),
359 ('59614-58753', 'CRS05'),
360 ('98208-93540', 'CRS06'),
361 ('59614-58753', 'CRS07'),
362 ('98208-93540', 'CRS08'),
363 ('83265-24378', 'CRS09'),
364 ('64756-68937', 'PRJ01'),
365 ('94203-26749', 'PRJ02'),
366 ('98208-93540', 'PRJ03'),
367 ('83265-24378', 'PRJ04'),
368 ('83265-24378', 'PRJ05'),
369 ('52269-69987', 'PRJ06');
370
371
372Insert Into designs Values
373 ('25856-40101', 'CRS01'),
374 ('25856-40101', 'CRS02'),
375 ('61092-02471', 'CRS03'),
376 ('94203-26749', 'CRS04'),
377 ('61092-02471', 'CRS05'),
378 ('52269-69987', 'CRS06'),
379 ('59614-58753', 'CRS07'),
380 ('25856-40101', 'CRS08'),
381 ('98208-93540', 'CRS09'),
382 ('64756-68937', 'PRJ01'),
383 ('78910-58311', 'PRJ02'),
384 ('83265-24378', 'PRJ03'),
385 ('42374-12317', 'PRJ04'),
386 ('98208-93540', 'PRJ05'),
387 ('94203-26749', 'PRJ06');
388
389
390Insert Into requires Values
391 ('CRS01', 'HMW37', 'CRS01', 'HMW44'),
392 ('CRS01', 'HMW54', 'CRS05', 'HMW39'),
393 ('CRS01', 'HMW41', 'CRS05', 'HMW03'),
394 ('CRS01', 'HMW44', 'CRS05', 'HMW66'),
395 ('CRS05', 'HMW39', 'CRS05', 'HMW99'),
396 ('CRS05', 'HMW03', 'PRJ01', 'HMW59'),
397 ('CRS05', 'HMW66', 'PRJ02', 'HMW59'),
398 ('CRS05', 'HMW99', 'PRJ01', 'HMW23'),
399 ('PRJ01', 'HMW59', 'PRJ01', 'HMW04'),
400 ('PRJ02', 'HMW59', 'PRJ04', 'HMW74'),
401 ('PRJ01', 'HMW23', 'PRJ04', 'HMW13'),
402 ('PRJ01', 'HMW04', 'PRJ04', 'HMW62'),
403 ('PRJ04', 'HMW74', 'PRJ04', 'HMW78'),
404 ('PRJ04', 'HMW13', 'PRJ04', 'HMW34'),
405 ('PRJ04', 'HMW62', 'PRJ04', 'HMW55'),
406 ('PRJ04', 'HMW78', 'PRJ04', 'HMW16'),
407 ('PRJ04', 'HMW34', 'PRJ04', 'HMW77');
408
409
410Insert Into aregiven Values
411 ('0817347', 'CRS01', 'HMW54'),
412 ('0817347', 'CRS01', 'HMW37'),
413 ('0817347', 'CRS01', 'HMW41'),
414 ('0817347', 'CRS01', 'HMW44'),
415 ('0820356', 'CRS05', 'HMW39'),
416 ('0820356', 'CRS05', 'HMW99'),
417 ('0820356', 'CRS05', 'HMW03'),
418 ('0820356', 'CRS05', 'HMW66'),
419 ('0827171', 'PRJ01', 'HMW04'),
420 ('0827171', 'PRJ01', 'HMW59'),
421 ('0827171', 'PRJ01', 'HMW23'),
422 ('0886733', 'PRJ02', 'HMW59'),
423 ('0837712', 'PRJ04', 'HMW13'),
424 ('0837712', 'PRJ04', 'HMW62'),
425 ('0837712', 'PRJ04', 'HMW77'),
426 ('0837712', 'PRJ04', 'HMW74'),
427 ('0837712', 'PRJ04', 'HMW34'),
428 ('0837712', 'PRJ04', 'HMW55'),
429 ('0837712', 'PRJ04', 'HMW16'),
430 ('0837712', 'PRJ04', 'HMW78'),
431 ('0871899', 'CRS01', 'HMW54'),
432 ('0871899', 'CRS01', 'HMW37'),
433 ('0871899', 'CRS01', 'HMW41'),
434 ('0871899', 'CRS01', 'HMW44'),
435 ('0882325', 'CRS05', 'HMW39'),
436 ('0882325', 'CRS05', 'HMW99'),
437 ('0882325', 'CRS05', 'HMW03'),
438 ('0882325', 'CRS05', 'HMW66'),
439 ('0891031', 'CRS05', 'HMW39'),
440 ('0891031', 'CRS05', 'HMW99'),
441 ('0891031', 'CRS05', 'HMW03'),
442 ('0891031', 'CRS05', 'HMW66'),
443 ('0857631', 'PRJ01', 'HMW04'),
444 ('0857631', 'PRJ01', 'HMW59'),
445 ('0857631', 'PRJ01', 'HMW23'),
446 ('0857382', 'PRJ02', 'HMW59'),
447 ('0808692', 'PRJ04', 'HMW13'),
448 ('0808692', 'PRJ04', 'HMW62'),
449 ('0808692', 'PRJ04', 'HMW77'),
450 ('0808692', 'PRJ04', 'HMW74'),
451 ('0808692', 'PRJ04', 'HMW34'),
452 ('0808692', 'PRJ04', 'HMW55'),
453 ('0808692', 'PRJ04', 'HMW16'),
454 ('0808692', 'PRJ04', 'HMW78'),
455 ('0838774', 'CRS01', 'HMW54'),
456 ('0838774', 'CRS01', 'HMW37'),
457 ('0838774', 'CRS01', 'HMW41'),
458 ('0838774', 'CRS01', 'HMW44'),
459 ('0804162', 'CRS05', 'HMW39'),
460 ('0804162', 'CRS05', 'HMW99'),
461 ('0804162', 'CRS05', 'HMW03'),
462 ('0804162', 'CRS05', 'HMW66'),
463 ('0882301', 'PRJ01', 'HMW04'),
464 ('0882301', 'PRJ01', 'HMW59'),
465 ('0882301', 'PRJ01', 'HMW23'),
466 ('0835161', 'PRJ02', 'HMW59'),
467 ('0821986', 'PRJ04', 'HMW13'),
468 ('0821986', 'PRJ04', 'HMW62'),
469 ('0821986', 'PRJ04', 'HMW77'),
470 ('0821986', 'PRJ04', 'HMW74'),
471 ('0821986', 'PRJ04', 'HMW34'),
472 ('0821986', 'PRJ04', 'HMW55'),
473 ('0821986', 'PRJ04', 'HMW16'),
474 ('0821986', 'PRJ04', 'HMW78'),
475 ('0805577', 'CRS01', 'HMW54'),
476 ('0805577', 'CRS01', 'HMW37'),
477 ('0805577', 'CRS01', 'HMW41'),
478 ('0805577', 'CRS01', 'HMW44'),
479 ('0857370', 'CRS05', 'HMW39'),
480 ('0857370', 'CRS05', 'HMW99'),
481 ('0857370', 'CRS05', 'HMW03'),
482 ('0857370', 'CRS05', 'HMW66');
483
484
485Insert Into workson Values
486 ('61092-02471', 'CRS01', 'HMW37', 'designer'),
487 ('25856-40101', 'CRS01', 'HMW54', 'solver'),
488 ('52269-69987', 'CRS01', 'HMW41', 'solver'),
489 ('94203-26749', 'CRS01', 'HMW44', 'designer'),
490 ('94203-26749', 'CRS05', 'HMW39', 'solver'),
491 ('98208-93540', 'CRS05', 'HMW03', 'reviewer'),
492 ('64756-68937', 'CRS05', 'HMW66', 'reviewer'),
493 ('78910-58311', 'CRS05', 'HMW99', 'solver'),
494 ('25856-40101', 'PRJ01', 'HMW59', 'designer'),
495 ('78910-58311', 'PRJ02', 'HMW59', 'reviewer'),
496 ('78910-58311', 'PRJ01', 'HMW23', 'designer'),
497 ('94203-26749', 'PRJ01', 'HMW04', 'designer'),
498 ('25856-40101', 'PRJ04', 'HMW74', 'solver'),
499 ('78910-58311', 'PRJ04', 'HMW13', 'solver'),
500 ('52269-69987', 'PRJ04', 'HMW62', 'reviewer'),
501 ('78910-58311', 'PRJ04', 'HMW78', 'solver'),
502 ('78910-58311', 'PRJ04', 'HMW34', 'designer'),
503 ('78910-58311', 'PRJ04', 'HMW55', 'designer'),
504 ('64756-68937', 'PRJ04', 'HMW16', 'designer'),
505 ('78910-58311', 'PRJ04', 'HMW77', 'reviewer');