Here is the table
SQL> SQL> drop table offerings; Table dropped.-- ww w . j a v a 2s . com SQL> create table offerings( 2 course VARCHAR2(6) not null, 3 begindate DATE not null, 4 trainer NUMBER(4) , 5 location VARCHAR2(8)) ; SQL> SQL> insert into offerings values ('SQL',date '1999-04-12',7902,'DALLAS' ); SQL> insert into offerings values ('JSON',date '1999-08-10',7004,'CHICAGO'); SQL> insert into offerings values ('SQL',date '1999-10-04',7001,'SEATTLE'); SQL> insert into offerings values ('SQL',date '1999-12-13',7001,'DALLAS' ); SQL> insert into offerings values ('JAVA',date '1999-12-13',7004,'SEATTLE'); SQL> insert into offerings values ('XML',date '2000-02-03',7001,'DALLAS' ); SQL> insert into offerings values ('JAVA',date '2000-02-01',7011,'DALLAS' ); SQL> insert into offerings values ('PLS',date '2000-09-11',7008,'DALLAS' ); SQL> insert into offerings values ('XML',date '2000-09-18',NULL,'SEATTLE'); SQL> insert into offerings values ('JSON',date '2000-09-27',7902,'DALLAS' ); SQL> insert into offerings values ('ERM',date '2001-01-15',NULL, NULL ); SQL> insert into offerings values ('PRO',date '2001-02-19',NULL,'DALLAS' ); SQL> insert into offerings values ('RSD',date '2001-02-24',7008,'CHICAGO'); SQL> SQL> drop table registrations; Table dropped. SQL> create table registrations( 2 attendee NUMBER(4) not null, 3 course VARCHAR2(6) , 4 begindate DATE not null, 5 evaluation NUMBER(1) check (evaluation in (1,2,3,4,5))) ; SQL> SQL> insert into registrations values (7002,'SQL',date '1999-04-12',4 ); SQL> insert into registrations values (7934,'SQL',date '1999-04-12',5 ); SQL> insert into registrations values (7006,'SQL',date '1999-04-12',4 ); SQL> insert into registrations values (7011,'SQL',date '1999-04-12',2 ); SQL> insert into registrations values (7008,'SQL',date '1999-10-04',NULL); SQL> insert into registrations values (7009,'SQL',date '1999-10-04',3 ); SQL> insert into registrations values (7902,'SQL',date '1999-10-04',4 ); SQL> insert into registrations values (7902,'SQL',date '1999-12-13',NULL); SQL> insert into registrations values (7006,'SQL',date '1999-12-13',NULL); SQL> insert into registrations values (7003,'JSON',date '1999-08-10',4 ); SQL> insert into registrations values (7012,'JSON',date '1999-08-10',4 ); SQL> insert into registrations values (7902,'JSON',date '1999-08-10',5 ); SQL> insert into registrations values (7010,'JSON',date '2000-09-27',5 ); SQL> insert into registrations values (7002,'JAVA',date '1999-12-13',2 ); SQL> insert into registrations values (7007,'JAVA',date '1999-12-13',5 ); SQL> insert into registrations values (7011,'JAVA',date '1999-12-13',5 ); SQL> insert into registrations values (7008,'JAVA',date '1999-12-13',5 ); SQL> insert into registrations values (7009,'JAVA',date '1999-12-13',4 ); SQL> insert into registrations values (7004,'JAVA',date '2000-02-01',3 ); SQL> insert into registrations values (7008,'JAVA',date '2000-02-01',4 ); SQL> insert into registrations values (7006,'JAVA',date '2000-02-01',5 ); SQL> insert into registrations values (7012,'XML',date '2000-02-03',4 ); SQL> insert into registrations values (7002,'XML',date '2000-02-03',5 ); SQL> insert into registrations values (7004,'PLS',date '2000-09-11',NULL); SQL> insert into registrations values (7002,'PLS',date '2000-09-11',NULL); SQL> insert into registrations values (7011,'PLS',date '2000-09-11',NULL); SQL>
Write SQL to list trainer/course combinations where trainers attend a course taught by a senior colleague before teaching that course themselves.
SQL> SQL> select o.course, o.trainer 2 from offerings o 3 where exists-- w w w. j ava 2s . c om 4 (select r.* 5 from registrations r 6 where r.attendee = o.trainer 7 and r.course = o.course 8 and r.begindate < o.begindate) 9 and not exists 10 (select fo.* 11 from offerings fo 12 where fo.course = o.course 13 and fo.trainer = o.trainer 14 and fo.begindate < o.begindate); COURSE TRAINER ------ ---------- JAVA 7011 JSON 7902 SQL>