Assume we work for Big Money Bank, Inc, which owns several banks itself, and tha
ID: 3828749 • Letter: A
Question
Assume we work for Big Money Bank, Inc, which owns several banks itself, and that we have the following database: create table bank (bank_tax_id int primary key, bank_name varchar 200)); create table bank_branch (bank_branch_id int primary key, bank_tax_id int foreign key references bank (bank_tax_id), bank_city varchar(200), bank_state varchar(2), bank_zip_code numeric(5) bank_address varchar 200))); create table department (dept_id varchar(10), dept_name varchar(100), bank_branch_id int foreign key references bank_branch (bank_branch_id). create table employee (emp_id int primary key, emp_name varchar(200), emp_state varchar(2), emp_city varchar (200), emp_address varchar (200), emp_position varchar(100), date_of_hire datetime, dept_id varchar(10) foreign key references department (dept_id)); create table account (cust_ssn numeric (9) primary key, cust_name varchar (200), cust_state varchar(2), emp_city varchar(200), cust_address varchar(200), bank_branch_id int foreign key references bank_branch (bank_branch_id)); create table account (acct_num numeric(16) primary key, acct_type varchar(4), account_balance float, cust_ssn numeric(9) foreign key references customer(cust_ssn). create table transfers (txn_id numeric (16) primary key, acct_num numeric (16) references account (acct_num). opposite_acct_num numeric(16) references account(acct_num), transfer_amt float, transfer_date datetime); Our boss tells us that we need the following reports, so provide the SQL to do so. We need a list of customers with accounts with balances > 1,000,000,00 so that we can send them letters thanking them for their superb patronage. Include bank, branch, and customer info in the query. We need to find a list of the 10 customers with the highest balances so that we can send them champagne. Include bank, branch, and customer info in the query. We need, as a federal money laundering compliance measure, to find accounts that have transferred more than 80% of their account balance in the last 30 days (suspected laundering). Remember that sysdate will give us the current date, and sysdate-10 will give us a date from 10 days ago. You can simply obtain the account number, or you can get bank, branch, and customer info for extra credit. Acct_num gives the transferring account, and opposite acct_num gives the account being transferred to. Get the number of each type of account in the database for statistics purposes. We need to send letters of thanks to employees who have been with any of our banks more than 12 years, so please find all of them. Include bank, branch, and employee info in the query.Explanation / Answer
5. Select bank_name,bank_branch_id,cust_ssn,cust_name from bank,bank_branch,customer,account where account.account_balance > 100000.00 and account.cust_ssn=customer.cust_ssn and
bank.bank_tax_id=bank_branch.bank_tax_id and bank_branch.bank_branch_id=customer.bank_branch_id;