- Csc 134 Summer 2022 Assignment 5 Total 100 Points I Do Not Debug For Students Solving Assignment Problems Independentl 1 (74.95 KiB) Viewed 26 times
- Csc 134 Summer 2022 Assignment 5 Total 100 Points I Do Not Debug For Students Solving Assignment Problems Independentl 2 (59.65 KiB) Viewed 26 times
- Csc 134 Summer 2022 Assignment 5 Total 100 Points I Do Not Debug For Students Solving Assignment Problems Independentl 3 (66.64 KiB) Viewed 26 times
Branch (name: VARCHAR(25), address: VARCHAR(50)) Customer (ssn: CHAR(2), fname: VARCHAR(25), Iname: VARCHAR (25), phone CHAR(10), address: VARCHAR (50), fv_branch: VARCHAR(25)) Account (acc no: CHAR(9), balance: DECIMAL (15,2), atype: VARCHAR(10), fee: DECIMAL (7,2)) Owns( ssn: CHAR(2), acc_no: CHAR(2), own_date: DATE) foreign key (ssn) references customer(ssn), foreign key (acc_no) references account (acc_no) Note: DATE is in the format of yyyy-mm-dd'. Loan( loan no: CHAR(2), amount: DECIMAL(15,2), ltype: VARCHAR(10), interest rate: DECIMAL (7,4)) Payment (loan_no: CHAR(2), payment no: CHAR(4). amount: DECIMAL (10,2), method: VARCHAR(10), status: VARCHAR(10), due_date: DATE) foreign key(loan_no) references loan(loan_no) Borrows(ssn: CHAR(2), loan no: CHAR(2)) foreign key (ssn) references customer(ssn), foreign key (loan_no) references loan (loan_no) Figure 2 Schema with type 338 me nine Address CUSTOMER M owns N ACCOUNT for Inne phone N M Own dur Burrows baline) Type BRANCH favorite N Low No BigmatN Amoung Addres Name LOAN (Am Method type interest rate Figure 3 ER Diagram DucDate
Section 2 Populate the database. Please check sections 3 for details. Section 3 Use SQL to specify the following queries. When you populate the database, insert data such that at least one row will be display as the result of running each query. List each the loan payment (loan_no, payment_no, amount, method) that was paid on time and the payment was more than $15, order the result by loan_no in ascending order. (the status of the payment is "On time") 2) List all the customers (ssn, first name, last name) who have at least one account with balance more than $10000. Don't list a customer twice if he/she has more than one account satisfy the condition. 3) List all customers (ssn, first name, last name) who own more than 2 accounts. If an account is shared, you should also count it in. List all the customers (ssn, first name, last name, phone number) who own account '000000001'. 5)List each the loan payment (payment number, due date) of "John Smith" that has been paid by check. List each the customer (ssn, first name, last name) who has at least one loan. List a customer only once. 7) Retrieve all the loans that "John Smith" has. If a loan is shared by him and other customers, you should count it in. Please list John's ssn and number of loans he has. Retrieve the total account balance of a customer if a customer has more than 2 accounts. If an account is shared, you should also count it in. List ssn, number of accounts, and total balance. List each customer with his/her favorite branch (customer SSN, Customer Last Name, Branch Name, Branch address). If a customer does not have a favorite branch, then the branch name and branch address should be NULL. 201 For each customer who has a favorite branch, list the customer SSN, customer last name, and the name of his/her favorite branch. Retrieve the highest fee among all accounts. 121 Retrieve the loan that has the highest interest rate among all loans. List the loan number, type, and the interest rate. If there is a tie of highest interest rate, list all loans with the highest interest rate