Real Time Scenarios of ORACLE SQL developement - 1

Display Credit and Debit Amount in an account for a given date in ORACLE.

Table : Transaction

Queries:

drop table Transaction;

create table Transaction (Account_Number varchar2(10), 

Transaction_Type varchar2(10), 

Amount integer, Transaction_date date);

insert into Transaction values ('111','Credit', 1000, '11-Nov-27');

insert into Transaction values ('111','Credit', 1200, '11-Nov-27');

insert into Transaction values ('222','Debit', 2000, '11-Nov-29');

insert into Transaction values ('333','Credit', 1000, '11-Nov-28');

insert into  Transaction values('222','Credit', 1000, '11-Nov-29');

insert into Transaction values ('111','Debit', 1200, '11-Nov-27');

Sum of credits and debits of an account for the given dates.

select 

Transaction_date, 

Transaction_Type,

Account_Number, 

sum(Amount) amt 

from Transaction

group  by Transaction_date, Transaction_Type,Account_Number;

Sum of credits and debits of an account for the given dates in two separate  columns like below (credit_amt,debit_amt).

Note: Sub queries are marked in RED

select
Transaction_date,
Account_Number,
max(case when Transaction_Type = 'Credit' then amt end) credit_amt,
max(case when Transaction_Type = 'Debit' then amt end) debit_amt
from
(select Transaction_date, Transaction_Type,Account_Number, sum(Amount) amt from Transaction group by Transaction_date, Transaction_Type,Account_Number)
group by Transaction_date,Account_Number;

Substituting '0' in place of NULL values in the above table.

The NVL() function replaces null with an alternative in the results of a query.

Syntax: NVL(e1, e2)

In the below example Null will be replaced by 0

select 

Transaction_date,

Account_Number, 

NVL(max(case when Transaction_Type = 'Credit' then amt end),0) credit_amt, 

NVL

(max(case when Transaction_Type = 'Debit' then amt end),0) debit_amt 

from 

(select Transaction_date, Transaction_Type,Account_Number, sum(Amount) amt from Transaction group by Transaction_date, Transaction_Type,Account_Number) 

group by Transaction_date,Account_Number;

Total Amount in the account for the given date.

select 

Transaction_date,

Account_Number,

(credit_amt-debit_amt) as total_amt 

from 

(select Transaction_date,Account_Number, nvl(max(case when Transaction_Type = 'Credit' then amt end),0) credit_amt, NVL(max(case when Transaction_Type = 'Debit' 

then amt end),0) debit_amt 

from 

(select Transaction_date, Transaction_Type,Account_Number, sum(Amount) amt from Transaction

  group  by Transaction_date, Transaction_Type,Account_Number) 

group by Transaction_date,Account_Number);