· 7 years ago · Oct 11, 2018, 04:10 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 );
47INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'ÐлекÑандърхй Момчев', 3, 12, 'а', '2002-06-11', NULL );
48
49
50INSERT INTO Subjects(Id, Name) VALUES( 11, 'ÐнглийÑки език' );
51INSERT INTO Subjects(Name) VALUES( 'Литература' );
52INSERT INTO Subjects(Name) VALUES( 'Математика' );
53INSERT INTO Subjects(Name) VALUES( 'СУБД' );
54
55INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-03', 6 );
56INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-31', 5.50 );
57INSERT INTO StudentMarks VALUES( 102, 11, '2017-04-28', 5 );
58INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', 4 );
59INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 5 );
60INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
61INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50 );
62INSERT INTO StudentMarks VALUES( 105, 11, '2017-04-07', 4.50 );
63INSERT INTO StudentMarks VALUES( 106, 12, '2017-04-07', 4.50 );
64
65
66INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
67INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
68INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
69INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
70INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );
71
72CREATE VIEW students AS
73SELECT ClassNum, ClassLetter, AVG(stm.Mark),COUNT(distinct StudentId) AS studentsCount
74FROM Students AS st
75INNER JOIN StudentMarks AS stm ON st.Id = StudentId
76GROUP BY ClassLetter, ClassNum;
77
78-- SELECT * FROM students;
79SELECT * FROM students AS s
80WHERE (s.ClassNum = 11 AND s.ClassLetter = 'а') OR (s.ClassNum = 12 AND s.ClassLetter = 'б');
81
82SELECT ss.ClassNum, SUM(ss.studentsCount)
83FROM students AS ss
84GROUP BY ss.ClassNum;