SQL/MySQL/Select Clause/Exists
Subquery: Using EXIST Operator
<source lang="sql">
/* 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); </source>