· 8 years ago · Mar 30, 2018, 02:36 PM
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
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-- SELECT * FROM StudentMarks;
68
69SELECT MAX(sm.Mark)
70FROM StudentMarks sm
71INNER JOIN Subjects s
72WHERE s.Name = 'ÐнглийÑки език';
73
74SELECT s.Name, AVG(sm.Mark)
75FROM Students s
76INNER JOIN StudentMarks sm
77ON s.Id = sm.studentId
78GROUP BY s.Name;
79
80SELECT s.classLetter, s.ClassNum, AVG(sm.Mark)
81FROM Students s
82INNER JOIN StudentMarks sm
83ON s.Id = sm.studentId
84WHERE s.classNum = 10
85GROUP BY s.classLetter, s.classNum;
86
87SELECT COUNT(*)
88FROM Subjects s
89INNER JOIN StudentMarks sm
90ON s.id = sm.subjectId
91WHERE sm.Mark >= 5.50;