In and subquery : IN « Query Select « Oracle PL/SQL Tutorial

1. Oracle PL/SQL Tutorial
2. Query Select
3. IN

SQL> create table courses
  2  ( code        VARCHAR2(6)  constraint C_PK primary key
  3  , description VARCHAR2(30)
  4  , category    CHAR(3)
  5  , duration    NUMBER(2)
  6  ) ;

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> insert into registrations values (12,'OAU',date '2009-08-10',4   );

1 row created.

SQL>
SQL> select r.attendee, r.course, r.begindate
  2  from   registrations r
  3  where  r.course in (select c.code
  4                      from   courses c
  5                      where  c.category='BLD');

no rows selected

SQL> drop table registrations;

Table dropped.

SQL> drop table courses;

Table dropped.

SQL>