/*
mysql> Select * from StudentExam;
+-----------+------+------------+
| StudentID | Mark | Comments |
+-----------+------+------------+
| 10 | 46 | Java |
| 10 | 65 | C# |
| 10 | 79 | JavaScript |
| 11 | 66 | Java |
| 11 | 85 | C# |
| 11 | 99 | JavaScript |
+-----------+------+------------+
6 rows in set (0.01 sec)
mysql> /* Real command */
mysql> SELECT StudentID, AVG(Mark) AS AverageMark
-> FROM StudentExam
-> GROUP BY StudentID
-> ORDER BY AverageMark DESC
-> LIMIT 0, 5;
+-----------+-------------+
| StudentID | AverageMark |
+-----------+-------------+
| 11 | 83.3333 |
| 10 | 63.3333 |
+-----------+-------------+
2 rows in set (0.02 sec)
*/
/* Create table */
Drop TABLE StudentExam;
CREATE TABLE StudentExam (
StudentID INT NOT NULL,
Mark INT,
Comments VARCHAR(255)
)TYPE = InnoDB;
/* Insert data */
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (10,46,'Java');
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (10,65,'C#');
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (10,79,'JavaScript');
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,66,'Java');
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,85,'C#');
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (11,99,'JavaScript');
Select * from StudentExam;
/* Real command */
SELECT StudentID, AVG(Mark) AS AverageMark
FROM StudentExam
GROUP BY StudentID
ORDER BY AverageMark DESC
LIMIT 0, 5;