Oracle PL/SQL Tutorial/Query Select/NOT
Содержание
Not(condition1 and condition2)
<source lang="sql">
SQL> SQL> SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> select ename, init
2 from emp 3 where not (ename = "Jane" and init = "R");
Enter... Tom N Jack JAM Wil TF Jane JM Mary P Black R Chris AB Smart SCJ Peter CC Take JJ Ana AA Fake MG Mike TJA 13 rows selected. SQL> drop table emp; Table dropped. SQL></source>
Not empty(null)
<source lang="sql">
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "Coder", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "Coder", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "Coder", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "Coder", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 1 row created. SQL> SQL> select ename, job, sal, comm
2 from emp 3 where comm is not null;
ENAME JOB SAL COMM
-------- ---------- ----------
Jack Tester 1600 300 Wil Tester 1250 500 Mary Tester 1250 1400 Take Tester 1500 0 SQL> SQL> SQL> drop table emp; Table dropped.</source>
Not equals
<source lang="sql">
SQL> SQL> SQL> create table departments
2 ( deptno NUMBER(2) constraint D_PK 3 primary key 4 , dname VARCHAR2(10) 5 , location VARCHAR2(8) 6 , mgr NUMBER(4) 7 ) ;
Table created. SQL> SQL> insert into departments values (10,"ACCOUNTING","NEW YORK",7); 1 row created. SQL> insert into departments values (20,"TRAINING", "DALLAS", 4); 1 row created. SQL> insert into departments values (30,"SALES", "CHICAGO", 6); 1 row created. SQL> insert into departments values (40,"HR", "BOSTON", 9); 1 row created. SQL> SQL> SQL> select dname, location
2 from departments 3 where location <> "CHICAGO";
DNAME LOCATION
--------
ACCOUNTING NEW YORK TRAINING DALLAS HR BOSTON SQL> SQL> drop table departments; Table dropped. SQL></source>
Not equals: not vs <>
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE emp (
2 emp_id NUMBER, 3 ename VARCHAR2(40), 4 hire_date DATE DEFAULT sysdate, 5 end_date DATE, 6 rate NUMBER(5,2), 7 CONSTRAINT emp_pk PRIMARY KEY (emp_id) 8 );
Table created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300); 1 row created. SQL> SQL> SQL> SET ECHO ON SQL> SELECT *
2 FROM emp
3 WHERE emp_id <> 114;
101 Mary
15-NOV-61 $169.00
102 Tom
16-SEP-64 05-MAY-04 $135.00
104 Peter
29-DEC-87 01-APR-04 $99.00
105 Mike
15-JUN-04 $121.00
107 Less
02-JAN-04 $45.00
108 Park
01-MAR-94 15-NOV-04 $220.00
110 Ink
04-APR-04 30-SEP-04 $84.00
111 Tike
23-AUG-76 $100.00
112 Inn
15-NOV-61 04-APR-04 $70.00
113 Kate
03-MAR-04 31-OCT-04 $300.00
10 rows selected. SQL> SQL> SELECT *
2 FROM emp
3 WHERE NOT emp_id = 114;
101 Mary
15-NOV-61 $169.00
102 Tom
16-SEP-64 05-MAY-04 $135.00
104 Peter
29-DEC-87 01-APR-04 $99.00
105 Mike
15-JUN-04 $121.00
107 Less
02-JAN-04 $45.00
108 Park
01-MAR-94 15-NOV-04 $220.00
110 Ink
04-APR-04 30-SEP-04 $84.00
111 Tike
23-AUG-76 $100.00
112 Inn
15-NOV-61 04-APR-04 $70.00
113 Kate
03-MAR-04 31-OCT-04 $300.00
10 rows selected. SQL> SQL> SQL> SQL> drop table emp; Table dropped.</source>
Not has the lowerest priority
<source lang="sql">
SQL> SQL> CREATE TABLE emp (
2 emp_id NUMBER, 3 ename VARCHAR2(40), 4 hire_date DATE DEFAULT sysdate, 5 end_date DATE, 6 rate NUMBER(5,2), 7 CONSTRAINT emp_pk PRIMARY KEY (emp_id) 8 );
Table created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (101, "Mary", to_date("15-Nov-1961","dd-mon-yyyy"),null,169); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (102, "Tom", to_date("16-Sep-1964","dd-mon-yyyy"),to_date("5-May-2004","dd-mon-yyyy"),135); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (104, "Peter", to_date("29-Dec-1987","dd-mon-yyyy"),to_date("1-Apr-2004","dd-mon-yyyy"),99); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (105, "Mike", to_date("15-Jun-2004","dd-mon-yyyy"),null,121); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (107, "Less", to_date("2-Jan-2004","dd-mon-yyyy"),null,45); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (108, "Park", to_date("1-Mar-1994","dd-mon-yyyy"),to_date("15-Nov-2004","dd-mon-yyyy"),220); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (110, "Ink", to_date("4-Apr-2004","dd-mon-yyyy"),to_date("30-Sep-2004","dd-mon-yyyy"),84); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (111, "Tike", to_date("23-Aug-1976","dd-mon-yyyy"),null,100); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (112, "Inn", to_date("15-Nov-1961","dd-mon-yyyy"),to_date("4-Apr-2004","dd-mon-yyyy"),70); 1 row created. SQL> INSERT INTO emp(emp_id, ename, hire_date,end_date, rate)VALUES (113, "Kate", to_date("3-Mar-2004","dd-mon-yyyy"),to_date("31-Oct-2004","dd-mon-yyyy"),300); 1 row created. SQL> SQL> SET ECHO ON SQL> SELECT *
2 FROM emp
3 WHERE NOT ((emp_id = 114)
4 OR (hire_date >= TO_DATE("1-Jan-2004","dd-mon-yyyy")
5 AND REGEXP_LIKE(ename, "^Myk.*")));
101 Mary
15-NOV-61 $169.00
102 Tom
16-SEP-64 05-MAY-04 $135.00
104 Peter
29-DEC-87 01-APR-04 $99.00
105 Mike
15-JUN-04 $121.00
107 Less
02-JAN-04 $45.00
108 Park
01-MAR-94 15-NOV-04 $220.00
110 Ink
04-APR-04 30-SEP-04 $84.00
111 Tike
23-AUG-76 $100.00
112 Inn
15-NOV-61 04-APR-04 $70.00
113 Kate
03-MAR-04 31-OCT-04 $300.00
10 rows selected. SQL> SQL> SQL> drop table emp; Table dropped.</source>
not in (1,2,3,NULL)
<source lang="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> insert into registrations values (13,"OAU",date "2009-08-10",5 ); 1 row created. 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.</source>
not in vs not exists
<source lang="sql">
SQL> SQL> SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created. SQL> SQL> INSERT INTO EMP VALUES(7369, "SMITH", "CLERK", 7902,TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7499, "ALLEN", "SALESMAN", 7698,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7521, "WARD", "SALESMAN", 7698,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7566, "JONES", "MANAGER", 7839,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10); 1 row created. SQL> SQL> CREATE TABLE DEPT(
2 DEPTNO NUMBER(2), 3 DNAME VARCHAR2(14), 4 LOC VARCHAR2(13) 5 );
Table created. SQL> SQL> INSERT INTO DEPT VALUES (10, "ACCOUNTING", "NEW YORK"); 1 row created. SQL> INSERT INTO DEPT VALUES (20, "RESEARCH", "DALLAS"); 1 row created. SQL> INSERT INTO DEPT VALUES (30, "SALES", "CHICAGO"); 1 row created. SQL> INSERT INTO DEPT VALUES (40, "OPERATIONS", "BOSTON"); 1 row created. SQL> SQL> SQL> select ename from emp
2 where empno not in (select mgr from emp);
no rows selected SQL> SQL> select ename from emp
2 where NOT EXISTS (select null from emp e2 where e2.mgr = emp.empno);
ENAME
SMITH ALLEN WARD MARTIN TURNER ADAMS JAMES MILLER 8 rows selected. SQL> SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped. SQL> SQL> SQL></source>
NOT operator has higher priority than AND
<source lang="sql">
SQL> SQL> SQL> SQL> CREATE TABLE titles(
2 title_id CHAR(3) NOT NULL, 3 title_name VARCHAR(40) NOT NULL, 4 type VARCHAR(10) NULL , 5 pub_id CHAR(3) NOT NULL, 6 pages INTEGER NULL , 7 price DECIMAL(5,2) NULL , 8 sales INTEGER NULL , 9 pubdate DATE NULL , 10 contract SMALLINT NOT NULL 11 );
Table created. SQL> SQL> SQL> SQL> SQL> INSERT INTO titles VALUES("T01","Java","history","P01",111,21.99,566,DATE "2000-08-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T02","Oracle","history","P03", 114,19.95,9566,DATE "1998-04-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T03","SQL","computer","P02", 122,39.95,25667,DATE "2000-09-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T04","C++","psychology","P04", 511,12.99,13001,DATE "1999-05-31",1); 1 row created. SQL> INSERT INTO titles VALUES("T05","Python","psychology","P04", 101,6.95,201440,DATE "2001-01-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T06","JavaScript","biography","P01", 173,19.95,11320,DATE "2000-07-31",1); 1 row created. SQL> INSERT INTO titles VALUES("T07","LINQ","biography","P03", 331,23.95,1500200,DATE "1999-10-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T08","C#","children","P04", 861,10.00,4095,DATE "2001-06-01",1); 1 row created. SQL> INSERT INTO titles VALUES("T09","SQL Server","children","P04", 212,13.95,5000,DATE "2002-05-31",1); 1 row created. SQL> INSERT INTO titles VALUES("T10","AJAX","biography","P01", NULL,NULL,NULL,NULL,0); 1 row created. SQL> INSERT INTO titles VALUES("T11","VB","psychology","P04", 821,7.99,94123,DATE "2000-11-30",1); 1 row created. SQL> INSERT INTO titles VALUES("T12","Office","biography","P01", 507,12.99,100001,DATE "2000-08-31",1); 1 row created. SQL> INSERT INTO titles VALUES("T13","VBA","history","P03", 812,29.99,10467,DATE "1999-05-31",1); 1 row created. SQL> SQL> SQL> SQL> SQL> SELECT title_name, sales, price
2 FROM titles 3 WHERE NOT (price < 20) AND (sales > 15000);
TITLE_NAME SALES PRICE
---------- ----------
SQL 25667 39.95 LINQ 1500200 23.95 SQL> SQL> drop table titles; Table dropped. SQL></source>
NOT to negate a boolean value
<source lang="sql">
SQL> SQL> SQL> SQL> CREATE TABLE employees (
2 au_id CHAR(3) NOT NULL, 3 au_fname VARCHAR(15) NOT NULL, 4 au_lname VARCHAR(15) NOT NULL, 5 phone VARCHAR(12) NULL , 6 address VARCHAR(20) NULL , 7 city VARCHAR(15) NULL , 8 state CHAR(2) NULL , 9 zip CHAR(5) NULL 10 );
Table created. SQL> SQL> INSERT INTO employees VALUES("A01","S","B","111-111-1111","75 St","Boston","NY","11111"); 1 row created. SQL> INSERT INTO employees VALUES("A02","W","H","222-222-2222","2922 Rd","Boston","CO","22222"); 1 row created. SQL> INSERT INTO employees VALUES("A03","H","H","333-333-3333","3800 Ave, #14F","San Francisco","CA","33333"); 1 row created. SQL> INSERT INTO employees VALUES("A04","K","H","444-444-4444","3800 Ave, #14F","San Francisco","CA","44444"); 1 row created. SQL> INSERT INTO employees VALUES("A05","C","K","555-555-5555","114 St","New York","NY","55555"); 1 row created. SQL> INSERT INTO employees VALUES("A06"," ","K","666-666-666","390 Mall","Palo Alto","CA","66666"); 1 row created. SQL> INSERT INTO employees VALUES("A07","P","O","777-777-7777","1442 St","Sarasota","FL","77777"); 1 row created. SQL> SQL> SQL> SQL> SQL> SQL> SELECT au_fname, au_lname, state
2 FROM employees 3 WHERE NOT (state = "CA");
AU_FNAME AU_LNAME ST
--------------- --
S B NY W H CO C K NY P O FL SQL> SQL> SQL> drop table employees; Table dropped. SQL> SQL></source>
Or, and, not
<source lang="sql">
SQL> SQL> create table courses
2 ( code VARCHAR2(6) constraint C_PK 3 primary key 4 , description VARCHAR2(30) 5 , category CHAR(3) 6 , duration NUMBER(2) 7 ) ;
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> select code, category, duration
2 from courses 3 where (category = "BLD" or duration = 2) 4 and not (category = "BLD" and duration = 2);
CODE CAT DURATION
--- ----------
JAV BLD 4 PLS BLD 1 RSD DSG 2 SQL> SQL> SQL> drop table courses; Table dropped.</source>
Using Not in and subquery
<source lang="sql">
SQL> create table emp
2 ( empno NUMBER(4) constraint E_PK primary key 3 , ename VARCHAR2(8) 4 , init VARCHAR2(5) 5 , job VARCHAR2(8) 6 , mgr NUMBER(4) 7 , bdate DATE 8 , sal NUMBER(6,2) 9 , comm NUMBER(6,2) 10 , deptno NUMBER(2) default 10 11 ) ;
Table created. SQL> insert into emp values(1,"Tom","N", "TRAINER", 13,date "1965-12-17", 800 , NULL, 20); 1 row created. SQL> insert into emp values(2,"Jack","JAM", "Tester",6,date "1961-02-20", 1600, 300, 30); 1 row created. SQL> insert into emp values(3,"Wil","TF" , "Tester",6,date "1962-02-22", 1250, 500, 30); 1 row created. SQL> insert into emp values(4,"Jane","JM", "Designer", 9,date "1967-04-02", 2975, NULL, 20); 1 row created. SQL> insert into emp values(5,"Mary","P", "Tester",6,date "1956-09-28", 1250, 1400, 30); 1 row created. SQL> insert into emp values(6,"Black","R", "Designer", 9,date "1963-11-01", 2850, NULL, 30); 1 row created. SQL> insert into emp values(7,"Chris","AB", "Designer", 9,date "1965-06-09", 2450, NULL, 10); 1 row created. SQL> insert into emp values(8,"Smart","SCJ", "TRAINER", 4,date "1959-11-26", 3000, NULL, 20); 1 row created. SQL> insert into emp values(9,"Peter","CC", "Designer",NULL,date "1952-11-17", 5000, NULL, 10); 1 row created. SQL> insert into emp values(10,"Take","JJ", "Tester",6,date "1968-09-28", 1500, 0, 30); 1 row created. SQL> insert into emp values(11,"Ana","AA", "TRAINER", 8,date "1966-12-30", 1100, NULL, 20); 1 row created. SQL> insert into emp values(12,"Jane","R", "Manager", 6,date "1969-12-03", 800 , NULL, 30); 1 row created. SQL> insert into emp values(13,"Fake","MG", "TRAINER", 4,date "1959-02-13", 3000, NULL, 20); 1 row created. SQL> insert into emp values(14,"Mike","TJA","Manager", 7,date "1962-01-23", 1300, NULL, 10); 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> SQL> select empno
2 from emp 3 where empno not in (select attendee 4 from registrations 5 where course = "JAV");
Enter...
1
2
3
4
5
6
7
8
9
10
11
12
13
14
14 rows selected. SQL> drop table registrations; Table dropped. SQL> drop table emp; Table dropped.</source>
