Oracle PL/SQL/Result Set/Minus

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

All locations, where courses are offering, have no departments

   <source lang="sql">
  

SQL> create table departments

 2  ( deptno NUMBER(2)     constraint D_PK
 3                         primary key
 4  , dname  VARCHAR2(10)
 5  , location VARCHAR2(8)
 6  , mgr    NUMBER(4)
 7  ) ;

Table created. SQL> SQL> insert into departments values (10,"ACCOUNTING","NEW YORK",7); 1 row created. SQL> insert into departments values (20,"TRAINING", "DALLAS", 4); 1 row created. SQL> insert into departments values (30,"SALES", "CHICAGO", 6); 1 row created. SQL> insert into departments values (40,"HR", "BOSTON", 9); 1 row created. SQL> create table offerings

 2  ( course     VARCHAR2(6)
 3  , begindate  DATE
 4  , trainer    NUMBER(4)
 5  , location   VARCHAR2(8)
 6  ) ;

Table created. SQL> insert into offerings values ("SQL",date "2009-04-12",13,"DALLAS" ); 1 row created. SQL> insert into offerings values ("OAU",date "2009-08-10",4,"CHICAGO"); 1 row created. SQL> insert into offerings values ("SQL",date "2009-10-04",1,"SEATTLE"); 1 row created. SQL> insert into offerings values ("SQL",date "2009-12-13",1,"DALLAS" ); 1 row created. SQL> insert into offerings values ("JAV",date "2009-12-13",4,"SEATTLE"); 1 row created. SQL> insert into offerings values ("XML",date "2000-02-03",1,"DALLAS" ); 1 row created. SQL> insert into offerings values ("JAV",date "2000-02-01",11,"DALLAS" ); 1 row created. SQL> insert into offerings values ("PLS",date "2000-09-11",8,"DALLAS" ); 1 row created. SQL> insert into offerings values ("XML",date "2000-09-18",NULL,"SEATTLE"); 1 row created. SQL> insert into offerings values ("OAU",date "2000-09-27",13,"DALLAS" ); 1 row created. SQL> insert into offerings values ("ERM",date "2001-01-15",NULL, NULL ); 1 row created. SQL> insert into offerings values ("PRO",date "2001-02-19",NULL,"DALLAS" ); 1 row created. SQL> insert into offerings values ("RSD",date "2001-02-24",8,"CHICAGO"); 1 row created. SQL> SQL> select o.location from offerings o

 2  minus
 3  select d.location from departments d;

LOCATION


SEATTLE

SQL> SQL> SQL> drop table offerings; Table dropped. SQL> drop table departments; Table dropped. SQL>


 </source>
   
  


An MINUS query that uses an ORDER BY clause

   <source lang="sql">
 

SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,

 2                    ENAME VARCHAR2(10),
 3                    JOB VARCHAR2(9),
 4                    MGR NUMBER(4),
 5                    HIREDATE DATE,
 6                    SAL NUMBER(7, 2),
 7                    COMM NUMBER(7, 2),
 8                    DEPTNO NUMBER(2));

SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> SQL> select * from emp;

    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- --------- ---------- --------- ---------- ---------- ----------
     7369 SMITH      CLERK           7902 17-DEC-80        800                    20
     7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300         30
     7521 WARD       SALESMAN        7698 22-FEB-81       1250        500         30
     7566 JONES      MANAGER         7839 02-APR-81       2975                    20
     7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400         30
     7698 BLAKE      MANAGER         7839 01-MAY-81       2850                    30
     7782 CLARK      MANAGER         7839 09-JUN-81       2450                    10
     7788 SCOTT      ANALYST         7566 09-DEC-82       3000                    20
     7839 KING       PRESIDENT            17-NOV-81       5000                    10
     7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0         30
     7876 ADAMS      CLERK           7788 12-JAN-83       1100                    20
    EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM     DEPTNO

