SQL> create table courses
2 ( code VARCHAR2(6) constraint C_PK
3 primary key
4 , description VARCHAR2(30)
5 , category CHAR(3)
6 , duration NUMBER(2)
7 ) ;
Table created.
SQL> insert into courses values('SQL','SQL','GEN',4);
1 row created.
SQL> insert into courses values('OAU','Java','GEN',1);
1 row created.
SQL> insert into courses values('JAV','C++','BLD',4);
1 row created.
SQL> insert into courses values('PLS','C','BLD',1);
1 row created.
SQL> insert into courses values('XML','XML','BLD',2);
1 row created.
SQL> insert into courses values('ERM','ERP','DSG',3);
1 row created.
SQL> insert into courses values('PMT','ERP','DSG',1);
1 row created.
SQL> insert into courses values('RSD','jQuery','DSG',2);
1 row created.
SQL> insert into courses values('PRO','Linux','DSG',5);
1 row created.
SQL> insert into courses values('GEN','Oracle','DSG',4);
1 row created.
SQL>
SQL> create table registrations
2 ( attendee NUMBER(4)
3 , course VARCHAR2(6)
4 , begindate DATE
5 , evaluation NUMBER(1)
6 , constraint R_PK primary key (attendee,course,begindate)
7 ) ;
Table created.
SQL>
SQL> insert into registrations values (2,'SQL',date '2009-04-12',4 );
1 row created.
SQL> insert into registrations values (14,'SQL',date '2009-04-12',5 );
1 row created.
SQL> insert into registrations values (6,'SQL',date '2009-04-12',4 );
1 row created.
SQL> insert into registrations values (11,'SQL',date '2009-04-12',2 );
1 row created.
SQL> insert into registrations values (8,'SQL',date '2009-10-04',NULL);
1 row created.
SQL> insert into registrations values (9,'SQL',date '2009-10-04',3 );
1 row created.
SQL> insert into registrations values (13,'SQL',date '2009-10-04',4 );
1 row created.
SQL> insert into registrations values (13,'SQL',date '2009-12-13',NULL);
1 row created.
SQL> insert into registrations values (6,'SQL',date '2009-12-13',NULL);
1 row created.
SQL> insert into registrations values (3,'OAU',date '2009-08-10',4 );
1 row created.
SQL>
SQL> select attendee
2 from registrations
3 where course in (select code
4 from courses
5 where category = 'BLD');
no rows selected
SQL> select attendee
2 from registrations
3 where evaluation in (select duration
4 from courses
5 where category = 'BLD');
ATTENDEE
----------
11
2
6
3
13
SQL> drop table registrations;
Table dropped.
SQL> drop table courses;
Table dropped.