3tGfC05B

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);
63
64INSERT INTO MarkWords VALUES(2.00, 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.00, 'Отличен');
69
70-- Най-висока оценка по АЕ
71SELECT MAX(m.mark) AS maxMark FROM StudentMarks m
72LEFT JOIN Subjects sub ON sub.id = m.subjectId
73WHERE sub.name = 'Английски език';
74
75-- Име на ученик и среден успех
76SELECT s.name, ROUND(AVG(m.mark), 2) AS averageMark FROM Students s
77LEFT JOIN StudentMarks m ON s.id = m.studentId
78GROUP BY s.id, s.name
79ORDER BY averageMark DESC;
80
81-- Среден успех на всеки от 10-тите класове
82SELECT s.classNum, s.classLetter, ROUND(AVG(m.mark), 2) AS averageMark FROM Students s
83LEFT JOIN StudentMarks m ON s.id = m.studentId
84WHERE s.classNum = 10
85GROUP BY s.classNum, s.classLetter;
86
87-- Брой отлични оценки по АЕ
88SELECT COUNT(*) FROM StudentMarks m
89LEFT JOIN MarkWords mw ON m.mark >= mw.rangeStart AND m.mark < mw.rangeEnd
90WHERE mw.markAsWord = 'Отличен';