OCP license.E03.130626

*UNION Operator

* UNION ALL Operator

* INTERSECT Operator

* MINUS Operator

SQL> select * from a;

        ID
----------
         1
         2
         3

SQL> select * from b;

        ID
----------
         3
         4
         5


SQL> select id  from a
      union all
      select id  from b;

        ID
----------
         1
         2
         3
         3
         4
         5

SQL> select id from a

union

select id from b;

        ID
----------
         1
         2
         3
         4
         5


SQL> select id from a

intersect

select id from b;

        ID
----------
         3

SQL> select id from a

minus

select id from b;

        ID
----------
         1
         2

SQL> select id from b

minus

select id from a;

Set Operator Guidelines
• The expressions in the SELECT lists must match in number and data type.
• Parentheses can be used to alter the sequence of execution.
• The ORDER BY clause:
– Can appear only at the very end of the statement
– Will accept the column name, aliases from the first SELECT statement, or the positional notation

The Oracle Server and Set Operators
• Duplicate rows are automatically eliminated except in UNION ALL.
• Column names from the first query appear in the result.
• The output is sorted in ascending order by default except in UNION ALL.

The iSQL*Plus COLUMN Command
You can use the iSQL*Plus COLUMN command to customize column headings.
Syntax:
      COL[UMN] [{column|alias} [option]]
Where OPTION is:       

      CLE[AR]: Clears any column formats       

      HEA[DING] text: Sets the column heading

      FOR[MAT] format: Changes the display of the column using a format model       

      NOPRINT | PRINT: Suppresses or displays the column heading and data

      NULL

The following statement suppresses the column data and title heading for the column named
A_DUMMY. Notice that the first SELECT clause in the previous slide creates a dummy column
named A_DUMMY.
      COLUMN a_dummy NOPRINT

• DataBases Objects

object	Dscription
Table	Basic unit of storage; composed of rows
View	Logically represents subsets of data from one or more tables
Sequence	Generates numeric values
Index	Improves the performance of some queries
Sysnonym	gives alternative names to objects

Including Constraints
• Constraints enforce rules at the table level.
• Constraints prevent the deletion of a table if there are dependencies.
• The following constraint types are valid:
     – NOT NULL
     – UNIQUE
     – PRIMARY KEY
     – FOREIGN KEY
     – CHECK