Đang chuẩn bị liên kết để tải về tài liệu:
SQL VISUAL QUICKSTART GUIDE- P32

SQL VISUAL QUICKSTART GUIDE- P32: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 8.45 lists the books that outsold all the books that author A06 wrote or cowrote . The inner query uses a join to find the sales of each book by author A06. The outer query inspects the highest sales figure in the list and determines whether each book sold more copies. See Figure 8.45 for the result. Again the IS NOT NULL condition is needed in case sales is null for a book by author A06. I can replicate Listing 8.45 by using GROUP BY HAVING and MAX instead of ALL SELECT title_id FROM titles Listing 8.45 List the books that outsold all the books that author A06 wrote or cowrote . See Figure 8.45 for the result. Listing SELECT title_id title_name FROM titles WHERE sales ALL SELECT sales FROM title_authors ta INNER JOIN titles t ON t.title_id ta.title_id WHERE ta.au_id A06 AND sales IS NOT NULL GROUP BY title_id HAVING MAX sales SELECT MAX sales title_id title_name FROM title_authors ta Comparing All Subquery Values with ALL INNER JOIN titles t ON t.title_id ta.title_id WHERE ta.au_id A06 Listing 8.46 uses a correlated subquery in the HAVING clause of the outer query to list the types of books for which the highest sales figure is more than twice the average sales for that type. The inner query is evaluated once for each group defined in the outer query once for each type of book . See Figure 8.46 for the result. Tips ALL is equivalent to NOTIN see Testing Set Membership with IN earlier in this chapter. DBMS MySQL 4.0 and earlier don t support subqueries see the DBMS Tip in Understanding Subqueries earlier in this chapter. In older PostgreSQL versions convert the floating-point numbers in Listing 8.46 to DECIMAL see Converting Data Types with CAST in Chapter 5. To run Listing 8.46 change the floating-point literal to T05 Exchange of Platitudes T07 I Blame My Mother T12 Spontaneous Not Annoying Figure 8.45 Result of Listing 8.45. Listing 8.46 List the types of books for which the highest sales figure is more than twice the average sales for that .