---------- --------- ---------- --------- ---------- ---------- ----------
     7900 JAMES      CLERK           7698 03-DEC-81        950                    30
     7902 FORD       ANALYST         7566 03-DEC-81       3000                    20
     7934 MILLER     CLERK           7782 23-JAN-82       1300                    10

SQL> SQL> -- With ORDER BY Clause SQL> SQL> -- An MINUS query that uses an ORDER BY clause: SQL> SQL> select empno, ename

 2      from emp
 3      where sal > 1000
 4      MINUS
 5      select empno, ename
 6      from emp
 7      where sal > 2000
 8      ORDER BY 2;
    EMPNO ENAME

----------
     7876 ADAMS
     7499 ALLEN
     7654 MARTIN
     7934 MILLER
     7844 TURNER
     7521 WARD

SQL> SQL> drop table emp; SQL> SQL> SQL> SQL> SQL>


 </source>
   
  


Minus id column

   <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> CREATE TABLE server_usage (

 2    pro_id                   NUMBER(4),
 3    emp_id                  NUMBER,
 4    time_log_date                DATE,
 5    hours_logged                 NUMBER(8,2),
 6    dollars_charged              NUMBER(8,2),
 7    CONSTRAINT server_usage_pk  PRIMARY KEY (pro_id, emp_id, time_log_date)
 8  );

Table created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1001,101,to_date("4-Apr-2004","dd-mon-yyyy"),1123,222);

1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1002,102,to_date("4-Apr-2005","dd-mon-yyyy"),1124,223);

1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1003,103,to_date("4-Apr-2006","dd-mon-yyyy"),1125,224);

1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1004,104,to_date("4-Apr-2007","dd-mon-yyyy"),1126,225);

1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1005,105,to_date("4-Apr-2008","dd-mon-yyyy"),1127,226);

1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1001,106,to_date("4-Apr-2009","dd-mon-yyyy"),1128,227);

1 row created. SQL> INSERT INTO server_usage(pro_id, emp_id, time_log_date, hours_logged, dollars_charged)

 2                    VALUES (1002,107,to_date("4-Apr-2010","dd-mon-yyyy"),1129,228);

1 row created. SQL> SQL> SQL> SET ECHO ON SQL> SELECT emp_id

 2  FROM emp
 3  MINUS
 4  SELECT emp_id
 5  FROM server_usage;
      108
      110
      111
      112
      113

5 rows selected. SQL> SQL> SQL> drop table server_usage; Table dropped. SQL> drop table emp; Table dropped. SQL>


 </source>
   
  


MINUS: returns all those records from the first query, but not the second

   <source lang="sql">
 

SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,

 2                    ENAME VARCHAR2(10),
 3                    JOB VARCHAR2(9),
 4                    MGR NUMBER(4),
 5                    HIREDATE DATE,
 6                    SAL NUMBER(7, 2),
 7                    COMM NUMBER(7, 2),
 8                    DEPTNO NUMBER(2));

Table created. SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> -- MINUS set operator returns all those records that are returned from the first query, but not the second. SQL> SQL> select empno, ename

 2        from emp
 3       where ename like "A%"
 4          or ename like "B%"
 5      minus
 6      select empno, ename
 7        from emp
 8       where ename like "B%"
 9          or ename like "C%";
    EMPNO ENAME

----------
     7499 ALLEN
     7876 ADAMS

SQL> SQL> SQL> SQL> SQL> drop table emp; Table dropped. SQL> SQL> SQL>


 </source>
   
  


minus with like operator

   <source lang="sql">
  

SQL> SQL> CREATE TABLE employees

 2  ( employee_id          number(10)      not null,
 3    last_name            varchar2(50)      not null,
 4    email                varchar2(30),
 5    hire_date            date,
 6    job_id               varchar2(30),
 7    department_id        number(10),
 8    salary               number(6),
 9    manager_id           number(6)
10  );

