Objectives

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

( > , = , >= , < , < > ,

( 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

ADAMS

JAMES

MILLER

SMITH

ADAMS

JAMES

MILLER

SAL

1100

950

1300

800

1100

950

1300

DEPTNO

20

30

10

20

20

30

10

7 rows selected.

5

JOB

CLERK

CLERK

CLERK

CLERK

CLERK

CLERK

CLERK

 ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download