Chapter 12

1 / 24

# Chapter 12 - PowerPoint PPT Presentation

Chapter 12. Subqueries. Subquery. A subquery is a query embedded in another query Needed because result of a subquery is used to perform another query Frequently used to compare a field to the result of an aggregate function

I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.

## PowerPoint Slideshow about ' Chapter 12' - tonya

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.

- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Chapter 12

Subqueries

FoxPro Chapter 12

Subquery
• A subquery is a query embedded in another query
• Needed because result of a subquery is used to perform another query
• Frequently used to compare a field to the result of an aggregate function
• In Visual FoxPro, a subquery can only be located in the WHERE clause of a query

FoxPro Chapter 12

Example: Find the Name of the Fastest Runner
• A100(class, no, name, house, result)

7A 01 Chan Love 13.3

7A 15 Lee Faith 13.8

7A 09 Cheung Wisdom 12.9

7S 38 Wong Hope 13.1

7S 12 Ho Faith 14.2

• SELECT name, min(result) FROM A100 && misleading

Ho 12.9 (Ho is the name of the last record)

• SELECT name, result FROM A100;

WHERE result = (SELECT min(result) FROM A100)

Cheung 12.9

FoxPro Chapter 12

General Syntax
• A SELECT statement included within another SELECT statement

SELECT name, result;

FROM A100;

WHERE result =;

(SELECT min(result) FROM A100)

Outer query, executed last

Subquery, executed first

FoxPro Chapter 12

Points to Note

The subquery

• must be enclosed in parentheses
• must appear on the right-hand side of the condition of the WHERE clause of the outer query
• It is possible to have up to 16 nested subqueries. (Not for FoxPro, only ONE)

FoxPro Chapter 12

Single-Row (value) Subqueries
• Subqueries that return a single value from a table

SELECT * from A100;

WHERE result > (SELECT avg(result) FROM A100)

class no name house result

7A 15 Lee Faith 13.8

7S 12 Ho Faith 14.2

Note: the subquery returns a single value - the average

FoxPro Chapter 12

Single-Row Comparison Operators
• When the result of a subquery is a single value, we can use simple relational operators like

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

• The result of the outer query may not be single valued

FoxPro Chapter 12

Multi-Row Subqueries
• Multi-Row subqueries return one or more rows (values)
• Multi-Row comparison operators are
• IN
• NOT IN
• =ANY (out of syllabus)

FoxPro Chapter 12

Examples
• AB100(class, no, name, house, grade, result)

7A 01 Chan Love A 13.3

7A 15 Lee Faith A 13.8

7A 09 Cheung Wisdom A 12.9

7S 38 Wong Hope A 13.1

7S 12 Ho Faith A 12.7 (Not 14.2)

6A 04 Au Wisdom B 13.0

6S 38 Chow Love B 13.4

5C 15 Wu Hope B 12.9

4B 41 Ma Faith B 13.1

3E 15 Mak Love B 13.5

FoxPro Chapter 12

Example

SELECT name, grade, result FROM ab100;

WHERE result IN;

(SELECT min(result) FROM ab100 group by grade)

Cheung A 12.9 (unexpected result)

Ho A 12.7

Wu B 12.9

Note 1 : the subquery returns 12.7 and 12.9 (two values)

Note 2 : the use of IN for multi-valued subquery

Note 3 : the unexpected result

FoxPro Chapter 12

Compare Within Same Grade- Remove unexpected results

SELECT name, grade, result FROM ab100 AS outer;

WHERE result IN;

(SELECT min(result) FROM ab100 AS inner;

Ho A 12.7

Wu B 12.9

FoxPro Chapter 12

Correlated Subqueries
• A query with a subquery is self contained if both the query and the subquery are referencing the SAME TABLE
• A query is a correlated subquery if the result of the subquery is also determined by the current record of the query that embeds it.

FoxPro Chapter 12

Syntax

SELECT outer-column(s);

FROM outer-table;

WHERE outer-column-value IN;

(SELECT inner-column;

FROM inner-table;

WHERE [another]inner-column = outer-column)

FoxPro Chapter 12

How Correlated Subqueries Are Executed?
• Correlated subqueries differ from simple subquery not only in the order of execution but also in the number of times that the queries are executed
• Execution starts with the outer query
• The outer query select each of the individual rows of the outer table and considers them as candidate rows
• For each one of these candidate rows, the correlated inner query is executed once

FoxPro Chapter 12

A Complicated Process
• During the execution of the inner query the system looks for rows that satisfy the inner WHERE condition for the value specified by the outer column (e.g. inner.grade=outer.grade)
• All rows of the inner table that satisfy this condition form a temporary set (e.g. Chan,…,Ho)
• The system then tests the outer condition (e.g. [result IN …]) against the rows stored in the temporary set
• All rows that satisfy the outer condition are then displayed, the process continues until all candidate rows have been processed

FoxPro Chapter 12

Two Tables

Chan A 13.5

Lee A 12.6

Cheung A 13.0

Wong A 12.7

Ho A 12.9

Au B 13.1

Chow B 13.2

Wu B 13.0

Ma B 12.8

Mak B 13.3

FoxPro Chapter 12

Query Involving Two Tables

SELECT * from best100;

WHERE name =;

(SELECT name FROM ab100 WHERE house = ‘Faith’)

Subquery return more than one record

SELECT * from best100;

WHERE name IN;

(SELECT name FROM ab100 WHERE house = ‘Faith’)

Lee A 12.6

Ho A 12.9

Ma B 12.8

FoxPro Chapter 12

Training Time < Record Time

SELECT * FROM best100;

WHERE best < (SELECT min(result) FROM ab100)

Lee A 12.6

SELECT * FROM best100 WHERE best <;

(SELECT min(result) FROM ab100 WHERE grade=\'B\')

Lee A 12.6

Wong A 12.7

Ma B 12.8

FoxPro Chapter 12

Best Training Time andBelow Average Time in Grade B

SELECT * FROM best100;

(SELECT min(result) FROM ab100 WHERE grade=\'B\')

Ma B 12.8

SELECT * FROM best100;

(SELECT avg(result) FROM ab100 WHERE grade=\'B\')

Chow B 13.2

Mak B 13.3

FoxPro Chapter 12

= ANY

Same results as “IN”

• select * from best100;

where name = ANY;

(select name from ab100 where class=\'7A\')

• select * from best100;

where name IN;

(select name from ab100 where class=\'7A\') Chan A 13.5

Lee A 12.6

Cheung A 13.0

FoxPro Chapter 12

EXISTS Operator
• The EXISTS operator can be used whenever we are interested in knowing whether or not the subquery returns a row and not in the values of any particular row
• This operator returns a TRUE value if the subquery returns any row
• Result is trivial (all records in the outer table) if the subquery is uncorrelated

FoxPro Chapter 12

EXISTS - Use in correlated subqueries

SELECT name, house from ab100 AS a;

WHERE EXISTS;

(SELECT * FROM houseinfo AS b;

WHERE a.house =b.house and b.house=‘Love’)

Houseinfo(house, colour,chairman)

Faith red Hung

Hope orange Chan

Wisdom blue Nam

Love green Luk

Chan

Chow

Mak

FoxPro Chapter 12

Lists of SQL Commands Used in ASL Computer Applications
• SELECT, ALL, DISTINCT
• FROM, AS
• WHERE, IN, BETWEEN, LIKE
• GROUP BY, HAVING
• ORDER BY, ASC, DESC
• TO FILE, TO PRINTER, TO SCREEN, INTO