Database1

Question 1 Tables below are extracted from a bank database: Table 1: BRANCH IN Branch Id 1 2. 3 4 Location Tel. No Kuala Lumpur 0321625321 Selangor 0356378924 Melaka 062356892 Negeri Sembilan 067698521 Table 2: CUSTOMER Cust. Id 1001 1002 1003 1004 1005 1006 Acc Id 12345678987 12345678988 12345678989 12345678990 12345678991 12345678992 Name Siti Aminah Mohd Ali Nur Arifah James Wong Nurafifah Jovian Address Kota Damansara Shah Alam Alor Gajah Kuala Pilah Klang Kajang Table 3: ACCOUNT Acc Id Branch Id 12345678987 1 12345678988 2 123456789893 123456789904 123456789912 123456789922 Cust.Id 1001 1002 1003 1004 1005 1006 Ace Type Saving Current Saving Saving Current Saving Acs Balance 1985.22 562.35 9870.50 56000.25 160.32 16000.00 Table 4: TRANSACTION Trans. Id 123456 123498 123457 Ace Id 12345678987 12345678988 12345678987 Type Deposit Deposit Withdrawal Amount 1000 500 250 Based on the tables, write an SQL statement for each of the question below: Page 3 of 9 CISB214/CISB3213, Semester 1, 2020/2021 (a) The bank opens a new branch with following details: Location : Perak Phone No. : 055462563 Insert this information into the related table in the database. [2 marks] (b) A customer whose ID is 1002, changed his address to Kajang, Update this information accordingly in the database. (c) The bank manager wants to obtain all transaction information of an account with ID, 12345678987. [4 marks] (d) The bank manager wants to display the number of accounts that are related to each branch. Sample result: Branch Id Total Accounts 1 2 3 3 1 4 1 [4 marks] (e) The bank manager wants to obtain customer information (CustId, Name) and their associated account information (Acc_IdAcs. Type. Acc Balance) of all customers. [6 marks]