Using Temporary Tables : Temporary Table « Table Index « SQL / MySQL

1. SQL / MySQL
2. Table Index
3. 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;

           
         
  

Related examples in the same category