SQL/MySQL/Select Clause/Exists

Материал из SQL эксперт
Перейти к: навигация, поиск

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>