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
<source lang="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> --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>
</source>
<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>
<source lang="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.
</source>
<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>
Inner query Demo
<source lang="sql">
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>
</source>
<source lang="sql">
SQL> SQL> SQL> SQL> CREATE TABLE EMP(
2 EMPNO NUMBER(4) NOT NULL, 3 ENAME VARCHAR2(10), 4 JOB VARCHAR2(9), 5 MGR NUMBER(4), 6 HIREDATE DATE, 7 SAL NUMBER(7, 2), 8 COMM NUMBER(7, 2), 9 DEPTNO NUMBER(2) 10 );
Table created.
SQL> 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.
</source>
<source lang="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> 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.
</source>
<source lang="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 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>
</source>
<source lang="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>
</source>
