· 6 years ago · Mar 22, 2019, 02:58 PM
1/*DROP DATABASE IF EXISTS school;
2CREATE DATABASE school CHARSET 'utf8';*/
3USE school;
4
5/*CREATE 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
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', 5 );
56INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', 4 );
57INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 5 );
58INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
59INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50 );
60
61INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
62INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
63INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
64INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
65INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );
66
67
68
69SELECT s.Name, COUNT(*) FROM StudentMarks AS sm
70LEFT JOIN Subjects AS s ON sm.SubjectId = s.Id WHERE sm.Mark = 6
71GROUP BY s.Name;
72
73
74
75
76SELECT st.Name, sb.Name, AVG(sm.Mark) FROM Students AS st
77LEFT JOIN StudentMarks AS sm ON st.Id = sm.StudentId
78LEFT JOIN Subjects as sb on sm.SubjectId = sb.Id
79GROUP BY sb.Name, st.Name;
80
81SELECT st.Name, st.Num, st.ClassNum, st.ClassLetter, AVG(sm.Mark) as avgs FROM StudentMarks as sm
82LEFT JOIN Students as st ON sm.StudentId = st.Id
83GROUP BY st.Name, st.Num, st.ClassNum, st.ClassLetter
84HAVING avgs > 5.49;
85
86SELECT sb.Name FROM Subjects as sb
87LEFT JOIN StudentMarks as sm ON sm.SubjectId = sb.Id
88WHERE sm.Mark is null
89GROUP BY sb.Name;
90
91
92SELECT s.ClassNum, s.ClassLetter, s.Name FROM Students as s
93GROUP BY s.Name, s.ClassNum, s.ClassLetter
94ORDER BY s.ClassNum ASC, s.ClassLetter ASC, s.Name ASC;
95
96
97SELECT sb.Name, sb.Id, AVG(sm.Mark) as avgs FROM Subjects as sb
98LEFT JOIN StudentMarks as sm ON sb.Id = sm.SubjectId
99GROUP BY sb.Name, sb.Id
100HAVING avgs < 2.50
101ORDER BY avgs ASC;
102*/