Write SQL Statements
Not what you're looking for?
Consider the following relational database schema:
CREATE TABLE ACCOUNTS
(
account_no char(12),
amount money,
CONSTRAINT PK_ACCOUNTS PRIMARY KEY(account_no)
)
CREATE TABLE PENDING_TRANSFERS
(
from_account_no char(12),
to_account_no char(12),
amount money,
CONSTRAINT PK_ PENDING_TRANSFERS PRIMARY KEY(from_account_no,
to_account_no),
CONSTRAINT FK_PENDING_TRANSFERS_FROM_ACCOUNT
FOREIGN KEY(from_account_no) REFERENCES ACCOUNTS(account_no),
CONSTRAINT FK_PENDING_TRANSFERS_TO_ACCOUNT
FOREIGN KEY(to_account_no) REFERENCES ACCOUNTS(account_no),
)
(a) Write three SQL statements: the first statement should add the pending amounts to the appropriate accounts, the second statement should subtract the pending amounts from the appropriate accounts, and the third statement should delete the pending balance transfer information.
(b) Explain the dangers of the sequence of statements in part (a) above, in terms of what would happen in case the database server or SQL interface were to crash in between two of these three statements.
(c) Show how to remove the dangers you described in part (b).
Purchase this Solution
Solution Summary
This solution provides assistance writing SQL statements.
Solution Preview
Please find below the requested queries.
(a)
1) add the pending amounts to the appropriate accounts
UPDATE ACCOUNTS
SET ACCOUNTS.amount = ACCOUNTS.amount + PENDING_TRANSFERS.amount
FROM PENDING_TRANSFERS
WHERE PENDING_TRANSFERS.to_account_no = ACCOUNTS.account_no
2) subtract the pending amounts from the appropriate accounts
UPDATE ...
Purchase this Solution
Free BrainMass Quizzes
Javscript Basics
Quiz on basics of javascript programming language.
C++ Operators
This quiz tests a student's knowledge about C++ operators.
Inserting and deleting in a linked list
This quiz tests your understanding of how to insert and delete elements in a linked list. Understanding of the use of linked lists, and the related performance aspects, is an important fundamental skill of computer science data structures.
C# variables and classes
This quiz contains questions about C# classes and variables.
Word 2010: Tables
Have you never worked with Tables in Word 2010? Maybe it has been a while since you have used a Table in Word and you need to brush up on your skills. Several keywords and popular options are discussed as you go through this quiz.