Table created. SQL> SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary,department_id ,manager_id)

 2                values ( 1001, "Lawson", "lawson@g.ru", "01-JAN-2002","MGR", 30000,1 ,1004);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)

 2                values ( 1002, "Wells", "wells@g.ru", "01-JAN-2002", "DBA", 20000,2, 1005 );

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id ,manager_id)

 2                 values( 1003, "Bliss", "bliss@g.ru", "01-JAN-2002", "PROG", 24000,3 ,1004);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1004,  "Kyte", "tkyte@a.ru", SYSDATE-3650, "MGR",25000 ,4, 1005);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1005, "Viper", "sdillon@a .ru", SYSDATE, "PROG", 20000, 1, 1006);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id,manager_id)

 2                 values( 1006, "Beck", "clbeck@g.ru", SYSDATE, "PROG", 20000, 2, null);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1007, "Java", "java01@g.ru", SYSDATE, "PROG", 20000, 3, 1006);

1 row created. SQL> SQL> insert into employees( employee_id, last_name, email, hire_date, job_id, salary, department_id, manager_id)

 2                 values( 1008, "Oracle", "wvelasq@g.ru", SYSDATE, "DBA", 20000, 4, 1006);

1 row created. SQL> SQL> select * from employees; EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
      1001 Lawson                                             lawson@g.ru                   01-JAN-02 MGR                                        1      30000       1004
      1002 Wells                                              wells@g.ru                    01-JAN-02 DBA                                        2      20000       1005
      1003 Bliss                                              bliss@g.ru                    01-JAN-02 PROG                                       3      24000       1004
      1004 Kyte                                               tkyte@a.ru                    13-JUN-98 MGR                                        4      25000       1005
      1005 Viper                                              sdillon@a .ru                 10-JUN-08 PROG                                       1      20000       1006
      1006 Beck                                               clbeck@g.ru                   10-JUN-08 PROG                                       2      20000
      1007 Java                                               java01@g.ru                   10-JUN-08 PROG                                       3      20000       1006

EMPLOYEE_ID LAST_NAME EMAIL HIRE_DATE JOB_ID DEPARTMENT_ID SALARY MANAGER_ID


-------------------------------------------------- ------------------------------ --------- ------------------------------ ------------- ---------- ----------
      1008 Oracle                                             wvelasq@g.ru                  10-JUN-08 DBA                                        4      20000       1006

8 rows selected. SQL> SQL> select employee_id, last_name

 2        from employees
 3       where last_name like "A%"
 4          or last_name like "B%"
 5      minus
 6      select employee_id, last_name
 7        from employees
 8       where last_name like "B%"
 9          or last_name like "C%"
10     /

no rows selected SQL> drop table employees; Table dropped. SQL>


 </source>
   
  


One result set "minus" another result set

   <source lang="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.56000000000000 Toronto Programmer 02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78000000000000 Vancouver Tester 03 James Smith 12-DEC-78 15-MAR-90 6544.78000000000000 Vancouver Tester 04 Celia Rice 24-OCT-82 21-APR-99 2344.78000000000000 Vancouver Manager 05 Robert Black 15-JAN-84 08-AUG-98 2334.78000000000000 Vancouver Tester 06 Linda Green 30-JUL-87 04-JAN-96 4322.78000000000000 New York Tester 07 David Larry 31-DEC-90 12-FEB-98 7897.78000000000000 New York Manager 08 James Cat 17-SEP-96 15-APR-02 1232.78000000000000 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> -- One result set "minus" another result set SQL> select id, last_name

 2        from Employee
 3      minus
 4      select id, last_name
 5        from Employee
 6       where last_name like "B%"
 7          or last_name like "C%";

ID LAST_NAME


----------

01 Martin 02 Mathews 03 Smith 04 Rice 06 Green 07 Larry 6 rows selected. SQL> SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL>


 </source>
   
  


Understanding Intersections and Differences

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

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

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

        2

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


 </source>
   
  


