Mega Code Archive

 

Categories / MySQL / Select Clause

 

Subquery

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