n0hBTR0y

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),
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
42INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
43INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
44INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
45INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
46INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Александър Момчев', 1, 8, 'а', '2002-06-11', NULL );
47
48INSERT INTO Subjects(Id, Name) VALUES( 11, 'Английски език' );
49INSERT INTO Subjects(Name) VALUES( 'Литература' );
50INSERT INTO Subjects(Name) VALUES( 'Математика' );
51INSERT INTO Subjects(Name) VALUES( 'СУБД' );
52
53INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-03', 6 );
54INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-31', 5.50 );
55INSERT INTO StudentMarks VALUES( 102, 11, '2017-04-28', 2 );
56INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', NULL );
57INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 2.20);
58INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
59INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50 );
60
61
62
63
64INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
65INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
66INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
67INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
68INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );
69
70
71-- SELECT sb.Name, COUNT(sm.Mark)
72-- FROM Subjects sb
73-- LEFT JOIN StudentMarks sm
74-- ON sb.Id = sm.SubjectId AND sm.Mark = 6
75-- GROUP BY sb.Name, sb.Id;
76-- 
77-- SELECT st.Name, sb.Name, AVG(sm.Mark)
78-- FROM Students st
79-- LEFT JOIN StudentMarks sm
80-- ON st.Id = sm.StudentId
81-- LEFT JOIN Subjects sb
82-- ON sb.Id = sm.SubjectId
83-- GROUP BY st.Name, sb.Name, st.Id, sb.Id;
84-- 
85-- SELECT st.Name, sb.Name, AVG(sm.Mark)
86-- FROM Students st
87-- LEFT JOIN StudentMarks sm
88-- ON st.Id = sm.StudentId
89-- LEFT JOIN Subjects sb
90-- ON sb.Id = sm.SubjectId
91-- GROUP BY st.Name, sb.Name, st.Id, sb.Id
92-- HAVING AVG(sm.Mark) >= 5.50;
93-- 
94-- SELECT sb.Name
95-- FROM Subjects sb
96-- INNER JOIN StudentMarks sm
97-- ON sb.Id = sm.SubjectId
98-- AND sm.Mark = NULL;
99
100-- SELECT st.Name, sm.Mark
101-- FROM Students st
102-- INNER JOIN StudentMarks sm
103-- ON st.Id = sm.StudentId AND sm.Mark IS NOT NULL;
104
105-- SELECT st.Name, st.ClassLetter, st.Name
106-- FROM Students st
107-- ORDER BY st.ClassNum, st.ClassLetter, st.Name;
108
109SELECT sb.Name, AVG(sm.Mark)
110FROM Subjects sb
111LEFT JOIN StudentMarks sm
112ON sb.Id = sm.SubjectId 
113GROUP BY sb.Name, sb.Id
114HAVING AVG(sm.Mark) <= 4.49
115ORDER BY AVG(sm.Mark) ASC;
116
117CREATE UNIQUE INDEX StudentIndex
118ON Students(Num, ClassNum, ClassLetter);