All locations, where courses are offering, have no departments (subquery) : Subquery IN « Subquery « Oracle PL / SQL






  1. Oracle PL / SQL
  2. Subquery
  3. Subquery IN

All locations, where courses are offering, have no departments (subquery)

  

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 DISTINCT o.location
  2  from   offerings o
  3  where  o.location not in
  4        (select d.location
  5         from   departments d);

LOCATION
--------
SEATTLE

SQL>
SQL>
SQL> drop table offerings;

Table dropped.

SQL> drop table departments;

Table dropped.

SQL>








Related examples in the same category

1.Multiple Row Subqueries: IN with subquery
2.Multiple Row Subqueries: NOT IN with subquery
3.Not in and subquery
4.in subquery
5.In and subquery
6.Multiple-row subqueries return more than one row of result from the subquery
7.Selecting Products That Belong to Department with Subquery
8.Subquery in from clause
9.Using Set Membership with Subqueries