sql exists是一個存在判斷 exists是一個存在判斷,如果後面的查詢中有結果,則exists為真,否則為假. 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);
