Question in SQL... 
Author Message
 Question in SQL...

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

Thanx.

Harshad Warnekar.



Sun, 18 Jul 2004 19:36:11 GMT
 Question in SQL...



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



Sun, 18 Jul 2004 22:58:23 GMT
 
 [ 2 post ] 

 Relevant Pages 

1. Update question and SQL question

2. ****Question for SQL Server GURU's**** SQL Server 6.5 on a Domain Controller

3. Simple SQL Server Data Access Question I-SQL

4. SQL question for SQL Gurus

5. question for sql sever 6.5 (Transact SQL)

6. Non Oracle-specific SQL question for SQL Guru's

7. Theoretical question: Pl/SQL and SQL*PLUS

8. Question: Sybase SQL/RPC security risk - sql.txt [1/1]

9. SQL delete question (ULTRIX/SQL, INGRES 6.?)

10. questions about SQL Server 7.0

11. I have a question in SQL 6.5

12. Question: TRANSACT-SQL Syntax


 
Powered by phpBB® Forum Software