Oracle PL/SQL Tutorial/Set/Union

Материал из SQL эксперт
Перейти к: навигация, поиск

Combining Data with UNION for single column

   <source lang="sql">

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));

SQL> SQL> 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> SQL> SQL> SELECT empID

 2     FROM empExam
 3     WHERE Mark < 40
 4  UNION
 5  SELECT empID
 6     FROM dept
 7     WHERE GRADE < 40
 8  ORDER BY empID;
    EMPID

        2

1 row selected. SQL> SQL> SQL> drop table empExam; Table dropped. SQL> drop table dept; Table dropped.</source>


Produce a summary of the grouped aggregate by using set functions

   <source lang="sql">

SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060

725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860

221","YYYYMMDD"), 6661.78, "Vancouver","Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900

315","YYYYMMDD"), 6544.78, "Vancouver","Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990

421","YYYYMMDD"), 2344.78, "Vancouver","Manager")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980

808","YYYYMMDD"), 2334.78, "Vancouver","Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary, City,        Description)
 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960

104","YYYYMMDD"), 4322.78,"New York", "Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary, City,        Description)
 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980

212","YYYYMMDD"), 7897.78,"New York", "Manager")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary, City,        Description)
 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020

415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")

 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SELECT count(*), city

 2  FROM employee
 3  GROUP BY city
 4  UNION
 5  SELECT count(*), null
 6  FROM employee;
 COUNT(*) CITY

----------
        1 Toronto
        2 New York
        5 Vancouver
        8

SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL></source>


Union date column

   <source lang="sql">

SQL> CREATE TABLE emp (

 2    emp_id               NUMBER,
 3    ename             VARCHAR2(40),
 4    hire_date        DATE DEFAULT sysdate,
 5    end_date DATE,
 6    rate     NUMBER(5,2),
 7    CONSTRAINT emp_pk    PRIMARY KEY (emp_id)
 8  );

Table created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300); 1 row created. SQL> SQL> SQL> SET ECHO ON SQL> SELECT hire_date emp_date

 2  FROM emp
 3  UNION
 4  SELECT end_date
 5  FROM emp;

15-NOV-61 16-SEP-64 23-AUG-76 29-DEC-87 01-MAR-94 02-JAN-04 03-MAR-04 01-APR-04 04-APR-04 05-MAY-04 15-JUN-04 30-SEP-04 31-OCT-04 15-NOV-04

15 rows selected. SQL> SQL> SQL> drop table emp; Table dropped.</source>


UNION with order by clause

   <source lang="sql">

SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SELECT id, first_name

 2  FROM employee where id <3
 3  UNION
 4  SELECT id, first_name
 5  FROM employee where id >4
 6  order by id;

ID FIRST_NAME


----------

01 Jason 02 Alison 05 Robert 06 Linda 07 David 08 James 6 rows selected.

SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped.</source>


Union with table join

   <source lang="sql">

SQL> create table employees(

 2    empno      NUMBER(4)
 3  , ename      VARCHAR2(8)
 4  , init       VARCHAR2(5)
 5  , job        VARCHAR2(8)
 6  , mgr        NUMBER(4)
 7  , bdate      DATE
 8  , msal       NUMBER(6,2)
 9  , comm       NUMBER(6,2)
10  , deptno     NUMBER(2) ) ;

Table created. SQL> SQL> SQL> insert into employees values(1,"Jason", "N", "TRAINER", 2, date "1965-12-18", 800 , NULL, 10); 1 row created. SQL> insert into employees values(2,"Jerry", "J", "SALESREP",3, date "1966-11-19", 1600, 300, 10); 1 row created. SQL> insert into employees values(3,"Jord", "T" , "SALESREP",4, date "1967-10-21", 1700, 500, 20); 1 row created. SQL> insert into employees values(4,"Mary", "J", "MANAGER", 5, date "1968-09-22", 1800, NULL, 20); 1 row created. SQL> insert into employees values(5,"Joe", "P", "SALESREP",6, date "1969-08-23", 1900, 1400, 30); 1 row created. SQL> insert into employees values(6,"Black", "R", "MANAGER", 7, date "1970-07-24", 2000, NULL, 30); 1 row created. SQL> insert into employees values(7,"Red", "A", "MANAGER", 8, date "1971-06-25", 2100, NULL, 40); 1 row created. SQL> insert into employees values(8,"White", "S", "TRAINER", 9, date "1972-05-26", 2200, NULL, 40); 1 row created. SQL> insert into employees values(9,"Yellow", "C", "DIRECTOR",10, date "1973-04-27", 2300, NULL, 20); 1 row created. SQL> insert into employees values(10,"Pink", "J", "SALESREP",null,date "1974-03-28", 2400, 0, 30); 1 row created. SQL> SQL> create table departments

 2  ( deptno NUMBER(2)
 3  , dname  VARCHAR2(10)
 4  , location VARCHAR2(20)
 5  , mgr    NUMBER(4)
 6  ) ;

