Create a view of the Join of Deposit and Withdraw transactions to Bank Branch UNION with the join of Bill Payment and Debit Purchase, or Return transactions to Merchant (i.e., all transactions with appropriate reference name.). BranchNbr stores RefNbr of either 'D', OR 'W' transactions, and MerchantNbr stores RefNbr of either 'B', 'P' or 'R' transactions. List of each Account showing the first Transaction date, type, and amount. (Hint: This is a correlated sub-query.) Count and Total Amount for each Transaction Type within each Account (Hint: This is an extended GROUP BY.) PLEASE DONT NOT COPY THE EXISTING SOLUTION ON BARTLEBY IT IS WRONG AND DOES NOT WORK AND IS INCOMPLETE. GIVEN THE BELOW: --THIS CREATES THE BANK BRANCH TABLE: CREATE TABLE BankBranch ( BranchNbr INT PRIMARY KEY, BranchName VARCHAR(250)); --THIS CREATES THE MERCHANT TABLE: CREATE TABLE Merchant ( MerchantNbr INT PRIMARY KEY, MerchantName VARCHAR(250)); --THIS CREATES THE TRANSACTION TABLE: CREATE TABLE Transaction ( TxNbr INT PRIMARY KEY, AccountNbr INT, TxTypeCode VARCHAR(250), TxDate DATE, TxTime TIMESTAMP, TxAmount FLOAT, RefNbr INT, FOREIGN KEY (AccountNbr)REFERENCES Account(AccountNbr), FOREIGN KEY(TxTypeCode) REFERENCES TxType(TxTypeCode), FOREIGN KEY (RefNbr) REFERENCES BankBranch (BranchNbr), FOREIGN KEY (RefNbr) REFERENCES Merchant (MerchantNbr)); -- View for join of Transaction to Type description CREATE VIEW TranTypeDesc_view AS SELECT tr.TxNbr, tr.TxDate, tr.TxTime, tr.TxAmount, ty.TxTypeCode, ty.TxTypeDescription FROM Transaction tr LEFT JOIN TxType ty ON tr.TxTypeCode = ty.TxTypeCode;
Create a view of the Join of Deposit and Withdraw transactions to Bank Branch UNION with the join of Bill Payment and Debit Purchase, or Return transactions to Merchant (i.e., all transactions with appropriate reference name.).
BranchNbr stores RefNbr of either 'D', OR 'W' transactions, and MerchantNbr stores RefNbr of either 'B', 'P' or 'R' transactions.
-
List of each Account showing the first Transaction date, type, and amount. (Hint: This is a correlated sub-query.)
-
Count and Total Amount for each Transaction Type within each Account (Hint: This is an extended GROUP BY.)
PLEASE DONT NOT COPY THE EXISTING SOLUTION ON BARTLEBY IT IS WRONG AND DOES NOT WORK AND IS INCOMPLETE.
GIVEN THE BELOW:
--THIS CREATES THE BANK BRANCH TABLE:
CREATE TABLE BankBranch (
BranchNbr INT PRIMARY KEY,
BranchName VARCHAR(250));
--THIS CREATES THE MERCHANT TABLE:
CREATE TABLE Merchant (
MerchantNbr INT PRIMARY KEY,
MerchantName VARCHAR(250));
--THIS CREATES THE TRANSACTION TABLE:
CREATE TABLE Transaction (
TxNbr INT PRIMARY KEY,
AccountNbr INT,
TxTypeCode VARCHAR(250),
TxDate DATE,
TxTime TIMESTAMP,
TxAmount FLOAT,
RefNbr INT,
FOREIGN KEY (AccountNbr)REFERENCES Account(AccountNbr),
FOREIGN KEY(TxTypeCode) REFERENCES TxType(TxTypeCode),
FOREIGN KEY (RefNbr) REFERENCES BankBranch (BranchNbr),
FOREIGN KEY (RefNbr) REFERENCES Merchant (MerchantNbr));
-- View for join of Transaction to Type description
CREATE VIEW TranTypeDesc_view
AS SELECT tr.TxNbr, tr.TxDate, tr.TxTime, tr.TxAmount, ty.TxTypeCode, ty.TxTypeDescription
FROM Transaction tr
LEFT JOIN TxType ty ON tr.TxTypeCode = ty.TxTypeCode;
Step by step
Solved in 2 steps
