Prosper is a peer-to-peer lending platform. It allows borrowers to borrow loans from a pool of potential online lenders. Borrowers (i.e., Members) posted their loan Requests with a title and description. Borrowers specify how much they will borrow and the interest rate they will pay. If loan requests are fully funded (i.e., reached the requested amount) and become loans, borrowers will pay for the loans regularly (LoanPayment entity).
The complete RDM is provided above. An Access Database with data is also available for downloading from Blackboard.
The following table provides Table structure:
Tables
Columns
Data Type
Explanations
Members
BorrowerID
Varchar(50)
Borrower ID, primary key
state
Varchar(50)
Member state
LoanRequests
ListingNumber
Number
Loan requested, primary key
BorrowerID
Varchar(50)
Borrower ID, foreign key links to Member table
AmountRequested
Number
Requested Loan Amount
CreditGrade
Varchar(50)
Borrower credit grade
Title
Varchar(350)
The title of loan requests
Loanpayments
Installment_num
Number
The installment number, part of primary key
ListingNumber
Number
Loan request ID, part of primary key,
Foreign key relates to Loan Request table.
Principal_balance
Number
Loan principal balance (i.e., how much loan is left) after current installment payment
Principal_Paid
Number
Loan principal amount was paid in current installment payment
InterestPaid
NUMBER
Loan interests were paid in current installment payment
1. Write the code to create loanpayments Table
2. Please insert the following record into this table
ListingNumber
BorrowerID
AmountRequested
CreditGrade
Title
123123
"26A634056994248467D42E8"
1900
"AA"10
"Paying off my credit cards"
3. Borrowers who have CreditGrade of AA want to double their requested amount. Please modify the LoanRequests table to reflect this additional information
4. Show loan requests that are created by borrowers from CA and that are created for Debts, Home Improvement, or credit card (hint: the purpose of loans are in the column of title in Loanrequests table)
5. Write the code to show UNIQUE loan request information for borrowers from California, Florida, or Georgia. (8 points)
6. Show borrower id, borrower state, borrowing amount for loan requests with the largest loan requested amount.(20 points). Please use two approaches to answer this question.
A. One approach will use TOP .
B. Another approach uses subquery .
7. Show borrower id, borrower state, borrower registration date, requested amount for all borrowers including borrowers who havenât requested any loans
8. Show listing number for all loans that have paid more than 15 installments, rank them by the total number of installments so far in descending (please use having).
9 .Each borrower has credit grade when he/she requests loans. Within each credit grade, please show loan request information (listing number, requested amount) for loan requests that have the lowest loan requested amount at that credit grade. Please use inline query