Value pair and in operator : Query IN « Select Query « Oracle PL / SQL






Value pair and in operator

    
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
  7                            (attendee,course,begindate)
  8  ) ;

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>
SQL> create table offerings
  2  ( course     VARCHAR2(6)
  3  , begindate  DATE
  4  , Coder    NUMBER(4)
  5  , location   VARCHAR2(8)
  6  ) ;

Table created.

SQL> insert into offerings values ('SQL',date '2009-04-12',13,'DALLAS' );

1 row created.

SQL> insert into offerings values ('OAU',date '2009-08-10',4,'CHICAGO');

1 row created.

SQL> insert into offerings values ('SQL',date '2009-10-04',1,'SEATTLE');

1 row created.

SQL> insert into offerings values ('SQL',date '2009-12-13',1,'DALLAS' );

1 row created.

SQL> insert into offerings values ('JAV',date '2009-12-13',4,'SEATTLE');

1 row created.

SQL> insert into offerings values ('XML',date '2000-02-03',1,'DALLAS' );

1 row created.

SQL> insert into offerings values ('JAV',date '2000-02-01',11,'DALLAS' );

1 row created.

SQL> insert into offerings values ('PLS',date '2000-09-11',8,'DALLAS' );

1 row created.

SQL> insert into offerings values ('XML',date '2000-09-18',NULL,'SEATTLE');

1 row created.

SQL> insert into offerings values ('OAU',date '2000-09-27',13,'DALLAS' );

1 row created.

SQL> insert into offerings values ('ERM',date '2001-01-15',NULL, NULL    );

1 row created.

SQL> insert into offerings values ('PRO',date '2001-02-19',NULL,'DALLAS' );

1 row created.

SQL> insert into offerings values ('RSD',date '2001-02-24',8,'CHICAGO');

1 row created.

SQL>
SQL>
SQL> select attendee
  2  from   registrations
  3  where (course, begindate) in
  4        (select course, begindate
  5         from   offerings
  6         where  location = 'CHICAGO');

no rows selected

SQL>
SQL> drop table offerings;

Table dropped.

SQL> drop table registrations;

Table dropped.

SQL>

   
    
    
    
  








Related examples in the same category

1.Query with an IN operator
2.Use in operator with number type values
3.Use in operator with 'and' in where clause
4.Use in operator with table join
5.Using the IN operator: compare with OR
6.NOT IN processes a null in a list of values
7.Use in operator with varchar2 type value
8.all customers who have placed at least one order from us in the last month
9.not in (1,2,3,NULL)
10.not in vs not exists
11.All attendees whose id is not in the list
12.Use in operator to match three employee numbers