tailieunhanh - 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 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 for the result. The sum of the values in each of the last three columns in the result equals the corresponding total in Figure . Tip Using WHERE syntax Listing is equivalent to SELECT COUNT AS Num books SUM AS Total royalties SUM AS Total advances SUM AS Total due to authors FROM titles t royalties r WHERE AND IS NOT NULL GROUP BY ORDER BY ASC Listing Calculate the total royalties paid by each publisher. See Figure for the result. Listing SELECT COUNT AS Num books SUM AS Total royalties SUM AS Total advances SUM - AS Total due to authors FROM titles t INNER JOIN royalties r ON WHERE IS NOT NULL GROUP BY ORDER BY ASC pub_id Num books Total royalties Total advances Total due to authors P01 3 P02 1 P03 3 P04 5 Figure Result of Listing . 230 Joins Listing is similar to Listing except that it calculates the total royalties earned by each author for all books written or cowritten . See Figure for the result. The sum of the values in each of the last three columns in the result equals the corresponding total in Figure . Listing Calculate the total royalties earned by each author for all books written or cowritten . See Figure for the result. .