Using Set Membership with Subqueries
SQL> CREATE TABLE manager (
2 managerID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL);
Table created.
SQL> INSERT INTO manager (managerID,Name) VALUES (1,'Victor');
1 row created.
SQL> INSERT INTO manager (managerID,Name) VALUES (2,'Bill');
1 row created.
SQL> INSERT INTO manager (managerID,Name) VALUES (3,'Mary');
1 row created.
SQL> INSERT INTO manager (managerID,Name) VALUES (4,'Jack');
1 row created.
SQL> INSERT INTO manager (managerID,Name) VALUES (5,'Peter');
1 row created.
SQL> INSERT INTO manager (managerID,Name) VALUES (6,'Tom');
1 row created.
SQL>
SQL>
SQL> CREATE TABLE Course (
2 CourseID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50),
4 Credits INT);
Table created.
SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (1,'SQL',5);
1 row created.
SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (2,'Java',5);
1 row created.
SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (3,'XML',5);
1 row created.
SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (4,'ERP',5);
1 row created.
SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (5,'Oracle',5);
1 row created.
SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (6,'MySQL',5);
1 row created.
SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (7,'SQL Server',5);
1 row created.
SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (8,'Javascript',10);
1 row created.
SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (9,'Office',5);
1 row created.
SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (10,'VB',5);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE Room (
2 RoomID INT NOT NULL PRIMARY KEY,
3 Comments VARCHAR(50),
4 Capacity INT);
Table created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (1,'Main hall',500);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (2,'Science Department',200);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (3,'Science Room 1',100);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (4,'Languages Block',300);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (5,'Languages Room 1',75);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (6,'Languages Room 2',50);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (7,'Engineering Center',200);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (8,'Engineering Room 1',100);
1 row created.
SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (9,'Engineering Room 2',50);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE Class (
2 ClassID INT NOT NULL PRIMARY KEY,
3 CourseID INT NOT NULL,
4 managerID INT NOT NULL,
5 RoomID INT NOT NULL,
6 Time VARCHAR(50));
Table created.
SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (1,1,1,6,'Mon 09:00-11:00');
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (2,2,1,5,'Mon 11:00-12:00, Thu 09:00-11:00');
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (3,3,2,3,'Mon 14:00-16:00');
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (4,4,3,2,'Tue 10:00-12:00, Thu 14:00-15:00');
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (5,5,2,9,'Tue 14:00-16:00');
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (6,6,3,2,'Tue 16:00-17:00, Thu 15:00-17:00');
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (7,7,4,3,'Wed 09:00-11:00');
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (8,8,5,8,'Wed 11:00-13:00, Fri 09:00-11:00');
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (9,9,1,5,'Fri 11:00-13:00');
1 row created.
SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (10,10,6,9,'Fri 14:00-16:00');
1 row created.
SQL>
SQL>
SQL> CREATE TABLE emp (
2 empID INT NOT NULL PRIMARY KEY,
3 Name VARCHAR(50) NOT NULL);
Table created.
SQL> INSERT INTO emp (empID,Name) VALUES (1,'Tom');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (2,'Jack');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (3,'Mary');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (4,'Bill');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (5,'Cat');
1 row created.
SQL> INSERT INTO emp (empID,Name) VALUES (6,'Victor');
1 row created.
SQL>
SQL> CREATE TABLE Exam (
2 ExamID INT NOT NULL PRIMARY KEY,
3 CourseID INT NOT NULL,
4 managerID INT NOT NULL,
5 SustainedOn DATE,
6 Comments VARCHAR(255));
Table created.
SQL> INSERT INTO Exam (ExamID,CourseID,managerID,SustainedOn,Comments) VALUES (1,1,1,DATE '2003-03-12','Hard');
1 row created.
SQL> INSERT INTO Exam (ExamID,CourseID,managerID,SustainedOn,Comments) VALUES (2,2,1,DATE '2003-03-13','Simple');
1 row created.
SQL> INSERT INTO Exam (ExamID,CourseID,managerID,SustainedOn,Comments) VALUES (3,3,2,DATE '2003-03-11','1 hour long');
1 row created.
SQL> INSERT INTO Exam (ExamID,CourseID,managerID,SustainedOn) VALUES (4,4,3,DATE '2003-03-18');
1 row created.
SQL> INSERT INTO Exam (ExamID,CourseID,managerID,SustainedOn,Comments) VALUES (5,5,2,DATE '2003-03-19','2 hours long');
1 row created.
SQL>
SQL> CREATE TABLE dept (
2 deptID INT NOT NULL PRIMARY KEY,
3 empID INT NOT NULL,
4 ClassID INT NOT NULL,
5 EnrolledOn DATE,
6 Grade INT);
Table created.
SQL>
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (1,1,1,DATE '2002-09-23',62);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (2,1,2,DATE '2002-09-30',70);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (3,2,3,DATE '2003-09-23',51);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (4,2,5,DATE '2003-09-23',41);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (5,2,6,DATE '2003-09-23',68);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (6,3,4,DATE '2002-09-30',78);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (7,3,7,DATE '2002-09-30',80);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (8,4,8,DATE '2002-09-20',70);
1 row created.
SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (9,4,5,DATE '2002-09-20',60);
1 row created.
SQL>
SQL>
SQL> CREATE TABLE empExam (
2 empID INT NOT NULL,
3 ExamID INT NOT NULL,
4 Mark INT,
5 Taken SMALLINT,
6 Comments VARCHAR(255),
7 CONSTRAINT PK_empExam PRIMARY KEY (empID, ExamID));
Table created.
SQL>
SQL>
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,1,55,1,'Satisfactory');
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,2,73,1,'Good result');
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,3,44,1,'Hard');
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,5,39,0,'Simple');
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken) VALUES (2,6,63,1);
1 row created.
SQL>
SQL>
SQL> SELECT empID, Name FROM emp WHERE empID IN
2 (SELECT empID FROM dept WHERE ClassID IN
3 (SELECT ClassID FROM Class WHERE managerID IN
4 (SELECT managerID FROM manager
5 WHERE Name LIKE '%Williams%')));
no rows selected
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table dept;
Table dropped.
SQL> drop table Class;
Table dropped.
SQL> drop table manager;
Table dropped.
SQL>
Related examples in the same category