Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
SQL VISUAL QUICKSTART GUIDE- P25: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 You also can join values in two columns that aren t equal. Listing 7.16 uses greater-than join to find each book whose revenue price X sales is at least 10 times greater than the advance paid to the author s . See Figure 7.16 for the result. The use of and joins is common but not-equal joins are used rarely. Generally notequal joins make sense only when used with a self-join see Creating a Self-Join later in this chapter. Tip Using WHERE syntax Listing 7.16 is equivalent to SELECT t.title_id t.title_name r.advance t.price t.sales AS Revenue FROM titles t royalties r WHERE t.price t.sales r.advance 10 AND t.title_id r.title_id ORDER BY t.price t.sales DESC Listing 7.16 List each book whose revenue price X sales is at least 10 times greater than its advance. See Figure 7.16 for the result. Listing SELECT t.title_id t.title_name r.advance t.price t.sales AS Revenue FROM titles t INNER JOIN royalties r ON t.price t.sales r.advance 10 AND t.title_id r.title_id ORDER BY t.price t.sales DESC title_id title_name advance Revenue T07 I Blame My Mother 1000000.00 35929790.00 T05 Exchange of Platitudes 100000.00 1400008.00 T12 Spontaneous Not Annoying 50000.00 1299012.99 T03 Ask Your System Administrator 15000.00 1025396.65 T13 What Are The Civilian Applications 20000.00 313905.33 T06 How About Never 20000.00 225834.00 T02 200 Years of German Humor 1000.00 190841.70 T09 Kiss My Boo-Boo .00 69750.00 T08 Just Wait Until After School .00 40950.00 Figure 7.16 Result of Listing 7.16. 220 Joins Listing 7.17 List the author names and the names of the books that each author wrote or cowrote . See Figure 7.17 for the result. Listing SELECT a.au_fname a.au_lname t.title_name FROM authors a INNER JOIN title_authors ta ON a.au_id ta.au_id INNER JOIN titles t ON t.title_id ta.title_id ORDER BY a.au_lname ASC a.au_fname ASC t.title_name ASC Complicated queries can arise from simple questions. In Listing 7.17 I must join three tables to list .
