· 8 years ago · Dec 07, 2017, 09:40 PM
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 | AVG(average) |
34+------------------+-----------------+--------------+
35| agam | rafaeli | 87.20832500 |
36| amir | aizinger | 83.75000000 |
37| avi | caspi | 96.75000000 |
38| avia | wolf | 79.75000000 |
39| ben | moskovich | 80.50000000 |
40| chen | segalovich | 90.50000000 |
41| dana | levy | 72.00000000 |
42| daniel | marcus | 88.00000000 |
43| daphna | chwarts | 90.25000000 |
44| david | cohen | 87.75000000 |
45+------------------+-----------------+--------------+
46
47SELECT StudentFirstName, StudentLastName, AVG(average)
48FROM
49(
50SELECT StudentFirstName, StudentLastName, AVG(Grade) AS average
51FROM tests
52INNER JOIN students ON tests.StudentID=students.StudentID
53GROUP BY StudentFirstName,ClassName) AS t
54GROUP BY StudentFirstName, StudentLastName;
55
56DROP DATABASE IF EXISTS error404;
57CREATE DATABASE error404;
58USE error404
59CREATE TABLE students
60(
61 StudentID INT NOT NULL AUTO_INCREMENT,
62 StudentFirstName VARCHAR(25),
63 StudentLastName VARCHAR(25),
64 PRIMARY KEY (StudentID)
65);
66INSERT INTO students (StudentFirstName,StudentLastName) VALUES
67('agam' , 'rafaeli'), ('amir' , 'aizinger'), ('avi' , 'caspi'),
68('avia' , 'wolf ');
69CREATE TABLE tests
70(
71 testid INT NOT NULL AUTO_INCREMENT,
72 TestDate DATE,
73 ClassName VARCHAR(25),
74 StudentID INT NOT NULL,
75 Grade INT NOT NULL,
76 PRIMARY KEY (testid),
77 KEY (StudentID)
78);
79INSERT INTO tests (TestDate,ClassName,StudentID,Grade) VALUES
80('2017-07-01', 'Algebra' , 1 , 88 ), ('2017-08-02', 'Algo' , 1 , 97 ),
81('2017-09-01', 'Algebra' , 1 , 80 ), ('2017-09-01', 'Algebra' , 1 , 97 ),
82('2017-09-01', 'Set-theory' , 1 , 85 ), ('2017-09-04', 'Calcules' , 1 , 86 ),
83('2016-05-03', 'Set-theory' , 2 , 84 ), ('2016-07-02', 'Calcules' , 2 , 89 ),
84('2016-07-04', 'Algo' , 2 , 83 ), ('2016-07-05', 'Algebra' , 2 , 79 ),
85('2016-06-03', 'Algebra' , 3 , 99 ), ('2016-07-02', 'Algo' , 3 , 97 ),
86('2016-07-03', 'Calcules' , 3 , 96 ), ('2016-09-03', 'Set-theory' , 3 , 95 ),
87('2016-06-03', 'Algebra' , 4 , 78 );