Oracle PL/SQL Tutorial/Query Select/Correlated Subquery

Материал из SQL эксперт
Перейти к: навигация, поиск

A correlated subquery: the subquery references a column from a table referred to in the parent statement.

   <source lang="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> 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 deptno, ename, sal

 2      FROM   emp e1
 3      WHERE  sal = (SELECT MAX(sal) FROM emp
 4                       WHERE  deptno = e1.deptno)
 5

SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped. SQL></source>


Correlated subquery using the EXISTS operator

   <source lang="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> 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 e1.ename, d.dname

 2      FROM   emp e1, dept d
 3      WHERE  e1.deptno = d.deptno
 4      AND EXISTS
 5      (SELECT "x" FROM emp e2
 6      WHERE  e2.ename = "Smart" AND    e2.deptno = e1.deptno)
 7  /

ENAME DNAME


--------------

Jane RESEARCH Smart RESEARCH Fake RESEARCH SQL> SQL> drop table emp; Table dropped. SQL> drop table dept; Table dropped.</source>


Extract a Subset of the Results

   <source lang="sql">

SQL> SQL> CREATE TABLE myTable (username VARCHAR(20),score INT); Table created. SQL> INSERT INTO myTable VALUES ("gordon",10); 1 row created. SQL> INSERT INTO myTable VALUES ("user01",20); 1 row created. SQL> INSERT INTO myTable VALUES ("user02",30); 1 row created. SQL> INSERT INTO myTable VALUES ("user03",40); 1 row created. SQL> INSERT INTO myTable VALUES ("user04",50); 1 row created. SQL> INSERT INTO myTable VALUES ("user05",60); 1 row created. SQL> INSERT INTO myTable VALUES ("user06",70); 1 row created. SQL> INSERT INTO myTable VALUES ("user07",80); 1 row created. SQL> INSERT INTO myTable VALUES ("user08",90); 1 row created. SQL> INSERT INTO myTable VALUES ("user09",100); 1 row created. SQL> INSERT INTO myTable VALUES ("user10",110); 1 row created. SQL> INSERT INTO myTable VALUES ("user11",120); 1 row created. SQL> SQL> SELECT username,score FROM

 2  (SELECT username,score FROM myTable ORDER BY score DESC) t
 3    WHERE rownum <= 10;

USERNAME SCORE


----------

user11 120 user10 110 user09 100 user08 90 user07 80 user06 70 user05 60 user04 50 user03 40 user02 30 10 rows selected. SQL> DROP TABLE myTable; Table dropped. SQL> SQL></source>


How Many Products By Department with correlated subqueries 1

   <source lang="sql">

SQL> SQL> CREATE TABLE Department (

 2  DepartmentID INT NOT NULL PRIMARY KEY,
 3  Name VARCHAR(50) NOT NULL,
 4  Description VARCHAR(200) NULL);

SQL> SQL> CREATE SEQUENCE DepartmentIDSeq; SQL> SQL> CREATE OR REPLACE TRIGGER DepartmentAutonumberTrigger

 2  BEFORE INSERT ON Department
 3  FOR EACH ROW
 4  BEGIN
 5    SELECT DepartmentIDSeq.NEXTVAL
 6    INTO :NEW.DepartmentID FROM DUAL;
 7  END;
 8  /

SQL> SQL> INSERT INTO Department (Name, Description)

 2     VALUES ("Software", "Coding");

SQL> INSERT INTO Department (Name, Description)

 2     VALUES ("Hardware", "Building");

SQL> INSERT INTO Department (Name, Description)

 2     VALUES ("QA", "Testing");

SQL> SQL> 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);

SQL> SQL> CREATE SEQUENCE CategoryIDSeq; 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  /

SQL> INSERT INTO Category (DepartmentID, Name, Description)

 2     VALUES (1, "Local", "In town");

SQL> INSERT INTO Category (DepartmentID, Name, Description)

 2     VALUES (1, "Remote", "Telecommute");

SQL> INSERT INTO Category (DepartmentID, Name, Description)

 2     VALUES (2, "Masks", "By bits");

SQL> INSERT INTO Category (DepartmentID, Name, Description)

 2     VALUES (3, "Wireless", "Not connected");

SQL> INSERT INTO Category (DepartmentID, Name, Description)

 2     VALUES (3, "Wired", "Connected");

SQL> SQL> 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);

SQL> SQL> CREATE SEQUENCE ProductIDSeq; 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  /

SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)

 2  VALUES ("Pen", "Ball Pen",5.99, "pen.jpg", 1, 0);

SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)

 2  VALUES ("Ruler","Long",14.99, "ruler.jpg", 0, 0);

SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)

 2  VALUES ("Desk", "Computer Desk",5.99, "desk.jpg", 0, 1);

SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)

 2  VALUES ("PC", "Notebook",49.99, "pc.jpg", 0, 1);

SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)

 2  VALUES ("Mouse", "Wireless",9.99, "mouse.jpg",  1, 0);

SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)

 2  VALUES ("Keyboard","keyboard",3.75, "keyboard.jpg", 0, 0);

SQL> SQL> SQL> CREATE TABLE ProductCategory (

 2  ProductID INT NOT NULL,
 3  CategoryID INT NOT NULL,
 4  PRIMARY KEY (ProductID, CategoryID)
 5  );

SQL> SQL> SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5); SQL> SQL> SELECT D.Name AS "Department", COUNT(P.Name) AS "Products"

 2  FROM Department D, Product P
 3  WHERE ProductID IN
 4       (SELECT ProductID
 5        FROM ProductCategory INNER JOIN Category
 6        ON ProductCategory.CategoryID = Category.CategoryID
 7        WHERE Category.DepartmentID = D.DepartmentID)
 8  GROUP BY D.Name
 9  ORDER BY D.Name;

Hardware

        4

QA

        1

Software

        3

SQL> SQL> SQL> drop table department; SQL> drop sequence DepartmentIDSeq; SQL> drop table Product; SQL> drop table ProductCategory; SQL> drop table Category; SQL> drop sequence CategoryIDSeq; SQL> drop sequence ProductIDSeq;</source>


How Many Products By Department with correlated subqueries 2

   <source lang="sql">

SQL> SQL> CREATE TABLE Department (

 2  DepartmentID INT NOT NULL PRIMARY KEY,
 3  Name VARCHAR(50) NOT NULL,
 4  Description VARCHAR(200) NULL);

SQL> SQL> CREATE SEQUENCE DepartmentIDSeq; SQL> SQL> CREATE OR REPLACE TRIGGER DepartmentAutonumberTrigger

 2  BEFORE INSERT ON Department
 3  FOR EACH ROW
 4  BEGIN
 5    SELECT DepartmentIDSeq.NEXTVAL
 6    INTO :NEW.DepartmentID FROM DUAL;
 7  END;
 8  /

SQL> SQL> INSERT INTO Department (Name, Description)

 2     VALUES ("Software", "Coding");

SQL> INSERT INTO Department (Name, Description)

 2     VALUES ("Hardware", "Building");

SQL> INSERT INTO Department (Name, Description)

 2     VALUES ("QA", "Testing");

SQL> SQL> 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);

SQL> SQL> CREATE SEQUENCE CategoryIDSeq; 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  /

SQL> INSERT INTO Category (DepartmentID, Name, Description)

 2     VALUES (1, "Local", "In town");

SQL> INSERT INTO Category (DepartmentID, Name, Description)

 2     VALUES (1, "Remote", "Telecommute");

SQL> INSERT INTO Category (DepartmentID, Name, Description)

 2     VALUES (2, "Masks", "By bits");

SQL> INSERT INTO Category (DepartmentID, Name, Description)

 2     VALUES (3, "Wireless", "Not connected");

SQL> INSERT INTO Category (DepartmentID, Name, Description)

 2     VALUES (3, "Wired", "Connected");

SQL> SQL> 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);

SQL> SQL> CREATE SEQUENCE ProductIDSeq; 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  /

SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)

 2  VALUES ("Pen", "Ball Pen",5.99, "pen.jpg", 1, 0);

SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)

 2  VALUES ("Ruler","Long",14.99, "ruler.jpg", 0, 0);

SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath,soldout, Promotion)

 2  VALUES ("Desk", "Computer Desk",5.99, "desk.jpg", 0, 1);

SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)

 2  VALUES ("PC", "Notebook",49.99, "pc.jpg", 0, 1);

SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)

 2  VALUES ("Mouse", "Wireless",9.99, "mouse.jpg",  1, 0);

SQL> SQL> INSERT INTO Product (Name, Description, Price, ImagePath, soldout, Promotion)

 2  VALUES ("Keyboard","keyboard",3.75, "keyboard.jpg", 0, 0);

