Subqueries A query can be based on the results of an embedded query. The embedded query (SQL command) is analyzed first and then the primary query uses those results to obtain its answer. The embedded query is contained in the primary query’s clause (surrounded by parenthesis). Where The embedded query is evaluated first, creating a temporary table of records. The main query then processes, using the temporary query results as its row criteria. Subqueries can only return the values from one column. Queries can be to as many levels as necessary nested (query inside a query, inside a query…) One common use of subqueries is to determine which Show the employee who records are related to a statistic (MIN, MAX, etc). has the highest salary. which customer has the Show the donor name who ”Determine highest credit limit.” made the largest donation the students who scored lower than the average on (2 nested queries) ”List the test.” Select custfirst, custlast From tblCustomer Where creditlimit = (Select Max(creditlimit) From tblCustomer) Select studentfirst, studentlast From tblTest Where score < (Select Avg(score) From tblTest)
Subqueries A query can be based on the results of an embedded query. The embedded query (SQL command) is analyzed first and then the primary query uses those results to obtain its answer. The embedded query is contained in the primary query’s Where clause (surrounded by parenthesis). The embedded query is evaluated first, creating a temporary table of records. The main query then processes, using the temporary query results as its row criteria. Subqueries can only return the values from one column. Queries can be nested to as many levels as necessary (query inside a query, inside a query…) One common use of subqueries is to determine which records are related to a statistic (MIN, MAX, etc). ”Determine which customer has the highest credit limit.” ”List the students who scored lower than the average on the test.” Select custfirst, custlast From tblCustomer Where creditlimit = (Select Max(creditlimit) From tblCustomer) Select studentfirst, studentlast From tblTest Where score < (Select Avg(score) From tblTest) Show the employee who has the highest salary. Show the donor name who made the largest donation (2 nested queries)
4%20-%20Multi-Table%20Queries.docx