n8u78RwH

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 VARCHAR(10),
46    Id INTEGER NOT NULL,
47    Name VARCHAR(100) NOT NULL,
48    EGN VARCHAR(10)
49);
50 
51CREATE TABLE H_StudentMarks(
52    UID INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY,
53    EventDate DATETIME NOT NULL,
54    EventType VARCHAR(10) NOT NULL,
55    ExamDate DATETIME NOT NULL,
56    StId INTEGER NOT NULL,
57    SuId INTEGER NOT NULL,
58    Mark NUMERIC(3,2) NOT NULL
59);
60 
61CREATE TABLE EventType(
62    Id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT,
63    Name VARCHAR(10)
64);
65 
66delimiter |
67CREATE TRIGGER StudentsUpdate BEFORE UPDATE ON Students
68  FOR EACH ROW
69  BEGIN
70    INSERT INTO H_Students(EventDate,EventType,Id,Name,EGN) VALUES (NOW() + 1,'UPDATE',NEW.Id,NEW.Name,NEW.EGN);
71  END
72|
73CREATE TRIGGER StudentsInsert AFTER INSERT ON Students
74  FOR EACH ROW
75  BEGIN
76    INSERT INTO H_Students(EventDate,EventType,Id,Name,EGN) VALUES (NOW(),'INSERT',NEW.Id,NEW.Name,NEW.EGN);
77  END
78|
79CREATE TRIGGER StudentsDelete BEFORE DELETE ON Students
80  FOR EACH ROW
81  BEGIN
82    INSERT INTO H_Students(EventDate,EventType,Id,Name,EGN) VALUES (NOW() + 2,'DELETE',Id,Name,EGN);
83  END
84|
85CREATE TRIGGER StudentMarksUpdate BEFORE UPDATE ON StudentMarks
86  FOR EACH ROW
87  BEGIN
88    INSERT INTO H_StudentMarks(EventDate,EventType,StId,SuId,Mark,ExamDate) VALUES (NOW()+1,'UPDATE',NEW.StudentId,NEW.SubjectId,NEW.Mark,NOW());
89  END
90|
91CREATE TRIGGER StudentMarksInsert AFTER INSERT ON StudentMarks
92  FOR EACH ROW
93  BEGIN
94    INSERT INTO H_StudentMarks(EventDate,EventType,StId,SuId,Mark,ExamDate) VALUES (NOW(),'INSERT',NEW.StudentId,NEW.SubjectId,NEW.Mark,NOW());
95  END
96|
97 
98CREATE TRIGGER StudentMarksDelete BEFORE DELETE ON StudentMarks
99  FOR EACH ROW
100  BEGIN
101    INSERT INTO H_StudentMarks(EventDate,EventType,StId,SuId,Mark,ExamDate) VALUES (NOW()+2,'INSERT',StudentId,SubjectId,Mark,NOW());
102  END
103|
104delimiter ;
105 
106 
107INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
108INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
109INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
110INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
111INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Александър Момчев', 1, 8, 'а', '2002-06-11', NULL );
112 
113UPDATE Students
114SET ClassLetter = 'г'
115WHERE Id > 100;
116 
117 
118INSERT INTO Subjects(Id, Name) VALUES( 11, 'Английски език' );
119INSERT INTO Subjects(Name) VALUES( 'Литература' );
120INSERT INTO Subjects(Name) VALUES( 'Математика' );
121INSERT INTO Subjects(Name) VALUES( 'СУБД' );
122 
123INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-03', 6 );
124INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-31', 5.50 );
125INSERT INTO StudentMarks VALUES( 102, 11, '2017-04-28', 5 );
126INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', 4 );
127INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 5 );
128INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
129INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50 );
130 
131INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
132INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
133INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
134INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
135INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );
136 
137SELECT * FROM H_Students
138WHERE Id = 101;
139 
140SELECT * FROM H_Students
141WHERE Id = 102
142ORDER BY EventDate DESC
143LIMIT 1;
144 
145SELECT * FROM( SELECT * FROM H_Students ORDER BY EventDate DESC) as d
146GROUP BY Id;
147
148SELECT hs.*  , AVG(hsm.Mark)
149FROM H_Students hs
150LEFT JOIN H_Students st_later
151ON st_later.Id = hs.Id AND st_later.UID > hs.UID
152LEFT JOIN H_StudentMarks hsm
153ON hs.Id = hsm.StId
154LEFT JOIN H_StudentMarks hsm_later
155ON hsm_later.StId = hsm.StId AND hsm_later.ExamDate = hsm.ExamDate AND hsm_later.SuId = hsm.SuId AND hsm_later.UID > hsm.UID
156WHERE st_later.UID IS NULL AND hs.EventType <> 'D'  AND hsm_later.UID  IS NULL
157GROUP BY hs.Id;