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