· 8 years ago · Mar 30, 2018, 12:04 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 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, 'Sasho', 10, 11, 'a', '1999-02-28', NULL );
43INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Toni', 15, 10, 'b', '2000-02-29', '0042294120' );
44INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Bibi', 20, 10, 'b', '2000-05-01', NULL );
45INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Valeri', 20, 10, 'a', '1999-12-25', '9912256301' );
46INSERT INTO Students(Name, Num, ClassNum, ClassLetter, Birthday, EGN) VALUES( 'Vladi', 1, 8, 'a', '2002-06-11', NULL );
47
48INSERT INTO Subjects(Id, Name) VALUES( 11, 'English' );
49INSERT INTO Subjects(Name) VALUES( 'literature' );
50INSERT INTO Subjects(Name) VALUES( 'Math' );
51INSERT INTO Subjects(Name) VALUES( 'SUBD' );
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, 'Slab' );
62INSERT INTO MarkWords VALUES( 2.50, 3.50, 'Sreden' );
63INSERT INTO MarkWords VALUES( 3.50, 4.50, 'Dobur' );
64INSERT INTO MarkWords VALUES( 4.50, 5.50, 'Mnogo dobur' );
65INSERT INTO MarkWords VALUES( 5.50, 6, 'Excellent' );
66
67SELECT * FROM StudentMarks;
68
69SELECT MAX(m.Mark)
70FROM Students st
71LEFT JOIN StudentMarks m ON st.Id = m.StudentId
72LIMIT 1;
73
74SELECT st.Name, AVG(m.Mark)
75FROM Students st
76LEFT JOIN StudentMarks m ON st.Id = m.StudentId
77GROUP BY st.Id, st.Name
78ORDER BY AVG(m.Mark) DESC
79LIMIT 3;
80
81SELECT st.classNum, st.Name, AVG(m.Mark)
82FROM Students st
83LEFT JOIN StudentMarks m ON st.Id = m.StudentId
84WHERE st.classNum = 10
85GROUP BY st.Id, st.Name;
86
87SELECT st.Name, m.Mark
88FROM Students st
89LEFT JOIN StudentMarks m ON st.Id = m.StudentId
90WHERE m.SubjectId = 11 && m.Mark = 6
91GROUP BY st.Id, st.Name;