|
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 sWHERE EXISTS ( SELECT StudentID FROM StudentExam e WHERE Mark < 40 AND e.StudentID = s.StudentID); |
