jCU9LKwx

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