Oracle SQL - Write SQL to list course offerings for course category GEN in 1999

Requirements

Here is the table

Demo

SQL>
SQL> drop table offerings;

Table dropped.--   w  w  w. jav  a 2 s. c  om

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 courses;

Table dropped.

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>

Write SQL to list course offerings for course category GEN in 1999

Demo

SQL>
SQL> select *--   w w w  .j ava 2 s . c  o m
  2  from   offerings
  3  where  begindate between date '1999-01-01'
  4                         and date '1999-12-31'
  5  and    course in (select code
  6                      from   courses
  7                      where  category = 'GEN');

COURSE BEGINDATE    TRAINER LOCATION
------ --------- ---------- --------
SQL    12-APR-99       7902 DALLAS
JSON   10-AUG-99       7004 CHICAGO
SQL    04-OCT-99       7001 SEATTLE
SQL    13-DEC-99       7001 DALLAS

SQL>

• Previous
• Next