Subqueries - Subqueries Chapter 6 Objectives After...

Subqueries
Chapter 6
Objectives
After completing this lesson, you should be able to do the
follovving:
•
Describe the types of problems that
subqueries can solve
• Define subqueries
• List the types of subqueries
•
Write single-row and multiple-row
subqueries
1

Using a Subquery to Solve a Problem
"Who has a salary greater than Jones'?"
"Which employees have a salary greater
than Jones' salary?"
"What is Jones' salary?"
2

Subqueries
SELECT select_list
FROM table
WHERE expr operator
( SELECT select_List
FROM table );
•
The subquery (inner query) executes
once before the main query.
•
The result of the subquery is used by
the main query (outerquery).
A subquery is a SELECT statement that is embedded in a clause of another
SELECT statement. You can build powerful statements out of simple ones by
using subqueries. They can be very useful when you need to select rows from a
table with a condition that depends on the data in the table itself.
You can place the subquery in a number of SQL clauses
•WHERE clauss
•HAVING clause
•
FROM clause
ln the syntax;
operator includes a comparison operatör such as >, =, or IN
Note: Comparison operutors fall iııto two classes: single-row operators
( > , = , >= , < , < > , <= )
and multiple-row operators ( IN , ANY , ALL ).
3

Using a Subquery
SELECT ename
FROM EMP
WHERE sal >
( SELECT sal
FROM emp
WHERE empno=7566);
ENAME
FORD
SCOTT
KING
FORD
4

Using a Subquery
SELECT ename, sal, deptno, job
FROM EMP
WHERE job =
( SELECT job
FROM emp
WHERE empno=7369);
ENAME SAL DEPTNO JOB
ADAMS 1100 20 CLERK
JAMES 950 30 CLERK
MILLER 1300 10 CLERK
SMITH 800 20 CLERK
ADAMS 1100 20 CLERK
JAMES 950 30 CLERK
MILLER 1300 10 CLERK
7 rows selected.
5

SELECT ename, sal, deptno
FROM EMP
WHERE sal IN
( SELECT MIN(sal)
FROM emp
GROUP BY deptno );
ENAME SAL DEPTNO
JAMES 950 30
SMITH 800 20
MILLER 1300 10
6

SELECT empno, ename, job
FROM emp
WHERE sal < ANY
( SELECT sal
FROM emp
WHERE job = 'CLERK' );
EMPNO ENAME JOB
7369 SMITH CLERK
7900 JAMES CLERK
7876 ADAMS CLERK
7521 WARD SALESMAN
7654 MARTIN SALESMAN
7

SELECT empno, ename, job
FROM emp
WHERE sal < ANY
( SELECT sal
FROM emp
WHERE job = 'CLERK' )
AND job <> 'CLERK' ;
EMPNO ENAME JOB
7521 WARD SALESMAN
7654 MARTIN SALESMAN
8

SELECT empno, ename, job
FROM emp
WHERE sal > ALL
( SELECT sal
FROM emp
WHERE job = 'CLERK' )
AND job <> 'CLERK' ;
EMPNO ENAME JOB
7499 ALLEN SALESMAN
7566 JONES MANAGER
7698 BLAKE MANAGER
7782 CLARK MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7844 TURNER SALESMAN
7902 FORD ANALYST
8 rows selected.
9

SELECT empno, ename, job
FROM emp
WHERE sal > ALL
( SELECT AVG(sal)
FROM emp
GROUP BY deptno ) ;
EMPNO ENAME JOB
7566 JONES MANAGER
7788 SCOTT ANALYST
7839 KING PRESIDENT
7902 FORD ANALYST
10

Guidelines for Using Subqueries
• Enclose subqueries in parentheses.
•
Place subqueries on the right side of the
comparison operator.
•
Do not add an ORDER BY clause to a
subquery.
•
Use single-row operators with single-
row subqueries.
•
Use multiple-row operators with
multiple-row subqueries.