Table created. SQL> SQL> insert into departments values (10,"ACCOUNTING","NEW YORK" , 2); 1 row created. SQL> insert into departments values (20,"TRAINING", "VANCOUVER", 3); 1 row created. SQL> insert into departments values (30,"SALES", "CHICAGO", 4); 1 row created. SQL> insert into departments values (40,"HR", "BOSTON", 5); 1 row created. SQL> SQL> select d.deptno

 2  ,        d.dname
 3  ,        count(e.empno) as headcount
 4  from     employees e
 5  ,        departments d
 6  where    e.deptno = d.deptno
 7  group by d.deptno
 8  ,        d.dname
 9  union
10  select   x.deptno
11  ,        x.dname
12  ,        0        as headcount
13  from     departments x
14  where    x.deptno not in (select y.deptno
15                            from   employees y);
   DEPTNO DNAME       HEADCOUNT

---------- ----------
       10 ACCOUNTING          2
       20 TRAINING            3
       30 SALES               3
       40 HR                  2

SQL> SQL> drop table employees; Table dropped. SQL> SQL> drop table departments; Table dropped. SQL></source>


Use UNION to connect column data and summary data

   <source lang="sql">

--create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060

725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860

221","YYYYMMDD"), 6661.78, "Vancouver","Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900

315","YYYYMMDD"), 6544.78, "Vancouver","Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990

421","YYYYMMDD"), 2344.78, "Vancouver","Manager")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary,  City,       Description)
 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980

808","YYYYMMDD"), 2334.78, "Vancouver","Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary, City,        Description)
 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960

104","YYYYMMDD"), 4322.78,"New York", "Tester")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary, City,        Description)
 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980

212","YYYYMMDD"), 7897.78,"New York", "Manager")

 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date,

                 Salary, City,        Description)
 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020

415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")

 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SELECT first_name, salary

 2  FROM employee
 3  UNION
 4  SELECT "The average .......", average
 5  FROM
 6    (SELECT avg(salary) average
 7    FROM employee);

FIRST_NAME SALARY


----------

Alison 6661.78 Celia 2344.78 David 7897.78 James 1232.78 James 6544.78 Jason 1234.56 Linda 4322.78 Robert 2334.78 The average ....... 4071.7525 9 rows selected. SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL></source>


Using the UNION Operator

The UNION operator returns all non-duplicate rows retrieved by the queries.



   <source lang="sql">

SQL> -- create demo table SQL> create table Employee(

 2    ID                 VARCHAR2(4 BYTE)         NOT NULL,
 3    First_Name         VARCHAR2(10 BYTE),
 4    Last_Name          VARCHAR2(10 BYTE),
 5    Start_Date         DATE,
 6    End_Date           DATE,
 7    Salary             Number(8,2),
 8    City               VARCHAR2(10 BYTE),
 9    Description        VARCHAR2(15 BYTE)
10  )
11  /

Table created. SQL> SQL> -- prepare data SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2               values ("01","Jason",    "Martin",  to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto",  "Programmer")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("02","Alison",   "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("03","James",    "Smith",   to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("04","Celia",    "Rice",    to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("05","Robert",   "Black",   to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("06","Linda",    "Green",   to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York",  "Tester")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("07","David",    "Larry",   to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York",  "Manager")
 3  /

1 row created. SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)

 2                values("08","James",    "Cat",     to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /

ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION


---------- ---------- --------- --------- ---------- ---------- ---------------

01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SELECT id, first_name

 2  FROM employee where id <3
 3  UNION
 4  SELECT id, first_name
 5  FROM employee where id >4;

ID FIRST_NAME


----------

01 Jason 02 Alison 05 Robert 06 Linda 07 David 08 James 6 rows selected. SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL></source>


Using union in subquery

   <source lang="sql">

SQL> SQL> CREATE TABLE emp (

 2    emp_id               NUMBER,
 3    ename             VARCHAR2(40),
 4    hire_date        DATE DEFAULT sysdate,
 5    end_date DATE,
 6    rate     NUMBER(5,2),
 7    CONSTRAINT emp_pk    PRIMARY KEY (emp_id)
 8  );

Table created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300); 1 row created. SQL> SQL> SQL> SET ECHO ON SQL> SELECT all_dates.emp_date, COUNT(*)

 2  FROM (
 3     SELECT hire_date emp_date
 4     FROM emp
 5     UNION ALL
 6     SELECT end_date
 7     FROM emp) all_dates
 8  GROUP BY all_dates.emp_date
 9  ORDER BY COUNT(*) DESC;
                  4

15-NOV-61 2 04-APR-04 2 02-JAN-04 1 03-MAR-04 1 01-APR-04 1 05-MAY-04 1 15-JUN-04 1 30-SEP-04 1 31-OCT-04 1 15-NOV-04 1 01-MAR-94 1 29-DEC-87 1 23-AUG-76 1 16-SEP-64 1 15 rows selected. SQL> SQL> SQL> drop table emp; Table dropped. SQL></source>