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. GIVEN THE BELOW: --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)); --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)); -- Insert test data into TxType table INSERT INTO TxType (TxTypeCode, TxTypeDescription) VALUES (‘D’, ‘DEPOSIT’); INSERT INTO TxType (TxTypeCode, TxTypeDescription) VALUES (‘W’, ‘WITHDRAWAL’); INSERT INTO TxType (TxTypeCode, TxTypeDescription) VALUES (‘P’, ‘PURCHASE’); INSERT INTO TxType (TxTypeCode, TxTypeDescription) VALUES (‘R’, ‘RETURN’); INSERT INTO TxType (TxTypeCode, TxTypeDescription) VALUES (‘B’, ‘BILL PAYMENT’); BRANCH Nbr Name 101 … make up your own name 102 … make up your own name 103 … make up your own name 104 … make up your own name MERCHANT Nbr Name 301 … make up your own name 302 … make up your own name 303 … make up your own name 304 … make up your own name
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.
GIVEN THE BELOW:
--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));
--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));
-- Insert test data into TxType table
INSERT INTO TxType (TxTypeCode, TxTypeDescription)
VALUES (‘D’, ‘DEPOSIT’);
INSERT INTO TxType (TxTypeCode, TxTypeDescription)
VALUES (‘W’, ‘WITHDRAWAL’);
INSERT INTO TxType (TxTypeCode, TxTypeDescription)
VALUES (‘P’, ‘PURCHASE’);
INSERT INTO TxType (TxTypeCode, TxTypeDescription)
VALUES (‘R’, ‘RETURN’);
INSERT INTO TxType (TxTypeCode, TxTypeDescription)
VALUES (‘B’, ‘BILL PAYMENT’);
| BRANCH | ||
| Nbr | Name | |
| 101 | … make up your own name | |
| 102 | … make up your own name | |
| 103 | … make up your own name | |
| 104 | … make up your own name | |
| MERCHANT | ||
| Nbr | Name | |
| 301 | … make up your own name | |
| 302 | … make up your own name | |
| 303 | … make up your own name | |
| 304 | … make up your own name |
Step by step
Solved in 2 steps
