Challenge: Transaction Creation

Now, let's walk through creating a transaction ourselves based on the bank_accounts table described in the previous chapter.

We're going to perform a straightforward financial transaction: transferring funds from one account to another. Ensuring that both steps occur as a single logical unit exemplifies a real-life transaction.

We will work with account numbers 101 and 789 and the initial balance will be the following:

Tarea

Swipe to start coding

Your task is to fill in the gaps in the query to create a SQL transaction. Use the keywords BEGIN and COMMIT to do it.

_{Once you've completed this task, click the button below the code to check your solution.}

Solución

BEGIN;

-- Deduct the amount from the source account if sufficient balance is available

UPDATE BankAccounts

SET balance = CASE WHEN balance >= 800 THEN balance - 800 ELSE balance END

WHERE account_number = 101;

-- Add the amount to the destination account if deduction was successful

UPDATE BankAccounts

SET balance = CASE WHEN (SELECT balance FROM BankAccounts WHERE account_number = 101) >= 800 THEN balance + 800 ELSE balance END

WHERE account_number = 789;

COMMIT;

-- Retrieve updated account balances

SELECT account_number, balance

FROM BankAccounts

WHERE account_number IN (101, 789);

¿Todo estuvo claro?

¡Gracias por tus comentarios!

Sección 1. Capítulo 4

___;

-- Deduct the amount from the source account if sufficient balance is available

UPDATE BankAccounts

SET balance = CASE WHEN balance >= 800 THEN balance - 800 ELSE balance END

WHERE account_number = 101;

-- Add the amount to the destination account if deduction was successful

UPDATE BankAccounts

SET balance = CASE WHEN (SELECT balance FROM BankAccounts WHERE account_number = 101) >= 800 THEN balance + 800 ELSE balance END

WHERE account_number = 789;

___;

-- Retrieve updated account balances

SELECT account_number, balance

FROM BankAccounts

WHERE account_number IN (101, 789);

Query Result

No query executed yet...

Pregunte a AI

Pregunte lo que quiera o pruebe una de las preguntas sugeridas para comenzar nuestra charla