· 8 years ago · Jun 02, 2018, 01:02 PM
1DROP TABLE IF EXISTS [studentInfo]
2
3GO
4DROP TABLE IF EXISTS [users]
5
6GO
7DROP TABLE IF EXISTS [marks]
8
9GO
10DROP TABLE IF EXISTS [warnings]
11
12GO
13DROP TABLE IF EXISTS [students]
14
15GO
16DROP TABLE IF EXISTS [parkingPlaces]
17
18GO
19DROP TABLE IF EXISTS [classesTeachers]
20
21GO
22DROP TABLE IF EXISTS [timetable]
23
24GO
25DROP TABLE IF EXISTS [subjectsTeachers]
26
27GO
28DROP TABLE IF EXISTS [ads]
29
30GO
31DROP TABLE IF EXISTS [teachers]
32
33GO
34DROP TABLE IF EXISTS [classes]
35
36GO
37DROP TABLE IF EXISTS [subjects]
38
39GO
40DROP TABLE IF EXISTS [employeeInfo]
41
42GO
43DROP TABLE IF EXISTS [employees]
44
45GO
46DROP TABLE IF EXISTS [salaries]
47
48GO
49DROP INDEX IF EXISTS [usernameAllowNull] ON [users]
50
51GO
52DROP INDEX IF EXISTS [parkingPlacesAllowNull] ON [parkingPlaces]
53
54GO
55DROP PROCEDURE IF EXISTS [addEmployee]
56
57GO
58DROP PROCEDURE IF EXISTS [addMark]
59
60GO
61DROP PROCEDURE IF EXISTS [updateMark]
62
63GO
64DROP PROCEDURE IF EXISTS [addStudent]
65
66GO
67DROP PROCEDURE IF EXISTS [updateStudent]
68
69GO
70DROP PROCEDURE IF EXISTS [promoteStudent]
71
72GO
73DROP PROCEDURE IF EXISTS [addTeacher]
74
75GO
76DROP PROCEDURE IF EXISTS [fillParking]
77
78GO
79DROP FUNCTION IF EXISTS [showStudentMarks]
80
81GO
82DROP FUNCTION IF EXISTS [showTeacherMarks]
83
84GO
85DROP FUNCTION IF EXISTS [showMarksFromSubject]
86
87GO
88DROP FUNCTION IF EXISTS [showWarnings]
89
90GO
91DROP FUNCTION IF EXISTS [showAds]
92
93GO
94DROP FUNCTION IF EXISTS [showMarksInfo]
95
96GO
97DROP FUNCTION IF EXISTS [showStudentTimetable]
98
99GO
100DROP FUNCTION IF EXISTS [showTeacherTimetable]
101
102GO
103DROP FUNCTION IF EXISTS [checkPES]
104
105GO
106DROP FUNCTION IF EXISTS [checkEmail]
107
108GO
109DROP FUNCTION IF EXISTS [showUserSettings]
110
111GO
112DROP FUNCTION IF EXISTS [showTeacherClasses]
113
114GO
115DROP FUNCTION IF EXISTS [checkTeacherSubject]
116
117GO
118DROP FUNCTION IF EXISTS [searchStudentBySurname]
119
120GO
121DROP FUNCTION IF EXISTS [searchStudentByCardNumber]
122
123GO
124DROP FUNCTION IF EXISTS [searchStudentByClassName]
125
126GO
127DROP FUNCTION IF EXISTS [searchTeacherBySurname]
128
129GO
130DROP FUNCTION IF EXISTS [searchTeacherByCardNumber]
131
132GO
133DROP FUNCTION IF EXISTS [searchTeacherBySubjectName]
134
135GO
136DROP TRIGGER IF EXISTS [addUserStudent]
137
138GO
139DROP TRIGGER IF EXISTS [addUserTeacher]
140
141GO
142DROP TRIGGER IF EXISTS [deleteStudent]
143
144GO
145DROP TRIGGER IF EXISTS [assignParkingOnInsert]
146
147GO
148DROP TRIGGER IF EXISTS [assignParkingOnDelete]
149
150GO
151----------------------------------------------------------------------------------------------------
152CREATE TABLE [studentInfo] (
153 studentInfoId INTEGER NOT NULL IDENTITY(1,1),
154 studentId INTEGER NOT NULL,
155 PES CHAR(11) NOT NULL UNIQUE,
156 dateOfBirth DATE NOT NULL,
157 street VARCHAR(30) NOT NULL,
158 houseNumber VARCHAR(10) NOT NULL,
159 postCode CHAR(6) NOT NULL,
160 city VARCHAR(30) NOT NULL,
161 email VARCHAR(50) NOT NULL UNIQUE,
162 CONSTRAINT [PK_STUDENTINFO] PRIMARY KEY CLUSTERED
163 (
164 [studentInfoId] ASC
165 ) WITH (IGNORE_DUP_KEY = OFF)
166
167)
168GO
169CREATE TABLE [students] (
170 studentId INTEGER NOT NULL IDENTITY(1,1),
171 name VARCHAR(20) NOT NULL,
172 surname VARCHAR(30) NOT NULL,
173 cardNumber CHAR(10) NOT NULL UNIQUE,
174 classId INTEGER NOT NULL,
175 CONSTRAINT [PK_STUDENTS] PRIMARY KEY CLUSTERED
176 (
177 [studentId] ASC
178 ) WITH (IGNORE_DUP_KEY = OFF)
179
180)
181GO
182CREATE TABLE [users] (
183 userId INTEGER NOT NULL IDENTITY(1,1),
184 studentId INTEGER,
185 teacherId INTEGER,
186 accountType VARCHAR(10) NOT NULL,
187 username AS 'u' + RIGHT('000' + CAST(userId AS VARCHAR(20)), 3) PERSISTED UNIQUE,
188 usernameAlias VARCHAR(30),
189 password CHAR(32) NOT NULL,
190 lastLogin DATETIME NOT NULL DEFAULT GETDATE(),
191 CONSTRAINT [PK_USERS] PRIMARY KEY CLUSTERED
192 (
193 [userId] ASC
194 ) WITH (IGNORE_DUP_KEY = OFF)
195
196)
197GO
198CREATE TABLE [teachers] (
199 teacherId INTEGER NOT NULL IDENTITY(1,1),
200 employeeId INTEGER NOT NULL,
201 cardNumber CHAR(10) NOT NULL UNIQUE,
202 CONSTRAINT [PK_TEACHERS] PRIMARY KEY CLUSTERED
203 (
204 [teacherId] ASC
205 ) WITH (IGNORE_DUP_KEY = OFF)
206
207)
208GO
209CREATE TABLE [classes] (
210 classId INTEGER NOT NULL IDENTITY(1,1),
211 name CHAR(2) NOT NULL UNIQUE,
212 CONSTRAINT [PK_CLASSES] PRIMARY KEY CLUSTERED
213 (
214 [classId] ASC
215 ) WITH (IGNORE_DUP_KEY = OFF)
216
217)
218GO
219CREATE TABLE [classesTeachers] (
220 ctId INTEGER NOT NULL IDENTITY(1,1),
221 classId INTEGER NOT NULL,
222 teacherId INTEGER,
223 subjectId INTEGER NOT NULL,
224 CONSTRAINT [PK_CLASSESTEACHERS] PRIMARY KEY CLUSTERED
225 (
226 [ctId] ASC
227 ) WITH (IGNORE_DUP_KEY = OFF)
228
229)
230GO
231CREATE TABLE [subjects] (
232 subjectId INTEGER NOT NULL IDENTITY(1,1),
233 name VARCHAR(30) NOT NULL UNIQUE,
234 CONSTRAINT [PK_SUBJECTS] PRIMARY KEY CLUSTERED
235 (
236 [subjectId] ASC
237 ) WITH (IGNORE_DUP_KEY = OFF)
238
239)
240GO
241CREATE TABLE [marks] (
242 markId INTEGER NOT NULL IDENTITY(1,1),
243 studentId INTEGER NOT NULL,
244 teacherId INTEGER NOT NULL,
245 subjectId INTEGER NOT NULL,
246 value FLOAT NOT NULL,
247 valueStr VARCHAR(2) NOT NULL,
248 markType VARCHAR(20) NOT NULL,
249 weight INTEGER NOT NULL,
250 addDate DATETIME NOT NULL DEFAULT GETDATE(),
251 semester TINYINT NOT NULL,
252 comment VARCHAR(255),
253 CONSTRAINT [PK_MARKS] PRIMARY KEY CLUSTERED
254 (
255 [markId] ASC
256 ) WITH (IGNORE_DUP_KEY = OFF)
257
258)
259GO
260CREATE TABLE [employees] (
261 employeeId INTEGER NOT NULL IDENTITY(1,1),
262 employeeType VARCHAR(30) NOT NULL,
263 name VARCHAR(20) NOT NULL,
264 surname VARCHAR(30) NOT NULL,
265 CONSTRAINT [PK_EMPLOYEES] PRIMARY KEY CLUSTERED
266 (
267 [employeeId] ASC
268 ) WITH (IGNORE_DUP_KEY = OFF)
269
270)
271GO
272CREATE TABLE [salaries] (
273 salaryId INTEGER NOT NULL IDENTITY(1,1),
274 value MONEY NOT NULL,
275 CONSTRAINT [PK_SALARIES] PRIMARY KEY CLUSTERED
276 (
277 [salaryId] ASC
278 ) WITH (IGNORE_DUP_KEY = OFF)
279
280)
281GO
282CREATE TABLE [employeeInfo] (
283 employeeInfoId INTEGER NOT NULL IDENTITY(1,1),
284 employeeId INTEGER NOT NULL,
285 salaryId INTEGER NOT NULL,
286 PES CHAR(11) NOT NULL UNIQUE,
287 dateOfBirth DATE NOT NULL,
288 street VARCHAR(30) NOT NULL,
289 houseNumber VARCHAR(10) NOT NULL,
290 postCode CHAR(6) NOT NULL,
291 city VARCHAR(30) NOT NULL,
292 email VARCHAR(50) NOT NULL,
293 CONSTRAINT [PK_EMPLOYEEINFO] PRIMARY KEY CLUSTERED
294 (
295 [employeeInfoId] ASC
296 ) WITH (IGNORE_DUP_KEY = OFF)
297
298)
299GO
300CREATE TABLE [parkingPlaces] (
301 parkingId INTEGER NOT NULL IDENTITY(1,1),
302 teacherId INTEGER DEFAULT NULL,
303 CONSTRAINT [PK_PARKINGPLACES] PRIMARY KEY CLUSTERED
304 (
305 [parkingId] ASC
306 ) WITH (IGNORE_DUP_KEY = OFF)
307
308)
309GO
310CREATE TABLE [timetable] (
311 lessonId INTEGER NOT NULL IDENTITY(1,1),
312 subjectId INTEGER NOT NULL,
313 teacherId INTEGER NOT NULL,
314 classId INTEGER NOT NULL,
315 lessonHour INTEGER NOT NULL,
316 dayString VARCHAR(12) NOT NULL,
317 classroom VARCHAR(17) NOT NULL,
318 CONSTRAINT [PK_LESSONPLAN] PRIMARY KEY CLUSTERED
319 (
320 [lessonId] ASC
321 ) WITH (IGNORE_DUP_KEY = OFF)
322
323)
324GO
325
326CREATE TABLE [warnings] (
327 warningId INTEGER NOT NULL IDENTITY(1,1),
328 studentId INTEGER NOT NULL,
329 teacherId INTEGER NOT NULL,
330 title VARCHAR(50) NOT NULL,
331 content VARCHAR(255) NOT NULL,
332 addDate DATETIME NOT NULL DEFAULT GETDATE(),
333 CONSTRAINT [PK_WARNINGS] PRIMARY KEY CLUSTERED
334 (
335 [warningId] ASC
336 ) WITH (IGNORE_DUP_KEY = OFF)
337)
338GO
339
340CREATE TABLE [ads] (
341 adId INTEGER NOT NULL IDENTITY(1,1),
342 teacherId INTEGER NOT NULL,
343 target VARCHAR(20) NOT NULL,
344 title VARCHAR(50) NOT NULL,
345 content VARCHAR(255) NOT NULL,
346 addDate DATETIME NOT NULL DEFAULT GETDATE(),
347 CONSTRAINT [PK_ADS] PRIMARY KEY CLUSTERED
348 (
349 [adId] ASC
350 ) WITH (IGNORE_DUP_KEY = OFF)
351)
352GO
353
354CREATE TABLE [subjectsTeachers] (
355 stId INTEGER NOT NULL IDENTITY(1,1),
356 subjectId INTEGER NOT NULL,
357 teacherId INTEGER NOT NULL,
358 CONSTRAINT [PK_SUBJECTSTEACHERS] PRIMARY KEY CLUSTERED
359 (
360 [stId] ASC
361 ) WITH (IGNORE_DUP_KEY = OFF)
362)
363GO
364----------------------------------------------------------------------------------------------------
365CREATE FUNCTION [checkPES] (@pes CHAR(11))
366RETURNS BIT
367AS
368BEGIN
369 IF (LEN(@pes) != 11) OR (ISNUMERIC(@pes) = 0)
370 RETURN 0
371
372 DECLARE @weights AS TABLE (numberPosition TINYINT IDENTITY(1,1), weight TINYINT)
373 INSERT INTO @weights VALUES (1), (3), (7), (9), (1), (3), (7), (9), (1), (3), (1)
374
375 IF (SELECT SUM(CONVERT(TINYINT, SUBSTRING(@pes, numberPosition, 1)) * weight) % 10 FROM @weights ) = 0
376 RETURN 1
377
378 RETURN 0
379END
380GO
381----------------------------------------------------------------------------------------------------
382CREATE FUNCTION [checkEmail] (@email VARCHAR(50))
383RETURNS BIT
384AS
385BEGIN
386 IF (@email = '' AND @email NOT LIKE '_%@__%.__%')
387 RETURN 0
388
389 RETURN 1
390END
391GO
392----------------------------------------------------------------------------------------------------
393CREATE FUNCTION [checkTeacherSubject] (@teacherId INTEGER, @subjectId INTEGER)
394RETURNS BIT
395AS
396BEGIN
397 DECLARE @subjectIdTeacher BIT
398 SELECT @subjectIdTeacher = COUNT(subjectId) FROM subjectsTeachers ST WHERE ST.teacherId = @teacherId AND ST.subjectId = @subjectId
399 RETURN @subjectIdTeacher
400END
401GO
402----------------------------------------------------------------------------------------------------
403ALTER TABLE [studentInfo] WITH CHECK ADD CONSTRAINT [studentInfo_fk0] FOREIGN KEY ([studentId]) REFERENCES [students]([studentId])
404GO
405ALTER TABLE [studentInfo] CHECK CONSTRAINT [studentInfo_fk0]
406GO
407ALTER TABLE [studentInfo] ADD CONSTRAINT [studentInfo_ch0] CHECK (dbo.checkPES(PES) = 1)
408GO
409ALTER TABLE [studentInfo] ADD CONSTRAINT [studentInfo_ch1] CHECK (dbo.checkEmail(email) = 1)
410GO
411----------------------------------------------------------------------------------------------------
412ALTER TABLE [users] WITH CHECK ADD CONSTRAINT [users_fk0] FOREIGN KEY ([studentId]) REFERENCES [students]([studentId])
413GO
414ALTER TABLE [users] CHECK CONSTRAINT [users_fk0]
415GO
416ALTER TABLE [users] WITH CHECK ADD CONSTRAINT [users_fk1] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
417GO
418ALTER TABLE [users] CHECK CONSTRAINT [users_fk1]
419GO
420----------------------------------------------------------------------------------------------------
421ALTER TABLE [teachers] WITH CHECK ADD CONSTRAINT [teachers_fk0] FOREIGN KEY ([employeeId]) REFERENCES [employees]([employeeId])
422GO
423ALTER TABLE [teachers] CHECK CONSTRAINT [teachers_fk0]
424GO
425----------------------------------------------------------------------------------------------------
426ALTER TABLE [classesTeachers] WITH CHECK ADD CONSTRAINT [classesTeachers_fk0] FOREIGN KEY ([classId]) REFERENCES [classes]([classId])
427GO
428ALTER TABLE [classesTeachers] CHECK CONSTRAINT [classesTeachers_fk0]
429GO
430ALTER TABLE [classesTeachers] WITH CHECK ADD CONSTRAINT [classesTeachers_fk1] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
431GO
432ALTER TABLE [classesTeachers] CHECK CONSTRAINT [classesTeachers_fk1]
433GO
434ALTER TABLE [classesTeachers] WITH CHECK ADD CONSTRAINT [classesTeachers_fk2] FOREIGN KEY ([subjectId]) REFERENCES [subjects]([subjectId])
435GO
436ALTER TABLE [classesTeachers] CHECK CONSTRAINT [classesTeachers_fk2]
437GO
438ALTER TABLE [classesTeachers] ADD CONSTRAINT [classesTeachers_ch0] CHECK (dbo.checkTeacherSubject(teacherId, subjectId) = 1)
439GO
440----------------------------------------------------------------------------------------------------
441ALTER TABLE [marks] WITH CHECK ADD CONSTRAINT [marks_fk0] FOREIGN KEY ([studentId]) REFERENCES [students]([studentId])
442GO
443ALTER TABLE [marks] CHECK CONSTRAINT [marks_fk0]
444GO
445ALTER TABLE [marks] WITH CHECK ADD CONSTRAINT [marks_fk1] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
446GO
447ALTER TABLE [marks] CHECK CONSTRAINT [marks_fk1]
448GO
449ALTER TABLE [marks] WITH CHECK ADD CONSTRAINT [marks_fk2] FOREIGN KEY ([subjectId]) REFERENCES [subjects]([subjectId])
450GO
451----------------------------------------------------------------------------------------------------
452ALTER TABLE [employeeInfo] WITH CHECK ADD CONSTRAINT [employeeInfo_fk0] FOREIGN KEY ([employeeId]) REFERENCES [employees]([employeeId])
453GO
454ALTER TABLE [employeeInfo] CHECK CONSTRAINT [employeeInfo_fk0]
455GO
456ALTER TABLE [employeeInfo] WITH CHECK ADD CONSTRAINT [employeeInfo_fk1] FOREIGN KEY ([salaryId]) REFERENCES [salaries]([salaryId])
457GO
458ALTER TABLE [employeeInfo] CHECK CONSTRAINT [employeeInfo_fk1]
459GO
460ALTER TABLE [employeeInfo] WITH CHECK ADD CONSTRAINT [employeeInfo_fk2] FOREIGN KEY ([salaryId]) REFERENCES [salaries]([salaryId])
461GO
462ALTER TABLE [employeeInfo] CHECK CONSTRAINT [employeeInfo_fk2]
463GO
464ALTER TABLE [employeeInfo] ADD CONSTRAINT [employeeInfo_ch0] CHECK (dbo.checkPES(PES) = 1)
465GO
466ALTER TABLE [employeeInfo] ADD CONSTRAINT [employeeInfo_ch1] CHECK (dbo.checkEmail(email) = 1)
467GO
468----------------------------------------------------------------------------------------------------
469ALTER TABLE [parkingPlaces] WITH CHECK ADD CONSTRAINT [parkingPlaces_fk0] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
470GO
471ALTER TABLE [parkingPlaces] CHECK CONSTRAINT [parkingPlaces_fk0]
472GO
473----------------------------------------------------------------------------------------------------
474ALTER TABLE [timetable] WITH CHECK ADD CONSTRAINT [timetable_fk0] FOREIGN KEY ([subjectId]) REFERENCES [subjects]([subjectId])
475GO
476ALTER TABLE [timetable] CHECK CONSTRAINT [timetable_fk0]
477GO
478ALTER TABLE [timetable] WITH CHECK ADD CONSTRAINT [timetable_fk1] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
479GO
480ALTER TABLE [timetable] CHECK CONSTRAINT [timetable_fk1]
481GO
482ALTER TABLE [timetable] WITH CHECK ADD CONSTRAINT [timetable_fk2] FOREIGN KEY ([classId]) REFERENCES [classes]([classId])
483GO
484ALTER TABLE [timetable] CHECK CONSTRAINT [timetable_fk2]
485GO
486----------------------------------------------------------------------------------------------------
487ALTER TABLE [warnings] WITH CHECK ADD CONSTRAINT [warnings_fk0] FOREIGN KEY ([studentId]) REFERENCES [students]([studentId])
488GO
489ALTER TABLE [warnings] CHECK CONSTRAINT [warnings_fk0]
490GO
491ALTER TABLE [warnings] WITH CHECK ADD CONSTRAINT [warnings_fk1] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
492GO
493ALTER TABLE [warnings] CHECK CONSTRAINT [warnings_fk1]
494----------------------------------------------------------------------------------------------------
495ALTER TABLE [ads] WITH CHECK ADD CONSTRAINT [ads_fk0] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
496GO
497ALTER TABLE [ads] CHECK CONSTRAINT [ads_fk0]
498GO
499----------------------------------------------------------------------------------------------------
500ALTER TABLE [subjectsTeachers] WITH CHECK ADD CONSTRAINT [subjectsTeachers_fk0] FOREIGN KEY ([subjectId]) REFERENCES [subjects]([subjectId])
501GO
502ALTER TABLE [subjectsTeachers] CHECK CONSTRAINT [subjectsTeachers_fk0]
503GO
504ALTER TABLE [subjectsTeachers] WITH CHECK ADD CONSTRAINT [subjectsTeachers_fk1] FOREIGN KEY ([teacherId]) REFERENCES [teachers]([teacherId])
505GO
506ALTER TABLE [subjectsTeachers] CHECK CONSTRAINT [subjectsTeachers_fk1]
507GO
508----------------------------------------------------------------------------------------------------
509CREATE UNIQUE NONCLUSTERED INDEX [usernameAllowNull]
510ON [users] (usernameAlias)
511WHERE usernameAlias IS NOT NULL
512GO
513----------------------------------------------------------------------------------------------------
514CREATE UNIQUE NONCLUSTERED INDEX [parkingPlacesAllowNull]
515ON [parkingPlaces] (teacherId)
516WHERE teacherId IS NOT NULL
517GO
518----------------------------------------------------------------------------------------------------
519CREATE PROCEDURE [addEmployee] (@name varchar(20), @surname varchar(20), @employeeType varchar(20),
520@salaryId INT, @PES CHAR(11), @dateOfBirth DATE, @street varchar(30), @houseNumber VARCHAR(10),
521@postCode CHAR(6), @city VARCHAR(30), @email varchar(50))
522AS
523DECLARE @employeeId INT
524INSERT INTO [employees] VALUES (@employeeType, @name, @surname)
525SELECT @employeeId = MAX(employeeId) FROM employees
526INSERT INTO [employeeInfo] VALUES (@employeeId, @salaryId, @PES, @dateOfBirth, @street, @houseNumber, @postCode, @city, @email)
527GO
528----------------------------------------------------------------------------------------------------
529CREATE PROCEDURE [addMark] (@studentId INTEGER, @teacherId INTEGER, @subjectId INTEGER, @valueStr VARCHAR(2), @markType VARCHAR(30), @addDate DATETIME = NULL, @comment VARCHAR(max), @weight INTEGER = 0, @value Float = 0, @semester TINYINT)
530AS
531
532IF @addDate IS NULL SET @addDate = GETDATE()
533IF @valueStr = '1' SET @value = 1
534ELSE IF @valueStr = '2-' SET @value = 1.75
535ELSE IF @valueStr = '2' SET @value = 2
536ELSE IF @valueStr = '2+' SET @value = 2.5
537ELSE IF @valueStr = '3-' SET @value = 2.75
538ELSE IF @valueStr = '3' SET @value = 3
539ELSE IF @valueStr = '3+' SET @value = 3.5
540ELSE IF @valueStr = '4-' SET @value = 3.75
541ELSE IF @valueStr = '4' SET @value = 4
542ELSE IF @valueStr = '4+' SET @value = 4.5
543ELSE IF @valueStr = '5-' SET @value = 4.75
544ELSE IF @valueStr = '5' SET @value = 5
545ELSE IF @valueStr = '6' SET @value = 6
546
547IF @markType = 'Kartkówka' SET @weight = 2
548ELSE IF @markType = 'Sprawdzian' SET @weight = 3
549ELSE IF @markType = 'Odpowiedź ustna' SET @weight = 2
550ELSE IF @markType = 'Aktywność' SET @weight = 1
551ELSE IF @markType = 'Zadanie domowe' SET @weight = 1
552ELSE IF @markType = 'Projekt' SET @weight = 3
553
554INSERT INTO [marks]
555(studentId, teacherId, subjectId, value, valueStr, markType, weight, addDate, comment, semester)
556VALUES
557(@studentId, @teacherId, @subjectId, @value, @valueStr, @markType, @weight, @addDate, @comment, @semester)
558GO
559----------------------------------------------------------------------------------------------------
560CREATE PROCEDURE [updateMark] (@markId INTEGER, @valueStr VARCHAR(2), @value Float = 0, @markType VARCHAR(30), @weight INTEGER = 0, @addDate DATETIME, @comment VARCHAR(max))
561AS
562
563IF @valueStr = '1' SET @value = 1
564ELSE IF @valueStr = '2-' SET @value = 1.75
565ELSE IF @valueStr = '2' SET @value = 2
566ELSE IF @valueStr = '2+' SET @value = 2.5
567ELSE IF @valueStr = '3-' SET @value = 2.75
568ELSE IF @valueStr = '3' SET @value = 3
569ELSE IF @valueStr = '3+' SET @value = 3.5
570ELSE IF @valueStr = '4-' SET @value = 3.75
571ELSE IF @valueStr = '4' SET @value = 4
572ELSE IF @valueStr = '4+' SET @value = 4.5
573ELSE IF @valueStr = '5-' SET @value = 4.75
574ELSE IF @valueStr = '5' SET @value = 5
575ELSE IF @valueStr = '6' SET @value = 6
576
577IF @markType = 'Kartkówka' SET @weight = 2
578ELSE IF @markType = 'Sprawdzian' SET @weight = 3
579ELSE IF @markType = 'Odpowiedź ustna' SET @weight = 2
580ELSE IF @markType = 'Aktywność' SET @weight = 1
581ELSE IF @markType = 'Zadanie domowe' SET @weight = 1
582ELSE IF @markType = 'Projekt' SET @weight = 3
583
584UPDATE [marks]
585SET valueStr = @valueStr, value = @value, markType = @markType, weight = @weight, addDate = @addDate, comment = @comment
586WHERE markId = @markId
587GO
588----------------------------------------------------------------------------------------------------
589CREATE PROCEDURE [addStudent] (@name VARCHAR(20), @surname VARCHAR(30), @PES CHAR(11), @cardNumber CHAR(10), @dateOfBirth DATE, @street VARCHAR(30), @houseNumber VARCHAR(10), @postCode CHAR(6), @city VARCHAR(30), @email VARCHAR(50), @className VARCHAR(2))
590AS
591
592DECLARE @classId INTEGER
593DECLARE @studentId INTEGER
594
595SELECT @classId = classId FROM classes WHERE name = @className
596
597INSERT INTO [students] VALUES (@name, @surname, @cardNumber, @classId)
598SELECT @studentId = MAX(studentId) FROM students
599INSERT INTO [studentInfo] VALUES (@studentId, @PES, @dateOfBirth, @street, @houseNumber, @postCode, @city, @email)
600GO
601----------------------------------------------------------------------------------------------------
602CREATE PROCEDURE [updateStudent] (@studentId INTEGER, @name VARCHAR(20), @surname VARCHAR(30), @PES CHAR (11), @cardNumber CHAR(10), @dateOfBirth DATE, @street VARCHAR(30), @houseNumber VARCHAR(10), @postCode CHAR(6), @city VARCHAR(30), @email VARCHAR(50), @className VARCHAR(2))
603AS
604
605DECLARE @classId INTEGER
606SELECT @classId = classId FROM classes WHERE name = @className
607
608UPDATE [students] SET name = @name, surname = @surname, cardNumber = @cardNumber, classId = @classId WHERE studentId = @studentId
609UPDATE [studentInfo] SET PES = @PES, dateOfBirth = @dateOfBirth, street = @street, houseNumber = @houseNumber, postCode = @postCode, city = @city, email = @email WHERE studentId = @studentId
610GO
611----------------------------------------------------------------------------------------------------
612CREATE PROCEDURE [promoteStudent] (@studentId INTEGER)
613AS
614
615DECLARE @classNumber CHAR(1)
616DECLARE @classChar CHAR(1)
617DECLARE @className CHAR(2)
618DECLARE @classId INTEGER
619
620SELECT @classNumber = SUBSTRING((SELECT C.name FROM classes C JOIN students S ON S.classId = C.classId WHERE S.studentId = @studentId), 1, 1) + 1
621SELECT @classChar = SUBSTRING((SELECT C.name FROM classes C JOIN students S ON S.classId = C.classId WHERE S.studentId = @studentId), 2, 1)
622SET @className = @classNumber + @classChar
623SELECT @classId = classId FROM classes WHERE name = @className
624
625BEGIN TRANSACTION
626BEGIN TRY
627 UPDATE [students] SET [classId] = @classId WHERE studentId = @studentId
628 COMMIT
629END TRY
630BEGIN CATCH
631 RAISERROR('Uczen nie moze dostac promocji (nie ma juz wyzszej klasy). Mozna juz go tylko usunac.', 16, 1)
632 ROLLBACK
633END CATCH
634GO
635----------------------------------------------------------------------------------------------------
636CREATE PROCEDURE [addTeacher] (@name VARCHAR(20), @surname VARCHAR(30), @salary MONEY, @cardNumber CHAR(10), @PES CHAR(11), @dateOfBirth DATE, @street VARCHAR(30), @houseNumber VARCHAR(10), @postCode CHAR(6), @city VARCHAR(30), @email VARCHAR(50))
637AS
638
639DECLARE @salaryId INTEGER
640DECLARE @employeeId INTEGER
641
642SELECT @salaryId = salaryId FROM salaries WHERE value = @salary
643
644INSERT INTO [employees] VALUES ('Nauczyciel', @name, @surname)
645
646SELECT @employeeId = MAX(employeeId) FROM employees
647
648INSERT INTO [employeeInfo] VALUES (@employeeId, @salaryId, @PES, @dateOfBirth, @street, @houseNumber, @postCode, @city, @email)
649INSERT INTO [teachers] VALUES (@employeeId, @cardNumber)
650GO
651----------------------------------------------------------------------------------------------------
652CREATE PROCEDURE [fillParking] (@amount INTEGER)
653AS
654
655DECLARE @i INTEGER = 0
656
657WHILE @i < @amount
658BEGIN
659 INSERT INTO [parkingPlaces] (parkingId) VALUES (@i + 1)
660 SET @i = @i + 1
661END
662GO
663----------------------------------------------------------------------------------------------------
664CREATE FUNCTION [showUserSettings] (@userId INTEGER)
665RETURNS TABLE
666AS
667RETURN
668 SELECT U.userId, U.username, U.usernameAlias, U.accountType
669 FROM users U
670 WHERE U.userId = @userId
671GO
672----------------------------------------------------------------------------------------------------
673CREATE FUNCTION [showStudentMarks] (@studentId INTEGER, @semester TINYINT)
674RETURNS TABLE
675AS
676RETURN
677 SELECT S.name AS subjectName, ROUND(Sum(M.value * M.weight)/SUM(M.weight),2) AS average,
678 STUFF((SELECT ' ' + M.valueStr
679 FROM marks M
680 JOIN students ST ON ST.studentId = M.studentId
681 WHERE M.subjectId = S.subjectId AND ST.studentId = @studentId AND M.semester = @semester
682 FOR XML PATH('')),1,1,'') [marks]
683 FROM subjects S
684 JOIN marks M ON M.subjectId = S.subjectId
685 JOIN students St ON St.studentId = M.studentId WHERE St.studentId = @studentId AND M.semester = @semester
686 GROUP BY S.name, S.subjectId
687GO
688----------------------------------------------------------------------------------------------------
689CREATE FUNCTION [showTeacherMarks] (@subjectName VARCHAR(20), @className VARCHAR(2), @semester TINYINT)
690RETURNS TABLE
691AS
692RETURN
693 SELECT S.name + ' ' + S.surname AS student, S.studentId, Su.subjectId, Su.name AS subjectName, ROUND(SUM(M.value * M.weight)/SUM(M.weight),2) AS average,
694 STUFF((SELECT ' ' + M.valueStr
695 FROM marks M
696 JOIN subjects Su ON Su.subjectId = M.subjectId
697 WHERE S.studentId = M.studentId AND Su.name = @subjectName AND M.semester = @semester
698 FOR XML PATH('')),1,1,'') [marks]
699 FROM students S
700 JOIN marks M ON M.studentId = S.studentId
701 JOIN subjects Su ON Su.subjectId = M.subjectId
702 JOIN classes C ON C.classId = S.classId
703 WHERE Su.name = @subjectName AND C.name = @className AND M.semester = @semester
704 GROUP BY S.name, S.surname, S.studentId, Su.name, Su.subjectId
705GO
706----------------------------------------------------------------------------------------------------
707CREATE FUNCTION [showMarksFromSubject] (@studentId INTEGER, @subjectName VARCHAR(30), @semester TINYINT)
708RETURNS TABLE
709AS RETURN
710 SELECT M.valueStr, M.weight, M.markType, M.markId, FORMAT(M.addDate, 'dd.MM.yyyy HH:mm:ss') AS addDate, M.comment
711 FROM marks M
712 JOIN subjects S ON S.subjectId = M.subjectId
713 WHERE M.studentId = @studentId AND S.name = @subjectName AND M.semester = @semester
714GO
715----------------------------------------------------------------------------------------------------
716CREATE FUNCTION [showWarnings] (@studentId INTEGER)
717RETURNS TABLE
718AS
719RETURN
720 SELECT W.warningId, W.title, W.content, FORMAT(W.addDate, 'dd.MM.yyyy HH:mm:ss') AS addDate, E.name + ' ' + E.surname AS teacher
721 FROM warnings W
722 JOIN teachers T ON T.teacherId = W.teacherId
723 JOIN employees E ON E.employeeId = T.employeeId
724 WHERE W.studentId = @studentId
725GO
726----------------------------------------------------------------------------------------------------
727CREATE FUNCTION [showAds] (@target VARCHAR(20), @target2 VARCHAR(20))
728RETURNS TABLE
729AS
730RETURN
731 SELECT A.adId, A.title, A.content, FORMAT(A.addDate, 'dd.MM.yyyy HH:mm:ss') AS addDate, E.name + ' ' + E.surname AS teacher
732 FROM ads A
733 JOIN teachers T ON T.teacherId = A.teacherId
734 JOIN employees E ON E.employeeID = T.employeeId
735 WHERE A.target = @target OR A.target = @target2
736GO
737----------------------------------------------------------------------------------------------------
738CREATE FUNCTION [showStudentTimetable] (@studentId INTEGER, @dayString VARCHAR(12))
739RETURNS TABLE
740AS
741RETURN
742 SELECT SU.name AS subjectName, E.name + ' ' + E.surname AS teacher, T.classroom
743 FROM timetable T
744 JOIN subjects SU ON SU.subjectId = T.subjectId
745 JOIN teachers TE ON TE.teacherId = T.teacherId
746 JOIN employees E ON E.employeeId = TE.employeeId
747 JOIN students S ON S.classId = T.classId
748 WHERE S.studentId = @studentId AND T.dayString = @dayString
749GO
750----------------------------------------------------------------------------------------------------
751CREATE FUNCTION [showTeacherTimetable] (@teacherId INTEGER, @dayString VARCHAR(12))
752RETURNS TABLE
753AS
754RETURN
755 SELECT SU.name AS subjectName, C.name AS className, T.classroom
756 FROM timetable T
757 JOIN subjects SU ON SU.subjectId = T.subjectId
758 JOIN teachers TE ON TE.teacherId = T.teacherId
759 JOIN classes C ON C.classId = T.classId
760 WHERE TE.teacherId = @teacherId AND T.dayString = @dayString
761GO
762----------------------------------------------------------------------------------------------------
763CREATE FUNCTION [showTeacherClasses] (@teacherId INTEGER)
764RETURNS TABLE
765AS
766RETURN
767 SELECT C.name AS className, S.name AS subjectName
768 FROM classes C
769 JOIN classesTeachers CT ON C.classId = CT.classId
770 JOIN subjects S ON S.subjectId = CT.subjectId
771 WHERE CT.teacherId = @teacherId
772 GROUP BY C.name, S.name
773GO
774----------------------------------------------------------------------------------------------------
775CREATE FUNCTION [searchStudentBySurname] (@surname VARCHAR(30))
776RETURNS TABLE
777AS
778RETURN
779 SELECT DISTINCT S.*, SI.street, SI.houseNumber, SI.postCode, SI.city, SI.street + ' ' + SI.houseNumber + ' ' + SI.postCode + ' ' + ' ' + SI.city AS address2, SI.PES, FORMAT(SI.dateOfBirth, 'dd.MM.yyyy') AS dateOfBirth, SI.email, C.name AS className
780 FROM students S
781 JOIN studentInfo SI ON S.studentId = SI.studentId
782 JOIN classes C ON C.classId = S.classId
783 WHERE S.surname LIKE @surname
784GO
785----------------------------------------------------------------------------------------------------
786CREATE FUNCTION [searchStudentByCardNumber] (@cardNumber CHAR(10))
787RETURNS TABLE
788AS
789RETURN
790 SELECT DISTINCT S.*, SI.street, SI.houseNumber, SI.postCode, SI.city, SI.street + ' ' + SI.houseNumber + ' ' + SI.postCode + ' ' + ' ' + SI.city AS address2, SI.PES, FORMAT(SI.dateOfBirth, 'dd.MM.yyyy') AS dateOfBirth, SI.email, C.name AS className
791 FROM students S
792 JOIN studentInfo SI ON S.studentId = SI.studentId
793 JOIN classes C ON C.classId = S.classId
794 WHERE S.cardNumber LIKE @cardNumber
795GO
796----------------------------------------------------------------------------------------------------
797CREATE FUNCTION [searchStudentByClassName] (@className VARCHAR(2))
798RETURNS TABLE
799AS
800RETURN
801 SELECT DISTINCT S.*, SI.street, SI.houseNumber, SI.postCode, SI.city, SI.street + ' ' + SI.houseNumber + ' ' + SI.postCode + ' ' + ' ' + SI.city AS address2, SI.PES, FORMAT(SI.dateOfBirth, 'dd.MM.yyyy') AS dateOfBirth, SI.email, C.name AS className
802 FROM students S
803 JOIN studentInfo SI ON S.studentId = SI.studentId
804 JOIN classes C ON C.classId = S.classId
805 WHERE C.name = @className
806GO
807----------------------------------------------------------------------------------------------------
808CREATE FUNCTION [searchTeacherBySurname] (@surname VARCHAR(30))
809RETURNS TABLE
810AS
811RETURN
812 SELECT DISTINCT T.cardNumber, S.value AS salary, E.*, EI.street, EI.houseNumber, EI.postCode, EI.city, EI.street + ' ' + EI.houseNumber + ' ' + EI.postCode + ' ' + ' ' + EI.city AS address2,
813 EI.PES, FORMAT(EI.dateOfBirth, 'dd.MM.yyyy') AS dateOfBirth, EI.email
814 FROM employees E
815 JOIN employeeInfo EI ON EI.employeeId = E.employeeId
816 JOIN salaries S ON S.salaryId = EI.salaryId
817 JOIN teachers T ON T.employeeId = E.employeeId
818 WHERE E.surname LIKE @surname
819GO
820----------------------------------------------------------------------------------------------------
821CREATE FUNCTION [searchTeacherByCardNumber] (@cardNumber CHAR(10))
822RETURNS TABLE
823AS
824RETURN
825 SELECT DISTINCT T.cardNumber, S.value AS salary, E.*, EI.street, EI.houseNumber, EI.postCode, EI.city, EI.street + ' ' + EI.houseNumber + ' ' + EI.postCode + ' ' + ' ' + EI.city AS address2,
826 EI.PES, FORMAT(EI.dateOfBirth, 'dd.MM.yyyy') AS dateOfBirth, EI.email
827 FROM employees E
828 JOIN employeeInfo EI ON EI.employeeId = E.employeeId
829 JOIN salaries S ON S.salaryId = EI.salaryId
830 JOIN teachers T ON T.employeeId = E.employeeId
831 WHERE T.cardNumber = @cardNumber
832GO
833----------------------------------------------------------------------------------------------------
834CREATE FUNCTION [searchTeacherBySubjectName] (@subjectName VARCHAR(20))
835RETURNS TABLE
836AS
837RETURN
838 SELECT DISTINCT T.cardNumber, S.value AS salary, E.*, EI.street, EI.houseNumber, EI.postCode, EI.city, EI.street + ' ' + EI.houseNumber + ' ' + EI.postCode + ' ' + ' ' + EI.city AS address2,
839 EI.PES, FORMAT(EI.dateOfBirth, 'dd.MM.yyyy') AS dateOfBirth, EI.email
840 FROM employees E
841 JOIN employeeInfo EI ON EI.employeeId = E.employeeId
842 JOIN salaries S ON S.salaryId = EI.salaryId
843 JOIN teachers T ON T.employeeId = E.employeeId
844 JOIN subjectsTeachers ST ON ST.teacherId = T.teacherId
845 JOIN subjects Su ON Su.subjectId = ST.subjectId
846 WHERE SU.name = @subjectName
847GO
848----------------------------------------------------------------------------------------------------
849CREATE TRIGGER [addUserStudent] ON [students] AFTER INSERT
850AS
851BEGIN
852 DECLARE @studentId INTEGER
853 DECLARE @login VARCHAR(20)
854 DECLARE @password CHAR(32)
855 DECLARE @decryptedPassword CHAR(15)
856
857 SELECT @decryptedPassword = CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
858 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
859 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
860 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
861 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
862 SELECT @password = LOWER(CONVERT(CHAR(32), HashBytes('MD5', @decryptedPassword), 2))
863 SELECT @studentId = MAX(studentId) FROM students
864
865 INSERT INTO [users] (studentId, teacherId, accountType, password) VALUES (@studentId, NULL, 'Uczen', @password)
866END
867GO
868----------------------------------------------------------------------------------------------------
869CREATE TRIGGER [addUserTeacher] ON [teachers] AFTER INSERT
870AS
871BEGIN
872 DECLARE @teacherId INTEGER
873 DECLARE @login VARCHAR(20)
874 DECLARE @password CHAR(32)
875 DECLARE @decryptedPassword CHAR(15)
876
877 SELECT @decryptedPassword = CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
878 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
879 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
880 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
881 + CHAR(RAND()*26+65) + CHAR(RAND()*26+65) + CHAR(RAND()*26+65)
882 SELECT @password = LOWER(CONVERT(CHAR(32), HashBytes('MD5', @decryptedPassword), 2))
883 SELECT @teacherId = MAX(teacherId) FROM teachers
884
885 INSERT INTO [users] (studentId, teacherId, accountType, password) VALUES (NULL, @teacherId, 'Nauczyciel', @password)
886END
887GO
888----------------------------------------------------------------------------------------------------
889CREATE TRIGGER [deleteStudent] ON [studentInfo] AFTER DELETE
890AS
891BEGIN
892 DELETE FROM [users] WHERE studentId IN (SELECT deleted.studentId FROM deleted)
893 DELETE FROM [marks] WHERE studentId IN (SELECT deleted.studentId FROM deleted)
894 DELETE FROM [warnings] WHERE studentId IN (SELECT deleted.studentId FROM deleted)
895 DELETE FROM [students] WHERE studentId IN (SELECT deleted.studentId FROM deleted)
896END
897GO
898----------------------------------------------------------------------------------------------------
899CREATE TRIGGER [assignParkingOnInsert] ON [teachers] AFTER INSERT
900AS
901BEGIN
902 DECLARE @teacherId INTEGER
903 DECLARE @i INTEGER = 1
904 DECLARE @amount INTEGER
905 SELECT @teacherId = MAX(teacherId) FROM teachers
906 SELECT @amount = COUNT(*) FROM parkingPlaces
907 BEGIN TRANSACTION
908 WHILE @i < @amount
909 BEGIN
910 IF (SELECT COUNT(teacherId) FROM parkingPlaces WHERE parkingId = @i) = 0
911 BEGIN
912 BEGIN TRY
913 UPDATE [parkingPlaces] SET teacherId = @teacherId WHERE parkingId = @i
914 COMMIT
915 END TRY
916 BEGIN CATCH
917 RAISERROR('Nie ma dostepnego miejsca parkingowego.', 16, 1)
918 ROLLBACK
919 END CATCH
920 BREAK
921 END
922 SET @i = @i + 1
923 END
924END
925GO
926----------------------------------------------------------------------------------------------------
927CREATE TRIGGER [assignParkingOnDelete] ON [teachers] AFTER DELETE
928AS
929BEGIN
930 DECLARE @teacherId INTEGER
931 DECLARE @parkingId INTEGER
932 SELECT @teacherId = MIN(teacherId) FROM teachers WHERE NOT EXISTS (SELECT teacherId FROM parkingPlaces)
933 SELECT @parkingId = MIN(parkingId) FROM parkingPlaces WHERE teacherId IS NULL
934 UPDATE [parkingPlaces] SET teacherId = @teacherId WHERE parkingId = @parkingId
935END
936GO
937----------------------------------------------------------------------------------------------------
938SET IDENTITY_INSERT [parkingPlaces] ON;
939EXECUTE fillParking @amount = 10;
940SET IDENTITY_INSERT [parkingPlaces] OFF;
941----------------------------------------------------------------------------------------------------
942INSERT INTO [salaries] VALUES (2000), (2300), (2500), (2700), (3000), (3200), (3500), (3700), (4000), (4200), (4500);
943----------------------------------------------------------------------------------------------------
944INSERT INTO [classes] VALUES ('1a'), ('1b'), ('1c'), ('2a'), ('2b'), ('2c'), ('3a'), ('3b'), ('3c');
945----------------------------------------------------------------------------------------------------
946INSERT INTO [subjects] VALUES ('Matematyka'), ('Jezyk polski'), ('Fizyka'), ('Historia'), ('Jezyk angielski'), ('Wychowanie fizyczne'), ('Jezyk niemiecki'), ('Jezyk francuski'),
947('Biologia'), ('Chemia'), ('Geografia'), ('Informatyka'), ('Wiedza o spoleczenstwie'), ('Podstawy przedsiebiorczosci'), ('Przyroda');
948----------------------------------------------------------------------------------------------------
949EXECUTE addTeacher @name = 'Jerzy', @surname = 'Middle', @salary = 4000, @cardNumber = 'ASDFG12345', @PES = '73092734713', @dateOfBirth = '27/09/1973', @street = 'Matematyczna', @houseNumber = '3/14',
950@postCode = '87-321', @city = 'Macierzowo', @email = 'jerzymiddle@email.pl';
951EXECUTE addTeacher @name = 'Paweł', @surname = 'Pablito', @salary = 4000, @cardNumber = 'SZXCV87642', @PES = '70021234216', @dateOfBirth = '12/02/1970', @street = 'Piłsudskiego', @houseNumber = '21',
952@postCode = '21-987', @city = 'Wrocław', @email = 'pawelpablito@email.pl';
953EXECUTE addTeacher @name = 'Ka', @surname = 'Åukasz', @salary = 2000, @cardNumber = 'POIUY12345', @PES = '77061007615', @dateOfBirth = '10/06/1977', @street = 'Warzywna', @houseNumber = '15',
954@postCode = '24-213', @city = 'Gdańsk', @email = 'kalukasze@email.pl';
955EXECUTE addTeacher @name = 'Andrzej', @surname = 'Szydłowski', @salary = 2500, @cardNumber = 'GHSDD12312', @PES = '58101141317', @dateOfBirth = '11/10/1958', @street = 'Spacerowa', @houseNumber = '98',
956@postCode = '85-230', @city = 'Kartuzy', @email = 'andrzejszydlowski@email.pl';
957EXECUTE addTeacher @name = 'Henryk', @surname = 'Grzejnik', @salary = 3000, @cardNumber = 'KJNMX12111', @PES = '80071797513', @dateOfBirth = '17/07/1980', @street = 'Szekspira', @houseNumber = '76/12',
958@postCode = '15-250', @city = 'Gdynia', @email = 'henrykgrzejnik@email.pl';
959----------------------------------------------------------------------------------------------------
960INSERT INTO [subjectsTeachers] VALUES (1,1), (1,2), (2,1), (2,2), (3,3), (4,4), (4,5);
961----------------------------------------------------------------------------------------------------
962INSERT INTO [classesTeachers] VALUES (1,1,1), (1,1,2), (9,1,2);
963----------------------------------------------------------------------------------------------------
964EXECUTE addStudent @name = 'Piter', @surname = 'Rootkowski', @PES = '94053078901', @cardNumber = 'XLDOJLD765', @dateOfBirth = '30/05/1994', @street = 'Inzynierska',
965@houseNumber = '5', @postCode = '82-451', @city = 'Olszyn', @email = 'piterrot@email.pl', @className = '1a';
966EXECUTE addStudent @name = 'Marcin', @surname = 'Ruszniekiewiecz', @PES = '97031778901', @cardNumber = 'XLDOJLD321', @dateOfBirth = '17/03/1997', @street = 'Javowska',
967@houseNumber = '23', @postCode = '53-211', @city = 'Szusz', @email = 'marcinekrusz@email.pl', @className = '1a';
968EXECUTE addStudent @name = 'Bartosz', @surname = 'Wisnia', @PES = '94100790312', @cardNumber = 'XLDEJLD765', @dateOfBirth = '07/10/1994', @street = 'Pizzowska',
969@houseNumber = '21', @postCode = '72-321', @city = 'Nowe Miasto', @email = 'wisnia@email.pl', @className = '1a';
970EXECUTE addStudent @name = 'Mati', @surname = 'Brezowski', @PES = '94060686311', @cardNumber = 'ALDOJLD765', @dateOfBirth = '06/06/1994', @street = 'Spacerowa',
971@houseNumber = '10', @postCode = '83-340', @city = 'Sierakowice', @email = 'breza@email.pl', @className = '1a';
972EXECUTE addStudent @name = 'Roman', @surname = 'Wieżba', @PES = '00261001514', @cardNumber = 'XLDOJLD997', @dateOfBirth = '10/06/2000', @street = 'Osiemnasta',
973@houseNumber = '18', @postCode = '18-181', @city = 'Gdansk', @email = 'wiezba@email.pl', @className = '2b';
974EXECUTE addStudent @name = 'Kajeton', @surname = 'Paszczon', @PES = '96091879113', @cardNumber = 'XLDOJLP722', @dateOfBirth = '18/09/1996', @street = 'Kozacka',
975@houseNumber = '2', @postCode = '85-320', @city = 'Gdynia', @email = 'paszczon@email.pl', @className = '2b';
976EXECUTE addStudent @name = 'Jakub', @surname = 'Silded', @PES = '97073052810', @cardNumber = 'XLDOJLP999', @dateOfBirth = '30/07/1997', @street = 'Spacerowa',
977@houseNumber = '4', @postCode = '83-340', @city = 'Sierakowice', @email = 'silded@email.pl', @className = '3c';
978EXECUTE addStudent @name = 'Marjusz', @surname = 'Żepa', @PES = '97022091718', @cardNumber = 'XLDOJXD222', @dateOfBirth = '20/02/1997', @street = 'Wojska Polskiego',
979@houseNumber = '34', @postCode = '83-340', @city = 'Sierakowice', @email = 'zepa@email.pl', @className = '3c';
980----------------------------------------------------------------------------------------------------
981DELETE FROM [users] WHERE studentId = 1;
982DELETE FROM [users] WHERE teacherId = 1;
983INSERT INTO [users] (studentId, teacherId, accountType, usernameAlias, password) VALUES (1, NULL, 'Uczen', 'uczentest', '912ec803b2ce49e4a541068d495ab570');
984INSERT INTO [users] (studentId, teacherId, accountType, usernameAlias, password) VALUES (NULL, 1, 'Nauczyciel', 'nauczycieltest', 'd16fb36f0911f878998c136191af705e');
985----------------------------------------------------------------------------------------------------
986INSERT INTO [warnings] (studentId, teacherId, title, content) VALUES (1, 1, 'JD', 'Nieprawilne zachowania podczas lekcji');
987INSERT INTO [warnings] (studentId, teacherId, title, content) VALUES (1, 1, 'XD', 'Nie da sie wygrac w lige jak cos');
988----------------------------------------------------------------------------------------------------
989INSERT INTO [ads] (teacherId, target, title, content) VALUES (1, 'Uczniowie', 'U', 'XDDsadasdasdasd')
990INSERT INTO [ads] (teacherId, target, title, content) VALUES (2, 'Nauczyciele', 'N', 'XDDsadasdasdasd')
991INSERT INTO [ads] (teacherId, target, title, content) VALUES (3, 'Wszyscy', 'W', 'XDDsadasdasdasd')
992----------------------------------------------------------------------------------------------------
993EXECUTE addMark @studentId = 1, @teacherId = 1, @subjectId = 1, @valueStr = '4', @markType = 'Sprawdzian', @comment = 'XD', @semester = 1;
994EXECUTE addMark @studentId = 1, @teacherId = 1, @subjectId = 1, @valueStr = '3+', @markType = 'Aktywność', @comment = 'XD', @semester = 1;
995EXECUTE addMark @studentId = 3, @teacherId = 1, @subjectId = 1, @valueStr = '4', @markType = 'Sprawdzian', @comment = 'XD', @semester = 1;
996EXECUTE addMark @studentId = 4, @teacherId = 1, @subjectId = 1, @valueStr = '3+', @markType = 'Aktywność', @comment = 'XD', @semester = 1;
997EXECUTE addMark @studentId = 1, @teacherId = 1, @subjectId = 2, @valueStr = '4-', @markType = 'Zadanie domowe', @comment = 'XD', @semester = 1;
998EXECUTE addMark @studentId = 3, @teacherId = 5, @subjectId = 3, @valueStr = '4+', @markType = 'Sprawdzian', @comment = 'XD', @semester = 1;
999EXECUTE addMark @studentId = 3, @teacherId = 4, @subjectId = 4, @valueStr = '2-', @markType = 'Aktywność', @comment = 'XD', @semester = 1;
1000EXECUTE addMark @studentId = 4, @teacherId = 5, @subjectId = 1, @valueStr = '3', @markType = 'Kartkówka', @comment = 'XD', @semester = 1;
1001EXECUTE addMark @studentId = 4, @teacherId = 4, @subjectId = 4, @valueStr = '6', @markType = 'Sprawdzian', @comment = 'XD', @semester = 1;
1002EXECUTE addMark @studentId = 5, @teacherId = 1, @subjectId = 1, @valueStr = '5-', @markType = 'Zadanie domowe', @comment = 'XD', @semester = 1;
1003EXECUTE addMark @studentId = 5, @teacherId = 4, @subjectId = 4, @valueStr = '5', @markType = 'Sprawdzian', @comment = 'XD', @semester = 1;
1004EXECUTE addMark @studentId = 6, @teacherId = 4, @subjectId = 4, @valueStr = '3+', @markType = 'Aktywność', @comment = 'XD', @semester = 1;
1005EXECUTE addMark @studentId = 6, @teacherId = 1, @subjectId = 1, @valueStr = '1', @markType = 'Projekt', @comment = 'XD', @semester = 2;
1006EXECUTE addMark @studentId = 1, @teacherId = 1, @subjectId = 1, @valueStr = '2', @markType = 'Sprawdzian', @comment = 'XD', @semester = 2;
1007EXECUTE addMark @studentId = 1, @teacherId = 1, @subjectId = 1, @valueStr = '3', @markType = 'Aktywność', @comment = 'XD', @semester = 2;
1008EXECUTE addMark @studentId = 3, @teacherId = 1, @subjectId = 1, @valueStr = '4', @markType = 'Sprawdzian', @comment = 'XD', @semester = 2;
1009EXECUTE addMark @studentId = 4, @teacherId = 1, @subjectId = 1, @valueStr = '3+', @markType = 'Aktywność', @comment = 'XD', @semester = 2;
1010EXECUTE addMark @studentId = 1, @teacherId = 1, @subjectId = 2, @valueStr = '4-', @markType = 'Zadanie domowe', @comment = 'XD', @semester = 2;
1011EXECUTE addMark @studentId = 3, @teacherId = 5, @subjectId = 3, @valueStr = '4+', @markType = 'Sprawdzian', @comment = 'XD', @semester = 2;
1012EXECUTE addMark @studentId = 3, @teacherId = 4, @subjectId = 4, @valueStr = '2-', @markType = 'Aktywność', @comment = 'XD', @semester = 2;
1013EXECUTE addMark @studentId = 4, @teacherId = 5, @subjectId = 1, @valueStr = '3', @markType = 'Kartkówka', @comment = 'XD', @semester = 2;
1014EXECUTE addMark @studentId = 4, @teacherId = 4, @subjectId = 4, @valueStr = '6', @markType = 'Sprawdzian', @comment = 'XD', @semester = 2;
1015EXECUTE addMark @studentId = 5, @teacherId = 1, @subjectId = 1, @valueStr = '5-', @markType = 'Zadanie domowe', @comment = 'XD', @semester = 2;
1016EXECUTE addMark @studentId = 5, @teacherId = 4, @subjectId = 4, @valueStr = '5', @markType = 'Sprawdzian', @comment = 'XD', @semester = 2;
1017EXECUTE addMark @studentId = 6, @teacherId = 4, @subjectId = 4, @valueStr = '3+', @markType = 'Aktywność', @comment = 'XD', @semester = 2;
1018EXECUTE addMark @studentId = 6, @teacherId = 1, @subjectId = 1, @valueStr = '4', @markType = 'Projekt', @comment = 'XD', @semester = 2;
1019----------------------------------------------------------------------------------------------------
1020INSERT INTO [timetable] VALUES (1, 1, 1, 1, 'Poniedziałek', 'Sala 112'),
1021(1, 1, 1, 2, 'Poniedziałek', 'Sala 112'), (3, 3, 1, 3, 'Poniedziałek', 'Sala 231'),
1022(1, 1, 1, 1, 'Poniedziałek', 'Sala 112'), (1, 1, 1, 2, 'Wtorek', 'Sala 112'),
1023(3, 3, 1, 3, 'Wtorek', 'Sala 231'), (1, 1, 1, 4, 'Wtorek', 'Sala 112'),
1024(3, 3, 1, 5, 'Wtorek', 'Sala 231'), (1, 1, 1, 6, 'Wtorek', 'Sala 112'),
1025(1, 1, 1, 1, 'Sroda', 'Sala 112'), (1, 1, 1, 2, 'Sroda', 'Sala 112'),
1026(3, 3, 1, 3, 'Sroda', 'Sala 231'), (1, 1, 1, 1, 'Czwartek', 'Sala 112'),
1027(3, 3, 1, 2, 'Czwartek', 'Sala 231'), (1, 1, 1, 3, 'Czwartek', 'Sala 112'),
1028(3, 3, 1, 4, 'Czwartek', 'Sala 231'), (1, 1, 1, 1, 'Piatek', 'Sala 112'),
1029(3, 3, 1, 2, 'Piatek', 'Sala 231'), (1, 1, 1, 3, 'Piatek', 'Sala 112');
1030----------------------------------------------------------------------------------------------------
1031----------------------------------------------------------------------------------------------------