Oracle PL/SQL/Subquery/Correlated Subquery
Содержание
- 1 A correlated subquery references one or more columns in the outer query
- 2 A correlated subquery: the subquery references a column from a table referred to in the parent statement.
- 3 Get Categories and Products (with Correlated Subqueries)
- 4 How Many Products By Department with correlated subqueries 2
- 5 Inner query Demo
- 6 To delete the records of emps whose salary is below the average salary in the department (correlated subquery):
- 7 To update the salary of all emps to the maximum salary in the corresponding department (correlated subquery):
- 8 Using EXISTS with a Correlated Subquery
- 9 Using NOT EXISTS with a Correlated Subquery
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-DEC-17 800 20
7499 ALLEN SALESMAN 7698 1981-FEB-20 1600 300 30
7521 WARD SALESMAN 7698 1981-FEB-22 1250 500 30
7566 JONES MANAGER 7839 1981-APR-02 2975 20
7654 MARTIN SALESMAN 7698 1981-SEP-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-MAY-01 2850 30
7782 CLARK MANAGER 7839 1981-JUN-09 2450 10
7788 SCOTT ANALYST 7566 1982-DEC-09 3000 20
7839 KING PRESIDENT 1981-NOV-17 5000 10
7844 TURNER SALESMAN 7698 1981-SEP-08 1500 0 30
7876 ADAMS CLERK 7788 1983-JAN-12 1100 20
7900 JAMES CLERK 7698 1981-DEC-03 950 30
7902 FORD ANALYST 7566 1981-DEC-03 3000 20
7934 MILLER CLERK 7782 1982-JAN-23 1300 10
14 rows selected.
SQL>
SQL> --Writing Correlated Subqueries
SQL>
SQL> --A correlated subquery references one or more columns in the outer query.
SQL> --The subquery is known as a correlated subquery because the subquery is related to the outer query.
SQL>
SQL> SELECT empno, mgr,ename, sal
2 FROM emp outer
3 WHERE sal >
4 (SELECT AVG(sal)
5 FROM emp inner
6 WHERE inner.empno = outer.mgr);
EMPNO MGR ENAME SAL
---------- ---------- ---------- ----------
7788 7566 SCOTT 3000
7902 7566 FORD 3000
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL>
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL> 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> 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>
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) VALUES (1, "Local", "In town");
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (1, "Remote", "Telecommute");
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (2, "Masks", "By bits");
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, "Wireless", "Not connected");
1 row created.
SQL> INSERT INTO Category (DepartmentID, Name, Description) VALUES (3, "Wired", "Connected");
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT C.Name as "Category Name", Product.Name as "Product Name"
2 FROM Product, Category C
3 WHERE Product.ProductID IN
4 (SELECT ProductID FROM ProductCategory
5 WHERE ProductCategory.CategoryID = C.CategoryID)
6 ORDER BY C.Name, Product.Name;
Category Name
--------------------------------------------------
Product Name
--------------------------------------------------
Local
PC
Local
Ruler
Masks
Desk
Masks
Keyboard
Masks
Pen
Masks
Ruler
Remote
Mouse
Wireless
Keyboard
8 rows selected.
SQL>
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>
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;
Inner query Demo
SQL>
SQL> create table t
2 as
3 select object_name ename,
4 mod(object_id,50) deptno,
5 object_id sal
6 from all_objects
7 where rownum <= 1000
8 /
Table created.
SQL>
SQL>
SQL>
SQL> select deptno, ename, sal
2 from t e1
3 where (select count(*)
4 from t e2
5 where e2.deptno = e1.deptno
6 and e2.sal >= e1.sal) <= 3
7 order by deptno, sal desc
8 /
DEPTNO ENAME SAL
------ ------------------------------ --------
0 V_$LOCK 1050.00
V_$BUFFER_POOL_STATISTICS 1000.00
V_$DLM_ALL_LOCKS 950.00
1 V$LOCK 1051.00
V$BUFFER_POOL_STATISTICS 1001.00
V$DLM_ALL_LOCKS 951.00
2 V_$SESSTAT 1052.00
V_$INSTANCE_RECOVERY 1002.00
V_$DLM_LOCKS 952.00
DEPTNO ENAME SAL
------ ------------------------------ --------
3 V$SESSTAT 1053.00
V$INSTANCE_RECOVERY 1003.00
V$DLM_LOCKS 953.00
4 V_$MYSTAT 1054.00
V_$CONTROLFILE 1004.00
V_$DLM_RESS 954.00
5 V$MYSTAT 1055.00
V$CONTROLFILE 1005.00
DEPTNO ENAME SAL
------ ------------------------------ --------
5 V$DLM_RESS 955.00
6 V_$SUBCACHE 1056.00
V_$LOG 1006.00
V_$HVMASTER_INFO 956.00
7 V$SUBCACHE 1057.00
V$LOG 1007.00
V$HVMASTER_INFO 957.00
8 V_$SYSSTAT 1058.00
DEPTNO ENAME SAL
------ ------------------------------ --------
8 V_$STANDBY_LOG 1008.00
V_$GCSHVMASTER_INFO 958.00
9 V$SYSSTAT 1059.00
V$STANDBY_LOG 1009.00
V$GCSHVMASTER_INFO 959.00
10 V_$STATNAME 1060.00
V_$DATAGUARD_STATUS 1010.00
V_$GCSPFMASTER_INFO 960.00
DEPTNO ENAME SAL
------ ------------------------------ --------
11 V$STATNAME 1061.00
V$DATAGUARD_STATUS 1011.00
V$GCSPFMASTER_INFO 961.00
12 V_$OSSTAT 1062.00
V_$THREAD 1012.00
GV_$DLM_TRAFFIC_CONTROLLER 962.00
13 V$OSSTAT 1063.00
V$THREAD 1013.00
GV$DLM_TRAFFIC_CONTROLLER 963.00
DEPTNO ENAME SAL
------ ------------------------------ --------
14 V_$ACCESS 1064.00
V_$PROCESS 1014.00
V_$DLM_TRAFFIC_CONTROLLER 964.00
15 V$ACCESS 1065.00
V$PROCESS 1015.00
V$DLM_TRAFFIC_CONTROLLER 965.00
16 V_$OBJECT_DEPENDENCY 1066.00
V_$BGPROCESS 1016.00
DEPTNO ENAME SAL
------ ------------------------------ --------
16 V_$GES_ENQUEUE 966.00
17 V$OBJECT_DEPENDENCY 1067.00
V$BGPROCESS 1017.00
V$GES_ENQUEUE 967.00
18 V_$DBFILE 1068.00
V_$SESSION 1018.00
V_$GES_BLOCKING_ENQUEUE 968.00
19 V$DBFILE 1069.00
DEPTNO ENAME SAL
------ ------------------------------ --------
19 V$SESSION 1019.00
V$GES_BLOCKING_ENQUEUE 969.00
20 V_$FILESTAT 1070.00
V_$LICENSE 1020.00
V_$GC_ELEMENT 970.00
21 V$FILESTAT 1071.00
V$LICENSE 1021.00
V$GC_ELEMENT 971.00
DEPTNO ENAME SAL
------ ------------------------------ --------
22 V_$TEMPSTAT 1072.00
V_$TRANSACTION 1022.00
V_$CR_BLOCK_SERVER 972.00
23 V$TEMPSTAT 1073.00
V$TRANSACTION 1023.00
V$CR_BLOCK_SERVER 973.00
24 V_$LOGFILE 1074.00
V_$BSP 1024.00
V_$CURRENT_BLOCK_SERVER 974.00
DEPTNO ENAME SAL
------ ------------------------------ --------
25 V$LOGFILE 1075.00
V$BSP 1025.00
V$CURRENT_BLOCK_SERVER 975.00
26 V_$FLASHBACK_DATABASE_LOGFILE 1076.00
V_$FAST_START_SERVERS 1026.00
V_$GC_ELEMENTS_W_COLLISIONS 976.00
27 V$FLASHBACK_DATABASE_LOGFILE 1077.00
V$FAST_START_SERVERS 1027.00
DEPTNO ENAME SAL
------ ------------------------------ --------
27 V$GC_ELEMENTS_WITH_COLLISIONS 977.00
28 V_$FLASHBACK_DATABASE_LOG 1078.00
V_$FAST_START_TRANSACTIONS 1028.00
V_$FILE_CACHE_TRANSFER 978.00
29 V$FLASHBACK_DATABASE_LOG 1079.00
V$FAST_START_TRANSACTIONS 1029.00
V$FILE_CACHE_TRANSFER 979.00
30 V_$FLASHBACK_DATABASE_STAT 1080.00
DEPTNO ENAME SAL
------ ------------------------------ --------
30 V_$LOCKED_OBJECT 1030.00
V_$TEMP_CACHE_TRANSFER 980.00
31 V$FLASHBACK_DATABASE_STAT 1081.00
V$LOCKED_OBJECT 1031.00
V$TEMP_CACHE_TRANSFER 981.00
32 V_$RESTORE_POINT 1082.00
V_$LATCH 1032.00
V_$CLASS_CACHE_TRANSFER 982.00
DEPTNO ENAME SAL
------ ------------------------------ --------
33 V$RESTORE_POINT 1083.00
V$LATCH 1033.00
V$CLASS_CACHE_TRANSFER 983.00
34 V_$ROLLNAME 1084.00
V_$LATCH_CHILDREN 1034.00
V_$BH 984.00
35 V$ROLLNAME 1085.00
V$LATCH_CHILDREN 1035.00
V$BH 985.00
DEPTNO ENAME SAL
------ ------------------------------ --------
36 V_$ROLLSTAT 1086.00
V_$LATCH_PARENT 1036.00
V_$LOCK_ELEMENT 986.00
37 V$ROLLSTAT 1087.00
V$LATCH_PARENT 1037.00
V$LOCK_ELEMENT 987.00
38 V_$UNDOSTAT 1088.00
V_$LATCHNAME 1038.00
DEPTNO ENAME SAL
------ ------------------------------ --------
38 V_$LOCKS_WITH_COLLISIONS 988.00
39 V$UNDOSTAT 1089.00
V$LATCHNAME 1039.00
V$LOCKS_WITH_COLLISIONS 989.00
40 V_$SGA 1090.00
V_$LATCHHOLDER 1040.00
V_$FILE_PING 990.00
41 V$LATCHHOLDER 1041.00
DEPTNO ENAME SAL
------ ------------------------------ --------
41 V$FILE_PING 991.00
V$SQL_OPTIMIZER_ENV 941.00
42 V_$LATCH_MISSES 1042.00
V_$TEMP_PING 992.00
V_$DLM_MISC 942.00
43 V$LATCH_MISSES 1043.00
V$TEMP_PING 993.00
V$DLM_MISC 943.00
DEPTNO ENAME SAL
------ ------------------------------ --------
44 V_$SESSION_LONGOPS 1044.00
V_$CLASS_PING 994.00
V_$DLM_LATCH 944.00
45 V$SESSION_LONGOPS 1045.00
V$CLASS_PING 995.00
V$DLM_LATCH 945.00
46 V_$RESOURCE 1046.00
V_$INSTANCE_CACHE_TRANSFER 996.00
V_$DLM_CONVERT_LOCAL 946.00
DEPTNO ENAME SAL
------ ------------------------------ --------
47 V$RESOURCE 1047.00
V$INSTANCE_CACHE_TRANSFER 997.00
V$DLM_CONVERT_LOCAL 947.00
48 V_$_LOCK 1048.00
V_$BUFFER_POOL 998.00
V_$DLM_CONVERT_REMOTE 948.00
49 V$_LOCK 1049.00
V$BUFFER_POOL 999.00
DEPTNO ENAME SAL
------ ------------------------------ --------
49 V$DLM_CONVERT_REMOTE 949.00
150 rows selected.
SQL>
SQL> drop table t;
Table dropped.
SQL>
SQL>
SQL>
SQL>
SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL,
3 ENAME VARCHAR2(10),
4 JOB VARCHAR2(9),
5 MGR NUMBER(4),
6 HIREDATE DATE,
7 SAL NUMBER(7, 2),
8 COMM NUMBER(7, 2),
9 DEPTNO NUMBER(2)
10 );
Table created.
SQL> 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> DELETE FROM emp e
2 WHERE sal < (SELECT AVG(sal) FROM emp
3 WHERE deptno = e.deptno);
6 rows deleted.
SQL>
SQL> drop table emp;
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>
SQL> UPDATE emp
2 SET sal = (SELECT MAX(sal)
3 FROM emp e
4 WHERE e.deptno = emp.deptno);
10 rows updated.
SQL>
SQL> drop table emp;
Table dropped.
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-DEC-17 800 20
7499 ALLEN SALESMAN 7698 1981-FEB-20 1600 300 30
7521 WARD SALESMAN 7698 1981-FEB-22 1250 500 30
7566 JONES MANAGER 7839 1981-APR-02 2975 20
7654 MARTIN SALESMAN 7698 1981-SEP-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-MAY-01 2850 30
7782 CLARK MANAGER 7839 1981-JUN-09 2450 10
7788 SCOTT ANALYST 7566 1982-DEC-09 3000 20
7839 KING PRESIDENT 1981-NOV-17 5000 10
7844 TURNER SALESMAN 7698 1981-SEP-08 1500 0 30
7876 ADAMS CLERK 7788 1983-JAN-12 1100 20
7900 JAMES CLERK 7698 1981-DEC-03 950 30
7902 FORD ANALYST 7566 1981-DEC-03 3000 20
7934 MILLER CLERK 7782 1982-JAN-23 1300 10
14 rows selected.
SQL>
SQL> --Using EXISTS with a Correlated Subquery
SQL>
SQL> SELECT empno, ename
2 FROM emp outer
3 WHERE EXISTS
4 (SELECT empno
5 FROM emp inner
6 WHERE inner.mgr = outer.empno);
EMPNO ENAME
---------- ----------
7902 FORD
7698 BLAKE
7839 KING
7566 JONES
7788 SCOTT
7782 CLARK
6 rows selected.
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>
SQL> CREATE TABLE EMP (EMPNO NUMBER(4) NOT NULL,
2 ENAME VARCHAR2(10),
3 JOB VARCHAR2(9),
4 MGR NUMBER(4),
5 HIREDATE DATE,
6 SAL NUMBER(7, 2),
7 COMM NUMBER(7, 2),
8 DEPTNO NUMBER(2));
Table created.
SQL>
SQL> INSERT INTO EMP VALUES (7369, "SMITH", "CLERK", 7902, TO_DATE("17-DEC-1980", "DD-MON-YYYY"), 800, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7499, "ALLEN", "SALESMAN", 7698, TO_DATE("20-FEB-1981", "DD-MON-YYYY"), 1600, 300, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7521, "WARD", "SALESMAN", 7698, TO_DATE("22-FEB-1981", "DD-MON-YYYY"), 1250, 500, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7566, "JONES", "MANAGER", 7839, TO_DATE("2-APR-1981", "DD-MON-YYYY"), 2975, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7654, "MARTIN", "SALESMAN", 7698,TO_DATE("28-SEP-1981", "DD-MON-YYYY"), 1250, 1400, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7698, "BLAKE", "MANAGER", 7839,TO_DATE("1-MAY-1981", "DD-MON-YYYY"), 2850, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7782, "CLARK", "MANAGER", 7839,TO_DATE("9-JUN-1981", "DD-MON-YYYY"), 2450, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7788, "SCOTT", "ANALYST", 7566,TO_DATE("09-DEC-1982", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7839, "KING", "PRESIDENT", NULL,TO_DATE("17-NOV-1981", "DD-MON-YYYY"), 5000, NULL, 10);
1 row created.
SQL> INSERT INTO EMP VALUES (7844, "TURNER", "SALESMAN", 7698,TO_DATE("8-SEP-1981", "DD-MON-YYYY"), 1500, 0, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7876, "ADAMS", "CLERK", 7788,TO_DATE("12-JAN-1983", "DD-MON-YYYY"), 1100, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7900, "JAMES", "CLERK", 7698,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 950, NULL, 30);
1 row created.
SQL> INSERT INTO EMP VALUES (7902, "FORD", "ANALYST", 7566,TO_DATE("3-DEC-1981", "DD-MON-YYYY"), 3000, NULL, 20);
1 row created.
SQL> INSERT INTO EMP VALUES (7934, "MILLER", "CLERK", 7782,TO_DATE("23-JAN-1982", "DD-MON-YYYY"), 1300, NULL, 10);
1 row created.
SQL>
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ----------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-DEC-17 800 20
7499 ALLEN SALESMAN 7698 1981-FEB-20 1600 300 30
7521 WARD SALESMAN 7698 1981-FEB-22 1250 500 30
7566 JONES MANAGER 7839 1981-APR-02 2975 20
7654 MARTIN SALESMAN 7698 1981-SEP-28 1250 1400 30
7698 BLAKE MANAGER 7839 1981-MAY-01 2850 30
7782 CLARK MANAGER 7839 1981-JUN-09 2450 10
7788 SCOTT ANALYST 7566 1982-DEC-09 3000 20
7839 KING PRESIDENT 1981-NOV-17 5000 10
7844 TURNER SALESMAN 7698 1981-SEP-08 1500 0 30
7876 ADAMS CLERK 7788 1983-JAN-12 1100 20
7900 JAMES CLERK 7698 1981-DEC-03 950 30
7902 FORD ANALYST 7566 1981-DEC-03 3000 20
7934 MILLER CLERK 7782 1982-JAN-23 1300 10
14 rows selected.
SQL>
SQL> --Using NOT EXISTS with a Correlated Subquery
SQL>
SQL> SELECT empno, ename
2 FROM emp outer
3 WHERE NOT EXISTS
4 (SELECT 1
5 FROM emp inner
6 WHERE inner.mgr = outer.empno);
EMPNO ENAME
---------- ----------
7844 TURNER
7521 WARD
7654 MARTIN
7499 ALLEN
7934 MILLER
7369 SMITH
7876 ADAMS
7900 JAMES
8 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
SQL>
SQL>