/*
mysql> select * from Course;
+----------+----------------------+---------+
| CourseID | Name | Credits |
+----------+----------------------+---------+
| 1 | Mediaeval Romanian | 5 |
| 2 | Philosophy | 5 |
| 3 | History of Computing | 5 |
+----------+----------------------+---------+
3 rows in set (0.00 sec)
mysql> SELECT Name FROM Course
-> WHERE CourseID IN
-> (
-> SELECT CourseID from EXAM
-> WHERE SustainedOn='26-MAR-03'
-> );
Empty set, 1 warning (0.00 sec)
*/
Drop TABLE Course;
Drop TABLE Exam;
CREATE TABLE Course (
CourseID INT NOT NULL PRIMARY KEY,
Name VARCHAR(50),
Credits INT)
TYPE = InnoDB;
CREATE TABLE Exam (
ExamID INT NOT NULL PRIMARY KEY,
CourseID INT NOT NULL,
SustainedOn DATE,
Comments VARCHAR(255),
INDEX examcourse_index(CourseID)
)TYPE = InnoDB;
INSERT INTO Course (CourseID,Name,Credits) VALUES (1,'Mediaeval Romanian',5);
INSERT INTO Course (CourseID,Name,Credits) VALUES (2,'Philosophy',5);
INSERT INTO Course (CourseID,Name,Credits) VALUES (3,'History of Computing',5);
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES
(1,1,'2003-03-12','JavaScript');
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES
(2,1,'2003-03-13','Java');
INSERT INTO Exam (ExamID,CourseID,SustainedOn,Comments) VALUES
(3,2,'2003-03-11','Python');
INSERT INTO Exam (ExamID,CourseID,SustainedOn) VALUES
(4,3,'2003-03-18','Swing');
select * from Course;
SELECT Name FROM Course
WHERE CourseID IN
(
SELECT CourseID from EXAM
WHERE SustainedOn='26-MAR-03'
);