Question

• Customer = {customerID, firstName, lastName, income, birthDate}
• Account = {accNumber, type, balance, branchNumberFK-Branch}
• Owns = {customerIDFK-Customer, accNumberFK-Account}
• Transaction = {transNumber, accNumberFK-Account}
• Employee = {ssn, firstName, lastName, salary, branchNumber FK-I Branch}
• Branch = {branchNumber, branchName, managerSSNFK-Employee, budget} „FK-Account, amount}
Notes.
• The customerID attribute (Customer) is a unique number that represents a customer, it is not a cus- tomer's SSN
• The accNumber attribute (Account) represents the account number
• The balance (Account) attribute represents the total amount in an account
• The type (Account) attribute represents the type an account: checking, saving, or business
• The transNumber attribute (Transactions) represents a transaction number, combined with account number it uniquely identify a transaction
• The branchNumber attribute (Branch) uniquely identifies a branch
• The managerSSN attribute (Branch) represents the SSN of the branch manager
PART 1:-
Write multiple SQL queries to achieve the following goal: remove the customer (or the customers) who has the lowest total balance (of all his/her accounts) among all customers. Besides, you should also clear their association to any accounts. Finally, if an account is no longer associate with any customers, it should be removed as well. In this question, you can assume that all the foreign key constraints specified in the database have ON DELETE CASCADE referential actions.
PART 2:-
Return branch name, account type, account number, transaction number, and amount for transactions of accounts where their average transaction amount is greater than three times the (overall) transaction amount of account of that type. For example, if the average transaction amount of all business accounts is $2,000then return transactions from business accounts where the average transaction amount for that account is greater than $6,000. Order by branch name, then account type, account number and finally transaction number. Note that all transactions of qualifying accounts should be returned even if they are less than the average amount of the account type.
PART 3:-
Return the branch numbers, branch names and their manager's first name for all the branches that Jane Doe owns an account, but John Doe does not, except that you are not allowed to use EXCEPT in your query.

Answer 1

hello

Explanation:

hcbc hi chxchyvkvci