SQL> SQL> SQL> CREATE TABLE ProductCategory (

 2  ProductID INT NOT NULL,
 3  CategoryID INT NOT NULL,
 4  PRIMARY KEY (ProductID, CategoryID)
 5  );

SQL> SQL> SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4); SQL> INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5); SQL> SQL> SELECT D.Name AS "Department",

 2       (SELECT COUNT(ProductID)
 3        FROM ProductCategory INNER JOIN Category
 4        ON ProductCategory.CategoryID = Category.CategoryID
 5        WHERE Category.DepartmentID = D.DepartmentID) AS "Products"
 6  FROM Department D;

Software

        3

Hardware

        4

QA

        3

SQL> SQL> SQL> SQL> SQL> SELECT D.Name AS "Department", COUNT(P.Name) AS "Products"

 2  FROM Department D, Product P
 3  WHERE ProductID IN
 4       (SELECT ProductID
 5        FROM ProductCategory INNER JOIN Category
 6        ON ProductCategory.CategoryID = Category.CategoryID
 7        WHERE Category.DepartmentID = D.DepartmentID)
 8  GROUP BY D.Name
 9  ORDER BY D.Name;

Hardware

        4

QA

        1

Software

        3

SQL> SQL> SQL> drop table department; SQL> drop sequence DepartmentIDSeq; SQL> drop table Product; SQL> drop table ProductCategory; SQL> drop table Category; SQL> drop sequence CategoryIDSeq; SQL> drop sequence ProductIDSeq;</source>


List all employees who attended XML course and Java course

   <source lang="sql">

SQL> 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 attendee

 2  from   registrations
 3  where  course   = "JAV"
 4  and    attendee in (select attendee
 5                      from   registrations
 6                      where  course = "XML");

no rows selected SQL> SQL> drop table registrations; Table dropped.</source>


list all employees who is younger than Joe

   <source lang="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> SQL> select ename, init, bdate

 2  from   employees
 3  where  bdate > (select bdate
 4                  from   employees
 5                  where  ename = "JONES");

no rows selected SQL> SQL> drop table employees; Table dropped.</source>


The subquery returning the literal value 1

   <source lang="sql">

SQL> SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    EMPNO         NUMBER(3),
 3    ENAME         VARCHAR2(15 BYTE),
 4    HIREDATE      DATE,
 5    ORIG_SALARY   NUMBER(6),
 6    CURR_SALARY   NUMBER(6),
 7    REGION        VARCHAR2(1 BYTE),
 8    MANAGER_ID    NUMBER(3)
 9  )
10  /

Table created. SQL> SQL> create table job (

 2    EMPNO         NUMBER(3),
 3    jobtitle      VARCHAR2(20 BYTE)
 4  )
 5  /

