1 / 24

Chapter 12

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

tonya
Download Presentation

Chapter 12

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Chapter 12 Subqueries FoxPro Chapter 12

  2. 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

  3. 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

  4. 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

  5. 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

  6. 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

  7. 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

  8. 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

  9. 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

  10. 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

  11. 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

  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

  13. 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

  14. 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

  15. 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

  16. 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

  17. 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

  18. 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

  19. 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

  20. = 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

  21. 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

  22. 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

  23. 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

  24. Exercises • Subqueries • Further Practice 19, 23, 24 • For inner joins and outer joins • Further Practice 21, 22 FoxPro Chapter 12

More Related