not exists and subquery
SQL>
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> create table registrations
2 ( attendee NUMBER(4)
3 , course VARCHAR2(6)
4 , begindate DATE
5 , evaluation NUMBER(1)
6 , constraint R_PK primary key(attendee,course,begindate)
7 ) ;
Table created.
SQL>
SQL> insert into registrations values (2,'SQL',date '2009-04-12',4 );
1 row created.
SQL> insert into registrations values (14,'SQL',date '2009-04-12',5 );
1 row created.
SQL> insert into registrations values (6,'SQL',date '2009-04-12',4 );
1 row created.
SQL> insert into registrations values (11,'SQL',date '2009-04-12',2 );
1 row created.
SQL> insert into registrations values (8,'SQL',date '2009-10-04',NULL);
1 row created.
SQL> insert into registrations values (9,'SQL',date '2009-10-04',3 );
1 row created.
SQL> insert into registrations values (13,'SQL',date '2009-10-04',4 );
1 row created.
SQL> insert into registrations values (13,'SQL',date '2009-12-13',NULL);
1 row created.
SQL> insert into registrations values (6,'SQL',date '2009-12-13',NULL);
1 row created.
SQL> insert into registrations values (3,'OAU',date '2009-08-10',4 );
1 row created.
SQL>
SQL> select o.*
2 from offerings o
3 where not exists
4 (select r.*
5 from registrations r
6 where r.course = o.course
7 and r.begindate = o.begindate);
COURSE BEGINDATE TRAINER LOCATION
------ ---------- ---------- --------
JAV 13-12-2009 4 SEATTLE
XML 03-02-2000 1 DALLAS
JAV 01-02-2000 11 DALLAS
PLS 11-09-2000 8 DALLAS
XML 18-09-2000 SEATTLE
OAU 27-09-2000 13 DALLAS
ERM 15-01-2001
PRO 19-02-2001 DALLAS
RSD 24-02-2001 8 CHICAGO
9 rows selected.
SQL> drop table offerings;
Table dropped.
SQL> drop table registrations;
Table dropped.
SQL>
Related examples in the same category