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