· 8 years ago · Jun 08, 2018, 08:30 PM
1DROP DATABASE IF EXISTS school;
2CREATE DATABASE school;
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 PhysicsExamResult 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
42
43INSERT INTO Students(Id, Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 101, 'Зюмбюл Петров', 10, 11, 'а', '1999-02-28', NULL );
44INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'ИÑидор Иванов', 15, 10, 'б', '2000-02-29', '0042294120' );
45INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Панчо Лалов', 20, 10, 'б', '2000-05-01', NULL );
46INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Петраки Ганьов', 20, 10, 'а', '1999-12-25', '9912256301' );
47INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'ÐлекÑандър Момчев', 1, 8, 'а', '2002-06-11', NULL );
48
49INSERT INTO Subjects(Id, Name) VALUES( 11, 'ÐнглийÑки език' );
50INSERT INTO Subjects(Name) VALUES( 'Литература' );
51INSERT INTO Subjects(Name) VALUES( 'Математика' );
52INSERT INTO Subjects(Name) VALUES( 'СУБД' );
53
54INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-03', 6 );
55INSERT INTO StudentMarks VALUES( 101, 11, '2017-03-31', 5.50 );
56INSERT INTO StudentMarks VALUES( 102, 11, '2017-04-28', 5 );
57INSERT INTO StudentMarks VALUES( 103, 12, '2017-04-28', 4 );
58INSERT INTO StudentMarks VALUES( 104, 13, '2017-03-03', 5 );
59INSERT INTO StudentMarks VALUES( 104, 13, '2017-04-07', 6 );
60INSERT INTO StudentMarks VALUES( 104, 11, '2017-04-07', 4.50 );
61
62INSERT INTO MarkWords VALUES( 2, 2.50, 'Слаб' );
63INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Среден' );
64INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Добър' );
65INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Мн. добър' );
66INSERT INTO MarkWords VALUES( 5.50, 6, 'Отличен' );
67
68
69
70SELECT st.Name as 'Student Name', sm.ExamDate as 'Exam Date', sb.Name as 'Subject Name', mw.MarkAsWord as 'Mark'
71FROM Students st
72INNER JOIN StudentMarks sm
73ON sm.studentid = st.id
74INNER JOIN Subjects sb
75ON sb.Id = sm.subjectId
76INNER JOIN MarkWords mw
77ON mw.RangeStart <= sm.Mark and mw.RangeEnd >= sm.Mark;
78
79
80SELECT st.Name, sb.Name, AVG(sm.Mark)
81FROM Students st
82INNER JOIN StudentMarks sm
83ON sm.studentId = st.Id
84INNER JOIN subjects sb
85ON sb.Id = sm.subjectId
86GROUP BY st.Name, sb.Name;
87
88
89DROP FUNCTION IF EXISTS AVRGMark;
90DELIMITER //
91
92CREATE FUNCTION AVRGMark(StudentName varchar(150))
93RETURNS FLOAT DETERMINISTIC
94BEGIN
95 DECLARE result FLOAT;
96 SET @result := (SELECT AVRG sm.MARK
97 INTO result
98 FROM Strudents st
99 INNER JOIN StudentMarks sm
100 ON sm.StudentId = st.ID
101 WHERE st.Name = StudentName
102 GROUP BY st.Name);
103 RETURN result;
104END//
105
106DELIMITER ;
107
108SELECT AVRGMark(st.Name) from Students st;