Oracle PL/SQL/Select Query/EXISTS
Содержание
Display the customer number and last name of all customers with no orders.
<source lang="sql">
SQL> SQL> SQL> create table ord(
2 order_no integer primary key 3 ,cust_no integer 4 ,order_date date not null 5 ,total_order_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment_method varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,gift_message varchar2(100) 12 );
Table created. SQL> SQL> SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO" , 2, "Cora", "Happy Birthday from John");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI", 3, "Larry", "Happy New Year from Lawrence");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created. SQL> SQL> SQL> create table customer(
2 cust_no 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 ,zip_4 varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,company_name varchar2(50) 14 );
Table created. SQL> SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1, "Allen", "Joe","J","10 Ave","London","CA","11111","1111","111", "111-1111","Big Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Ward","Sue","W","20 Ave","New York","NY","44444","4444","444", "436-4444","B Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Jason","Pure","J","50 St","Longli","CA","55555","5555","555", "234-4444","C Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Bird","Jill", null,"30 St","Pais","NY","22222","2222","222", "634-7733","D Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"Hill","Carl","H","19 Drive","A Town","CA","66666","6566","666", "243-4243","E Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Yari","P","38 Ave","Small City","NY","77777","7777","777", "454-5443","F Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Joe","Paula","J","78 St. Apt 3A","Queen City","NY","32322","2323","888", "664-4333","E Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Chili","Steve","C","38 Ave Apt 62","Mili","CA","88888","8888","787", "456-4566","G Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","M","930 Ave933","Kansas City","MO","12345","1234","412", "456-4563","H Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hack","Kisi","H","Kings Rd","Bellmore","NY","54321","3898","516", "767-5677","I Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bill","Jose","B","12 Giant Rd.","Newton","NJ","23454","1234","958", "123-7367","J Associates");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(12,"Taker","Lawrence","T","1 Sask Rd.","Camp","NJ","19191","3298","928", "123-7384","K Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(13,"Richer","Doris","R","213 Easy Street","WarPease","RI","34343","2112","501", "123-7384","L Inc");
1 row created. SQL> insert into customer( cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(14,"Pete","Doris","P","9 Ave","New York","NY","45454","4222","112", "123-1234","M Company");
1 row created. SQL> SQL> SQL> SQL> select cust_no, lastname
2 from customer c 3 where not exists 4 (select * from ord o where o.cust_no = c.cust_no); CUST_NO LASTNAME
--------------------
5 Hill 8 Chili 3 Jason 10 Hack 14 Pete 13 Richer 11 Bill
7 rows selected. SQL> SQL> drop table ord; Table dropped. SQL> drop table customer; Table dropped. SQL> --
</source>
Test the NOT EXISTS version
<source lang="sql">
SQL> --Test the NOT EXISTS version SQL> SQL> create table customer(
2 cust_no 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 ,zip_4 varchar2(4) 11 ,area_code varchar2(3) 12 ,phone varchar2(8) 13 ,company_name varchar2(50) 14 );
Table created. SQL> SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1, "Allen", "Joe","J","10 Ave","London","CA","11111","1111","111", "111-1111","Big Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,"Ward","Sue","W","20 Ave","New York","NY","44444","4444","444", "436-4444","B Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,"Jason","Pure","J","50 St","Longli","CA","55555","5555","555", "234-4444","C Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,"Bird","Jill", null,"30 St","Pais","NY","22222","2222","222", "634-7733","D Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,"Hill","Carl","H","19 Drive","A Town","CA","66666","6566","666", "243-4243","E Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,"Peter","Yari","P","38 Ave","Small City","NY","77777","7777","777", "454-5443","F Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,"Joe","Paula","J","78 St. Apt 3A","Queen City","NY","32322","2323","888", "664-4333","E Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,"Chili","Steve","C","38 Ave Apt 62","Mili","CA","88888","8888","787", "456-4566","G Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,"Mona","Joe","M","930 Ave933","Kansas City","MO","12345","1234","412", "456-4563","H Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,"Hack","Kisi","H","Kings Rd","Bellmore","NY","54321","3898","516", "767-5677","I Inc");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,"Bill","Jose","B","12 Giant Rd.","Newton","NJ","23454","1234","958", "123-7367","J Associates");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(12,"Taker","Lawrence","T","1 Sask Rd.","Camp","NJ","19191","3298","928", "123-7384","K Company");
1 row created. SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(13,"Richer","Doris","R","213 Easy Street","WarPease","RI","34343","2112","501", "123-7384","L Inc");
1 row created. SQL> insert into customer( cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(14,"Pete","Doris","P","9 Ave","New York","NY","45454","4222","112", "123-1234","M Company");
1 row created. SQL> SQL> create table ord(
2 order_no integer primary key 3 ,cust_no integer 4 ,order_date date not null 5 ,total_order_price number(7,2) 6 ,deliver_date date 7 ,deliver_time varchar2(7) 8 ,payment_method varchar2(2) 9 ,emp_no number(3,0) 10 ,deliver_name varchar2(35) 11 ,gift_message varchar2(100) 12 );
Table created. SQL> SQL> SQL> insert into ord(order_no,cust_no,order_date,total_order_price,deliver_date,deliver_time,payment_method,emp_no,deliver_name,gift_message)
2 values(1,1,"14-Feb-2002", 23.00, "14-Feb-2002", "12 noon", "CA",1, null, "Gift for wife");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time ,payment_method ,emp_no,deliver_name ,gift_message )
2 values(2,1,"14-Feb-2003", 510.98, "14-feb-2003", "5 pm", "NY",7, "Rose Ted", "Happy Valentines Day to Mother");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(3, 2,"14-Feb-2004", 315.99, "14-feb-2004", "3 pm", "VS",2, "Ani Forest", "Happy Valentines Day to Father");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(4, 2,"14-Feb-1999", 191.95, "14-feb-1999", "2 pm", "NJ",2, "O. John", "Happy Valentines Day");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(5, 6,"4-mar-2002", 101.95, "5-mar-2002", "2:30 pm", "MO" , 2, "Cora", "Happy Birthday from John");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(6, 9,"7-apr-2003", 221.95, "7-apr-2003", "3 pm", "MA", 2, "Sake Keith", "Happy Birthday from Joe" );
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(7, 9,"20-jun-2004", 315.95, "21-jun-2004", "12 noon", "BC", 2, "Jessica Li", "Happy Birthday from Jessica");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (8, 12, "31-dec-1999", 135.95, "1-jan-2000", "12 noon", "DI", 3, "Larry", "Happy New Year from Lawrence");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values (9, 12, "26-dec-2003", 715.95, "2-jan-2004", "12 noon", "SK",7, "Did", "Happy Birthday from Nancy" );
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(10, 4, sysdate-1, 119.95, sysdate+2, "6:30 pm", "VG",2, "P. Jing", "Happy Valentines Day to Jason");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message )
2 values(11, 2, sysdate, 310.00, sysdate+2, "3:30 pm", "DC",2, "C. Late", "Happy Birthday Day to Jack");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(12, 7, sysdate-3, 121.95, sysdate-2, "1:30 pm", "AC",2, "W. Last", "Happy Birthday Day to You");
1 row created. SQL> insert into ord(order_no ,cust_no ,order_date ,total_order_price ,deliver_date ,deliver_time,payment_method ,emp_no,deliver_name ,gift_message)
2 values(13, 7, sysdate, 211.95, sysdate-4, "4:30 pm", "CA",2, "J. Bond", "Thanks for hard working");
1 row created. SQL> SQL> SQL> SQL> SELECT cust_no, lastname
2 FROM customer c 3 WHERE NOT EXISTS 4 (SELECT cust_no FROM ord 5 WHERE cust_no = c.cust_no); CUST_NO LASTNAME
--------------------
5 Hill 8 Chili 3 Jason 10 Hack 14 Pete 13 Richer 11 Bill
7 rows selected. SQL> SQL> drop table customer; Table dropped. SQL> SQL> set autotrace off SQL> SQL> drop table ord; Table dropped. SQL> SQL> SQL> --
</source>
The EXISTS condition can also be combined with the NOT operator
<source lang="sql">
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) ); SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); SQL> SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> SQL> select * from dept;
DEPTNO DNAME LOC
-------------- -------------
10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SQL> SQL> -- NOT EXISTS SQL> SQL> -- The EXISTS condition can also be combined with the NOT operator. SQL> SQL> SELECT *
2 FROM emp 3 WHERE not exists (select * from dept Where dept.deptno = emp.deptno);
SQL> SQL> SQL> SQL> drop table dept; SQL> drop table emp;
</source>
Use EXISTS to link two queries
<source lang="sql">
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10), 3 JOB VARCHAR2(9), 4 MGR NUMBER(4), 5 HIREDATE DATE, 6 SAL NUMBER(7, 2), 7 COMM NUMBER(7, 2), 8 DEPTNO NUMBER(2));
SQL> SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); SQL> SQL> CREATE TABLE DEPT (DEPTNO NUMBER(2),DNAME VARCHAR2(14),LOC VARCHAR2(13) ); SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); SQL> SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7369 SMITH CLERK 7902 17-DEC-80 800 20 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7900 JAMES CLERK 7698 03-DEC-81 950 30 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10
SQL> SQL> select * from dept;
DEPTNO DNAME LOC
-------------- -------------
10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SQL> SQL> SQL> SQL> SELECT * FROM emp WHERE EXISTS
2 (select * from dept where dept.deptno = emp.deptno); EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7934 MILLER CLERK 7782 23-JAN-82 1300 10 7839 KING PRESIDENT 17-NOV-81 5000 10 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7902 FORD ANALYST 7566 03-DEC-81 3000 20 7876 ADAMS CLERK 7788 12-JAN-83 1100 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7566 JONES MANAGER 7839 02-APR-81 2975 20 7369 SMITH CLERK 7902 17-DEC-80 800 20 7900 JAMES CLERK 7698 03-DEC-81 950 30 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 30 7698 BLAKE MANAGER 7839 01-MAY-81 2850 30 EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- --------- ---------- --------- ---------- ---------- ----------
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
SQL> SQL> SQL> SQL> drop table dept; SQL> drop table emp;
</source>