|
/* 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,76,'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,69,'JavaScript');
Select * from StudentExam;
/* Real command */
SELECT StudentID, AVG(Mark) AS AverageMark
FROM StudentExam
WHERE ExamID IN ( 5, 8, 11 )
GROUP BY StudentID
HAVING AVG(Mark) < 50 OR AVG(Mark) > 70;
|