Re: Question in SQL...

From: Nis Jorgensen <nis_at_dkik.dk>
Date: Wed, 30 Jan 2002 15:58:23 +0100
Message-ID: <6a2g5u0ednruajjejb7rvru6d2n5hnfaj5_at_4ax.com>


On 30 Jan 2002 03:36:11 -0800, harshadwarnekar_at_yahoo.com (Harshad) wrote:

>Hi All,
>
> This question I found in one of the exam papers.
>
>---------------------------------------------------------------------
>Q> Consider a bank database with only one relation
>
> transaction(transno, acctno, date, amount)
>
> The amount attribute value is positive for deposits and negative for
>withdrawals.
>
> (a) Define an SQL view TP containing the information (acctno,
>T1.date, T2.amount) for every pair of transactions T1, T2 such that T1
>an T2 are transactions on same account and the date of T2 is <= date
>of T1.
>
> (b) Using only the above view TP, write a query to find for each
>account the minimum balance it ever reached (not including the balance
>when the account is created). Assume there is atmost one transaction
>per day on each account, and each account has had atleast one
>transaction since it was created.
> To simplify your query, break it up into 2 steps by defining an
>intermediate view V.
>---------------------------------------------------------------------
>
> For the (a) part I feel the query to be as follows:
>
> create view TP(acctno, T1.date, T2.amount)
> as (select T1.acctno, T1.date, T2.amount
> from transaction as T1, transaction as T2
> where T1.acctno = T2.acctno
> and T2.date <= T1.date)
>
> Please check the query for (a) I have written. And suggest me query
>for part (b).

This looks OK, except for possible typos. I would normally use ANSI join syntax, but your solution looks OK.

Now create a view containing the account balance on each day, and select the minimum.

-- 
Nis Jorgensen
Amsterdam

Join the Patti Beadles Fan Club!
Details to follow.
Received on Wed Jan 30 2002 - 15:58:23 CET

Original text of this message