Get all managers : IN « Query Select « Oracle PL/SQL Tutorial






SQL>
SQL>
SQL> create table employees(
  2    empno      NUMBER(4)
  3  , ename      VARCHAR2(8)
  4  , init       VARCHAR2(5)
  5  , job        VARCHAR2(8)
  6  , mgr        NUMBER(4)
  7  , bdate      DATE
  8  , msal       NUMBER(6,2)
  9  , comm       NUMBER(6,2)
 10  , deptno     NUMBER(2) ) ;

Table created.

SQL>
SQL>
SQL> insert into employees values(1,'Jason',  'N',  'TRAINER', 2,   date '1965-12-18',  800 , NULL,  10);

1 row created.

SQL> insert into employees values(2,'Jerry',  'J',  'SALESREP',3,   date '1966-11-19',  1600, 300,   10);

1 row created.

SQL> insert into employees values(3,'Jord',   'T' , 'SALESREP',4,   date '1967-10-21',  1700, 500,   20);

1 row created.

SQL> insert into employees values(4,'Mary',   'J',  'MANAGER', 5,   date '1968-09-22',  1800, NULL,  20);

1 row created.

SQL> insert into employees values(5,'Joe',    'P',  'SALESREP',6,   date '1969-08-23',  1900, 1400,  30);

1 row created.

SQL> insert into employees values(6,'Black',  'R',  'MANAGER', 7,   date '1970-07-24',  2000, NULL,  30);

1 row created.

SQL> insert into employees values(7,'Red',    'A',  'MANAGER', 8,   date '1971-06-25',  2100, NULL,  40);

1 row created.

SQL> insert into employees values(8,'White',  'S',  'TRAINER', 9,   date '1972-05-26',  2200, NULL,  40);

1 row created.

SQL> insert into employees values(9,'Yellow', 'C',  'DIRECTOR',10,  date '1973-04-27',  2300, NULL,  20);

1 row created.

SQL> insert into employees values(10,'Pink',  'J',  'SALESREP',null,date '1974-03-28',  2400, 0,     30);

1 row created.

SQL>
SQL> select empno, ename, init
  2  from   employees
  3  where  empno in (select mgr
  4                   from   employees);

     EMPNO ENAME    INIT
---------- -------- -----
         2 Jerry    J
         3 Jord     T
         4 Mary     J
         5 Joe      P
         6 Black    R
         7 Red      A
         8 White    S
         9 Yellow   C
        10 Pink     J

9 rows selected.

SQL>
SQL> drop table employees;

Table dropped.

SQL>
SQL>








2.17.IN
2.17.1.Using the IN Operator
2.17.2.Use null value with IN operator
2.17.3.Use varchar2 type value with IN operator
2.17.4.Use in with subquery
2.17.5.Map pair with subquery by using IN operator
2.17.6.Get all managers
2.17.7.List all non-manager employee
2.17.8.Combine subquery and other conditions
2.17.9.Subquery and update statement
2.17.10.In subquery
2.17.11.In and subquery
2.17.12.Value pair and in operator