Student Student ID Unique identifier for each student First Name Student's first name Last Name Student's last name Address Student's address Email Student's email address GPA Student's grade point average Funding Request Request ID Unique identifier for each funding request Student ID Foreign key students Application Date Date when the funding request was submitted Total Amount Needed Total amount of financial aid requested Deadline Date when the funding request needs to be paid Original Repayment Amount Amount to be repaid by the students Educational Use Purpose for which the funds will be used Reliability Rating Likelihood of student repaying funds Donor Donor ID Unique identifier for each donor First Name Donor's first name Last Name Donor's last name Address Donor's address Email Donor's email address Phone Donor's phone number Preferred Contact Method Donor's preferred way to be contacted Contribution Contribution ID Unique identifier for each contribution Funding Request ID Foreign key funding request Donor ID Foreign key donor Contribution Date Date when the contribution was made Amount Amount donated Repayment Repayment ID Unique identifier for each repayment Donor ID Foreign key donor Amount Amount repaid Repayment Date Date when the repayments made Funding Request ID Foreign key funding request 1 Write a SQL query that, given a student borrower, returns all the fund details and the remaining due on all their funds loans In the query, use the primary key for one of the borrowers (rather than a name) such that it will execute correctly Note that it should reflect the most up to date information possible for each fund loan (as adjustments and payments should be considered) 2 Write a SQL query that returns information for each fund loan and each donor for that fund, the fraction of how much they contributed For example, for a single Fund 1 I might have Lender A that contributed 80 and Lender B that contributed 20 , or something like that 3 Write a SQL query that, given a fund id, will return the details of that fund and all of the changes to the due date, purpose, and payback amount as appropriate 4 Some donors treat this more like micro loans and would like to have a report wherein they can see if they are making money or not Write a SQL query where, given a donor id, you calculate the total amount of money they have lent out, the total they expect to receive back, and the total collected to date If they gave a $10k loan at 50 , then they only paid out $5k On the same loan, if the amount due was $12k and it had been entirely paid off, then the lender only received $6k (due to their 50 contribution)

The Answer is in the image, click to view ...

Answered step by step

Verified Expert Solution

Link Copied!

Question

1 Approved Answer

Posted on Sep 10, 2024

Student: Student ID: Unique identifier for each student First Name: Student's first name Last Name: Student's last name Address: Student's address Email: Student's email address

Student:
- Student ID: Unique identifier for each student
- First Name: Student's first name
- Last Name: Student's last name
- Address: Student's address
- Email: Student's email address
- GPA: Student's grade point average
Funding Request:
- Request ID: Unique identifier for each funding request
- Student ID: Foreign key - students
- Application Date: Date when the funding request was submitted
- Total Amount Needed: Total amount of financial aid requested
- Deadline: Date when the funding request needs to be paid
- Original Repayment Amount: Amount to be repaid by the students
- Educational Use: Purpose for which the funds will be used
- Reliability Rating: Likelihood of student repaying funds
Donor:
- Donor ID: Unique identifier for each donor
- First Name: Donor's first name
- Last Name: Donor's last name
- Address: Donor's address
- Email: Donor's email address
- Phone: Donor's phone number
- Preferred Contact Method: Donor's preferred way to be contacted
Contribution:
- Contribution ID: Unique identifier for each contribution
- Funding Request ID: Foreign key - funding request
- Donor ID: Foreign key - donor
- Contribution Date: Date when the contribution was made
- Amount: Amount donated
Repayment:
- Repayment ID: Unique identifier for each repayment
- Donor ID: Foreign key - donor
- Amount: Amount repaid
- Repayment Date: Date when the repayments made
- Funding Request ID: Foreign key - funding request

1. Write a SQL query that, given a student borrower, returns all the fund details and the remaining due on all their funds/loans. In the query, use the primary key for one of the borrowers (rather than a name) such that it will execute correctly. Note that it should reflect the most up-to-date information possible for each fund/loan (as adjustments and payments should be considered).

2. Write a SQL query that returns information for each fund/loan and each donor for that fund, the fraction of how much they contributed. For example, for a single Fund 1 I might have Lender A that contributed 80% and Lender B that contributed 20%, or something like that

3. Write a SQL query that, given a fund id, will return the details of that fund and all of the changes to the due date, purpose, and payback amount as appropriate.

4. Some donors treat this more like micro-loans and would like to have a report wherein they can see if they are making money or not. Write a SQL query where, given a donor id, you calculate the total amount of money they have lent out, the total they expect to receive back, and the total collected to date. If they gave a $10k loan at 50%, then they only paid out $5k. On the same loan, if the amount due was $12k and it had been entirely paid off, then the lender only received $6k (due to their 50% contribution).