Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
SQL VISUAL QUICKSTART GUIDE- P33: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 Testing Existence with EXISTS Although I use SELECT COUNT in some of the DBMS-specific subqueries in the DBMS Tip in this section you should be wary of using an aggregate function in a subquery s SELECT clause. The existence test in Listing 8.59 for example always is true because COUNT always will return a row with the value zero here . I could argue that the result Figure 8.59 is flawed logically because no publisher ID XXX exists. To run Listings 8.55 8.57 and 8.58 in Microsoft Access change SELECT to SELECT 1. Additionally in Listing 8.57 add the clause FROM authors to the outer query and in Listing 8.58 add the clause FROM title_authors to the outer query. To run Listings 8.57 and 8.58 in Oracle add the clause FROM DUAL to the outer query see the DBMS Tip in Creating Derived Columns in Chapter 5. To run Listings 8.55 8.57 and 8.58 in DB2 change SELECT to SELECT 1. Additionally in Listings 8.57 and 8.58 add the clause FROM SYSIBM.SYSDUMMY1 to the outer query see the DBMS Tip in Creating Derived Columns in Chapter 5. For example change Listing 8.57 to SELECT DISTINCT Yes AS Duplicates FROM SYSIBM.SYSDUMMY1 WHERE EXISTS SELECT 1 FROM authors GROUP BY au_id HAVING COUNT 1 In MySQL to run Listing 8.57 add the clause FROM authors to the outer query and in Listing 8.58 add the clause FROM title_authors to the outer query. MySQL 4.0 and earlier don t support subqueries see the DBMS Tip in Understanding Subqueries earlier in this chapter. To run Listings 8.55 8.57 and 8.58 in PostgreSQL change SELECT to SELECT 1. Listing 8.59 Be careful when using aggregate functions in a subquery SELECT clause. See Figure 8.59 for the result. SELECT pub_id FROM publishers WHERE EXISTS SELECT COUNT FROM titles Listing WHERE pub_id XXX pub_id P01 P02 P03 P04 Figure 8.59 The result of Listing 8.59. 300 Subqueries Listing 8.60 These six queries are equivalent semantically they all list the authors who have written or cowritten at least one book. See Figure 8.60 for the result. .
