· 8 years ago · Dec 07, 2017, 11:54 AM
1pk-INT VARCHAR VARCHAR
2+-----------+------------------+-----------------+
3| StudentID | StudentFirstName | StudentLastName |
4+-----------+------------------+-----------------+
5| 1 | agam | rafaeli |
6| 2 | amir | aizinger |
7| 3 | avi | caspi |
8| 4 | avia | wolf |
9+-----------+------------------+-----------------+
10
11PK-VARCHR PK-VARCHR PK&FK-INT INT
12+------------+------------+-----------+-------+
13| TestDate | ClassName | StudentID | Grade |
14+------------+------------+-----------+-------+
15| 2017-07-01 | Algebra | 1 | 88 |
16| 2017-08-02 | Algo | 1 | 97 |
17| 2017-09-01 | Algebra | 1 | 80 |
18| 2017-09-01 | Algebra | 1 | 97 |
19| 2017-09-01 | Set-theory | 1 | 85 |
20| 2017-09-04 | Calcules | 1 | 86 |
21| 2016-05-03 | Set-theory | 2 | 84 |
22| 2016-07-02 | Calcules | 2 | 89 |
23| 2016-07-04 | Algo | 2 | 83 |
24| 2016-07-05 | Algebra | 2 | 79 |
25| 2016-06-03 | Algebra | 3 | 99 |
26| 2016-07-02 | Algo | 3 | 97 |
27| 2016-07-03 | Calcules | 3 | 96 |
28| 2016-09-03 | Set-theory | 3 | 95 |
29| 2016-06-03 | Algebra | 4 | 78 |
30+------------+------------+-----------+-------+
31
32+------------------+-----------------+------------+------------+
33| StudentFirstName | StudentLastName | ClassName | AVG(Grade) |
34+------------------+-----------------+------------+------------+
35| agam | rafaeli | Algebra | 88.3333 |
36| agam | rafaeli | Algo | 97.0000 |
37| agam | rafaeli | Calcules | 86.0000 |
38| agam | rafaeli | Set-theory | 77.5000 |
39| amir | aizinger | Algebra | 79.0000 |
40| amir | aizinger | Algo | 83.0000 |
41| amir | aizinger | Calcules | 89.0000 |
42| amir | aizinger | Set-theory | 84.0000 |
43| avi | caspi | Algebra | 99.0000 |
44| avi | caspi | Algo | 97.0000 |
45| avi | caspi | Calcules | 96.0000 |
46| avi | caspi | Set-theory | 95.0000 |
47| avia | wolf | Algebra | 78.0000 |
48| avia | wolf | Algo | 77.0000 |
49| avia | wolf | Calcules | 67.0000 |
50| avia | wolf | Set-theory | 97.0000 |
51+------------------+-----------------+------------+------------+
52
53SELECT StudentFirstName,StudentLastName, ClassName, AVG(Grade) AS 'average for this subject'
54FROM tests
55INNER JOIN students ON tests.StudentID=students.StudentID
56GROUP BY StudentFirstName,StudentLastName,ClassName;
57
58DROP DATABASE IF EXISTS error404;
59CREATE DATABASE error404;
60USE error404
61CREATE TABLE students
62(
63 StudentID INT NOT NULL AUTO_INCREMENT,
64 StudentFirstName VARCHAR(25),
65 StudentLastName VARCHAR(25),
66 PRIMARY KEY (StudentID)
67);
68INSERT INTO students (StudentFirstName,StudentLastName) VALUES
69('agam' , 'rafaeli'), ('amir' , 'aizinger'), ('avi' , 'caspi'),
70('avia' , 'wolf ');
71CREATE TABLE tests
72(
73 testid INT NOT NULL AUTO_INCREMENT,
74 TestDate DATE,
75 ClassName VARCHAR(25),
76 StudentID INT NOT NULL,
77 Grade INT NOT NULL,
78 PRIMARY KEY (testid),
79 KEY (StudentID)
80);
81INSERT INTO tests (TestDate,ClassName,StudentID,Grade) VALUES
82('2017-07-01', 'Algebra' , 1 , 88 ), ('2017-08-02', 'Algo' , 1 , 97 ),
83('2017-09-01', 'Algebra' , 1 , 80 ), ('2017-09-01', 'Algebra' , 1 , 97 ),
84('2017-09-01', 'Set-theory' , 1 , 85 ), ('2017-09-04', 'Calcules' , 1 , 86 ),
85('2016-05-03', 'Set-theory' , 2 , 84 ), ('2016-07-02', 'Calcules' , 2 , 89 ),
86('2016-07-04', 'Algo' , 2 , 83 ), ('2016-07-05', 'Algebra' , 2 , 79 ),
87('2016-06-03', 'Algebra' , 3 , 99 ), ('2016-07-02', 'Algo' , 3 , 97 ),
88('2016-07-03', 'Calcules' , 3 , 96 ), ('2016-09-03', 'Set-theory' , 3 , 95 ),
89('2016-06-03', 'Algebra' , 4 , 78 );