What are some examples of SQL transactions?

What are some examples of SQL transactions?

SQL transactions are sequences of operations performed as a single logical unit of work. Here are a few examples:

1. Bank Transfer: Moving money from one account to another involves debiting one account and crediting another.
2. Order Processing: When a customer places an order, the transaction might include updating inventory, creating an order record, and charging the customer.
3. Booking a Flight: Reserving a flight may involve checking seat availability, updating the seat status, and processing payment.

Can you explain the bank transfer example in more detail?

Sure! In a bank transfer transaction, you typically have two main operations:

1. Debit the Source Account: This operation reduces the balance of the account from which money is being transferred.
2. Credit the Destination Account: This operation increases the balance of the account receiving the money.

Both operations must succeed for the transaction to be valid. If one fails (e.g., insufficient funds), the entire transaction is rolled back to maintain data integrity.

What SQL commands are used to implement these transactions?

To implement these transactions, you would typically use the following SQL commands:

1. BEGIN TRANSACTION: Starts the transaction.
2. UPDATE: Used to modify the balances of the accounts. For example:
   • UPDATE accounts SET balance = balance - amount WHERE account_id = source_account;
   • UPDATE accounts SET balance = balance + amount WHERE account_id = destination_account;
3. COMMIT: Finalizes the transaction if all operations succeed.
4. ROLLBACK: Reverts the transaction if any operation fails.