The Gateway to Computer Science Excellence
First time here? Checkout the FAQ!
x
+1 vote
556 views

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.

  1. Define an SQL view TP containing the information
    (acctno,T1.date,T2.amount) 
    for every pair of transaction T1,T2 and such that T1 and T2 are transaction on the same account and the date of T2 is $\leq $ the date of T1.
  2. 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 at least one transaction since it was created. To simplify your query, break it up into 2 steps by defining an intermediate view V.
asked in Databases by Veteran (59.6k points)
edited by | 556 views

1 Answer

0 votes

a.

Create view TP(T1.acctno, T1.date, T2.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.

i.

Create view V(acctno, date, balance)
as (select acctno, date, sum(amount)
    from TP
    group by acctno, date);

ii.

select acctno, min(balance) 
from V
group by acctno;

 

answered by Active (1.5k points)

Related questions



Quick search syntax
tags tag:apple
author user:martin
title title:apple
content content:apple
exclude -tag:apple
force match +apple
views views:100
score score:10
answers answers:2
is accepted isaccepted:true
is closed isclosed:true

42,579 questions
48,566 answers
155,473 comments
63,596 users