Oracle PL/SQL/Subquery/Subquery IN
Содержание
- 1 All locations, where courses are offering, have no departments (subquery)
- 2 In and subquery
- 3 in subquery
- 4 Multiple Row Subqueries: IN with subquery
- 5 Multiple Row Subqueries: NOT IN with subquery
- 6 Multiple-row subqueries return more than one row of result from the subquery
- 7 Not in and subquery
- 8 Selecting Products That Belong to Department with Subquery
- 9 Subquery in from clause
- 10 Using Set Membership with Subqueries
All locations, where courses are offering, have no departments (subquery)
<source lang="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> create table offerings
2 ( course VARCHAR2(6) 3 , begindate DATE 4 , trainer 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> select DISTINCT o.location
2 from offerings o 3 where o.location not in 4 (select d.location 5 from departments d);
LOCATION
SEATTLE SQL> SQL> SQL> drop table offerings; Table dropped. SQL> drop table departments; Table dropped. SQL>
</source>
In and subquery
<source lang="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> 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> SQL> select attendee
2 from registrations 3 where course in (select code 4 from courses 5 where category = "BLD");
no rows selected SQL> select attendee
2 from registrations 3 where evaluation in (select duration 4 from courses 5 where category = "BLD"); ATTENDEE
11
2
6
3
13
SQL> drop table registrations; Table dropped. SQL> drop table courses; Table dropped.
</source>
in subquery
<source lang="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> create table offerings
2 ( course VARCHAR2(6) 3 , begindate DATE 4 , trainer 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> select e.*
2 from emp e
3 where e.empno in (select o.trainer
4 from offerings o
5 where o.course = "SQL");
EMPNO ENAME INIT JOB MGR BDATE SAL COMM
-------- ----- -------- ---------- ---------- ---------- ----------
DEPTNO
1 Tom N TRAINER 13 17-12-1965 800
20
13 Fake MG 4 13-02-1959 3000
20
SQL> drop table offerings; Table dropped. SQL> drop table emp; Table dropped.
</source>
Multiple Row Subqueries: IN with subquery
<source lang="sql">
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_DATE END_DATE SALARY CITY DESCRIPTION
---------- ---------- ----------- ----------- ---------- ---------- ---------------
01 Jason Martin 1996-JUL-25 2006-JUL-25 1234.56 Toronto Programmer 02 Alison Mathews 1976-MAR-21 1986-FEB-21 6661.78 Vancouver Tester 03 James Smith 1978-DEC-12 1990-MAR-15 6544.78 Vancouver Tester 04 Celia Rice 1982-OCT-24 1999-APR-21 2344.78 Vancouver Manager 05 Robert Black 1984-JAN-15 1998-AUG-08 2334.78 Vancouver Tester 06 Linda Green 1987-JUL-30 1996-JAN-04 4322.78 New York Tester 07 David Larry 1990-DEC-31 1998-FEB-12 7897.78 New York Manager 08 James Cat 1996-SEP-17 2002-APR-15 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> SQL> SQL> --Multiple Row Subqueries: IN with subquery. SQL> SQL> SELECT id, first_name
2 FROM employee 3 WHERE id IN 4 (SELECT id 5 FROM employee 6 WHERE first_name LIKE "%e%");
ID FIRST_NAME
----------
03 James 04 Celia 05 Robert 08 James SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>
Multiple Row Subqueries: NOT IN with subquery
<source lang="sql">
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_DATE END_DATE SALARY CITY DESCRIPTION
---------- ---------- ----------- ----------- ---------- ---------- ---------------
01 Jason Martin 1996-JUL-25 2006-JUL-25 1234.56 Toronto Programmer 02 Alison Mathews 1976-MAR-21 1986-FEB-21 6661.78 Vancouver Tester 03 James Smith 1978-DEC-12 1990-MAR-15 6544.78 Vancouver Tester 04 Celia Rice 1982-OCT-24 1999-APR-21 2344.78 Vancouver Manager 05 Robert Black 1984-JAN-15 1998-AUG-08 2334.78 Vancouver Tester 06 Linda Green 1987-JUL-30 1996-JAN-04 4322.78 New York Tester 07 David Larry 1990-DEC-31 1998-FEB-12 7897.78 New York Manager 08 James Cat 1996-SEP-17 2002-APR-15 1232.78 Vancouver Tester 8 rows selected. SQL> SQL> SQL> --Multiple Row Subqueries: NOT IN with subquery SQL> SQL> SELECT id, first_name
2 FROM employee 3 WHERE id NOT IN 4 (SELECT id 5 FROM employee where salary > 4000);
ID FIRST_NAME
----------
05 Robert 01 Jason 08 James 04 Celia SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee
2 /
Table dropped. SQL> SQL>
</source>
Multiple-row subqueries return more than one row of result from the subquery
<source lang="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> INSERT INTO EMP VALUES(2, "Jack", "Tester", 6,TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30); 1 row created. SQL> INSERT INTO EMP VALUES(3, "Wil", "Tester", 6,TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30); 1 row created. SQL> INSERT INTO EMP VALUES(4, "Jane", "Designer", 9,TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(5, "Mary", "Tester", 6,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30); 1 row created. SQL> INSERT INTO EMP VALUES(6, "Black", "Designer", 9,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30); 1 row created. SQL> INSERT INTO EMP VALUES(7, "Chris", "Designer", 9,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(8, "Smart", "Helper", 4,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20); 1 row created. SQL> INSERT INTO EMP VALUES(9, "Peter", "Manager", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10); 1 row created. SQL> INSERT INTO EMP VALUES(10, "Take", "Tester", 6,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30); 1 row created.
SQL> INSERT INTO EMP VALUES(13, "Fake", "Helper", 4,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20); 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, deptno
2 FROM emp 3 WHERE deptno NOT IN (SELECT deptno FROM emp WHERE ename = "Smart");
ENAME DEPTNO
----------
Jack 30 Wil 30 Mary 30 Black 30 Chris 10 Peter 10 Take 30 7 rows selected. SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.
</source>
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>
Selecting Products That Belong to Department with Subquery
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE Product (
2 ProductID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL, 4 Description VARCHAR(1000) NOT NULL, 5 Price NUMBER NULL, 6 ImagePath VARCHAR(50) NULL, 7 soldout NUMBER(1,0) NULL, 8 Promotion NUMBER(1,0) NULL);
Table created. SQL> SQL> CREATE SEQUENCE ProductIDSeq; Sequence created. SQL> SQL> CREATE OR REPLACE TRIGGER ProductAutonumberTrigger
2 BEFORE INSERT ON Product 3 FOR EACH ROW 4 BEGIN 5 SELECT ProductIDSeq.NEXTVAL 6 INTO :NEW.ProductID FROM DUAL; 7 END; 8 /
Trigger created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)
2 VALUES ("Pen", "Ball Pen",5.99, "pen.jpg", 1, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Ruler","Long",14.99, "ruler.jpg", 0, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Desk", "Computer Desk",5.99, "desk.jpg", 0, 1);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("PC", "Notebook",49.99, "pc.jpg", 0, 1);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Mouse", "Wireless",9.99, "mouse.jpg", 1, 0);
1 row created. SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)
2 VALUES ("Keyboard","keyboard",3.75, "keyboard.jpg", 0, 0);
1 row created. SQL> SQL> SQL> CREATE TABLE ProductCategory (
2 ProductID INT NOT NULL, 3 CategoryID INT NOT NULL, 4 PRIMARY KEY (ProductID, CategoryID) 5 );
Table created. SQL> SQL> SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4); 1 row created. SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5); 1 row created. SQL> SQL> SQL> CREATE TABLE Category (
2 CategoryID INT NOT NULL PRIMARY KEY, 3 DepartmentID INT NOT NULL, 4 Name VARCHAR(50) NOT NULL, 5 Description VARCHAR (200) NULL);
Table created. SQL> SQL> CREATE SEQUENCE CategoryIDSeq; Sequence created. SQL> SQL> CREATE OR REPLACE TRIGGER CategoryAutonumberTrigger
2 BEFORE INSERT ON Category 3 FOR EACH ROW 4 BEGIN 5 SELECT CategoryIDSeq.NEXTVAL 6 INTO :NEW.CategoryID FROM DUAL; 7 END; 8 /
Trigger created. SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (1, "Local", "In town");
1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (1, "Remote", "Telecommute");
1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (2, "Masks", "By bits");
1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (3, "Wireless", "Not connected");
1 row created. SQL> INSERT INTO Category (DepartmentID, Name, Description)
2 VALUES (3, "Wired", "Connected");
1 row created. SQL> SQL> SELECT Product.ProductID, Product.Name
2 FROM Product INNER JOIN ProductCategory 3 ON Product.ProductID = ProductCategory.ProductID 4 WHERE ProductCategory.CategoryID IN 5 (SELECT CategoryID 6 FROM Category 7 WHERE DepartmentID = 1) 8 ORDER BY Product.Name; PRODUCTID NAME
--------------------------------------------------
5 Mouse
4 PC
2 Ruler
3 rows selected. SQL> SQL> SQL> drop table Product; Table dropped. SQL> drop table ProductCategory; Table dropped. SQL> drop table Category; Table dropped. SQL> drop sequence CategoryIDSeq; Sequence dropped. SQL> drop sequence ProductIDSeq; Sequence dropped. SQL>
</source>
Subquery in from clause
<source lang="sql">
SQL> SQL> create table myTable
2 as 3 select rownum id, a.* 4 from all_objects a 5 where 1=0 6 /
Table created. SQL> SQL> SQL> select *
2 from myTable myTable1, ( select owner, max(last_ddl_time) max_time from myTable group by owner ) myTable2 3 where myTable1.owner = myTable2.owner 4 /
no rows selected SQL> drop table myTable; Table dropped.
</source>
Using Set Membership with Subqueries
<source lang="sql">
SQL> CREATE TABLE manager (
2 managerID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL);
Table created. SQL> INSERT INTO manager (managerID,Name) VALUES (1,"Victor"); 1 row created. SQL> INSERT INTO manager (managerID,Name) VALUES (2,"Bill"); 1 row created. SQL> INSERT INTO manager (managerID,Name) VALUES (3,"Mary"); 1 row created. SQL> INSERT INTO manager (managerID,Name) VALUES (4,"Jack"); 1 row created. SQL> INSERT INTO manager (managerID,Name) VALUES (5,"Peter"); 1 row created. SQL> INSERT INTO manager (managerID,Name) VALUES (6,"Tom"); 1 row created. SQL> SQL> SQL> CREATE TABLE Course (
2 CourseID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50), 4 Credits INT);
Table created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (1,"SQL",5); 1 row created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (2,"Java",5); 1 row created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (3,"XML",5); 1 row created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (4,"ERP",5); 1 row created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (5,"Oracle",5); 1 row created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (6,"MySQL",5); 1 row created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (7,"SQL Server",5); 1 row created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (8,"Javascript",10); 1 row created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (9,"Office",5); 1 row created. SQL> INSERT INTO Course (CourseID,Name,Credits) VALUES (10,"VB",5); 1 row created. SQL> SQL> SQL> CREATE TABLE Room (
2 RoomID INT NOT NULL PRIMARY KEY, 3 Comments VARCHAR(50), 4 Capacity INT);
Table created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (1,"Main hall",500); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (2,"Science Department",200); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (3,"Science Room 1",100); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (4,"Languages Block",300); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (5,"Languages Room 1",75); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (6,"Languages Room 2",50); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (7,"Engineering Center",200); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (8,"Engineering Room 1",100); 1 row created. SQL> INSERT INTO Room (RoomID,Comments,Capacity) VALUES (9,"Engineering Room 2",50); 1 row created. SQL> SQL> SQL> CREATE TABLE Class (
2 ClassID INT NOT NULL PRIMARY KEY, 3 CourseID INT NOT NULL, 4 managerID INT NOT NULL, 5 RoomID INT NOT NULL, 6 Time VARCHAR(50));
Table created. SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (1,1,1,6,"Mon 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (2,2,1,5,"Mon 11:00-12:00, Thu 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (3,3,2,3,"Mon 14:00-16:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (4,4,3,2,"Tue 10:00-12:00, Thu 14:00-15:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (5,5,2,9,"Tue 14:00-16:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (6,6,3,2,"Tue 16:00-17:00, Thu 15:00-17:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (7,7,4,3,"Wed 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (8,8,5,8,"Wed 11:00-13:00, Fri 09:00-11:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (9,9,1,5,"Fri 11:00-13:00"); 1 row created. SQL> INSERT INTO Class (ClassID,CourseID,managerID,RoomID,Time) VALUES (10,10,6,9,"Fri 14:00-16:00"); 1 row created. SQL> SQL> SQL> CREATE TABLE emp (
2 empID INT NOT NULL PRIMARY KEY, 3 Name VARCHAR(50) NOT NULL);
Table created. SQL> INSERT INTO emp (empID,Name) VALUES (1,"Tom"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (2,"Jack"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (3,"Mary"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (4,"Bill"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (5,"Cat"); 1 row created. SQL> INSERT INTO emp (empID,Name) VALUES (6,"Victor"); 1 row created. SQL> SQL> CREATE TABLE Exam (
2 ExamID INT NOT NULL PRIMARY KEY, 3 CourseID INT NOT NULL, 4 managerID INT NOT NULL, 5 SustainedOn DATE, 6 Comments VARCHAR(255));
Table created. SQL> INSERT INTO Exam (ExamID,CourseID,managerID,SustainedOn,Comments) VALUES (1,1,1,DATE "2003-03-12","Hard"); 1 row created. SQL> INSERT INTO Exam (ExamID,CourseID,managerID,SustainedOn,Comments) VALUES (2,2,1,DATE "2003-03-13","Simple"); 1 row created. SQL> INSERT INTO Exam (ExamID,CourseID,managerID,SustainedOn,Comments) VALUES (3,3,2,DATE "2003-03-11","1 hour long"); 1 row created. SQL> INSERT INTO Exam (ExamID,CourseID,managerID,SustainedOn) VALUES (4,4,3,DATE "2003-03-18"); 1 row created. SQL> INSERT INTO Exam (ExamID,CourseID,managerID,SustainedOn,Comments) VALUES (5,5,2,DATE "2003-03-19","2 hours long"); 1 row created. SQL> SQL> CREATE TABLE dept (
2 deptID INT NOT NULL PRIMARY KEY, 3 empID INT NOT NULL, 4 ClassID INT NOT NULL, 5 EnrolledOn DATE, 6 Grade INT);
Table created. SQL> SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (1,1,1,DATE "2002-09-23",62); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (2,1,2,DATE "2002-09-30",70); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (3,2,3,DATE "2003-09-23",51); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (4,2,5,DATE "2003-09-23",41); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (5,2,6,DATE "2003-09-23",68); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (6,3,4,DATE "2002-09-30",78); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (7,3,7,DATE "2002-09-30",80); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (8,4,8,DATE "2002-09-20",70); 1 row created. SQL> INSERT INTO dept (deptID,empID,ClassID,EnrolledOn,Grade) VALUES (9,4,5,DATE "2002-09-20",60); 1 row created. SQL> SQL> SQL> CREATE TABLE empExam (
2 empID INT NOT NULL, 3 ExamID INT NOT NULL, 4 Mark INT, 5 Taken SMALLINT, 6 Comments VARCHAR(255), 7 CONSTRAINT PK_empExam PRIMARY KEY (empID, ExamID));
Table created. SQL> SQL> SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,1,55,1,"Satisfactory"); 1 row created. SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,2,73,1,"Good result"); 1 row created. SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,3,44,1,"Hard"); 1 row created. SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,5,39,0,"Simple"); 1 row created. SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken) VALUES (2,6,63,1); 1 row created. SQL> SQL> SQL> SELECT empID, Name FROM emp WHERE empID IN
2 (SELECT empID FROM dept WHERE ClassID IN 3 (SELECT ClassID FROM Class WHERE managerID IN 4 (SELECT managerID FROM manager 5 WHERE Name LIKE "%Williams%")));
no rows selected SQL> SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped. SQL> drop table Class; Table dropped. SQL> drop table manager; Table dropped. SQL>
</source>
