Database SQL

SECTION B: SQL QUESTIONS (2 QUESTIONS, 40 MARKS) Instruction: Answer ALL questions. Question 1 Tables below are extracted from a bank database: Table 1: BRANCH 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 Acs. 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 123456789871 123456789882 123456789893 Cust. Id 1001 1002 1003 Acc. Iype Saving Current Saving Acc Balance 1985.22 562.35 9870.50 14 FI Table 3: ACCOUNT Acc Id Branch Id 123456789871 123456789882 123456789893 123456789904 123456789912 123456789922 Cust Id 1001 1002 1003 1004 1005 1006 Acc Type Saving Current Saving Saving Current Saving Acc Balance 1985.22 562.35 9870.50 56000.25 160.32 16000.00 Table 4: TRANSACTION Trans Id Acc Id 123456 12345678987 123498 12345678988 123457 12345678987 Type Deposit Deposit Withdrawal Amount 1000 500 250 Based on the tables, write an SQL statement for each of the question below: (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. (b) A customer whose ID is 1002, changed his address to Kajang, Update this information accordingly in the database. [4 marks] (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 1 2 3 3 1 4 [4 marks] (e) The bank manager wants to obtain customer information (Cust_Id, Name) and their associated account information (Aco Id. Acc. Iype, Aco Balance) of all customers. [6 marks]