Oracle PL/SQL Tutorial/Query Select/Correlated Subquery
Содержание
- 1 A correlated subquery: the subquery references a column from a table referred to in the parent statement.
- 2 Correlated subquery using the EXISTS operator
- 3 Extract a Subset of the Results
- 4 How Many Products By Department with correlated subqueries 1
- 5 How Many Products By Department with correlated subqueries 2
- 6 List all employees who attended XML course and Java course
- 7 list all employees who is younger than Joe
- 8 The subquery returning the literal value 1
- 9 Using EXISTS and NOT EXISTS with a Correlated Subquery
- 10 Using NOT EXISTS with a Correlated Subquery
- 11 Writing Correlated Subqueries
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>
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.
Extract a Subset of the Results
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>
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;
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;
List all employees who attended XML course and Java course
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.
list all employees who is younger than Joe
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.
The subquery returning the literal value 1
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>
EXISTS operator checks for the existence of rows returned by a subquery.
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>
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>
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.
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>