Oracle PL/SQL Tutorial/Query Select/IN — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
|
(нет различий)
| |
Текущая версия на 10:11, 26 мая 2010
Содержание
- 1 Combine subquery and other conditions
- 2 Get all managers
- 3 In and subquery
- 4 In subquery
- 5 List all non-manager employee
- 6 Map pair with subquery by using IN operator
- 7 Subquery and update statement
- 8 Use in with subquery
- 9 Use null value with IN operator
- 10 Use varchar2 type value with IN operator
- 11 Using the IN Operator
- 12 Value pair and in operator
Combine subquery and other conditions
SQL>
SQL>
SQL> create table course_schedule
2 ( course VARCHAR2(6)
3 , begindate DATE
4 , trainer NUMBER(4)
5 , location VARCHAR2(20)) ;
Table created.
SQL>
SQL>
SQL> insert into course_schedule values ("SQL",date "1999-04-12",1,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("OAU",date "1999-08-10",2,"CHICAGO");
1 row created.
SQL> insert into course_schedule values ("SQL",date "1999-10-04",3,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("SQL",date "1999-12-13",4,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("JAV",date "1999-12-13",5,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("XML",date "2000-02-03",6,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("JAV",date "2000-02-01",7,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("PLS",date "2000-09-11",8,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("XML",date "2000-09-18",NULL,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("OAU",date "2000-09-27",9,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("ERM",date "2001-01-15",10, NULL );
1 row created.
SQL> insert into course_schedule values ("PRO",date "2001-02-19",NULL,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("RSD",date "2001-02-24",8,"CHICAGO");
1 row created.
SQL>
SQL>
SQL> create table courses
2 ( code VARCHAR2(6)
3 , description VARCHAR2(30)
4 , category CHAR(3)
5 , duration NUMBER(2)) ;
SQL>
SQL>
SQL> insert into courses values("SQL","SQL course", "GEN",4);
1 row created.
SQL> insert into courses values("OAU","Oracle course", "GEN",1);
1 row created.
SQL> insert into courses values("JAV","Java course", "BLD",4);
1 row created.
SQL> insert into courses values("PLS","PL/SQL course", "BLD",1);
1 row created.
SQL> insert into courses values("XML","XML course", "BLD",2);
1 row created.
SQL> insert into courses values("ERM","ERM course", "DSG",3);
1 row created.
SQL> insert into courses values("PMT","UML course", "DSG",1);
1 row created.
SQL> insert into courses values("RSD","C# course", "DSG",2);
1 row created.
SQL> insert into courses values("PRO","C++ course", "DSG",5);
1 row created.
SQL> insert into courses values("GEN","GWT course", "DSG",4);
1 row created.
SQL>
SQL>
SQL>
SQL> select *
2 from course_schedule
3 where begindate between date "1999-01-01" and date "1999-12-31"
4 and course in (select code from courses where category = "GEN");
COURSE BEGINDATE TRAINER LOCATION
------ --------- ---------- --------------------
SQL 13-DEC-99 4 DALLAS
SQL 04-OCT-99 3 SEATTLE
SQL 12-APR-99 1 VANCOUVER
OAU 10-AUG-99 2 CHICAGO
SQL>
SQL>
SQL> drop table course_schedule;
Table dropped.
SQL>
SQL> drop table courses;
Table dropped.
SQL>
SQL>
Get all managers
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>
In and subquery
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>
In subquery
SQL>
SQL> create table emp(
2 emp_id integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,shortZipCode varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,company_name varchar2(50));
Table created.
SQL>
SQL>
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (1,"Jones","Joe","J","1 Ave","New York","NY","11202","1111","212", "221-4333","Big Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (2,"Smith","Sue","J","1 Street","New York","NY","11444","1111","212", "436-6773","Little Company");
1 row created.
SQL> insert into emp(emp_id,lastname,firstname,midinit,street,city,state,zip,shortZipCode,area_code,phone,company_name)values
2 (3,"X","Peggy","J","1 Drive","New York","NY","45502","2222","212", "234-4444","Medium Company");
1 row created.
SQL>
SQL> create table gift(
2 gift_id integer primary key
3 ,emp_id integer
4 ,register_date date not null
5 ,total_price number(7,2)
6 ,deliver_date date
7 ,deliver_time varchar2(7)
8 ,payment varchar2(2)
9 ,emp_no number(3,0)
10 ,deliver_name varchar2(35)
11 ,message varchar2(100)
12 );
Table created.
SQL>
SQL> insert into gift(gift_id,emp_id,register_date,total_price,deliver_date,deliver_time,payment,emp_no,deliver_name,message)values
2 (1,1,"14-Feb-1999", 123.12, "14-Feb-1999", "12 noon", "CA",1, null, "Happy Birthday to you");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time ,payment ,emp_no,deliver_name ,message)values
2 (2,1,"14-Feb-1999", 50.98, "14-feb-1999", "1 pm", "CA",7, "name1", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (3, 2,"14-Feb-1999", 35.99, "14-feb-1999", "1 pm", "VS",2, "Tom", "Happy Birthday");
1 row created.
SQL> insert into gift(gift_id ,emp_id ,register_date ,total_price ,deliver_date ,deliver_time,payment ,emp_no,deliver_name ,message )values
2 (4, 2,"14-Feb-1999", 19.95, "14-feb-1999", "5 pm", "CA",2, "Mary", "Happy Birthday");
1 row created.
SQL>
SQL> select firstname || " " || lastname as "Name", "(" || area_code || ")" || phone as "Telephone"
2 from emp
3 where emp_id in
4 (select emp_id from gift where register_date > add_months(sysdate, -1));
no rows selected
SQL>
SQL> drop table emp;
Table dropped.
SQL> drop table gift;
Table dropped.
List all non-manager employee
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>
SQL> select empno, ename, init
2 from employees
3 where empno not in (select mgr
4 from employees
5 where mgr is not null);
EMPNO ENAME INIT
---------- -------- -----
1 Jason N
SQL>
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL>
Map pair with subquery by using IN operator
SQL>
SQL>
SQL> create table course_schedule
2 ( course VARCHAR2(6)
3 , begindate DATE
4 , trainer NUMBER(4)
5 , location VARCHAR2(20)) ;
Table created.
SQL>
SQL>
SQL> insert into course_schedule values ("SQL",date "1999-04-12",1,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("OAU",date "1999-08-10",2,"CHICAGO");
1 row created.
SQL> insert into course_schedule values ("SQL",date "1999-10-04",3,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("SQL",date "1999-12-13",4,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("JAV",date "1999-12-13",5,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("XML",date "2000-02-03",6,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("JAV",date "2000-02-01",7,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("PLS",date "2000-09-11",8,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("XML",date "2000-09-18",NULL,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("OAU",date "2000-09-27",9,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("ERM",date "2001-01-15",10, NULL );
1 row created.
SQL> insert into course_schedule values ("PRO",date "2001-02-19",NULL,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("RSD",date "2001-02-24",8,"CHICAGO");
1 row created.
SQL>
SQL> create table registrations
2 ( attendee NUMBER(4)
3 , course VARCHAR2(6)
4 , begindate DATE
5 , evaluation NUMBER(1)) ;
Table created.
SQL>
SQL>
SQL> insert into registrations values (1, "SQL",date "1999-04-12",4 );
1 row created.
SQL> insert into registrations values (2, "SQL",date "1999-12-13",NULL);
1 row created.
SQL> insert into registrations values (3, "SQL",date "1999-12-13",NULL);
1 row created.
SQL> insert into registrations values (4, "OAU",date "1999-08-10",4 );
1 row created.
SQL> insert into registrations values (5, "OAU",date "2000-09-27",5 );
1 row created.
SQL> insert into registrations values (6, "JAV",date "1999-12-13",2 );
1 row created.
SQL> insert into registrations values (7, "JAV",date "2000-02-01",4 );
1 row created.
SQL> insert into registrations values (8, "JAV",date "2000-02-01",5 );
1 row created.
SQL> insert into registrations values (9, "XML",date "2000-02-03",4 );
1 row created.
SQL> insert into registrations values (10,"XML",date "2000-02-03",5 );
1 row created.
SQL> insert into registrations values (1, "PLS",date "2000-09-11",NULL);
1 row created.
SQL> insert into registrations values (2, "PLS",date "2000-09-11",NULL);
1 row created.
SQL> insert into registrations values (3, "PLS",date "2000-09-11",NULL);
1 row created.
SQL>
SQL>
SQL> select attendee
2 from registrations
3 where (course, begindate) in
4 (select course, begindate
5 from course_schedule
6 where location = "CHICAGO");
ATTENDEE
----------
4
SQL>
SQL>
SQL> drop table registrations;
Table dropped.
SQL>
SQL> drop table course_schedule;
Table dropped.
SQL>
Subquery and update statement
SQL>
SQL>
SQL> create table course_schedule
2 ( course VARCHAR2(6)
3 , begindate DATE
4 , trainer NUMBER(4)
5 , location VARCHAR2(20)) ;
Table created.
SQL>
SQL>
SQL> insert into course_schedule values ("SQL",date "1999-04-12",1,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("OAU",date "1999-08-10",2,"CHICAGO");
1 row created.
SQL> insert into course_schedule values ("SQL",date "1999-10-04",3,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("SQL",date "1999-12-13",4,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("JAV",date "1999-12-13",5,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("XML",date "2000-02-03",6,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("JAV",date "2000-02-01",7,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("PLS",date "2000-09-11",8,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("XML",date "2000-09-18",NULL,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("OAU",date "2000-09-27",9,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("ERM",date "2001-01-15",10, NULL );
1 row created.
SQL> insert into course_schedule values ("PRO",date "2001-02-19",NULL,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("RSD",date "2001-02-24",8,"CHICAGO");
1 row created.
SQL>
SQL> create table registrations
2 ( attendee NUMBER(4)
3 , course VARCHAR2(6)
4 , begindate DATE
5 , evaluation NUMBER(1)) ;
Table created.
SQL>
SQL>
SQL> insert into registrations values (1, "SQL",date "1999-04-12",4 );
1 row created.
SQL> insert into registrations values (2, "SQL",date "1999-12-13",NULL);
1 row created.
SQL> insert into registrations values (3, "SQL",date "1999-12-13",NULL);
1 row created.
SQL> insert into registrations values (4, "OAU",date "1999-08-10",4 );
1 row created.
SQL> insert into registrations values (5, "OAU",date "2000-09-27",5 );
1 row created.
SQL> insert into registrations values (6, "JAV",date "1999-12-13",2 );
1 row created.
SQL> insert into registrations values (7, "JAV",date "2000-02-01",4 );
1 row created.
SQL> insert into registrations values (8, "JAV",date "2000-02-01",5 );
1 row created.
SQL> insert into registrations values (9, "XML",date "2000-02-03",4 );
1 row created.
SQL> insert into registrations values (10,"XML",date "2000-02-03",5 );
1 row created.
SQL> insert into registrations values (1, "PLS",date "2000-09-11",NULL);
1 row created.
SQL> insert into registrations values (2, "PLS",date "2000-09-11",NULL);
1 row created.
SQL> insert into registrations values (3, "PLS",date "2000-09-11",NULL);
1 row created.
SQL>
SQL>
SQL> update registrations
2 set evaluation = 1
3 where (course,begindate)
4 in (select course,begindate
5 from course_schedule
6 where location = "CHICAGO");
1 row updated.
SQL>
SQL> drop table course_schedule;
Table dropped.
SQL>
SQL> drop table registrations;
Table dropped.
SQL>
Use in with subquery
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>
SQL> create table course_schedule
2 ( course VARCHAR2(6)
3 , begindate DATE
4 , trainer NUMBER(4)
5 , location VARCHAR2(20)) ;
Table created.
SQL>
SQL>
SQL> insert into course_schedule values ("SQL",date "1999-04-12",1,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("OAU",date "1999-08-10",2,"CHICAGO");
1 row created.
SQL> insert into course_schedule values ("SQL",date "1999-10-04",3,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("SQL",date "1999-12-13",4,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("JAV",date "1999-12-13",5,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("XML",date "2000-02-03",6,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("JAV",date "2000-02-01",7,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("PLS",date "2000-09-11",8,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("XML",date "2000-09-18",NULL,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("OAU",date "2000-09-27",9,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("ERM",date "2001-01-15",10, NULL );
1 row created.
SQL> insert into course_schedule values ("PRO",date "2001-02-19",NULL,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("RSD",date "2001-02-24",8,"CHICAGO");
1 row created.
SQL>
SQL>
SQL> select *
2 from employees
3 where empno in
4 (select id, begindate
5 from course_schedule
6 where location = "CHICAGO");
(select id, begindate
*
ERROR at line 4:
ORA-00913: too many values
SQL>
SQL> drop table employees;
Table dropped.
SQL>
SQL> drop table course_schedule;
Table dropped.
SQL>
Use null value with IN operator
SQL>
SQL> create table registrations
2 ( attendee NUMBER(4)
3 , course VARCHAR2(6)
4 , begindate DATE
5 , evaluation NUMBER(1)) ;
Table created.
SQL>
SQL>
SQL> insert into registrations values (1, "SQL",date "1999-04-12",4 );
1 row created.
SQL> insert into registrations values (2, "SQL",date "1999-12-13",NULL);
1 row created.
SQL> insert into registrations values (3, "SQL",date "1999-12-13",NULL);
1 row created.
SQL> insert into registrations values (4, "OAU",date "1999-08-10",4 );
1 row created.
SQL> insert into registrations values (5, "OAU",date "2000-09-27",5 );
1 row created.
SQL> insert into registrations values (6, "JAV",date "1999-12-13",2 );
1 row created.
SQL> insert into registrations values (7, "JAV",date "2000-02-01",4 );
1 row created.
SQL> insert into registrations values (8, "JAV",date "2001-02-01",5 );
1 row created.
SQL> insert into registrations values (9, "XML",date "2002-02-03",4 );
1 row created.
SQL> insert into registrations values (10,"XML",date "2003-02-03",5 );
1 row created.
SQL> insert into registrations values (1, "PLS",date "2004-09-11",NULL);
1 row created.
SQL> insert into registrations values (2, "PLS",date "2005-09-11",NULL);
1 row created.
SQL> insert into registrations values (3, "PLS",date "2006-09-11",NULL);
1 row created.
SQL>
SQL>
SQL> select *
2 from registrations
3 where evaluation not in (1,2,3,NULL);
no rows selected
SQL>
SQL>
SQL> drop table registrations;
Table dropped.
SQL>
SQL>
Use varchar2 type value with IN operator
SQL>
SQL>
SQL> create table course_schedule
2 ( course VARCHAR2(6)
3 , begindate DATE
4 , trainer NUMBER(4)
5 , location VARCHAR2(20)) ;
Table created.
SQL>
SQL>
SQL> insert into course_schedule values ("SQL",date "1999-04-12",1,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("OAU",date "1999-08-10",2,"CHICAGO");
1 row created.
SQL> insert into course_schedule values ("SQL",date "1999-10-04",3,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("SQL",date "1999-12-13",4,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("JAV",date "1999-12-13",5,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("XML",date "2000-02-03",6,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("JAV",date "2000-02-01",7,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("PLS",date "2000-09-11",8,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("XML",date "2000-09-18",NULL,"SEATTLE");
1 row created.
SQL> insert into course_schedule values ("OAU",date "2000-09-27",9,"DALLAS" );
1 row created.
SQL> insert into course_schedule values ("ERM",date "2001-01-15",10, NULL );
1 row created.
SQL> insert into course_schedule values ("PRO",date "2001-02-19",NULL,"VANCOUVER" );
1 row created.
SQL> insert into course_schedule values ("RSD",date "2001-02-24",8,"CHICAGO");
1 row created.
SQL>
SQL>
SQL>
SQL> select distinct course
2 from course_schedule
3 where location in ("CHICAGO","SEATTLE");
COURSE
------
OAU
JAV
XML
SQL
RSD
SQL>
SQL> drop table course_schedule;
Table dropped.
Using the IN Operator
The IN operator in a WHERE clause selects only those rows whose column value is in a list.
The following SELECT statement uses the IN operator to retrieve rows from the employee table where the value in the id column is 2, 3, or 5:
SQL> -- create demo table
SQL> create table Employee(
2 ID VARCHAR2(4 BYTE) NOT NULL,
3 First_Name VARCHAR2(10 BYTE),
4 Last_Name VARCHAR2(10 BYTE),
5 Start_Date DATE,
6 End_Date DATE,
7 Salary Number(8,2),
8 City VARCHAR2(10 BYTE),
9 Description VARCHAR2(15 BYTE)
10 )
11 /
Table created.
SQL>
SQL> -- prepare data
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values ("01","Jason", "Martin", to_date("19960725","YYYYMMDD"), to_date("20060725","YYYYMMDD"), 1234.56, "Toronto", "Programmer")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("02","Alison", "Mathews", to_date("19760321","YYYYMMDD"), to_date("19860221","YYYYMMDD"), 6661.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("03","James", "Smith", to_date("19781212","YYYYMMDD"), to_date("19900315","YYYYMMDD"), 6544.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("04","Celia", "Rice", to_date("19821024","YYYYMMDD"), to_date("19990421","YYYYMMDD"), 2344.78, "Vancouver","Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("05","Robert", "Black", to_date("19840115","YYYYMMDD"), to_date("19980808","YYYYMMDD"), 2334.78, "Vancouver","Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("06","Linda", "Green", to_date("19870730","YYYYMMDD"), to_date("19960104","YYYYMMDD"), 4322.78,"New York", "Tester")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("07","David", "Larry", to_date("19901231","YYYYMMDD"), to_date("19980212","YYYYMMDD"), 7897.78,"New York", "Manager")
3 /
1 row created.
SQL> insert into Employee(ID, First_Name, Last_Name, Start_Date, End_Date, Salary, City, Description)
2 values("08","James", "Cat", to_date("19960917","YYYYMMDD"), to_date("20020415","YYYYMMDD"), 1232.78,"Vancouver", "Tester")
3 /
1 row created.
SQL>
SQL>
SQL>
SQL> -- display data in the table
SQL> select * from Employee
2 /
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
01 Jason Martin 25-JUL-96 25-JUL-06 1234.56 Toronto Programmer
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
04 Celia Rice 24-OCT-82 21-APR-99 2344.78 Vancouver Manager
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
06 Linda Green 30-JUL-87 04-JAN-96 4322.78 New York Tester
07 David Larry 31-DEC-90 12-FEB-98 7897.78 New York Manager
08 James Cat 17-SEP-96 15-APR-02 1232.78 Vancouver Tester
8 rows selected.
SQL>
SQL>
SQL> SELECT * FROM employee WHERE id IN (2, 3, 5);
ID FIRST_NAME LAST_NAME START_DAT END_DATE SALARY CITY DESCRIPTION
---- ---------- ---------- --------- --------- ---------- ---------- ---------------
02 Alison Mathews 21-MAR-76 21-FEB-86 6661.78 Vancouver Tester
03 James Smith 12-DEC-78 15-MAR-90 6544.78 Vancouver Tester
05 Robert Black 15-JAN-84 08-AUG-98 2334.78 Vancouver Tester
SQL>
SQL>
SQL>
SQL>
SQL> -- clean the table
SQL> drop table Employee
2 /
Table dropped.
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>