/*
mysql> INSERT INTO tmp
-> SELECT Student.Name AS StudentName, AVG(Mark) AS AverageMark
-> FROM StudentExam
-> INNER JOIN Student
-> ON StudentExam.StudentID = Student.StudentID
-> GROUP BY Student.Name;
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from tmp;
+-------------+-------------+
| StudentName | AverageMark |
+-------------+-------------+
| Cory But | 44 |
| Joe Wang | 64 |
+-------------+-------------+
2 rows in set (0.00 sec)
mysql> SELECT StudentName, AverageMark FROM tmp WHERE AverageMark < 50;
+-------------+-------------+
| StudentName | AverageMark |
+-------------+-------------+
| Cory But | 44 |
+-------------+-------------+
1 row in set (0.00 sec)
*/
/* Create Student and StudentExam TABLE */
Drop TABLE Student;
Drop TABLE StudentExam;
CREATE TABLE Student (
StudentID INT NOT NULL PRIMARY KEY,
Name VARCHAR(50) NOT NULL
)TYPE = InnoDB;
CREATE TABLE StudentExam (
StudentID INT NOT NULL,
Mark INT,
Comments VARCHAR(255),
CONSTRAINT FK_Student FOREIGN KEY (StudentID)
REFERENCES Student(StudentID)
)TYPE = InnoDB;
/* Insert Data*/
INSERT INTO Student (StudentID,Name) VALUES (1,"John Jones");
INSERT INTO Student (StudentID,Name) VALUES (2,"Gary Burton");
INSERT INTO Student (StudentID,Name) VALUES (3,"Emily Scarlett");
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (1,55,"Java");
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (1,73,"C#");
INSERT INTO StudentExam (StudentID,Mark,Comments) VALUES (2,44,"JavaScript");
/* Using Temporary Tables */
CREATE TEMPORARY TABLE tmp (StudentName VARCHAR(50), AverageMark INT);
INSERT INTO tmp
SELECT Student.Name AS StudentName, AVG(Mark) AS AverageMark
FROM StudentExam
INNER JOIN Student
ON StudentExam.StudentID = Student.StudentID
GROUP BY Student.Name;
select * from tmp;
SELECT StudentName, AverageMark FROM tmp WHERE AverageMark < 50;
SELECT StudentName, AverageMark FROM tmp WHERE AverageMark > 70;