J2ME DATA MANAGEMENT in Java

Maker PDF417 in Java J2ME DATA MANAGEMENT

345 278 825 Table 11-16

200 500 200

4 3 2

Define the Orders table as follows: OrderNumber, number, primary key ProdNumber, number CustomerNumber, number StoreNum, number Amount, number

A subquery is a query whose results are evaluated by an expression in the WHERE clause of another query, as illustrated in Listing 11-92 In this example, the subquery is defined below the first WHERE clause The subquery joins rows of the Sales table and the Order table by store number value, as shown in the second WHERE clause Once joined, the

122 334 365 534 555 717 874

5237 3255 3255 7466 1052 1052 7466

591 901 901 591 999 721 721

345 278 278 825 278 825 825

200 321 433 523 23 75 354

Table 11-17

11:

subquery totals the value of the Amount column for each store number and returns the results to a temporary table The query then returns a ResultSet that contains store numbers where the value of the Estimate column in the Sales table is equal to the total number of the Amount column returned by the subquery Listing 11-93 contains the DownRow() method that retrieves and displays the ResultSet from Listing 11-92

try { String query = new String (" SELECT StoreNumber " + " FROM Sales "+ " WHERE Estimate = (SELECT SUM(Amount) " + " FROM Orders, Sales " + " WHERE StoreNum = StoreNumber) "); DataRequest = DatabasecreateStatement(); Results = DataRequestexecuteQuery (query); Systemoutprintln("Store"); Systemoutprintln("-----"); DisplayResults (Results); DataRequestclose(); }

private void DownRow ( ResultSet DisplayResults ) throws SQLException { long Store; Store = DisplayResultsgetLong ( 1 ) ; Systemoutprintln(Store); }

Any conditional expression (as discussed previously in this chapter) can be used to evaluate a relationship between a query and the results of the subquery You can use four types of conditional tests with a subquery: Comparison test This test uses comparison operators to compare values in the temporary table with values in the table used by the query Existence test This test determines whether a value in the current row of the table used by the query also exists in the temporary table Set membership test This test is similar to the existence test in that the DBMS is directed to determine whether a value in the current row of the table used by the query also exists in the temporary table

Qualified test This test consists of either the ANY test or the ALL test and determines whether a value in the current row of the table used by the query is in one row of the temporary table or all rows of the temporary table The comparison test is illustrated in the previous Create a Subquery section The next several sections show how to perform the other tests Before executing code segments in these sections, insert rows shown in Table 11-18 in the Sales table

The existence test is used whenever you need to return rows in the ResultSet where a value in a column is present in the results of the subquery The existence test requires that you place the EXISTS modifier between the query and the subquery Listing 11-94 gives an example of how to use an existence test The DBMS is told to return a single instance of store numbers from the Sales table only if the store number is in the Orders table The store number isn t returned if the store hasn t placed an order Listing 11-93 contains the DownRow() method that can be used with Listing 11-94

try { String query = new String (" SELECT DISTINCT StoreNumber " + " FROM Sales "+ " WHERE EXISTS " + " (SELECT StoreNum " + " FROM Orders " + " WHERE StoreNum = StoreNumber) "); DataRequest = DatabasecreateStatement(); Results = DataRequestexecuteQuery (query); Systemoutprintln("Store"); Systemoutprintln("-----"); DisplayResults (Results); DataRequestclose(); }