07eUZKVf

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