CREATE TABLE ACCOUNT(
	AccountID int NOT NULL PRIMARY KEY,
	AccountType varchar(30) NOT NULL,
	OpenDate date NOT NULL,
	ModifiedDate date NOT NULL,
	CustomerID int NOT NULL
)	

CREATE TABLE CUSTOMER(
	CustomerID int NOT NULL PRIMARY KEY,
	FirstName varchar(30) NOT NULL,
	MiddleName varchar(30),
	LastName varchar(30) NOT NULL,
	DateOfBirth date NOT NULL
)

CREATE TABLE TRANSACTIONS(
	TransactionID int NOT NULL PRIMARY KEY,
	CustomerID int NOT NULL,
	AccountID int NOT NULL,
	TransactionAmount decimal(10,2) NOT NULL,
	TransactionType varchar(30) NOT NULL,
	TransactionDate date NOT NULL
)

--- Insert test data into Account table
INSERT INTO ACCOUNT (AccountID,AccountType,OpenDate,ModifiedDate,CustomerID)
VALUES(12345,'DDA',sysdate,sysdate,12345);
INSERT INTO ACCOUNT (AccountID,AccountType,OpenDate,ModifiedDate,CustomerID)
VALUES(12346,'CCD',sysdate,sysdate,12345);

--- Insert test data into Customer table
INSERT INTO CUSTOMER(CustomerID,FirstName,MiddleName,LastName,DateOfBirth)
VALUES(12345,'ABC','','XYZ','12-DEC-1993');

--- Insert test data into Transactions table
INSERT INTO TRANSACTIONS(TransactionID,CustomerID,AccountID,TransactionAmount,TransactionType,TransactionDate)
VALUES(1,12345,12345,3000.00,'Credit',sysdate);
INSERT INTO TRANSACTIONS(TransactionID,CustomerID,AccountID,TransactionAmount,TransactionType,TransactionDate)
VALUES(2,12345,12346,2500.00,'Debit',sysdate);
INSERT INTO TRANSACTIONS(TransactionID,CustomerID,AccountID,TransactionAmount,TransactionType,TransactionDate)
VALUES(3,12345,12346,3000.00,'Credit',sysdate);
INSERT INTO TRANSACTIONS(TransactionID,CustomerID,AccountID,TransactionAmount,TransactionType,TransactionDate)
VALUES(4,12345,12346,500.00,'Debit',sysdate);

--- Fetch Data from tables
SELECT * FROM ACCOUNT; 

SELECT * FROM CUSTOMER;  

SELECT * FROM TRANSACTIONS; 

--- Break Command
Break on row skip 1 

Select * from Transactions;   

--- Ttitle and Btitle
Ttitle “TRANSACTIONS” 

BTitle “END OF REPORT” 

SELECT * FROM TRANSACTIONS; 

--- Clear Break, Compute
Clear Break 

Clear Compute 

--- Clear Column 
Column var_type clear 

--- Generate Report
Set feedback off 

Clear Break 
Clear Compute 
Clear Columns 

break on TransactionType skip page 
compute sum of TransactionAmount "TotalAmount" on TransactionType 
column TransactionType new_value var_type noprint 

Ttitle left 'Transaction:'var_type center 'TotalAmount' right sql.pno skip2 

SELECT 
A.AccountID, C.FirstName, C.LastName, T.TransactionType, T.TransactionAmount
FROM Account A, Customer C, Transactions T 
WHERE A.AccountID=T.AccountID 
AND 
C.CustomerID=T.CustomerID 
ORDER BY T.TransactionType;