SQL Test for exerts only!!!!

From: Chris Close <mrclose_at_flash.net>
Date: Mon, 07 Jun 1999 19:58:39 GMT
Message-ID: <375C246C.CC8FA0C4@flash.net>

Oracle test ----------

You have the following three tables:

SQL>describe client_data;
Name Null? Type
--------------- ------ ----

CLIENT_NUMBER   NUMBER (10)
FIRST_NAME      CHAR   (6)
LAST_NAME       CHAR   (16)

SQL>describe west_coast;
Name            Null?  Type

--------------- ------ ----

CLIENT_NUMBER   NUMBER (10)
DEPOSIT_TYPE    CHAR   (6)
DEPOSIT_AMOUNT  NUMBER (12,2)

SQL> describe east_coast;
Name            Null?  Type

--------------- ------ ----

CLIENT_NUMBER   NUMBER (10)
DEPOSIT_TYPE    CHAR   (6)
DEPOSIT_AMOUNT  NUMBER (12,2)

The data in the tables is as follows:

SQL> select * from client data;
CLIENT_NUMBER FIRST_NAME LAST_NAME
------------- ---------- ---------

100001        Joe        Jones
100002        Bill       Blass
100003        Jane       Smith
100004        Kathy      Hunt
100005        Betty      Blass

SQL> select * from west_coast;
CLIENT_NUMBER DEPOSI DEPOSIT_AMOUNT
------------- ------ --------------

100001        CASH   25.5
100001        CHECK  50.5
100002        CASH   50.25
100003        CHECK  100

SQL> select * from east_coast;
CLIENT_NUMBER DEPOSI DEPOSIT AMOUNT
------------- ------ --------------

100001        CASH   100
100004        CHECK  250.5
100005        CASH   500

Create a single SQL query to join the three tables together and show the

amount of money deposited by cash, check and total in separate columns. Order the query by last name of the client ascending. Example of output:

NAME CLIENT_NUMBER CASH CHK AMT
-------------- ------------- ----- ----- ------

Blass, Betty   100005        500   0     500
Blass, Bill    100002        50.25 0     50.25
Hunt, Kathy    100004        0     250.5 250.5
Jones Joe      100001        25.5  50.5  76
Jones, Joe     100001        100   0     100
Smith, Jane    100003        0     100   100

6 rows selected

--End--

reply to mrclose_at_flash.net Received on Mon Jun 07 1999 - 14:58:39 CDT