*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
objectDscription
TableBasic unit of storage; composed of rows
ViewLogically represents subsets of data from one or more tables
SequenceGenerates numeric values
IndexImproves the performance of some queries
Sysnonymgives 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


'STUDY > OCP' 카테고리의 다른 글

OCP license.E05.130628  (0) 2013.07.06
OCP license.E04.130627  (0) 2013.07.06
OCP license.E03.130626  (0) 2013.07.06
OCP license.E02.130625  (0) 2013.07.06
OCP license.E01.130624  (0) 2013.07.06
oracle TRUNCATE  (0) 2013.06.26

Comment