· 6 years ago · Nov 21, 2019, 03:48 PM
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 H_Students (
17 Id INTEGER NOT NULL,
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
26 UId Integer Not null auto_increment primary key,
27 EventType char(1) Not null,
28 EventData dateTime not null
29);
30
31CREATE TABLE Subjects(
32 Id INTEGER NOT NULL AUTO_INCREMENT,
33 Name VARCHAR(100),
34
35 PRIMARY KEY(Id)
36);
37
38CREATE TABLE StudentMarks(
39 StudentId INTEGER NOT NULL,
40 SubjectId INTEGER NOT NULL,
41 ExamDate DATETIME NOT NULL,
42 Mark NUMERIC(3,2) NOT NULL,
43
44 PRIMARY KEY( StudentId, SubjectId, ExamDate ),
45 FOREIGN KEY (StudentId) REFERENCES Students(Id),
46 FOREIGN KEY (SubjectId) REFERENCES Subjects(Id)
47);
48
49CREATE TABLE MarkWords(
50 RangeStart NUMERIC(3,2) NOT NULL,
51 RangeEnd NUMERIC(3,2) NOT NULL,
52 MarkAsWord VARCHAR(15),
53
54 PRIMARY KEY(RangeStart, RangeEnd)
55);
56
57CREATE TABLE Teachers(
58 Id INTEGER NOT NULL auto_increment,
59 Name1 VARCHAR(100),
60 Name2 VARCHAR(100),
61 Name3 VARCHAR(100),
62 fullName VARCHAR(100) ,
63
64
65
66 primary key(Id)
67);
68
69
70
71
72INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
73INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
74INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
75INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
76INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Александър Момчев', 1, 8, 'а', '2002-06-11', NULL );
77
78INSERT INTO Teachers(Id,Name1, Name2, Name3) VALUES(1, 'Пенка', 'Пешова' , 'Станимирова');
79INSERT INTO Teachers(Name1, Name2, Name3) VALUES('Джани', 'Цачева', 'Панимирова');
80INSERT INTO Teachers(Name1, Name2, Name3) VALUES('Цеца', 'Александрова', 'Гиоцова');
81INSERT INTO Teachers(Name1, Name2, Name3) VALUES('Деца', 'Христова', 'Бегайте');
82INSERT INTO Teachers(Name1, Name2, Name3) VALUES('Цацка', 'Николаева', 'Първоумрелова');
83
84INSERT INTO Subjects(Id, Name) VALUES( 11, 'Английски език' );
85INSERT INTO Subjects(Name) VALUES( 'Литература' );
86INSERT INTO Subjects(Name) VALUES( 'Математика' );
87INSERT INTO Subjects(Name) VALUES( 'СУБД' );
88
89INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-03', 6 );
90INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-31', 5.50 );
91INSERT INTO StudentMarks VALUES( 102, 11, '2017-04-28', 5 );
92INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', 4 );
93INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 5 );
94INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
95INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50 );
96
97INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
98INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
99INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
100INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
101INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );
102
103-- SELECT Name, 'Учител' as position FROM Teachers
104-- UNION
105-- SELECT Name, concat(ClassNum, ClassLetter) from Students
106-- WHERE ClassNum = 10 OR ClassNum = 12
107
108--
109-- (SELECT st.Name, AVG(sm.Mark)
110-- From Students st
111-- INNER JOIN StudentMarks sm ON sm.StudentId = st.Id
112-- GROUP BY st.Name
113-- ORDER BY AVG(sm.Mark) DESC limit 1)
114-- union
115-- (SELECT st.Name, AVG(sm.Mark)
116-- From Students st
117-- INNER JOIN StudentMarks sm ON sm.StudentId = st.Id
118-- GROUP BY st.Name
119-- ORDER BY AVG(sm.Mark) asc limit 1)
120
121-- CREATE VIEW ClassMarks(
122-- Class,
123-- SubjectName,
124-- AverageMark
125-- )
126-- AS
127-- SELECT st.ClassNum, sb.Name, AVG(m.Mark)
128-- FROM StudentMarks m
129-- LEFT JOIN Students st
130-- ON m.StudentId = st.Id
131-- LEFT JOIN Subjects sb
132-- ON sb.Id = m.SubjectId
133-- GROUP BY st.ClassNum, sb.Id, sb.Name;
134--
135-- SELECT * FROM ClassMarks WHERE Class = 10
136
137
138--
139-- CREATE VIEW AverageMarks(
140-- StudentName,
141-- ClassNum,
142-- SubjectName,
143-- AverageMark
144--
145-- )
146-- AS
147-- SELECT st.Name, st.ClassNum, sb.Name, AVG(sm.Mark)
148-- FROM StudentMarks sm
149-- LEFT JOIN Students st ON st.Id = sm.StudentId
150-- LEFT JOIN Subjects sb ON sb.Id = sm.SubjectId
151-- GROUP BY st.Id, sb.Id, sb.Name, st.Name;
152--
153-- SELECT * FROM AverageMarks
154
155-- CREATE VIEW HighestAverageMarks(
156-- SubjectName,
157-- HighestAverage
158-- )
159-- AS
160-- SELECT sb.Name, Max(AVG(sm.Mark))
161-- FROM StudentMarks sm
162-- LEFT JOIN Subjects sb ON sb.Id = sm.SubjectId
163-- GROUP BY sb.Name;
164--
165-- Select * from HighestAverageMarks;
166
167
168--
169-- (SELECT sb.Name, AVG(sm.Mark)
170-- From StudentMarks sm
171-- INNER JOIN Subjects sb ON sb.Id = SubjectId and sb.Name = 'Английски език'
172-- GROUP BY sb.Name
173-- ORDER BY AVG(sm.Mark) asc limit 1)
174-- union
175-- (SELECT sb.Name, AVG(sm.Mark)
176-- From StudentMarks sm
177-- inner join Students st on st.id = sm.StudentId
178-- INNER JOIN Subjects sb ON sb.Id = SubjectId and sb.Name = 'Литература'
179-- GROUP BY sb.Name
180-- ORDER BY AVG(sm.Mark) asc limit 1)
181-- union
182-- (SELECT sb.Name, AVG(sm.Mark)
183-- From StudentMarks sm
184-- INNER JOIN Subjects sb ON sb.Id = SubjectId and sb.Name = 'Математика'
185-- GROUP BY sb.Name
186-- ORDER BY AVG(sm.Mark) asc limit 1)
187-- union
188-- (SELECT sb.Name, AVG(sm.Mark)
189-- From StudentMarks sm
190-- INNER JOIN Subjects sb ON sb.Id = SubjectId and sb.Name = 'СУБД'
191-- GROUP BY sb.Name
192-- ORDER BY AVG(sm.Mark) asc limit 1)
193--
194
195 create trigger InsertTrigger
196 before insert on Students for each row
197 insert into H_Students
198 set Id = new.Id,
199 Name = new.Name,
200 Num = new.Num,
201 ClassNum = new.ClassNum,
202 ClassLetter = new.ClassLetter,
203 Birthday = new.Birthday,
204 EGN = new.EGN,
205 EntranceExamResult = new.EntranceExamResult,
206 EventType = 'I',
207 EventData = NOW();
208
209 create trigger UpdateTrigger
210 before update on Students for each row
211 insert into H_Students
212 set Id = new.Id,
213 Name = new.Name,
214 Num = new.Num,
215 ClassNum = new.ClassNum,
216 ClassLetter = new.ClassLetter,
217 Birthday = new.Birthday,
218 EGN = new.EGN,
219 EntranceExamResult = new.EntranceExamResult,
220 EventType = 'U',
221 EventData = NOW();
222
223create trigger DeleteTrigger
224 before delete on Students for each row
225 insert into H_Students
226 set Id = Old.Id,
227 Name = old.Name,
228 Num = old.Num,
229 ClassNum = old.ClassNum,
230 ClassLetter = old.ClassLetter,
231 Birthday = old.Birthday,
232 EGN = old.EGN,
233 EntranceExamResult = old.EntranceExamResult,
234 EventType = 'D',
235 EventData = NOW();
236
237INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 11, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
238
239update Students set Name = 'Зюмбюлка Петрова' where id = 11;
240
241delete From Students where id = 11;
242
243
244
245INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
246INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
247
248
249Select *
250From H_Students hs
251where hs.id = 11
252order by EventData desc limit 1;
253
254Select hs.Name, hs.EventType, Min(hs.EventData)
255From H_Students hs
256where hs.id != 0
257group by Name, EventType