A bank stores customer account information in a table called ACCOUNT. AccountNo HolderName AccountType Balance OpenDate Active A1001 M. Santos Savings 5200....
Question 1 Report
A bank stores customer account information in a table called ACCOUNT.
| AccountNo | HolderName | AccountType | Balance | OpenDate | Active |
|---|---|---|---|---|---|
| A1001 | M. Santos | Savings | 5200.00 | 2020-03-15 | TRUE |
| A1002 | J. Park | Current | 1800.00 | 2021-07-20 | TRUE |
| A1003 | R. Okonkwo | Savings | 12000.00 | 2019-01-10 | TRUE |
| A1004 | E. Mueller | Current | 350.00 | 2022-05-05 | FALSE |
| A1005 | K. Johal | Savings | 8500.00 | 2020-11-30 | TRUE |
(a) Write an SQL statement to deposit 500.00 into account A1002 (increase the Balance by 500). [2]
(b) Write an SQL query to find the total Balance held across all active Savings accounts. [2]
Answer Details
(a) This question tests SQL UPDATE and arithmetic operations on column values. [2]
To deposit 500.00 into account A1002:
UPDATE ACCOUNT
SET Balance = Balance + 500.00
WHERE AccountNo = 'A1002'[1] for UPDATE ... SET Balance = Balance + 500, [1] for WHERE AccountNo = 'A1002'.
The key concept here is that Balance + 500.00 reads the current value of Balance and adds 500 to it. This is different from SET Balance = 500, which would replace the balance entirely. After this statement, J. Park's balance changes from 1800.00 to 2300.00.
(b) Finding the total balance across all active savings accounts: [2]
SELECT SUM(Balance)
FROM ACCOUNT
WHERE Active = TRUE AND AccountType = 'Savings'[1] for SUM(Balance), [1] for the WHERE clause combining both conditions.
The WHERE clause filters to only active savings accounts before the SUM is calculated:
- A1001: M. Santos, Savings, Active=TRUE, Balance=5200.00 (included)
- A1003: R. Okonkwo, Savings, Active=TRUE, Balance=12000.00 (included)
- A1005: K. Johal, Savings, Active=TRUE, Balance=8500.00 (included)
A1002 is excluded (Current account) and A1004 is excluded (Active=FALSE).
Expected result: 5200.00 + 12000.00 + 8500.00 = 25700.00
Download The App On Google Playstore
Everything you need to excel in your exams
