-
Consider a bank database with only one relation transaction (transno, acctno, data, 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 and T2 are transaction on the same account and the date of T2 is ≤ the 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 0 balance when the account is created). Assume there is at most one transaction per day on each account, and each account has had atleast one transaction since it was created. To simply your query, break it up into 2 steps by defining an intermediate view V.
-
- Only a
- only b
- Both a and b
- None of the above
Correct Option: A
Create view TP (acctno, date amount)
as select T1.acctno, T1.date
T2.amount from transaction T1,
transaction T2
where T1.acctno = T2.acctno
and T2.date < = T1.date
(b) Create view V (acctno, date, balance)
as select acctno, date, sum (amount)
from TP
group by acctno, date
Select acctno, min (balance)
from V
group by acctno.
