Three customers Brian, John and Melanie have checking accounts in 3 banks (CHASE

Question

Three customers Brian, John and Melanie have checking accounts in 3 banks (CHASE, Bank of America, TDBank). Please answer the following questions based on Snapshot model. Here is how the sample data would look for one bank (say CHASE). They will have similar snapshot tables for other banks Balance CustomerName Brian John Melanie Brian John Melanie Brian John Melanie Date 10/1/2016 10/1/2016 10/1/2016 10/2/2016 10/2/2016 10/2/2016 10/3/2016 10/3/2016 10/3/2016 $40 $50 S30 $50 $20 $30 $50 $30 $10 1. Total bank balance of Brian in all banks on Oct 3a 2016? 2. Total bank balance of all customers in all banks on Oct 3ra, 2016? 3. Average bank balance of all customers in all banks for March 2016 4. Average daily bank balance for CHASE bank for "summer" season of 2016 5. Average daily bank balance for all banks for “summer" season of 2016

Explanation / Answer

1.select sum(b.balance) from --------------finding the balance of all the separated bank and then sum the result

(select balance from chase where name='Brian' and date=to_date('10/3/2016','dd/mm/yyyy')

union all

select balance from boa  where name='Brian' and date=to_date('10/3/2016','dd/mm/yyyy')

union all

select balance from tdbank where name='Brian' and date=to_date('10/3/2016','dd/mm/yyyy')) b

2.select sum(b.balance) from --------------finding the balance of all the separated bank and then sum the result

(select balance from chase where date=to_date('10/3/2016','dd/mm/yyyy')

union all

select balance from boa  where date=to_date('10/3/2016','dd/mm/yyyy')

union all

select balance from tdbank where date=to_date('10/3/2016','dd/mm/yyyy')) b

3.select avg(b.balance) from --------------finding the balance of all the separated bank and then avarage the result

(select balance from chase where date=to_date('10/3/2016','dd/mm/yyyy')

union all

select balance from boa  where date=to_date('10/3/2016','dd/mm/yyyy')

union all

select balance from tdbank where date=to_date('10/3/2016','dd/mm/yyyy')) b

4.select b.avg_bal from (select date,avg(balance) avg_bal from chase group by(date) where  to_char(to_date(date,'dd/mm/yyyy'),'mm') in ('1','2','3')) b------you can change the month number

5.

select c.avg_bal from

(select b.date,avg(b.balance) avg_bal from (select date, balance from chase where date=to_date('10/3/2016','dd/mm/yyyy')

and  to_char(to_date(date,'dd/mm/yyyy'),'mm') in ('1','2','3')

union all

select date,balance from boa  where date=to_date('10/3/2016','dd/mm/yyyy') and  to_char(to_date(date,'dd/mm/yyyy'),'mm') in ('1','2','3')

union all

select date,balance from tdbank where date=to_date('10/3/2016','dd/mm/yyyy') and to_char(to_date(date,'dd/mm/yyyy'),'mm') in ('1','2','3')) b group by b.date)c---same logic as 1

thanks,

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.