You can force both columns to have the same datatype by converting Date_Time in the RIND_Event table to a date value by using the TO_DATE function, as shown in Listing 8.30.

Listing 8.30. Using a function to force both columns to have the same datatype.

SQL> select Epicenter_Latitude, Epicenter_Longitude, Event_Magnitude,
            Event_Time, Event_Wave
  2  from UNSD_Event
  3  UNION
    4  select Location_Lat, Location_Lon, Richter_Number,
            TO_DATE(Date_Time,'MM-DD-YY HH:MI:SS'), Wave_Type
  5  from RIND_Event;
EPICENTER_LATITUDE EPICENTER_LONGITUDE EVENT_MAGNITUDE EVENT_TIM E
------------------ ------------------- --------------- --------- -
             12.83              189.85             5.8 25-APR-95 P
             22.33              233.31             5.9 03-FEB-95 P
             23.33              179.11             5.3 10-JAN-95 P
             29.84              238.41             6.2 22-MAR-95 S
             31.17              208.33             6.6 19-APR-95 S
             31.84              241.21             6.1 12-MAR-95 S
             37.81              211.84             6.4 11-JAN-95 S

7 rows selected.

If you want to order the results by Event_Time, you can use the ORDER BY clause. However, instead of referring to the column by name, you must reference it by its order in the select list. Listing 8.31 shows how all the rows retrieved by the UNION are ordered by Event_Time, which is the fourth item in the select list.

Listing 8.31. Ordering the rows retrieved by a UNION.

SQL> select Epicenter_Latitude, Epicenter_Longitude, Event_Magnitude,
            Event_Time, Event_Wave
  2  from UNSD_Event
  3  UNION
    4  select Location_Lat, Location_Lon, Richter_Number,
            TO_DATE(Date_Time,'MM-DD-YY HH:MI:SS'), Wave_Type
  5  from RIND_Event
  6  order by 4;
EPICENTER_LATITUDE EPICENTER_LONGITUDE EVENT_MAGNITUDE EVENT_TIM E
------------------ ------------------- --------------- --------- -
             23.33              179.11             5.3 10-JAN-95 P
             37.81              211.84             6.4 11-JAN-95 S
             22.33              233.31             5.9 03-FEB-95 P
             31.84              241.21             6.1 12-MAR-95 S
             29.84              238.41             6.2 22-MAR-95 S
             31.17              208.33             6.6 19-APR-95 S
             12.83              189.85             5.8 25-APR-95 P

7 rows selected.

The MINUS Operator

In addition to the INTERSECT and UNION operators, Oracle also provides the MINUS operator for comparing one set of rows to another set.

The syntax for using the MINUS operator resembles the syntax for the UNION operator:

select-stmt1
MINUS
select-stmt2
[order-by-clause]

The variables are defined as follows:

select-stmt1 and select-stmt2 are valid SELECT statements.

order-by-clause is an ORDER BY clause that references the columns by number rather than by name.

The requirements and considerations for using the MINUS operator are essentially the same as those for the INTERSECT and UNION operators. To illustrate the use of the MINUS operator, consider again the Instructor and Hoover_Instructor tables. You want to determine which instructors at Flugle College are not also teaching at Hoover College. Listing 8.32 shows an example of how the MINUS operator can be used to accomplish this. The query returns nine rows, which represent the instructors at Flugle College who do not teach at Hoover College.

Listing 8.32. Using the MINUS operator.

SQL> select Last_Name, First_Name, MI, Position
    2  from Instructor
  3  minus
   4  select Last_Name, First_Name, MI, Position
   5  from Hoover_Instructor;

LAST_NAME       FIRST_NAME M POSITION
--------------- ---------- - ------------------------
ANGELO          ARTHUR       ASSOCIATE PROFESSOR
BILLINGS        BENJAMIN     ASSISTANT PROFESSOR
DANIELS         LAURA        ASSOCIATE PROFESSOR
EDWARDS         SAMANTHA     PROFESSOR
HITCHCOCK       BORIS        PROFESSOR
PARKER          WILLIAM      PROFESSOR
POULSON         RANIER       PROFESSOR
RICHARDSON      NANCY        ASSISTANT PROFESSOR
WEISS           ROBERTA      PROFESSOR

9 rows selected.

However, the query in Listing 8.32 doesn't tell anything about the instructors at Hoover College who don't teach at Flugle College. To view this information, the two SELECT statements must be reversed, as shown in Listing 8.33.

Listing 8.33. Reversing the SELECT statements associated with the MINUS operator.

SQL> select Last_Name, First_Name, MI, Position
   2  from Hoover_Instructor
  3  minus
   4  select Last_Name, First_Name, MI, Position
   5  from Instructor;

LAST_NAME       FIRST_NAME M POSITION
--------------- ---------- - ------------------------
CHRISTIANSON    PAUL       V PROFESSOR
HUSTON          MYRON      T PROFESSOR
RABINOWITZ      KERMIT     A LECTURER

This example demonstrates that MINUS doesn't tell you all the differences between the two tables—it returns only those rows in the first set that can't be found in the second set. Also, you can specify a WHERE clause in the SELECT statements used by the MINUS operator.

Creating a Table by Selecting from Another Table

When you develop a new database application, you usually go through a phase of experimenting with various table designs. Whether you use legacy data or artificial data, it's nice to have some simple methods for experimentation. One DDL statement that you might want to use is CREATE TABLE—with a subquery.

The syntax is

CREATE TABLE table-name
[(column-definition1, ... , column-definitionN)]
AS
select-statement

The variables are defined as follows:

table-name is the name of the new Oracle table.

column-definition1 through column-definitionN are optional column definitions that are used to specify different column names to be associated with the values returned by the subquery.

select-statement is a valid SELECT statement whose select list is used in creating the new table.

For example, Listing 8.34 contains a statement to create a new table whose rows are a subset of a subset of an existing table.

Listing 8.34. Creating a table based on a subquery.

SQL> create table Anthro_Instructors
  2  as
   3  select * from Instructor
  4  where
  5  Department_ID = `ANTHRO';

Table created.

SQL> select Department_ID, Last_Name, First_Name
  2  from Anthro_Instructors;

DEPARTMENT_ID        LAST_NAME       FIRST_NAME
-------------------- --------------- ----------
ANTHRO               HITCHCOCK       BORIS
ANTHRO               DANIELS         LAURA
ANTHRO               POULSON         RANIER

Notice that Oracle doesn't tell you how many rows are inserted into the new table. In fact, you can create a new table that is empty by specifying an impossible condition, as demonstrated in Listing 8.35.

Listing 8.35. Creating an empty table.

SQL> create table Empty_Instructor
  2  as
   3  select * from Instructor
  4  where
  5  1=2;

Table created.

SQL> select count(*) from Empty_Instructor;

COUNT(*)
--------
       0