Transactions and Indexes Coding Practice Challenges | CodeNivo

Question 1

Practical Walkthrough: Bank Fund Transfer

Accepted Answer

BEGIN;

UPDATE user_accounts
SET balance = balance - 1500.00
WHERE account_id = 1;

UPDATE user_accounts
SET balance = balance + 1500.00
WHERE account_id = 2;

COMMIT;

SELECT account_id, owner_name, balance FROM user_accounts ORDER BY account_id;

Answer

-- Practice query for Understanding Database Transactions

Question 2

Practical Example: A Bank Transfer

Accepted Answer

CREATE TEMP TABLE accounts (
    account_id INTEGER PRIMARY KEY,
    account_name TEXT NOT NULL,
    balance NUMERIC(12, 2) NOT NULL
        CHECK (balance >= 0)
);

INSERT INTO accounts VALUES
    (1, 'Account A', 5000.00),
    (2, 'Account B', 2000.00);

BEGIN;

UPDATE accounts
SET balance = balance - 1000.00
WHERE account_id = 1;

UPDATE accounts
SET balance = balance + 1000.00
WHERE account_id = 2;

-- Simulate abandoning the transfer.
ROLLBACK;

SELECT account_id, account_name, balance
FROM accounts
ORDER BY account_id;

Answer

-- Practice query for COMMIT and ROLLBACK

account_id	owner_name	balance
1	Aarav Sharma	3500.00
2	Diya Patel	2700.00

account_id	account_name	balance
1	Account A	5000.00
2	Account B	2000.00

Transactions and Indexes Problems