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