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';