Mega Code Archive

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.