Re: SQL Test for exerts only!!!!

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 08 Jun 1999 01:48:11 GMT
Message-ID: <375d7340.1793749@newshost.us.oracle.com>

A copy of this was sent to Chris Close <mrclose_at_flash.net> (if that email address didn't require changing) On Mon, 07 Jun 1999 19:58:39 GMT, you wrote:

>--------- Oracle test ----------
>
>You have the following three tables:
>

you need to get new tables. east coast and west coast -- bad design. but anyway one way is:

SQL> l
  1 select last_name || ', ' || first_name name,

  2          a.client_number,
  3          cash,
  4          chck "CHECK",
  5          amt
  6    from ( select client_number,
  7             sum(decode( deposit_type, 'CASH',  deposit_amount, 0 )) cash,
  8             sum(decode( deposit_type, 'CHECK', deposit_amount, 0 )) chck,
  9             sum(deposit_amount) amt
 10             from ( select * from west_coast union all
 11                    select * from east_coast )
 12            group by client_number
 13          ) a, client_data b

 14 where a.client_number = b.client_number  15* order by last_name asc
SQL> /

NAME                           CLIENT_NUMBER       CASH      CHECK        AMT
------------------------------ ------------- ---------- ---------- ----------
Blass, Bill                           100002      50.25          0      50.25
Blass, Betty                          100005        500          0        500
Hunt, Kathy                           100004          0      250.5      250.5
Jones, Joe                            100001      125.5       50.5        176
Smith, Jane                           100003          0        100        100

5 rows selected.

[snip]

>
>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:

your output has Jones 2 times...

>
>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

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Jun 07 1999 - 20:48:11 CDT