Oracle Database Administration: The Essential Refe

Subqueries

A subquery is a query inside another query. Normally, subqueries are used in INSERT, UPDATE, and DELETE statements. An example of a statement with a subquery is the following, in which we change King’s department to Sales:

UPDATE emp 
SET  deptno = (SELECT deptno FROM dept WHERE dname = 'SALES')
WHERE ename = 'KING';

In another example, everyone in the SALES department is given a 10% raise:

UPDATE emp
SET sal = sal * 1.1
WHERE deptno = (SELECT deptno FROM dept WHERE dname = 'SALES');

A final example shows how a query can be written as a join or as a subquery; specify either:

SELECT ename
FROM emp
WHERE deptno = (SELECT deptno FROM dept where dname = 'SALES');

or:

SELECT ename
FROM emp, dept
WHERE emp.deptno = dept.deptno 
AND dept.dname = 'SALES';