fMTCVbvk

1USE master;
2IF EXISTS(select * from sys.databases where name='university')
3ALTER DATABASE university SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
4DROP DATABASE if exists university
5go
6CREATE DATABASE university
7go
8USE university
9go
10
11create table grades (
12grade decimal(2,1) primary key);
13
14insert into grades values (2);
15insert into grades values (3);
16insert into grades values (3.5);
17insert into grades values (4);
18insert into grades values (4.5);
19insert into grades values (5);
20insert into grades values (5.5);
21insert into grades values (6);
22
23create table groups (
24group_no char(10) primary key);
25
26insert into groups values ('DZIe1001');
27insert into groups values ('Dtie1002');
28insert into groups values ('DZIe1003');
29insert into groups values ('DZie1004');
30insert into groups values ('DMIe1011');
31insert into groups values ('DGIe1012');
32insert into groups values ('DMIe1013');
33insert into groups values ('DMIe1014');
34insert into groups values ('ZSIe2001');
35insert into groups values ('ZZIe2002');
36insert into groups values ('ZZie2003');
37insert into groups values ('ZZIe2004');
38insert into groups values ('ZMie2011');
39insert into groups values ('ZMIe2012');
40insert into groups values ('ZTIe2013');
41insert into groups values ('ZMIe2014');
42insert into groups values ('DZZa3001');
43insert into groups values ('DZza3002');
44insert into groups values ('DZZa3003');
45insert into groups values ('DMza3011');
46insert into groups values ('DmZa3012');
47insert into groups values ('DMza3013');
48insert into groups values ('DwZa3014');
49
50create table acad_positions (
51acad_position varchar(40) primary key,
52overtime_rate smallmoney not null);
53
54insert into acad_positions values ('master','40');
55insert into acad_positions values ('doctor','45');
56insert into acad_positions values ('associate professor','50');
57insert into acad_positions values ('habilitated doctor','60');
58insert into acad_positions values ('habilitated associate professor','65');
59insert into acad_positions values ('full professor','80');
60
61create table employees (
62employee_id int identity primary key,
63surname varchar(30) not null,
64first_name varchar(25) not null,
65employment_date date,
66PESEL char(11));
67
68set identity_insert employees on;
69insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
70values (1, 'Smith','Rebecca','20180911','69101223456');
71insert into employees (employee_id, surname, first_name, employment_date) 
72values (2, 'Jones','Harry','20181011');
73insert into employees (employee_id, surname, first_name, PESEL) 
74values (3, 'Williams','Oliver','71110212345');
75insert into employees (employee_id, surname, first_name, PESEL) 
76values (4, 'Taylor','Lily','76032390876');
77insert into employees (employee_id, surname, first_name, employment_date) 
78values (5, 'Brown','Jack','20181023');
79insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
80values (6, 'Davis','Charlie','20181025', '81092309213');
81insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
82values (7, 'Taylor','Lily','20190104','74052390852');
83insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
84values (8, 'Evans','Thomas','20190115','82120927109');
85insert into employees (employee_id, surname, first_name, PESEL) 
86values (9, 'Smith','Harry','79021896549');
87insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
88values (10, 'Wilson','Jacob','20190115','65073092711');
89insert into employees (employee_id, surname, first_name, PESEL) 
90values (11, 'Craven','Lily','81081721785');
91insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
92values (12, 'Brown','Jacob','20190119','58022397463');
93insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
94values (13, 'Evans','Oliver','20190223','71012409212');
95insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
96values (14, 'Johnson','Emma','20190225','87043098777');
97insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
98values (15, 'Robinson','James','20190227','84071722567');
99insert into employees (employee_id, surname, first_name) 
100values (16, 'Davis','Charlie');
101insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
102values (17, 'Robinson','Grace','20190411','67012398876');
103insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
104values (18, 'Thompson','William','20190422','71022345533');
105insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
106values (19, 'Wright','Katie','20190412','64081212432');
107insert into employees (employee_id, surname, first_name, PESEL) 
108values (20, 'Walker','Sophia','63091922309');
109insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
110values (21, 'Robinson','Grace','20190823','60010804411');
111insert into employees (employee_id, surname, first_name, employment_date) 
112values (22, 'Evans','Dylan','20190827');
113insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
114values (23, 'Walker','Amy','20190912','62040925511');
115insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
116values (24, 'Craven','Lucas','20190927','64041688256');
117insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
118values (25, 'Davies','Katie','20191017','60022102999');
119insert into employees (employee_id, surname, first_name) 
120values (26, 'Jones','Harry');
121insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
122values (27, 'Jones','Rebecca','20191115','60110912211');
123insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
124values (28, 'Wright','Megan','20191202','70080821675');
125insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
126values (29, 'White','Henry','20191202','70041209872');
127insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
128values (30, 'Robinson','Harry','20191217','60020398234');
129insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
130values (31, 'White','Ryan','20191228','60070920076');
131insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
132values (32, 'Davies','Layla','20200115','61101906677');
133insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
134values (33, 'Davies','Layla','20200119','69110202299');
135insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
136values (34, 'Davies','Edward','20200123','61102309887');
137insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
138values (35, 'Jones','Lily','20200208','70022309900');
139insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
140values (36, 'Edwards','Molly','20200216','60022309564');
141insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
142values (37, 'Edwards','John','20200216','62121902554');
143insert into employees (employee_id, surname, first_name, employment_date) 
144values (38, 'Thompson','Sophia','20200216');
145insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
146values (39, 'Evans','Michael','20200227','60082793322');
147insert into employees (employee_id, surname, first_name, employment_date, PESEL) 
148values (40, 'Green','Tommy','20200227','75112081338');
149insert into employees (employee_id, surname, first_name, employment_date) 
150values (41, 'Hall','Elizabeth','20200328');
151insert into employees (employee_id, surname, first_name, PESEL) 
152values (42, 'Brown','John','69031493209');
153set identity_insert employees off;
154
155create table departments (
156department varchar(100) primary key);
157
158insert into departments values
159('Department of Economics'),
160('Department of Informatics'),
161('Department of Mathematics'),
162('Department of Statistics'),
163('Department of History'),
164('Department of Geography'),
165('Institute of Foreign Languages'),
166('Department of Management'),
167('Department of Physics'),
168('Department of Foreign Affairs'),
169('Institute of Teaching Methods');
170
171create table lecturers (
172lecturer_id int primary key,
173acad_position varchar(40),
174department varchar(100) not null,
175foreign key (acad_position) references acad_positions 
176on delete no action on update cascade,
177foreign key (lecturer_id) references employees (employee_id) on delete cascade,
178foreign key (department) references departments (department) on delete no action on update cascade
179);
180
181insert into lecturers values (1, 'doctor', 'Department of Mathematics');
182insert into lecturers values (2, 'associate professor', 'Department of Informatics');
183insert into lecturers values (3, 'doctor', 'Department of History');
184insert into lecturers values (4, 'master', 'Department of Economics');
185insert into lecturers values (5, 'associate professor', 'Department of Foreign Affairs');
186insert into lecturers (lecturer_id, department) values (7, 'Department of Informatics');
187insert into lecturers values (8, 'master', 'Department of Statistics');
188insert into lecturers values (11, 'doctor', 'Department of Informatics');
189insert into lecturers values (12, 'full professor', 'Department of Economics');
190insert into lecturers (lecturer_id, department) values (14, 'Department of Statistics');
191insert into lecturers values (15, 'master', 'Department of Informatics');
192insert into lecturers values (17, 'associate professor', 'Department of Statistics');
193insert into lecturers values (18, 'doctor', 'Department of Informatics');
194insert into lecturers values (19, 'doctor', 'Department of Geography');
195insert into lecturers values (21, 'full professor', 'Department of History');
196insert into lecturers values (22, 'associate professor', 'Department of Geography');
197insert into lecturers values (24, 'full professor', 'Department of Informatics');
198insert into lecturers values (25, 'associate professor', 'Department of Statistics');
199insert into lecturers values (26, 'doctor', 'Department of History');
200insert into lecturers values (27, 'associate professor', 'Department of Geography');
201insert into lecturers values (29, 'doctor', 'Department of Informatics');
202insert into lecturers (lecturer_id, department) values (30, 'Department of History');
203insert into lecturers values (32, 'full professor', 'Department of Mathematics');
204insert into lecturers values (35, 'master', 'Institute of Foreign Languages');
205insert into lecturers values (36, 'doctor', 'Department of Mathematics');
206insert into lecturers values (37, 'associate professor', 'Department of Statistics');
207insert into lecturers values (39, 'full professor', 'Department of Geography');
208insert into lecturers (lecturer_id, department) values (41, 'Department of Statistics');
209
210create table students (
211student_id int identity primary key,
212surname varchar(30) not null,
213first_name varchar(25),
214date_of_birth date,
215group_no char(10),
216foreign key (group_no) references groups
217on delete no action on update cascade
218);
219
220set identity_insert students on;
221insert into students (student_id, surname, first_name, date_of_birth, group_no)
222values (1, 'Bowen', 'Harry', '20001027', 'DMIe1011');
223insert into students (student_id, surname, first_name)
224values (2, 'Palmer', 'John');
225insert into students (student_id, surname, first_name, date_of_birth, group_no)
226values (3, 'Hunt', 'Melissa', '19781018', 'DMIe1011');
227insert into students (student_id, surname, first_name, group_no)
228values (4, 'Matthews', 'Amy', 'DMIe1011');
229insert into students (student_id, surname, first_name, date_of_birth, group_no)
230values (5, 'Mills', 'Laila', '19790311', 'ZZIe2002');
231insert into students (student_id, surname, first_name, date_of_birth, group_no)
232values (6, 'Holmes', 'Ben', '19820621', 'DMIe1011');
233insert into students (student_id, surname, first_name, date_of_birth)
234values (7, 'Webb', 'Oliver', '19861123');
235insert into students (student_id, surname, first_name, date_of_birth, group_no)
236values (8, 'Rogers', 'Frederick', '19900314', 'DMIe1014');
237insert into students (student_id, surname, first_name, date_of_birth, group_no)
238values (9, 'Gibson', 'Robert', '19921021', 'ZZIe2002');
239insert into students (student_id, surname, first_name, date_of_birth, group_no)
240values (10, 'Lancaster', 'Katie', '19870821', 'DMZa3013');
241insert into students (student_id, surname, first_name, date_of_birth, group_no)
242values (11, 'Hunt', 'Melissa', '19900130', 'ZMIe2011');
243insert into students (student_id, surname, first_name, date_of_birth)
244values (12, 'Palmer', 'John', '19891028');
245insert into students (student_id, surname, first_name, date_of_birth)
246values (13, 'Owen', 'Layla', '20010620');
247insert into students (student_id, surname, first_name, group_no)
248values (14, 'Gibson', 'Laila', 'ZMIe2014');
249insert into students (student_id, surname, first_name, date_of_birth, group_no)
250values (15, 'Holmes', 'Ben', '19900330', 'ZZIe2003');
251insert into students (student_id, surname, first_name)
252values (16, 'Powell', 'Rebecca');
253insert into students (student_id, surname, first_name, date_of_birth, group_no)
254values (17, 'Chapman', 'Grace', '19880831', 'DMZa3012');
255insert into students (student_id, surname, first_name, group_no)
256values (18, 'Mason', 'Rebecca', 'ZMIe2012');
257insert into students (student_id, surname, first_name, date_of_birth, group_no)
258values (19, 'Russell', 'Harry', '19850122', 'DZZa3001');
259insert into students (student_id, surname, first_name, date_of_birth)
260values (20, 'Foster', 'John', '19880402');
261insert into students (student_id, surname, first_name, date_of_birth, group_no)
262values (21, 'Fisher', 'Katie', '19900422', 'ZMIe2011');
263insert into students (student_id, surname, first_name, date_of_birth, group_no)
264values (22, 'Mason', 'Rebecca', '19881210', 'DMZa3012');
265insert into students (student_id, surname, first_name, date_of_birth, group_no)
266values (23, 'Mason', 'Ben', '20000718', 'ZZIe2002');
267insert into students (student_id, surname, first_name, date_of_birth, group_no)
268values (24, 'Wilkinson', 'Maria', '19910321', 'ZMIe2011');
269insert into students (student_id, surname, first_name, date_of_birth)
270values (25, 'Ellis', 'Taylor', '19880212');
271insert into students (student_id, surname, first_name, date_of_birth, group_no)
272values (26, 'Powell', 'Katie', '20010120', 'ZZIe2002');
273insert into students (student_id, surname, first_name, date_of_birth, group_no)
274values (27, 'Fisher', 'Elliot', '20010130', 'ZMIe2014');
275insert into students (student_id, surname, first_name, date_of_birth, group_no)
276values (28, 'Fisher', 'Lara', '19900221', 'ZMIe2011');
277insert into students (student_id, surname, first_name, date_of_birth, group_no)
278values (29, 'Procter', 'Laila', '19880718', 'DMIe1011');
279insert into students (student_id, surname, first_name, date_of_birth, group_no)
280values (30, 'Lancaster', 'Harry', '19870321', 'DMIe1011');
281insert into students (student_id, surname, first_name, date_of_birth, group_no)
282values (31, 'Craven', 'John', '19880221', 'DZIe1003');
283insert into students (student_id, surname, first_name, date_of_birth, group_no)
284values (32, 'Cox', 'Megan', '19900430', 'ZZIe2002');
285insert into students (student_id, surname, first_name, date_of_birth, group_no)
286values (33, 'Bowen', 'Charlie', '20000627', 'DZIe1003');
287insert into students (student_id, surname, first_name, date_of_birth, group_no)
288values (34, 'Mason', 'Gabriel', '19990422', 'DMIe1013');
289insert into students (student_id, surname, first_name, date_of_birth, group_no)
290values (35, 'Fisher', 'Elliot', '19980719', 'DMIe1013');
291set identity_insert students off;
292
293create table tuition_fees (
294payment_id int identity primary key,
295student_id int not null,
296fee_amount smallmoney not null,
297date_of_payment date not null default current_timestamp,
298constraint rtfs foreign key (student_id) references students (student_id) 
299on delete no action on update no action);
300
301set identity_insert tuition_fees on;
302insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
303values (1, 7, 2100, '20180922');
304insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
305values (2, 11, 1500, '20180922');
306insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
307values (3, 6, 600, '20180924');
308insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
309values (4, 26, 1450, '20180925');
310insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
311values (5, 31, 2300, '20181015');
312insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
313values (6, 17, 950, '20181016');
314insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
315values (7, 22, 1230, '20181019');
316insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
317values (8, 16, 1300, '20181019');
318insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
319values (9, 26, 450, '20181019');
320insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
321values (10, 6, 450, '20181019');
322insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
323values (11, 12, 1230, '20181020');
324insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
325values (12, 18, 850, '20181020');
326insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
327values (13, 7, 490, '20181020');
328insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
329values (14, 22, 890, '20181022');
330insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
331values (15, 31, 1200, '20181022');
332insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
333values (16, 25, 400, '20181023');
334insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
335values (17, 17, 1700, '20181025');
336insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
337values (18, 12, 300, '20181025');
338insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
339values (19, 16, 1500, '20181025');
340insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
341values (20, 19, 250, '20181026');
342insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
343values (21, 23, 1100, '20181026');
344insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
345values (22, 12, 450, '20181026');
346insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
347values (23, 7, 720, '20181028');
348insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
349values (24, 12, 620, '20181028');
350insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
351values (25, 22, 1830, '20181030');
352insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
353values (26, 17, 200, '20181030');
354insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
355values (27, 19, 2100, '20181030');
356insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
357values (28, 23, 1700, '20181030');
358insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
359values (29, 2, 450, '20181030');
360insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
361values (30, 5, 1100, '20181102');
362insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
363values (31, 16, 800, '20181102');
364insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
365values (32, 6, 670, '20181102');
366insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
367values (33, 16, 480, '20181102');
368insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
369values (34, 24, 920, '20181102');
370insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
371values (35, 32, 1300, '20181104');
372insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
373values (36, 16, 700, '20181108');
374insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
375values (37, 21, 1450, '20181108');
376insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
377values (38, 31, 1210, '20181109');
378insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
379values (39, 24, 200, '20181109');
380insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
381values (40, 33, 1700, '20181109');
382insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
383values (41, 11, 1600, '20181110');
384insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
385values (42, 25, 1670, '20181117');
386insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
387values (43, 4, 2100, '20181123');
388insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
389values (44, 14, 820, '20181123');
390insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
391values (45, 19, 620, '20181125');
392insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
393values (46, 21, 360, '20181127');
394insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
395values (47, 32, 600, '20181130');
396insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
397values (48, 15, 340, '20181130');
398insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
399values (49, 22, 670, '20181130');
400insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
401values (50, 16, 430, '20181212');
402insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
403values (51, 26, 1600, '20181214');
404insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
405values (52, 3, 210, '20181214');
406insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
407values (53, 16, 460, '20181214');
408insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
409values (54, 22, 580, '20181216');
410insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
411values (55, 18, 1280, '20181216');
412insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
413values (56, 21, 630, '20181219');
414insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
415values (57, 27, 1450, '20191023');
416insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
417values (58, 8, 370, '20191025');
418insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
419values (59, 1, 1300, '20191026');
420insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
421values (60, 13, 530, '20191106');
422insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
423values (61, 8, 290, '20191107');
424insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
425values (62, 22, 1350, '20191107');
426insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
427values (63, 27, 520, '20191109');
428insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
429values (64, 1, 370, '20191112');
430insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
431values (65, 22, 1320, '20191115');
432insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
433values (66, 27, 450, '20191202');
434insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
435values (67, 32, 1320, '20191206');
436insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
437values (68, 8, 1510, '20191207');
438insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
439values (69, 24, 1180, '20191209');
440insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
441values (70, 18, 670, '20191209');
442insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
443values (71, 25, 1760, '20191212');
444insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
445values (72, 17, 1350, '20191212');
446insert into tuition_fees (payment_id, student_id, fee_amount, date_of_payment)
447values (73, 27, 270, '20191212');
448set identity_insert tuition_fees off;
449
450create table modules (
451module_id int identity primary key,
452module_name varchar(50) unique not null,
453no_of_hours tinyint not null,
454lecturer_id int,
455preceding_module int references modules (module_id),
456department varchar(100) not null,
457foreign key (lecturer_id) references lecturers
458on delete no action on update no action,
459foreign key (department) references departments
460on delete no action on update cascade,
461check (no_of_hours <= 60)
462);
463
464set identity_insert modules on
465insert into modules (module_id, module_name, no_of_hours, lecturer_id, department)
466values (1, 'Economics', 30, 4, 'Department of Economics');
467insert into modules (module_id, module_name, no_of_hours, lecturer_id, department)
468values (2, 'Information Technology', 30, 2, 'Department of Informatics');
469insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
470values (3, 'Operational systems', 30, 4, 2, 'Department of Informatics');
471insert into modules (module_id, module_name, no_of_hours, department)
472values (4, 'Mathematics', 15, 'Department of Mathematics');
473insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
474values (5, 'Statistics', 30, 30, 4, 'Department of Statistics');
475insert into modules (module_id, module_name, no_of_hours, lecturer_id, department)
476values (6, 'Ancient history', 12, 26, 'Department of History');
477insert into modules (module_id, module_name, no_of_hours, department)
478values (7, 'Geography', 30, 'Department of Geography');
479insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
480values (8, 'Advanced Statistics', 9, 32, 5, 'Department of Statistics');
481insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
482values (9, 'Econometrics', 45, 25, 8, 'Department of Statistics');
483insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
484values (10, 'Computer programming', 30, 24, 3, 'Department of Informatics');
485insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
486values (11, 'Databases', 30, 2, 2, 'Department of Informatics');
487insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
488values (12, 'Mathematics II', 15, 32, 4, 'Department of Mathematics');
489insert into modules (module_id, module_name, no_of_hours, preceding_module, department)
490values (13, 'Theory of probability', 15, 12, 'Department of Mathematics');
491insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
492values (14, 'Contemporary history', 14, 21, 6, 'Department of History');
493insert into modules (module_id, module_name, no_of_hours, preceding_module, department)
494values (15, 'Advanced databases', 15, 11, 'Department of Informatics');
495insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
496values (16, 'Computer networks', 12, 15, 3, 'Department of Informatics');
497insert into modules (module_id, module_name, no_of_hours, lecturer_id, department)
498values (17, 'Foreign language', 30, 8, 'Institute of Foreign Languages');
499insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
500values (18, 'Web applications', 15, 26, 11, 'Department of Informatics');
501insert into modules (module_id, module_name, no_of_hours, lecturer_id, department)
502values (19, 'Management', 15, 29, 'Department of Management');
503insert into modules (module_id, module_name, no_of_hours, preceding_module, department)
504values (20, 'Computer programming II', 30, 10, 'Department of Informatics');
505insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
506values (21, 'Computer network devices', 10, 25, 16, 'Department of Informatics');
507insert into modules (module_id, module_name, no_of_hours, preceding_module, department)
508values (22, 'Economics II', 30, 1, 'Department of Economics');
509insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
510values (23, 'Macroeconomics', 15, 26, 1, 'Department of Economics');
511insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
512values (24, 'Microeceonomics', 30, 26, 1, 'Department of Economics');
513insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
514values (25, 'Windows server', 12, 8, 3, 'Department of Informatics');
515insert into modules (module_id, module_name, no_of_hours, lecturer_id, preceding_module, department)
516values (26, 'Windows server services', 15, 8, 25, 'Department of Informatics');
517set identity_insert modules off
518
519create table students_modules (
520student_id int not null,
521module_id int not null,
522planned_exam_date date,
523primary key (student_id, module_id),
524foreign key (module_id) references modules on delete no action,
525foreign key (student_id) references students on delete cascade
526);
527
528insert into students_modules (student_id, module_id) values(2,1);
529insert into students_modules (student_id, module_id) values(3,2);
530insert into students_modules (student_id, module_id) values(1,2);
531insert into students_modules (student_id, module_id, planned_exam_date) values(2,3, '20180321');
532insert into students_modules (student_id, module_id) values(6,4);
533insert into students_modules (student_id, module_id) values(2,4);
534insert into students_modules (student_id, module_id, planned_exam_date) values(32,12,'20180523');
535insert into students_modules (student_id, module_id, planned_exam_date) values(12,21,'20180916');
536insert into students_modules (student_id, module_id) values(26,19);
537insert into students_modules (student_id, module_id) values(29,6);
538insert into students_modules (student_id, module_id) values(12,7);
539insert into students_modules (student_id, module_id) values(12,9);
540insert into students_modules (student_id, module_id) values(21,19);
541insert into students_modules (student_id, module_id) values(3,4);
542insert into students_modules (student_id, module_id, planned_exam_date) values(3,8,'20181013');
543insert into students_modules (student_id, module_id) values(24,16);
544insert into students_modules (student_id, module_id) values(20,2);
545insert into students_modules (student_id, module_id, planned_exam_date) values(2,5,'20181023');
546insert into students_modules (student_id, module_id) values(4,12);
547insert into students_modules (student_id, module_id) values(23,18);
548insert into students_modules (student_id, module_id) values(21,6);
549insert into students_modules (student_id, module_id) values(18,19);
550insert into students_modules (student_id, module_id) values(2,9);
551insert into students_modules (student_id, module_id, planned_exam_date) values(1,14,'20181104');
552insert into students_modules (student_id, module_id) values(2,13);
553insert into students_modules (student_id, module_id) values(1,21);
554insert into students_modules (student_id, module_id) values(17,12);
555insert into students_modules (student_id, module_id, planned_exam_date) values(17,8,'20181107');
556insert into students_modules (student_id, module_id, planned_exam_date) values(19,3,'20180321');
557insert into students_modules (student_id, module_id) values(1,6);
558insert into students_modules (student_id, module_id) values(16,2);
559insert into students_modules (student_id, module_id, planned_exam_date) values(18,3,'20180321');
560insert into students_modules (student_id, module_id) values(29,15);
561insert into students_modules (student_id, module_id) values(33,4);
562insert into students_modules (student_id, module_id, planned_exam_date) values(9,3,'20180321');
563insert into students_modules (student_id, module_id) values(4,6);
564insert into students_modules (student_id, module_id) values(4,13);
565insert into students_modules (student_id, module_id) values(18,2);
566insert into students_modules (student_id, module_id) values(1,12);
567insert into students_modules (student_id, module_id) values(16,4);
568insert into students_modules (student_id, module_id) values(8,10);
569insert into students_modules (student_id, module_id) values(2,21);
570insert into students_modules (student_id, module_id) values(21,14);
571insert into students_modules (student_id, module_id) values(18,12);
572insert into students_modules (student_id, module_id) values(10,11);
573insert into students_modules (student_id, module_id) values(7,22);
574insert into students_modules (student_id, module_id) values(17,21);
575insert into students_modules (student_id, module_id) values(11,1);
576insert into students_modules (student_id, module_id) values(14,2);
577insert into students_modules (student_id, module_id) values(17,20);
578insert into students_modules (student_id, module_id, planned_exam_date) values(29,3,'20180321');
579insert into students_modules (student_id, module_id) values(26,6);
580insert into students_modules (student_id, module_id) values(26,20);
581insert into students_modules (student_id, module_id) values(24,1);
582insert into students_modules (student_id, module_id) values(26,16);
583insert into students_modules (student_id, module_id) values(8,9);
584insert into students_modules (student_id, module_id) values(8,12);
585insert into students_modules (student_id, module_id) values(9,21);
586insert into students_modules (student_id, module_id) values(9,12);
587insert into students_modules (student_id, module_id) values(12,13);
588insert into students_modules (student_id, module_id) values(15,12);
589insert into students_modules (student_id, module_id) values(15,13);
590insert into students_modules (student_id, module_id, planned_exam_date) values(14,3,'20180321');
591insert into students_modules (student_id, module_id) values(12,11);
592insert into students_modules (student_id, module_id) values(16,8);
593insert into students_modules (student_id, module_id) values(8,21);
594insert into students_modules (student_id, module_id) values(12,5);
595insert into students_modules (student_id, module_id) values(8,17);
596insert into students_modules (student_id, module_id) values(9,9);
597insert into students_modules (student_id, module_id, planned_exam_date) values(3,3,'20180321');
598insert into students_modules (student_id, module_id) values(11,19);
599insert into students_modules (student_id, module_id, planned_exam_date) values(10,3,'20180321');
600insert into students_modules (student_id, module_id) values(10,7);
601insert into students_modules (student_id, module_id) values(12,12);
602insert into students_modules (student_id, module_id) values(10,13);
603insert into students_modules (student_id, module_id) values(14,21);
604insert into students_modules (student_id, module_id) values(14,12);
605insert into students_modules (student_id, module_id) values(18,16);
606insert into students_modules (student_id, module_id) values(20,21);
607insert into students_modules (student_id, module_id) values(20,1);
608insert into students_modules (student_id, module_id) values(20,4);
609insert into students_modules (student_id, module_id, planned_exam_date) values(22,3,'20180321');
610insert into students_modules (student_id, module_id) values(32,5);
611insert into students_modules (student_id, module_id) values(32,13);
612insert into students_modules (student_id, module_id) values(2,10);
613insert into students_modules (student_id, module_id, planned_exam_date) values(21,3,'20180321');
614insert into students_modules (student_id, module_id) values(21,10);
615insert into students_modules (student_id, module_id) values(21,17);
616insert into students_modules (student_id, module_id) values(9,1);
617insert into students_modules (student_id, module_id) values(16,21);
618insert into students_modules (student_id, module_id) values(18,9);
619insert into students_modules (student_id, module_id) values(10,2);
620insert into students_modules (student_id, module_id) values(10,5);
621insert into students_modules (student_id, module_id) values(11,20);
622
623
624create table student_grades (
625student_id int,
626module_id int,
627exam_date date,
628grade decimal(2,1) not null,
629primary key (student_id, module_id, exam_date),
630foreign key (student_id, module_id) references students_modules 
631on delete cascade on update cascade,
632foreign key (grade) references grades on delete no action on update cascade
633);
634
635insert into student_grades values (2, 1, '20180221', 2);
636insert into student_grades values (1, 2, '20180221', 5);
637insert into student_grades values (3, 2, '20180221', 2);
638insert into student_grades values (3, 2, '20180223', 3);
639insert into student_grades values (2, 3, '20180223', 5);
640insert into student_grades values (2, 1, '20180223', 3.5);
641insert into student_grades values (6, 4, '20180224', 2);
642insert into student_grades values (6, 4, '20180227', 3);
643insert into student_grades values (32, 12, '20180302', 5.5);
644insert into student_grades values (26, 19, '20180302', 4);
645insert into student_grades values (29, 6, '20180306', 3.5);
646insert into student_grades values (12, 7, '20180306', 2);
647insert into student_grades values (21, 19, '20180309', 3);
648insert into student_grades values (3, 4, '20180309', 2);
649insert into student_grades values (3, 8, '20180312', 4);
650insert into student_grades values (20, 2, '20180314', 3.5);
651insert into student_grades values (12, 7, '20180316', 2);
652insert into student_grades values (12, 9, '20180316', 3.5);
653insert into student_grades values (2, 5, '20180318', 3);
654insert into student_grades values (21, 6, '20180318', 5);
655insert into student_grades values (18, 19, '20180322', 4);
656insert into student_grades values (26, 6, '20180926', 4);
657insert into student_grades values (12, 7, '20180322', 3.5);
658insert into student_grades values (2, 9, '20180322', 4.5);
659insert into student_grades values (7, 22, '20180926', 2);
660insert into student_grades values (9, 3, '20180930', 4.5);
661insert into student_grades values (1, 14, '20180322', 4);
662insert into student_grades values (1, 21, '20180322', 2);
663insert into student_grades values (17, 12, '20180920', 3.5);
664insert into student_grades values (3, 4, '20180920', 4);
665insert into student_grades values (17, 8, '20180923', 4.5);
666insert into student_grades values (19, 3, '20180923', 5);
667insert into student_grades values (18, 3, '20180923', 5.5);
668insert into student_grades values (33, 4, '20180923', 2);
669insert into student_grades values (4, 6, '20180924', 4.5);
670insert into student_grades values (18, 2, '20180924', 4.5);
671insert into student_grades values (1, 12, '20180924', 3);
672insert into student_grades values (1, 21, '20180924', 4.5);
673insert into student_grades values (18, 12, '20180924', 4);
674insert into student_grades values (10, 11, '20180925', 2);
675insert into student_grades values (29, 3, '20180925', 3);
676insert into student_grades values (8, 9, '20180926', 4.5);
677insert into student_grades values (9, 21, '20180926', 5.5);
678insert into student_grades values (12, 13, '20180926', 3.5);
679insert into student_grades values (15, 12, '20180926', 4);
680insert into student_grades values (14, 3, '20180927', 5);
681insert into student_grades values (8, 21, '20180928', 2);
682insert into student_grades values (10, 7, '20180928', 4.5);
683insert into student_grades values (10, 11, '20180928', 3.5);
684insert into student_grades values (18, 16, '20180930', 5);
685insert into student_grades values (20, 21, '20180930', 4);
686insert into student_grades values (20, 1, '20180930', 2);
687insert into student_grades values (32, 5, '20180930', 3.5);
688insert into student_grades values (2, 10, '20180930', 2);
689insert into student_grades values (21, 3, '20180930', 4.5);
690insert into student_grades values (20, 4, '20180930', 5.5);
691insert into student_grades values (7, 22, '20180930', 4);
692insert into student_grades values (26, 20, '20180930', 3);