Q1_20251109130224.webp

2. (2 points) From the complete ERD in question 1, write SQL statements to create
the table(s) corresponding to the entity(ies) added to the diagram. Insert 5 appropriate
data rows into each newly created table.
Notice: The balance of an account has up to 15 digits including 2 digits after the decimal
point and has a default value of 0. Suppose that the currency is USD.
3. (2 points) Write a stored procedure to withdraw money from an account. In order
for a withdrawal to be successful, the amount to be withdrawn must be a positive
number, the account ID must be valid and the balance of the account must not be lower
than the amount to be withdrawn. In case any criterion above is violated, rollback and/or
raise an error.
4. (2 points) Create a table - NotificationEmails (Id, Recipient, Subject, Content). Add
a trigger to the table containing information about accounts and create a new message
whenever this accounts table is updated. The following data is required to be filled for
each email:
•Recipient AccountId
Subject - "Update on account: {AccountId}"
• Content - "On {date} your balance was changed from {old balance} to {new
balance}."
For example:
Id Recipient Subject
1
1
Content
Update on account: 1
On 05-11-2023 14:09 your balance was changed
from 113.12 to 103.02.
Zoom
- FUO
...
5. (2 points) Create a view that displays the first names and last names of all clients
who have more than $300 in total of all their accounts.
+100%
End
Close