Table created. SQL> SQL> insert into job (EMPNO, Jobtitle) values (1,"Tester"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (2,"Accountant"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (3,"Developer"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (4,"COder"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (5,"Director"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (6,"Mediator"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (7,"Proffessor"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (8,"Programmer"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (9,"Developer"); 1 row created. SQL> SQL> SQL> -- prepare data SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (1,      "Jason", to_date("19960725","YYYYMMDD"), 1234,              8767,         "E",    2)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (2,      "John",  to_date("19970715","YYYYMMDD"), 2341,              3456,         "W",    3)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (3,      "Joe",   to_date("19860125","YYYYMMDD"), 4321,              5654,         "E",    3)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (4,      "Tom",   to_date("20060913","YYYYMMDD"), 2413,              6787,         "W",    4)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (5,      "Jane",  to_date("20050417","YYYYMMDD"), 7654,              4345,         "E",    4)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (6,      "James", to_date("20040718","YYYYMMDD"), 5679,              6546,         "W",    5)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (7,      "Jodd",  to_date("20030720","YYYYMMDD"), 5438,              7658,         "E",    6)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)

 2               values (8,      "Joke",  to_date("20020101","YYYYMMDD"), 8765,              4543,         "W")
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)

 2               values (9,      "Jack",  to_date("20010829","YYYYMMDD"), 7896,              1232,         "E")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /
    EMPNO ENAME           HIREDATE  ORIG_SALARY CURR_SALARY R MANAGER_ID

--------------- --------- ----------- ----------- - ----------
        1 Jason           25-JUL-96        1234        8767 E          2
        2 John            15-JUL-97        2341        3456 W          3
        3 Joe             25-JAN-86        4321        5654 E          3
        4 Tom             13-SEP-06        2413        6787 W          4
        5 Jane            17-APR-05        7654        4345 E          4
        6 James           18-JUL-04        5679        6546 W          5
        7 Jodd            20-JUL-03        5438        7658 E          6
        8 Joke            01-JAN-02        8765        4543 W
        9 Jack            29-AUG-01        7896        1232 E

9 rows selected. SQL> select * from job

 2  /
    EMPNO JOBTITLE

--------------------
        1 Tester
        2 Accountant
        3 Developer
        4 COder
        5 Director
        6 Mediator
        7 Proffessor
        8 Programmer
        9 Developer

9 rows selected. SQL> SQL> SELECT empno, ename

 2  FROM employee outer
 3  WHERE EXISTS
 4    (SELECT 1
 5     FROM employee inner
 6     WHERE inner.manager_id = outer.empno);
    EMPNO ENAME

---------------
        2 John
        3 Joe
        4 Tom
        5 Jane
        6 James

SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> drop table job

 2  /

Table dropped. SQL> SQL></source>


Using EXISTS and NOT EXISTS with a Correlated Subquery

EXISTS operator checks for the existence of rows returned by a subquery.



   <source lang="sql">

SQL> SQL> -- create demo table SQL> create table Employee(

 2    EMPNO         NUMBER(3),
 3    ENAME         VARCHAR2(15 BYTE),
 4    HIREDATE      DATE,
 5    ORIG_SALARY   NUMBER(6),
 6    CURR_SALARY   NUMBER(6),
 7    REGION        VARCHAR2(1 BYTE),
 8    MANAGER_ID    NUMBER(3)
 9  )
10  /

Table created. SQL> SQL> create table job (

 2    EMPNO         NUMBER(3),
 3    jobtitle      VARCHAR2(20 BYTE)
 4  )
 5  /

Table created. SQL> SQL> insert into job (EMPNO, Jobtitle) values (1,"Tester"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (2,"Accountant"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (3,"Developer"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (4,"COder"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (5,"Director"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (6,"Mediator"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (7,"Proffessor"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (8,"Programmer"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (9,"Developer"); 1 row created. SQL> SQL> SQL> -- prepare data SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (1,      "Jason", to_date("19960725","YYYYMMDD"), 1234,              8767,         "E",    2)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (2,      "John",  to_date("19970715","YYYYMMDD"), 2341,              3456,         "W",    3)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (3,      "Joe",   to_date("19860125","YYYYMMDD"), 4321,              5654,         "E",    3)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (4,      "Tom",   to_date("20060913","YYYYMMDD"), 2413,              6787,         "W",    4)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (5,      "Jane",  to_date("20050417","YYYYMMDD"), 7654,              4345,         "E",    4)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (6,      "James", to_date("20040718","YYYYMMDD"), 5679,              6546,         "W",    5)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (7,      "Jodd",  to_date("20030720","YYYYMMDD"), 5438,              7658,         "E",    6)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)

 2               values (8,      "Joke",  to_date("20020101","YYYYMMDD"), 8765,              4543,         "W")
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)

 2               values (9,      "Jack",  to_date("20010829","YYYYMMDD"), 7896,              1232,         "E")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /
    EMPNO ENAME           HIREDATE  ORIG_SALARY CURR_SALARY R MANAGER_ID

--------------- --------- ----------- ----------- - ----------
        1 Jason           25-JUL-96        1234        8767 E          2
        2 John            15-JUL-97        2341        3456 W          3
        3 Joe             25-JAN-86        4321        5654 E          3
        4 Tom             13-SEP-06        2413        6787 W          4
        5 Jane            17-APR-05        7654        4345 E          4
        6 James           18-JUL-04        5679        6546 W          5
        7 Jodd            20-JUL-03        5438        7658 E          6
        8 Joke            01-JAN-02        8765        4543 W
        9 Jack            29-AUG-01        7896        1232 E

9 rows selected. SQL> select * from job

 2  /
    EMPNO JOBTITLE

--------------------
        1 Tester
        2 Accountant
        3 Developer
        4 COder
        5 Director
        6 Mediator
        7 Proffessor
        8 Programmer
        9 Developer

9 rows selected. SQL> SQL> SELECT empno, ename

 2  FROM employee outer
 3  WHERE EXISTS
 4    (SELECT empno
 5     FROM employee inner
 6     WHERE inner.manager_id = outer.empno);
    EMPNO ENAME

---------------
        2 John
        3 Joe
        4 Tom
        5 Jane
        6 James

SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> drop table job

 2  /

Table dropped. SQL> SQL></source>


Using NOT EXISTS with a Correlated Subquery

   <source lang="sql">

SQL> SQL> SQL> SQL> -- create demo table SQL> create table Employee(

 2    EMPNO         NUMBER(3),
 3    ENAME         VARCHAR2(15 BYTE),
 4    HIREDATE      DATE,
 5    ORIG_SALARY   NUMBER(6),
 6    CURR_SALARY   NUMBER(6),
 7    REGION        VARCHAR2(1 BYTE),
 8    MANAGER_ID    NUMBER(3)
 9  )
10  /

Table created. SQL> SQL> create table job (

 2    EMPNO         NUMBER(3),
 3    jobtitle      VARCHAR2(20 BYTE)
 4  )
 5  /

Table created. SQL> SQL> insert into job (EMPNO, Jobtitle) values (1,"Tester"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (2,"Accountant"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (3,"Developer"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (4,"COder"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (5,"Director"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (6,"Mediator"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (7,"Proffessor"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (8,"Programmer"); 1 row created. SQL> insert into job (EMPNO, Jobtitle) values (9,"Developer"); 1 row created. SQL> SQL> SQL> -- prepare data SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (1,      "Jason", to_date("19960725","YYYYMMDD"), 1234,              8767,         "E",    2)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (2,      "John",  to_date("19970715","YYYYMMDD"), 2341,              3456,         "W",    3)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (3,      "Joe",   to_date("19860125","YYYYMMDD"), 4321,              5654,         "E",    3)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (4,      "Tom",   to_date("20060913","YYYYMMDD"), 2413,              6787,         "W",    4)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (5,      "Jane",  to_date("20050417","YYYYMMDD"), 7654,              4345,         "E",    4)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (6,      "James", to_date("20040718","YYYYMMDD"), 5679,              6546,         "W",    5)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION, MANAGER_ID)

 2               values (7,      "Jodd",  to_date("20030720","YYYYMMDD"), 5438,              7658,         "E",    6)
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)

 2               values (8,      "Joke",  to_date("20020101","YYYYMMDD"), 8765,              4543,         "W")
 3  /

1 row created. SQL> insert into Employee(EMPNO, EName, HIREDATE, ORIG_SALARY, CURR_SALARY, REGION)

 2               values (9,      "Jack",  to_date("20010829","YYYYMMDD"), 7896,              1232,         "E")
 3  /

1 row created. SQL> SQL> SQL> SQL> -- display data in the table SQL> select * from Employee

 2  /
    EMPNO ENAME           HIREDATE  ORIG_SALARY CURR_SALARY R MANAGER_ID

--------------- --------- ----------- ----------- - ----------
        1 Jason           25-JUL-96        1234        8767 E          2
        2 John            15-JUL-97        2341        3456 W          3
        3 Joe             25-JAN-86        4321        5654 E          3
        4 Tom             13-SEP-06        2413        6787 W          4
        5 Jane            17-APR-05        7654        4345 E          4
        6 James           18-JUL-04        5679        6546 W          5
        7 Jodd            20-JUL-03        5438        7658 E          6
        8 Joke            01-JAN-02        8765        4543 W
        9 Jack            29-AUG-01        7896        1232 E

9 rows selected. SQL> select * from job

 2  /
    EMPNO JOBTITLE

--------------------
        1 Tester
        2 Accountant
        3 Developer
        4 COder
        5 Director
        6 Mediator
        7 Proffessor
        8 Programmer
        9 Developer

9 rows selected. SQL> SQL> SELECT empno, ename

 2  FROM employee outer
 3  WHERE NOT EXISTS
 4    (SELECT 1
 5     FROM employee inner
 6     WHERE inner.empno = outer.manager_id);
    EMPNO ENAME

---------------
        9 Jack
        8 Joke

SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> drop table job

 2  /

Table dropped. SQL> SQL></source>


Writing Correlated Subqueries

A correlated subquery references one or more columns in the outer query.

The subquery is known as a correlated subquery because the subquery is related to the outer query.

A correlated subquery is used for a query depending on a value in each row contained in the outer query.



   <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_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 id, first_name, salary

 2  FROM employee outer
 3  WHERE salary >
 4    (SELECT AVG(salary)
 5     FROM employee inner
 6     WHERE inner.id = outer. id);

no rows selected SQL> SQL> SQL> SQL> -- clean the table SQL> drop table Employee

 2  /

Table dropped. SQL> SQL></source>