SQL/MySQL/Select Clause/Exists

Subquery: Using EXIST Operator

/*
mysql> SELECT StudentID, Name FROM Student s
    -> WHERE EXISTS (
    ->    SELECT StudentID FROM StudentExam e
    ->    WHERE Mark < 40 AND e.StudentID = s.StudentID);
+-----------+----------+
| StudentID | Name     |
+-----------+----------+
|         2 | Henry Al |
|         5 | Sam Jun  |
+-----------+----------+
2 rows in set (0.02 sec)

*/
Drop table Student;
Drop table StudentExam;
Drop table Exam;
CREATE TABLE Student (
   StudentID INT NOT NULL PRIMARY KEY,
   Name      VARCHAR(50) NOT NULL
)TYPE = InnoDB;

CREATE TABLE StudentExam (
   StudentID  INT NOT NULL,
   ExamID     INT NOT NULL,
   Mark       INT,
   IfPassed   SMALLINT,
   Comments   VARCHAR(255)
)TYPE = InnoDB;

CREATE TABLE Exam (
   ExamID      INT NOT NULL PRIMARY KEY,
   CourseID    INT NOT NULL,
   ProfessorID INT NOT NULL,
   SustainedOn DATE,
   Comments    VARCHAR(255),
   INDEX       examcourse_index(CourseID),
   CONSTRAINT  FK_ExamCourse FOREIGN KEY (CourseID)
               REFERENCES Course(CourseID),
   INDEX       examprof_index(ProfessorID),
   CONSTRAINT  FK_ExamProf FOREIGN KEY (ProfessorID)
               REFERENCES Professor(ProfessorID)
)TYPE = InnoDB;

INSERT INTO Student (StudentID,Name) VALUES (1,"Joe Wang");
INSERT INTO Student (StudentID,Name) VALUES (2,"Henry Al");
INSERT INTO Student (StudentID,Name) VALUES (3,"Amma Zee");
INSERT INTO Student (StudentID,Name) VALUES (4,"Lili Lee");
INSERT INTO Student (StudentID,Name) VALUES (5,"Sam Jun");
INSERT INTO Student (StudentID,Name) VALUES (6,"Dianna Wang");
INSERT INTO Exam (ExamID,CourseID,ProfessorID,SustainedOn,Comments) VALUES (1,1,1,"2003-03-12","A difficult test that should last an hour");
INSERT INTO Exam (ExamID,CourseID,ProfessorID,SustainedOn,Comments) VALUES (2,2,1,"2003-03-13","A simple two hour test");
INSERT INTO Exam (ExamID,CourseID,ProfessorID,SustainedOn,Comments) VALUES (3,3,2,"2003-03-11","1 hour long");
INSERT INTO Exam (ExamID,CourseID,ProfessorID,SustainedOn) VALUES (4,4,3,"2003-03-18");
INSERT INTO Exam (ExamID,CourseID,ProfessorID,SustainedOn,Comments) VALUES (5,5,2,"2003-03-19","2 hours long");
INSERT INTO Exam (ExamID,CourseID,ProfessorID,SustainedOn) VALUES (6,6,3,"2003-03-25");
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,1,55,1,"Satisfactory");
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (1,2,73,1,"Good result");
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,3,44,1,"Scraped through");
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (2,5,39,0,"Failed, and will need to retake this one later in the year");
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed) VALUES (2,6,63,1);
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (3,4,78,1,"Excellent result");
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (3,7,82,1,"Great result!");
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (4,8,65,1,"Adequate performance");
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (4,11,72,1,"Good result");
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (4,5,53,1,"Below expectations");
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (5,1,26,0,"Very poor performance. Recommend this student drop this module");
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (5,9,68,1,"Good result");
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (5,2,62,1,"Good result");
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (6,3,78,1,"Excellent work");
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed) VALUES (6,6,69,1);
INSERT INTO StudentExam (StudentID,ExamID,Mark,IfPassed,Comments) VALUES (6,10,58,1,"Adequate performance");

SELECT StudentID, Name FROM Student s
WHERE EXISTS (
   SELECT StudentID FROM StudentExam e
   WHERE Mark < 40 AND e.StudentID = s.StudentID);