Combining Data with UNION and subquery : UNION « Result Set « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Result Set
  3. UNION

Combining Data with UNION and subquery

  
SQL>
SQL> CREATE TABLE Instructor (
  2     InstructorID INT NOT NULL PRIMARY KEY,
  3     Name        VARCHAR(50) NOT NULL);

Table created.

SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (1,'Victor');

1 row created.

SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (2,'Bill');

1 row created.

SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (3,'Mary');

1 row created.

SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (4,'Jack');

1 row created.

SQL> INSERT INTO Instructor (InstructorID,Name) VALUES (5,'Peter');

1 row created.

SQL> INSERT INTO Instructor (InstructorID,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>
SQL>
SQL>
SQL> CREATE TABLE Room (
  2     RoomID   INT NOT NULL PRIMARY KEY,
  3     Comments VARCHAR(50),
  4     Capacity INT);

SQL>
SQL>
SQL>
SQL> CREATE TABLE Class (
  2     ClassID     INT NOT NULL PRIMARY KEY,
  3     CourseID    INT NOT NULL,
  4     InstructorID INT NOT NULL,
  5     RoomID      INT NOT NULL,
  6     Time        VARCHAR(50));

Table created.

SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,RoomID,Time) VALUES (1,1,1,6,'Mon 09:00-11:00');

1 row created.

SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,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,InstructorID,RoomID,Time) VALUES (3,3,2,3,'Mon 14:00-16:00');

1 row created.

SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,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,InstructorID,RoomID,Time) VALUES (5,5,2,9,'Tue 14:00-16:00');

1 row created.

SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,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,InstructorID,RoomID,Time) VALUES (7,7,4,3,'Wed 09:00-11:00');

1 row created.

SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,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,InstructorID,RoomID,Time) VALUES (9,9,1,5,'Fri 11:00-13:00');

1 row created.

SQL> INSERT INTO Class (ClassID,CourseID,InstructorID,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>
SQL> CREATE TABLE Exam (
  2     ExamID      INT NOT NULL PRIMARY KEY,
  3     CourseID    INT NOT NULL,
  4     InstructorID INT NOT NULL,
  5     SustainedOn DATE,
  6     Comments    VARCHAR(255));

Table created.

SQL> INSERT INTO Exam (ExamID,CourseID,InstructorID,SustainedOn,Comments) VALUES (1,1,1,DATE '2003-03-12','Hard');

1 row created.

SQL> INSERT INTO Exam (ExamID,CourseID,InstructorID,SustainedOn,Comments) VALUES (2,2,1,DATE '2003-03-13','Simple');

1 row created.

SQL> INSERT INTO Exam (ExamID,CourseID,InstructorID,SustainedOn,Comments) VALUES (3,3,2,DATE '2003-03-11','1 hour long');

1 row created.

SQL> INSERT INTO Exam (ExamID,CourseID,InstructorID,SustainedOn) VALUES (4,4,3,DATE '2003-03-18');

1 row created.

SQL> INSERT INTO Exam (ExamID,CourseID,InstructorID,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>
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>
SQL> SELECT Name, 'Instructor' As Role FROM Instructor
  2     WHERE InstructorID = (
  3        SELECT InstructorID FROM Class WHERE ClassID = 1)
  4  UNION
  5  SELECT Name, 'emp' FROM emp
  6     WHERE empID IN (
  7        SELECT empID FROM dept WHERE ClassID = 1);

NAME
--------------------------------------------------
ROLE
--------------------------------
Tom
emp

Victor
Instructor


2 rows selected.

SQL>
SQL>
SQL> drop table Instructor;

Table dropped.

SQL> drop table Class;

Table dropped.

SQL> drop table emp;

Table dropped.

SQL> drop table dept;

Table dropped.








Related examples in the same category

1.Union result set in action
2.union with like operator
3.UNION: adds all rows from the first and the second query, eliminate any duplicates, and return the results
4.A UNION query that uses an ORDER BY clause
5.Combining Data with UNION for single column
6.Union DBA_TABLES and DBA_INDEXES
7.Union date column
8.Union dba_indexes and dba_tables
9.Union two complex queries and subquery
10.Eliminate the duplication and mimic a UNION operator by appending the DISTINCT operator
11.Take the result of the MULTISET UNION DISTINCT operation and pass it as an argument to the SET operator to eliminate duplicates.
12.Using union in subquery