chapter 12
Download
Skip this Video
Download Presentation
Chapter 12

Loading in 2 Seconds...

play fullscreen
1 / 24

Chapter 12 - PowerPoint PPT Presentation


  • 61 Views
  • Uploaded on

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

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

PowerPoint Slideshow about ' Chapter 12' - tonya


An Image/Link below is provided (as is) to download presentation

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
Chapter 12

Subqueries

FoxPro Chapter 12

subquery
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
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
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
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
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
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
  • 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
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
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
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;

WHERE inner.grade=outer.grade)

Ho A 12.7

Wu B 12.9

FoxPro Chapter 12

correlated subqueries
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
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
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
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
Two Tables

Best100(name, grade, best)

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
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
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 and below average time in grade b
Best Training Time andBelow Average Time in Grade B

SELECT * FROM best100;

WHERE grade=‘B’ AND best <;

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

Ma B 12.8

SELECT * FROM best100;

WHERE grade=‘B’ AND best >;

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

Chow B 13.2

Mak B 13.3

FoxPro Chapter 12

slide20
= 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
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
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
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
  • ADDITIVE, UNION

FoxPro Chapter 12

exercises
Exercises
  • Subqueries
    • Further Practice 19, 23, 24
  • For inner joins and outer joins
    • Further Practice 21, 22

FoxPro Chapter 12

ad