Oracle SQL - Write SQL to list trainer/course combinations where trainers attend a course taught by a senior colleague before teaching that course themselves.

Requirements

Here is the table

Demo

SQL>
SQL> drop table offerings;

Table dropped.--   w w w .  jav  a2 s.c o  m

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.

Demo

SQL>
SQL> select o.course, o.trainer
  2  from   offerings o
  3  where  exists-- from   www. j a  v a  2  s.  c  o m
  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>

Related Quiz