Members BorrowerlD State Members The following table provides Table structure: Tables Columns LoanRequests Note: Loanpayments Prosper...
Fantastic news! We've Found the answer you've been seeking!
Question:
Transcribed Image Text:
Members BorrowerlD State Members The following table provides Table structure: Tables Columns LoanRequests Note: Loanpayments 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. BorrowerID state ListingNumber BorrowerID AmountRequested CreditGrade Title Installment num ListingNumber Loanrequests ListingNumber BorrowerlD AmountRequested CreditGrade Principal balance Title Principal Paid Interest Paid Data Type Varchar(50) Varchar(50) Number Varchar(50) Number Varchar(50) Varchar(350) Number Number Number 1 Number 00 NUMBER Loanpayments Installment num ListingNumber Principal Balance Principal Paid interest Paid Explanations Borrower ID, primary key Member state Loan requested, primary key Borrower ID, foreign key links to Member table Requested Loan Amount Borrower credit grade The title of loan requests The installment number, part of primary key Loan request ID, part of primary key, Foreign key relates to Loan Request table. Loan principal balance (i.e., how much loan is left) after current installment payment Loan principal amount was paid in current installment payment Loan interests were paid in current installment payment Please provide proper columns names in all queries. It is not required to be able run. But you can use the data to test your queries. · Please post your query code under each question. 1. a. Write the code to create loanpayments Table (10 points) I b. Please insert the following record into this table (5 points) AmountReques Listing Num ber 123123 BorrowerlD wwwwwwww "26A634056994248467D 42E8" ted 1900 CreditGra de www "AA"10 Title a. One approach will use TOP (9 points). b. Another approach uses subquery (11 points). "Paying off my credit cards" c. Borrowers who have CreditGrade of AA want to double their requested amount. Please modify the LoanRequests table to reflect this additional information (5 points) 2. 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) (10 points) 3. Write the code to show UNIQUE loan request information for borrowers from California, Florida, or Georgia. (8 points) 4. 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. 5. Show borrower id, borrower state, borrower registration date, requested amount for all borrowers including borrowers who haven't requested any loans (10 points). 6. 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). (15 points) 7. 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 (17 points). Please use inline query Members BorrowerlD State Members The following table provides Table structure: Tables Columns LoanRequests Note: Loanpayments 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. BorrowerID state ListingNumber BorrowerID AmountRequested CreditGrade Title Installment num ListingNumber Loanrequests ListingNumber BorrowerlD AmountRequested CreditGrade Principal balance Title Principal Paid Interest Paid Data Type Varchar(50) Varchar(50) Number Varchar(50) Number Varchar(50) Varchar(350) Number Number Number 1 Number 00 NUMBER Loanpayments Installment num ListingNumber Principal Balance Principal Paid interest Paid Explanations Borrower ID, primary key Member state Loan requested, primary key Borrower ID, foreign key links to Member table Requested Loan Amount Borrower credit grade The title of loan requests The installment number, part of primary key Loan request ID, part of primary key, Foreign key relates to Loan Request table. Loan principal balance (i.e., how much loan is left) after current installment payment Loan principal amount was paid in current installment payment Loan interests were paid in current installment payment Please provide proper columns names in all queries. It is not required to be able run. But you can use the data to test your queries. · Please post your query code under each question. 1. a. Write the code to create loanpayments Table (10 points) I b. Please insert the following record into this table (5 points) AmountReques Listing Num ber 123123 BorrowerlD wwwwwwww "26A634056994248467D 42E8" ted 1900 CreditGra de www "AA"10 Title a. One approach will use TOP (9 points). b. Another approach uses subquery (11 points). "Paying off my credit cards" c. Borrowers who have CreditGrade of AA want to double their requested amount. Please modify the LoanRequests table to reflect this additional information (5 points) 2. 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) (10 points) 3. Write the code to show UNIQUE loan request information for borrowers from California, Florida, or Georgia. (8 points) 4. 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. 5. Show borrower id, borrower state, borrower registration date, requested amount for all borrowers including borrowers who haven't requested any loans (10 points). 6. 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). (15 points) 7. 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 (17 points). Please use inline query
Expert Answer:
Related Book For
Essentials of Business Analytics
ISBN: 978-1285187273
1st edition
Authors: Jeffrey Camm, James Cochran, Michael Fry, Jeffrey Ohlmann, David Anderson, Dennis Sweeney, Thomas Williams
Posted Date:
Students also viewed these databases questions
-
Googles ease of use and superior search results have propelled the search engine to its num- ber one status, ousting the early dominance of competitors such as WebCrawler and Infos- eek. Even later...
-
Planning is one of the most important management functions in any business. A front office managers first step in planning should involve determine the departments goals. Planning also includes...
-
How many orders of magnitude is 3 . 2 \ times 1 0 - 9 m smaller than 0 . 0 0 0 0 4 m ?
-
Contrast milestones with phase-gates.
-
In Exercises use the rules of differentiation to find the derivative of the function. x/7 = (x)8
-
Separation of duties refers to separating all of these functions except which of the following? a. Authorizing transactions b. Keeping accounting records C. Hiring personnel d. Maintaining custody of...
-
A warehouse storage building company must determine how many storage sheds of each sizelarge or smallto build in its new 8,000-square-foot facility to maximize rental income. Each large shed is 150...
-
Comparative financial statements for Weller Corporation for the fiscal year ending December 31 appear below. The company did not issue any new common or preferred shares during the year. A total of...
-
A 1.33-cm-diameter metal bar with a 3-cm gage length (l 0 ) is subjected to a tensile test. The following measurements are made in the plastic region: Determine the strain-hardening coefficient for...
-
Imagine that you have determined to create your own business project. Determine whether the company will be a service or merchandising company
-
A disk (Idisk = 12MR2) of mass 200kg and radius 2m is rotating at rad/s. A small object of mass 40 kg then collides with and sticks to the disk at a location halfway between the center and the edge....
-
Greenville has provided the following information from its General Fund Revenues and Appropriations/Expenditure/Encumbrances subsidiary ledgers for the fiscal year ended. Assume the beginning fund...
-
A player kicks a soccer ball from the ground with a velocity 17 m/s at 35 from the horizontal. The ball strikes a vertical wall a distance 22 m from where the ball leaves the ground. Ignore the air...
-
A n aircraft of weight 3 4 7 0 0 0 N is catapulted off the deck of an aircraft carrier. if the aircraft starts at rest, and reaches a velocity of 7 2 m / s 2 in 2 . 5 seconds, how much force is...
-
A model electric train requires 14 volts to operate. When it is connected to a 118 volt circuit, a transformer is needed. If the primary coil of the transformer has 435 turns, how many turns must be...
-
Explain the Jacquinot advantage in FTIR spectroscopy.
-
In what ways does a well-designed enterprise search software vary from popular search engines (e.g., Bing, DuckDuckGo, and Google)?
-
Richardson Ski Racing (RSR) sells equipment needed for downhill ski racing. One of RSRs products is fencing used on downhill courses. The fence product comes in 150-foot rolls and sells for $215 per...
-
Translate the following monetary payoffs into utilities for a decision maker whose utility function is described by an exponential function with r = 250: - $200, $100, $0, $100, $200, $300, $400,...
-
Land Shark is investigating the sensitivity of its model to the assumptions it made on the random variables. In particular, Land Shark is interested in modeling how it generates its competitors bid...
-
What forms of business organization are permitted under Rule 505-Form of Practice and Firm Name?
-
There currently are thirteen Rules of Conduct. Listed below are circumstances pertaining to some of these rules: 1. A member shall exercise due professional care in the performance of an engagement....
-
a. What aspects of a company's financial statements are covered in the first three reporting standards? b. What is the objective of the fourth standard of reporting?
Study smarter with the SolutionInn App