tailieunhanh - SQL VISUAL QUICKSTART GUIDE- P31

SQL VISUAL QUICKSTART GUIDE- P31:SQL (pronounced es-kyoo-el) is the standard programming language for creating, updating, and retrieving information that is stored in databases. With SQL, you can turn your ordinary questions (“Where do our customers live?”) into statements that your database system can understand (SELECT DISTINCT city, state FROM customers;) | Chapter 8 Listing uses a correlated subquery to imitate a GROUP BY clause and list all books that have a price greater than the average for books of its type. For each possible value of tl the DBMS evaluates the subquery and includes the row in the result if the price value in that row exceeds the calculated average. It s unnecessary to group by type explicitly because the rows for which the average price is calculated are restricted by the subquery s WHERE clause. See Figure for the result. Listing List all books that have a price greater than the average for books of its type. See Figure for the result. Listing SELECT type title_id price FROM titles tl WHERE price SELECT AVG FROM titles t2 WHERE ORDER BY type ASC title_id ASC Listing uses the same structure as Listing to list all the books whose sales are less than the best-selling books of their types. See Figure for the result. Tips Comparing a Subquery Value If a subquery returns more than one row you can use ALL or ANY to modify the comparison operator or you can introduce the subquery with IN. ALL ANY and IN are covered later in this chapter. DBMS MySQL and earlier don t support subqueries see the DBMS Tip in Understanding Subqueries earlier in this chapter. type title_id price biography T06 biography T07 children T09 history T13 psychology T04 Figure Result of Listing . Listing List all the books whose sales are less than the best-selling books of their types. See Figure for the result. Listing SELECT type title_id sales FROM titles tl WHERE sales SELECT MAX sales FROM titles t2 WHERE AND sales IS NOT NULL ORDER BY type ASC title_id ASC type title_id sales biography T06 11320 biography T12 100001 children T08 4095 history T01 566 history T02 9566 psychology T04 13001 psychology T11 94123 Figure Result of Listing . 280 Subqueries Testing Set Membership with in List .