ETNuKgtW

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 StudentsHistory (
17    StudentId INTEGER,
18    Name VARCHAR(150) NOT NULL,
19    Num INTEGER NOT NULL,
20    ClassNum INTEGER NOT NULL,
21    ClassLetter CHAR(1) NOT NULL,
22    Birthday DATE,
23    EGN CHAR(10),
24    EntranceExamResult NUMERIC(3,2),
25    Id INTEGER NOT NULL PRIMARY KEY,
26    TypeOfChange CHAR NOT NULL,
27    TimeOfChange DATETIME NOT NULL
28);
29 
30 
31delimiter |
32 
33CREATE TRIGGER HistoryInsert AFTER INSERT ON Students
34  FOR EACH ROW
35  BEGIN
36    INSERT INTO StudentsHistory(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN, TypeOfChange, TimeOfChange) VALUES(NEW.Id, NEW.Name, NEW.Num, NEW.ClassNum, NEW.ClassLetter, NEW.Birthday, NEW.EGN, 'I', NOW());
37  END;
38|
39 
40delimiter ;
41 
42delimiter |
43 
44CREATE TRIGGER HistoryUpdate AFTER UPDATE ON Students
45  FOR EACH ROW
46  BEGIN
47    INSERT INTO StudentsHistory(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN, TypeOfChange, TimeOfChange) VALUES(NEW.Id, NEW.Name, NEW.Num, NEW.ClassNum, NEW.ClassLetter, NEW.Birthday, NEW.EGN, 'U', NOW());
48  END;
49|
50 
51delimiter ;
52 
53delimiter |
54 
55CREATE TRIGGER HistoryDelete AFTER DELETE ON Students
56  FOR EACH ROW
57  BEGIN
58    INSERT INTO StudentsHistory(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN, TypeOfChange, TimeOfChange) VALUES(OLD.Id, OLD.Name, OLD.Num, OLD.ClassNum, OLD.ClassLetter, OLD.Birthday, OLD.EGN, 'U', NOW());
59  END;
60|
61 
62delimiter ;
63 
64INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
65INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
66INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
67INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
68INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Александър Момчев', 1, 8, 'а', '2002-06-11', NULL );
69 
70UPDATE StudentsHistory SET Name='Иван Иванов' WHERE Id=101;
71
72
73SELECT * FROM StudentsHistory
74WHERE StudentsHistory.Name = 'Зюмбюл Петров'
75ORDER BY TimeOfChange ASC;
76 
77SELECT * FROM StudentsHistory
78WHERE StudentsHistory.Id = 101
79ORDER BY TimeOfChange DESC
80LIMIT 1;
81 
82SELECT hs.Num, hs.Name
83FROM StudentsHistory hs
84LEFT JOIN StudentsHistory later
85ON hs.id = later.id AND
86    hs.TimeOfChange < later.TimeOfChange
87WHERE later.id IS NULL AND
88    hs.TypeOfChange <> 'D';
89    
90    
91Select h.* 
92FROM StudentsHistory h
93INNER JOIN StudentsHistory st_beforeDate
94ON h.TimeOfChange <= '2019-11-07 07:43:55' AND
95st_beforeDate.StudentId = h.StudentId
96LEFT JOIN StudentsHistory st_later
97ON st_later.StudentId > h.StudentId AND
98	st_later.StudentId = h.ID AND
99    st_later.TimeOfChange <= '2019-11-07 07:43:55'
100WHERE st_later.StudentId IS NULL AND h.TypeOfChange <> 'D';