ZamR10Hj

1DROP DATABASE IF EXISTS school;
2
3CREATE DATABASE school CHARSET 'utf8';
4USE school;
5
6CREATE TABLE Students(
7	id                 INTEGER      NOT NULL AUTO_INCREMENT,
8	name               VARCHAR(150) NOT NULL,
9	num                INTEGER      NOT NULL,
10	classNum           INTEGER      NOT NULL,
11	classLetter        CHAR(1)      NOT NULL,
12	birthday           DATE,
13	EGN                CHAR(10),
14	entranceExamResult NUMERIC(3, 2),
15    
16    PRIMARY KEY (id)
17);
18
19CREATE TABLE Subjects(
20	id   INTEGER NOT NULL AUTO_INCREMENT,
21	name VARCHAR(100),
22	
23	PRIMARY KEY (id)
24);
25
26CREATE TABLE StudentMarks(
27	studentId INTEGER       NOT NULL,
28	subjectId INTEGER       NOT NULL,
29	examDate  DATETIME      NOT NULL,
30	mark      NUMERIC(3, 2) NOT NULL,
31	
32	PRIMARY KEY (studentId, subjectId, examDate),
33	FOREIGN KEY (studentId) REFERENCES Students(id),
34	FOREIGN KEY (subjectId) REFERENCES Subjects(id)
35);
36
37CREATE TABLE MarkWords(
38	rangeStart NUMERIC(3, 2) NOT NULL,
39	rangeEnd   NUMERIC(3, 2) NOT NULL,
40	markAsWord VARCHAR(15),
41	
42	PRIMARY KEY (rangeStart, rangeEnd)
43);
44
45INSERT INTO Students(id, name, num, classNum, classLetter, birthday, EGN) VALUES(101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL);
46INSERT INTO Students(name, num, classNum, classLetter, birthday, EGN)     VALUES('Исидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120');
47INSERT INTO Students(name, num, classNum, classLetter, birthday, EGN)     VALUES('Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL);
48INSERT INTO Students(name, num, classNum, classLetter, birthday, EGN)     VALUES('Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301');
49INSERT INTO Students(name, num, classNum, classLetter, birthday, EGN)     VALUES('Александър Момчев', 1, 8, 'а', '2002-06-11', NULL);
50
51INSERT INTO Subjects(id, name) VALUES(11, 'Английски език');
52INSERT INTO Subjects(name)     VALUES('Литература');
53INSERT INTO Subjects(name)     VALUES('Математика');
54INSERT INTO Subjects(name)     VALUES('СУБД');
55
56INSERT INTO StudentMarks VALUES(101, 11, '2017-03-03', 6);
57INSERT INTO StudentMarks VALUES(101, 11, '2017-03-31', 5.50);
58INSERT INTO StudentMarks VALUES(102, 11, '2017-04-28', 5);
59INSERT INTO StudentMarks VALUES(103, 12, '2017-04-28', 4);
60INSERT INTO StudentMarks VALUES(104, 13, '2017-03-03', 5);
61INSERT INTO StudentMarks VALUES(104, 13, '2017-04-07', 6);
62INSERT INTO StudentMarks VALUES(104, 11, '2017-04-07', 4.50);
63INSERT INTO StudentMarks VALUES(102, 14, '2017-04-08', 2);
64
65INSERT INTO MarkWords VALUES(2.00, 2.50, 'Слаб');
66INSERT INTO MarkWords VALUES(2.50, 3.50, 'Среден');
67INSERT INTO MarkWords VALUES(3.50, 4.50, 'Добър');
68INSERT INTO MarkWords VALUES(4.50, 5.50, 'Мн. добър');
69INSERT INTO MarkWords VALUES(5.50, 6.00, 'Отличен');
70
71-- Най-висока оценка по АЕ
72SELECT MAX(m.mark) AS maxMark FROM StudentMarks m
73LEFT JOIN Subjects sub ON sub.id = m.subjectId
74WHERE sub.name = 'Английски език';
75
76-- Име на ученик и среден успех
77SELECT s.name, ROUND(AVG(m.mark), 2) AS averageMark FROM Students s
78LEFT JOIN StudentMarks m ON s.id = m.studentId
79GROUP BY s.id, s.name
80ORDER BY averageMark DESC;
81
82-- Среден успех на всеки от 10-тите класове
83SELECT s.classNum, s.classLetter, ROUND(AVG(m.mark), 2) AS averageMark FROM Students s
84LEFT JOIN StudentMarks m ON s.id = m.studentId
85WHERE s.classNum = 10
86GROUP BY s.classNum, s.classLetter;
87
88-- Брой отлични оценки по АЕ
89SELECT COUNT(*) FROM StudentMarks m
90LEFT JOIN MarkWords mw ON m.mark >= mw.rangeStart AND m.mark < mw.rangeEnd
91WHERE mw.markAsWord = 'Отличен';
92
93-- Брой пълни шестици по всеки предмет (име, брой)
94SELECT sub.name, COUNT(m.mark) AS excellentGradesCount
95FROM Subjects sub
96LEFT JOIN StudentMarks m
97ON sub.id = m.subjectId AND m.mark = 6
98GROUP BY sub.id, sub.name;
99
100-- Среден успех по всеки предмет на всеки ученик
101SELECT st.name, sub.name, AVG(m.mark) AS averageGrade
102FROM StudentMarks m
103INNER JOIN Students st
104ON st.id = m.studentId
105INNER JOIN Subjects sub
106ON sub.id = m.subjectId
107GROUP BY st.id, st.name, sub.id, sub.name
108ORDER BY st.name, sub.name;
109
110-- Всички ученици с отличен среден успех (по всички предмети)
111SELECT st.name, AVG(m.mark) AS averageGrade
112FROM StudentMarks m
113INNER JOIN Students st
114ON st.id = m.studentId
115GROUP BY st.id, st.name
116HAVING averageGrade >= 5.50
117ORDER BY st.name;
118
119-- Всички предмети, по които няма нито една оценка
120SELECT sub.name
121FROM Subjects sub
122LEFT JOIN StudentMarks m
123ON sub.id = m.subjectId
124WHERE m.mark IS NULL
125GROUP BY sub.id, sub.name;
126
127-- Всички получени оценки на всеки ученик (ако няма оценки да не излиза)
128SELECT st.name, m.mark
129FROM Students st
130INNER JOIN StudentMarks m
131ON st.id = m.studentId
132ORDER BY st.name, m.mark;
133
134-- Всички ученици, подредени по клас, паралелка и име
135SELECT st.name, st.classNum, st.classLetter
136FROM Students st
137ORDER BY st.classNum, st.classLetter, st.name;
138
139-- Всички учебни предмети със слаб среден успех по тях във възходящ ред
140SELECT sub.name, AVG(m.mark) AS averageMark
141FROM Subjects sub
142INNER JOIN StudentMarks m
143ON sub.id = m.subjectId
144GROUP BY sub.id, sub.name
145HAVING averageMark < 2.50
146ORDER BY averageMark ASC;
147
148-- Да не могат да се повтарят номер, клас и паралелка в таблицата с ученици
149CREATE UNIQUE INDEX studentIndex ON Students(num, classNum, classLetter);