Here is the table
SQL> SQL> drop table courses; Table dropped.-- from w w w.java2 s . c om 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.
SQL> SQL> select o1.location 2 , o1.begindate, o1.course, c1.duration 3 , o2.begindate, o2.course-- w w w . ja v a 2 s .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>