Subquery: Using EXIST Operator : Exists « Select Clause « SQL / MySQL

1. SQL / MySQL
2. Select Clause
3. 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);

           
         
    
  

Related examples in the same category