Use COUNT to count the rows from a subquery with set operator

   <source lang="sql">
  

SQL> -- SQL> SQL> create table customer(

 2           cust_no                   integer                 primary key
 3          ,lastname                  varchar2(20)    not null
 4          ,firstname                 varchar2(15)    not null
 5          ,midinit                   varchar2(1)
 6          ,street                    varchar2(30)
 7          ,city                      varchar2(20)
 8          ,state                             varchar2(2)
 9          ,zip                               varchar2(5)
10          ,zip_4                             varchar2(4)
11          ,area_code                         varchar2(3)
12          ,phone                             varchar2(8)
13          ,company_name                      varchar2(50)
14  );

Table created. SQL> SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2                values(1, "Allen", "Joe","J","10 Ave","London","CA","11111","1111","111", "111-1111","Big Company");

1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2                values(2,"Ward","Sue","W","20 Ave","New York","NY","44444","4444","444", "436-4444","B Company");

1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2                values(3,"Jason","Pure","J","50 St","Longli","CA","55555","5555","555", "234-4444","C Company");

1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2                values(4,"Bird","Jill", null,"30 St","Pais","NY","22222","2222","222", "634-7733","D Company");

1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2                values(5,"Hill","Carl","H","19 Drive","A Town","CA","66666","6566","666", "243-4243","E Company");

1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2                values(6,"Peter","Yari","P","38 Ave","Small City","NY","77777","7777","777", "454-5443","F Inc");

1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2                values(7,"Joe","Paula","J","78 St. Apt 3A","Queen City","NY","32322","2323","888", "664-4333","E Inc");

1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2                values(8,"Chili","Steve","C","38 Ave Apt 62","Mili","CA","88888","8888","787", "456-4566","G Inc");

1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2                values(9,"Mona","Joe","M","930 Ave933","Kansas City","MO","12345","1234","412", "456-4563","H Inc");

1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2                values(10,"Hack","Kisi","H","Kings Rd","Bellmore","NY","54321","3898","516", "767-5677","I Inc");

1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2                values(11,"Bill","Jose","B","12 Giant Rd.","Newton","NJ","23454","1234","958", "123-7367","J Associates");

1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2                values(12,"Taker","Lawrence","T","1 Sask Rd.","Camp","NJ","19191","3298","928", "123-7384","K Company");

1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2                values(13,"Richer","Doris","R","213 Easy Street","WarPease","RI","34343","2112","501", "123-7384","L Inc");

1 row created. SQL> insert into customer( cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)

 2                values(14,"Pete","Doris","P","9 Ave","New York","NY","45454","4222","112", "123-1234","M Company");

1 row created. SQL> SQL> create table cust_history(

 2           cust_no                   integer           primary key
 3          ,lastname                  varchar2(20)    not null
 4          ,firstname                 varchar2(15)    not null
 5          ,midinit                   varchar2(1)
 6          ,street                    varchar2(30)
 7          ,city                      varchar2(20)
 8          ,state                     varchar2(2)
 9          ,zip                       varchar2(5)
10          ,zip_4                     varchar2(4)
11          ,area_code                 varchar2(3)
12          ,phone                     varchar2(8)
13         ,company_name               varchar2(50)
14  );

Table created. SQL> insert into cust_history(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone)

 2                    values(3,"Brad","Ted","T","13 Street","Newton","RI","22222","3333","401", "123-4321");

1 row created. SQL> insert into cust_history( cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone)

 2                    values(4,"Wite","Doris","R","94 Ave","New York","NY","22299","3222","212", "123-1234");

1 row created. SQL> SQL> SQL> SQL> select count(*)

 2  from (select cust_no, lastname
 3         from customer
 4         minus
 5        select cust_no, lastname
 6         from cust_history );
 COUNT(*)

       14

1 row selected. SQL> SQL> drop table customer; Table dropped. SQL> drop table cust_history; Table dropped. SQL> SQL> --


 </source>