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. Provide an alphabetic list by last name of all Clients showing their full name (e.g., Bob Barlow), with the number of Accounts they hold and the total balance of those Accounts Provide a count and total amount of Transactions for each Type Description PLEASE DONT NOT COPY THE EXISTING SOLUTION ON BARTLEBY IT IS 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 Client to Account via the Owns table CREATE VIEW ClientAccount_view AS SELECT ca.ClientNbr, ca.FName, ca.LName, ow.AccountNbr, acc.Balance FROM Client ca LEFT JOIN Owns ow ON ca.ClientNbr = ow.ClientNbr LEFT JOIN Account acc ON (ca.ClientNbr = ow.ClientNbr and ow.AccountNbr = acc.AccountNbr);
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.
Provide an alphabetic list by last name of all Clients showing their full name (e.g., Bob Barlow), with the number of Accounts they hold and the total balance of those Accounts
Provide a count and total amount of Transactions for each Type Description
PLEASE DONT NOT COPY THE EXISTING SOLUTION ON BARTLEBY IT IS 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 Client to Account via the Owns table
CREATE VIEW ClientAccount_view
AS SELECT ca.ClientNbr, ca.FName, ca.LName, ow.AccountNbr, acc.Balance
FROM Client ca
LEFT JOIN Owns ow ON ca.ClientNbr = ow.ClientNbr
LEFT JOIN Account acc
ON (ca.ClientNbr = ow.ClientNbr and ow.AccountNbr = acc.AccountNbr);
Step by step
Solved in 3 steps
