Chapter 10, Problem 6P

a)

Program Plan Intro

Transaction:

A transaction is a logical unit of work.

• A sequence of process that must be completely completed or aborted.
• Intermediate states are not acceptable in the process of transaction.
• To make a transaction use a keyword “BEGIN TRANSACTION;”, insert SQL queries and commit the transaction.

Syntax for “TRANSACTION”:

BEGIN {TRANSACTION|TRAN};

SQL queries;

COMMIT;

“insert” query:

The INSERT command in SQL is employed to add records to an existing table. INSERT will increase the number of rows in a table.

Syntax for INSERT command:

INSERT INTO table_name VALUES (value1, value2…);

Example:

INSERT INTO employee VALUES(101, ‘Smith’,10);

“update” query:

The UPDATE command is employed to update information present in a table.

It is always used with SET keyword either in bulk or individually. UPDATE will not increase the number of rows in a table.

Syntax for UPDATE command:

UPDATE table_name SET column_name1 = value WHERE some_condition;

Example:

UPDATE employee SET name= ‘Presil’ age= 24 WHERE id=115;

b)

Explanation of Solution

Program plan:

The following SQL query is to make a customer payment for a customer “10010” of “$100” in cash using the payment ID “3428”.

• Begin the transaction.
  • Insert the appropriate values into “payments” table using “insert” query.
  • Update the customer details to reduce the customer balance by “100” for the customer “10010” using “update” query...