Here is the table
SQL> SQL> drop table emp; Table dropped.-- ww w . j a v a 2 s. c o m SQL> create table emp( 2 empno NUMBER(4) primary key, 3 ename VARCHAR2(8) not null , 4 init VARCHAR2(5) not null , 5 job VARCHAR2(8) , 6 mgr NUMBER(4) , 7 bdate DATE not null , 8 msal NUMBER(6,2) not null , 9 comm NUMBER(6,2) , 10 deptno NUMBER(2) default 10) ; SQL> SQL> insert into emp values(7001,'SMITH','N', 'TRAINER', 7902,date '1975-12-17', 1800 , NULL, 20); SQL> insert into emp values(7002,'ALLEN','JAM','SALESREP',7006,date '1971-05-20', 1600, 300, 30); SQL> insert into emp values(7003,'WARD', 'TF' ,'SALESREP',7006,date '1972-03-02', 1250, 500, 10); SQL> insert into emp values(7004,'JACK', 'JM', 'MANAGER', 7009,date '1977-04-02', 2975, NULL, 20); SQL> insert into emp values(7005,'BROWN','P', 'SALESREP',7006,date '1976-09-28', 1250, 1400, 30); SQL> insert into emp values(7006,'BLAKE','R', 'MANAGER', 7009,date '1973-11-01', 2850, NULL, 10); SQL> insert into emp values(7007,'CLARK','AB', 'MANAGER', 7009,date '1975-06-09', 2450, NULL, 10); SQL> insert into emp values(7008,'SCOTT','DEF','TRAINER', 7004,date '1979-11-26', 3000, NULL, 20); SQL> insert into emp values(7009,'KING', 'CC', 'DIRECTOR',NULL,date '1972-10-17', 5000, NULL, 10); SQL> insert into emp values(7010,'BREAD','JJ', 'SALESREP',7006,date '1978-09-28', 1500, 0, 30); SQL> insert into emp values(7011,'ADAMS','AA', 'TRAINER', 7008,date '1976-12-30', 1100, NULL, 20); SQL> insert into emp values(7012,'JONES','R', 'ADMIN', 7006,date '1979-10-03', 8000, NULL, 30); SQL> insert into emp values(7902,'FORD', 'MG', 'TRAINER', 7004,date '1979-02-13', 3000, NULL, 20); SQL> insert into emp values(7934,'MARY', 'ABC','ADMIN', 7007,date '1972-01-23', 1300, NULL, 10); SQL> 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> 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 all course course code, begin date, course duration, and name of the trainer.
SQL> SQL> select c.code 2 , o.begindate-- from w ww. j a v a 2 s .c o m 3 , c.duration 4 , e.ename as trainer 5 from emp e 6 , courses c 7 , offerings o 8 where o.trainer = e.empno 9 and o.course = c.code; CODE BEGINDATE DURATION TRAINER ------ --------- ---------- -------- SQL 12-APR-99 4 FORD SQL 04-OCT-99 4 SMITH SQL 13-DEC-99 4 SMITH JSON 27-SEP-00 1 FORD JSON 10-AUG-99 1 JACK JAVA 01-FEB-00 4 ADAMS JAVA 13-DEC-99 4 JACK PLS 11-SEP-00 1 SCOTT XML 03-FEB-00 2 SMITH RSD 24-FEB-01 2 SCOTT 10 rows selected. SQL>