Using Set Membership with Subqueries : Subquery IN « Subquery « Oracle PL / SQL






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

1.Multiple Row Subqueries: IN with subquery
2.Multiple Row Subqueries: NOT IN with subquery
3.Not in and subquery
4.in subquery
5.All locations, where courses are offering, have no departments (subquery)
6.In and subquery
7.Multiple-row subqueries return more than one row of result from the subquery
8.Selecting Products That Belong to Department with Subquery
9.Subquery in from clause