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

SQL VISUAL QUICKSTART GUIDE- P26: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 7 Creating an Inner Join with INNER JOIN Listing 7.22 uses a GROUP BY clause to calculate the total royalties paid by each publisher. The aggregate function COUNT computes the total number of books for which each publisher pays royalties. Note that each author s royalty share is unnecessary here because no per-author calculations are involved. See Figure 7.22 for the result. The sum of the values in each of the last three columns in the result equals the corresponding total in Figure 7.19. Tip Using WHERE syntax Listing 7.22 is equivalent to SELECT t.pub_id COUNT t.sales AS Num books SUM t.sales t.price r.royalty_rate AS Total royalties SUM r.advance AS Total advances SUM t.sales t.price r.royalty_rate -r.advance AS Total due to authors FROM titles t royalties r WHERE r.title_id t.title_id AND t.sales IS NOT NULL GROUP BY t.pub_id ORDER BY t.pub_id ASC Listing 7.22 Calculate the total royalties paid by each publisher. See Figure 7.22 for the result. Listing SELECT t.pub_id COUNT t.sales AS Num books SUM t.sales t.price r.royalty_rate AS Total royalties SUM r.advance AS Total advances SUM t.sales t.price r.royalty_rate - r.advance AS Total due to authors FROM titles t INNER JOIN royalties r ON r.title_id t.title_id WHERE t.sales IS NOT NULL GROUP BY t.pub_id ORDER BY t.pub_id ASC pub_id Num books Total royalties Total advances Total due to authors P01 3 135600.21 80000.00 55600.21 P02 1 71777.77 15000.00 56777.77 P03 3 3982561.72 1021000.00 2961561.72 P04 5 197279.85 220000.00 -22720.15 Figure 7.22 Result of Listing 7.22. 230 Joins Listing 7.23 is similar to Listing 7.22 except that it calculates the total royalties earned by each author for all books written or cowritten . See Figure 7.23 for the result. The sum of the values in each of the last three columns in the result equals the corresponding total in Figure 7.19. Listing 7.23 Calculate the total royalties earned by each author for all books written or cowritten . See Figure 7.23 for the result. .