Oracle SQL - Write SQL to list classrooms used at the same time in the same course location.

Requirements

Here is the table

Demo

SQL>
SQL> drop table courses;

Table dropped.--  w w w  .  j  a  v  a  2 s .com

SQL> create table courses(
  2  code        VARCHAR2(6)  primary key,
  3  description VARCHAR2(30) not null,
  4  category    CHAR(3)      not null,
  5  duration    NUMBER(2)    not null) ;
SQL> insert into courses values('SQL','Introduction to SQL',         'GEN',4);
SQL> insert into courses values('JSON','Oracle for application users','GEN',1);
SQL> insert into courses values('JAVA','Java for Oracle developers',  'BLD',4);
SQL> insert into courses values('PLS','Introduction to PL/SQL',      'BLD',1);
SQL> insert into courses values('XML','XML for Oracle developers',   'BLD',2);
SQL> insert into courses values('ERM','Data modeling with ERM',      'DSG',3);
SQL> insert into courses values('PMT','Process modeling techniques', 'DSG',1);
SQL> insert into courses values('RSD','Relational system design',    'DEF',2);
SQL> insert into courses values('PRO','Prototyping',                 'DSG',5);
SQL> insert into courses values('GEN','System generation',           'DSG',4);
SQL>
SQL>
SQL> drop table offerings;

Table dropped.

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>

Write SQL to list classrooms used at the same time in the same course location.

Demo

SQL>
SQL> select o1.location
  2  ,      o1.begindate, o1.course, c1.duration
  3  ,      o2.begindate, o2.course-- w  w w.j  ava 2s. c  o m
  4  from   offerings    o1
  5  ,      offerings    o2
  6  ,      courses      c1
  7  where  o1.location  = o2.location
  8  and   (o1.begindate < o2.begindate or
  9         o1.course   <> o2.course      )
 10  and    o1.course    = c1.code
 11  and    o2.begindate between o1.begindate
 12                          and o1.begindate + c1.duration;

LOCATION BEGINDATE COURSE   DURATION BEGINDATE COURSE
-------- --------- ------ ---------- --------- ------
DALLAS   01-FEB-00 JAVA            4 03-FEB-00 XML

SQL>