SQL/MySQL/Table Index/Temporary Table

Using Temporary Tables

/*
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;