The National Bank of Erehwon handles money and maintains bank accounts on behalf of clients.A client is a person who does business with the bank.A client may have any number of accounts, and an account may belong to multiple clients (e.g., spouses, business partners).The client record is used for identification and contact data.For each account, the bank maintains the current balance on hand.Clients are identified by a five digit number starting with 10001.Accounts are identified by a seven digit number starting with 1000001.When an account is first opened, its balance is set to zero. During the course of day-to-day business, Erehwon Bank applies transactions to accounts, including deposits, withdrawals, bill payments, and debit purchases or returns. For each transaction, the date and time, amount, and account are recorded, along with reference data applicable to that type of transaction:Deposits and withdrawals require the branch number to be recorded.Bill payments, and debit purchases or returns require the merchant number.Queries:List of only the Accounts that have multiple Clients associated 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 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.) DATABASE INFO BELOW AND ATTACHEDTX_TYPE CodeDescription DDeposit WWithdrawal BBill Payment PPurchase RReturn BRANCH NbrName 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 NbrName 301… make up your own name 302… make up your own name 303… make up your own name 304… make up your own name 10001 has no account, so no transactions10002 account will have deposits and withdrawals only spread over 2 months10003, 10004 will have all types of transactions10005 will have deposits, then bill payments only10006 account will have no transactionsTRANSACTIONTxNbr TxCode AccountNbr Amount Date12345678910111213141516171820212223242526XDDDDDWWDDWDWDPBPRDDDBBBB1000001100000010000011000001100000110000011000001100000110000011000002100000210000031000003100000310000031000002100000210000021000004100000510000051000005100000510000051000005123.45 May 1 2019234.56 May 1 2019345.67 May 1 2019100.00 May 1 2019200.00 May 11 2019300.00 May 21 201950.00 May 29 200975.00 May 29 2009123.45 Jun 15 20191000.00 May 15 2019456.78 May 15 2019456.78 May 15 2019500.00 May 18 2019100.00 May 20 201965.78 May 20 2019100.00 May 21 2019200.00 May 21 201950.00 May 26 20192000.00 Jun 1 20192000.00 Jun 1 20192000.00 Jun 1 20193456.78 Jun 10 2019432.10 Jun 15 2019100.00 Jun 20 201980.00 Jun 25 2019Time12:0012:0012:0010:0011:0012:0010:0011:0013:009:009:059:1014:0013:0014:509:0010:0012:3413:0013:0014:0012:0014:3015:5516:56RefNbr101101111101101101102103101104104104104104304301302301101101102301302303304Balance***100.00300.00600.00550.00475.00598.451000.00543.22456.78-43.2256.78-9.00443.22243.22293.222000.002000.004000.00543.22111.1211.12-68.88NotesERROR no such TxTypeERROR non-existing AccountERROR no such BranchTwo tx same day diff timeTwo tx same day diff timeTwo tx same day diff timeTwo tx same day diff time CLIENTClientNbr FNameLNameStreet10001... your own names goes here10002 ... make up other names, etc.10003 ... make up other names, etc.10004 ... make up other names, etc.10005 ... make up other names, etc.10006 ... make up other names, etc.ACCOUNTAccountNbr Balance100000110000021000003100000410000051000006000000OWNSClientNbr AccountNbr100021000310003100041000410005100051000610007100000110000021000003100000210000031000004100000510000061000001CityNotesowner 10002owner 10003, 10004owner 10003, 10004owner 10005owner 10005owner 10006Notes1st shared account2nd shared account1st shared account2nd shared account1st account2nd accountProvPostCd Phone EmailERROR join non-existing client to accountNotesNo accountSingle accountShare accounts with 10004Share accounts with 10003Two accountsSingle account but no transactions

THIS IS THE ANSWER AS PER BARTLEBY GUIDELINES, WE ARE NOT ALOWED TO ANS MORE THAN 3 SUBPARTS OF A…

The National Bank of Erehwon handles money and maintains bank accounts on behalf of clients. A client is a person who does business with the bank. A client may have any number of accounts, and an account may belong to multiple clients (e.g., spouses, business partners). The client record is used for identification and contact data. For each account, the bank maintains the current balance on hand. Clients are identified by a five digit number starting with 10001. Accounts are identified by a seven digit number starting with 1000001. When an account is first opened, its balance is set to zero. During the course of day-to-day business, Erehwon Bank applies transactions to accounts, including deposits, withdrawals, bill payments, and debit purchases or returns. For each transaction, the date and time, amount, and account are recorded, along with reference data applicable to that type of transaction: Deposits and withdrawals require the branch number to be recorded. Bill payments, and debit purchases or returns require the merchant number. Queries: List of only the Accounts that have multiple Clients associated 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 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.)

Computer Networking: A Top-Down Approach (7th Edition)

7th Edition

ISBN:9780133594140

Author:James Kurose, Keith Ross

Publisher:James Kurose, Keith Ross

Chapter1: Computer Networks And The Internet

Section: Chapter Questions

Problem R1RQ: What is the difference between a host and an end system? List several different types of end...

See similar textbooks

Related questions

Question

The National Bank of Erehwon handles money and maintains bank accounts on behalf of clients.

A client is a person who does business with the bank.
A client may have any number of accounts, and an account may belong to multiple clients (e.g., spouses, business partners).
The client record is used for identification and contact data.
For each account, the bank maintains the current balance on hand.
Clients are identified by a five digit number starting with 10001.
Accounts are identified by a seven digit number starting with 1000001.

When an account is first opened, its balance is set to zero. During the course of day-to-day business, Erehwon Bank applies transactions to accounts, including deposits, withdrawals, bill payments, and debit purchases or returns. For each transaction, the date and time, amount, and account are recorded, along with reference data applicable to that type of transaction:

Deposits and withdrawals require the branch number to be recorded.
Bill payments, and debit purchases or returns require the merchant number.

Queries:

List of only the Accounts that have multiple Clients associated
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
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.)

DATABASE INFO BELOW AND ATTACHED

TX_TYPE
Code	Description
D	Deposit
W	Withdrawal
B	Bill Payment
P	Purchase
R	Return


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