x1zxEAhd

1DROP DATABASE IF EXISTS school;
2CREATE DATABASE school CHARSET 'utf8';
3USE school;
4 
5CREATE TABLE Students(
6    Id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
7    Name VARCHAR(150) NOT NULL,
8    Num INTEGER NOT NULL,
9    ClassNum INTEGER NOT NULL,
10    ClassLetter CHAR(1) NOT NULL,
11    Birthday DATE,
12    EGN CHAR(10),
13    EntranceExamResult NUMERIC(3,2)
14);
15 
16CREATE TABLE Subjects(
17    Id INTEGER NOT NULL AUTO_INCREMENT,
18    Name VARCHAR(100),
19   
20    PRIMARY KEY(Id)
21);
22 
23CREATE TABLE StudentMarks(
24    StudentId INTEGER NOT NULL,
25    SubjectId INTEGER NOT NULL,
26    ExamDate DATETIME NOT NULL,
27    Mark NUMERIC(3,2) NOT NULL,
28   
29    PRIMARY KEY( StudentId, SubjectId, ExamDate ),
30    FOREIGN KEY (StudentId) REFERENCES Students(Id),
31    FOREIGN KEY (SubjectId) REFERENCES Subjects(Id)
32);
33 
34CREATE TABLE MarkWords(
35    RangeStart NUMERIC(3,2) NOT NULL,
36    RangeEnd NUMERIC(3,2) NOT NULL,
37    MarkAsWord VARCHAR(15),
38   
39    PRIMARY KEY(RangeStart, RangeEnd)
40);
41 
42CREATE TABLE H_Students(
43    UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
44    EventDate DATETIME NOT NULL,
45    EventType CHAR(1),
46    Id INTEGER,
47    Name VARCHAR(150),
48    Num INTEGER,
49    ClassNum INTEGER,
50    ClassLetter CHAR(1),
51    Birthday DATE,
52    EGN CHAR(10),
53    EntranceExamResult NUMERIC(3,2)
54);
55 
56CREATE TABLE H_StudentMarks(
57    UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
58    EventDate DATETIME NOT NULL,
59    EventType CHAR(1),
60   
61    StudentId INTEGER,
62    SubjectId INTEGER,
63    ExamDate DATETIME,
64    Mark NUMERIC(3,2)
65);
66 
67DELIMITER $
68 
69CREATE TRIGGER Students_AI_Hist AFTER INSERT ON Students FOR EACH ROW
70BEGIN
71    INSERT INTO H_Students
72    VALUES(NULL, NOW(), 'I', NEW.Id, NEW.Name, NEW.Num,
73            NEW.ClassNum, NEW.ClassLetter, NEW.Birthday, NEW.EGN, NEW.EntranceExamResult);
74END$
75 
76CREATE TRIGGER Students_AU_Hist AFTER UPDATE ON Students FOR EACH ROW
77BEGIN
78    INSERT INTO H_Students
79    VALUES(NULL, NOW(), 'U', NEW.Id, NEW.Name, NEW.Num,
80            NEW.ClassNum, NEW.ClassLetter, NEW.Birthday, NEW.EGN, NEW.EntranceExamResult);
81END$
82 
83CREATE TRIGGER Students_AD_Hist AFTER DELETE ON Students FOR EACH ROW
84BEGIN
85    INSERT INTO H_Students
86    VALUES(NULL, NOW(), 'D', OLD.Id, OLD.Name, OLD.Num,
87            OLD.ClassNum, OLD.ClassLetter, OLD.Birthday, OLD.EGN, OLD.EntranceExamResult);
88END$
89 
90CREATE TRIGGER StudentMarks_AI_Hist AFTER INSERT ON StudentMarks FOR EACH ROW
91BEGIN
92    INSERT INTO H_StudentMarks
93    VALUES(NULL, NOW(), 'I', NEW.StudentId, NEW.SubjectId, NEW.ExamDate, NEW.Mark);
94END$
95 
96CREATE TRIGGER StudentMarks_AU_Hist AFTER UPDATE ON StudentMarks FOR EACH ROW
97BEGIN
98    INSERT INTO H_StudentMarks
99    VALUES(NULL, NOW(), 'U', NEW.StudentId, NEW.SubjectId, NEW.ExamDate, NEW.Mark);
100END$
101 
102CREATE TRIGGER StudentMarkss_AD_Hist AFTER DELETE ON StudentMarks FOR EACH ROW
103BEGIN
104    INSERT INTO H_StudentMarks
105    VALUES(NULL, NOW(), 'D', OLD.StudentId, OLD.SubjectId, OLD.ExamDate, OLD.Mark);
106END$
107 
108DELIMITER ;
109 
110INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
111INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
112INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
113INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
114INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Александър Момчев', 1, 8, 'а', '2002-06-11', NULL );
115 DELETE FROM Students WHERE Id=101;
116 
117INSERT INTO Subjects(Id, Name) VALUES( 11, 'Английски език' );
118INSERT INTO Subjects(Name) VALUES( 'Литература' );
119INSERT INTO Subjects(Name) VALUES( 'Математика' );
120INSERT INTO Subjects(Name) VALUES( 'СУБД' );
121 
122#INSERT INTO StudentMarks VALUES( 101, 11, '2018-03-03', 6 );
123#INSERT INTO StudentMarks VALUES( 101, 11, '2018-03-31', 5.50 );
124INSERT INTO StudentMarks VALUES( 102, 11, '2018-04-28', 5 );
125INSERT INTO StudentMarks VALUES( 103, 12, '2018-04-28', 4 );
126INSERT INTO StudentMarks VALUES( 104, 13, '2018-03-03', 5 );
127INSERT INTO StudentMarks VALUES( 104, 13, '2018-04-07', 6 );
128INSERT INTO StudentMarks VALUES( 104, 11, '2018-04-07', 4.50 );
129 
130INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
131INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
132INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
133INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
134INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );
135
136SELECT hs.Num, hs.Name
137FROM H_Students hs
138LEFT JOIN H_Students later
139ON hs.id = later.id AND hs.eventdate < later.eventdate
140WHERE later.id IS NULL AND hs.EventType <> 'D';
141
142UPDATE Students
143SET Name='Исидор Панов'
144WHERE Id=102;
145
146SELECT * FROM H_Students;
147
148SELECT hs.*
149FROM H_Students st_beforeDate
150INNER JOIN h_students st_beforeDate
151ON h.EventDate <= '2019-11-21 18:48:55' AND
152st_beforeDate.UID = h.UID
153LEFT JOIN H_Students later
154ON later.UID > h.UID AND hs.Id = later.id AND later.eventdate <= '2019-11-21 18:48:55'
155WHERE later.id IS NULL AND hs.EventType <> 'D';