Instruction 4.10
From the database created in the comprehensive problem (attached spreadsheet), perform queries based on the tables and query grid shown in Table 4-21. Your instructor may specify whether you are to use Design view or SQL view.
Table 4-21
Selected Query Screen for Chapter Comprehensive Problem: See image
You must complete Problem 4-10 questions A to J. Your queries should be named and saved in alignment with the parts of the problem, i.e. Query 4-10-a, Query 4-10-b, Query 4-10-c, etc.
Questions
a)Which borrowers use Advent Appraisers? List BorrowerID, LastName, and AppraiserName.
b) List all of the property appraisers with the letter “d” in their name, sorted by name. List AppriaiserID and AppraiserName.
c) List all of the lenders who have participated in a transaction that used AppraiserID 8. Provide LenderName, BorrowerID, and ReqMortAmount.
d) List the lenders that lent more than $100,000. Provide LenderName and AppraiserName. Only list each combination of LenderName and AppraiserName once.
e) Queries that use aggregate functions
f) What is the average amount borrowed from each lender? Provide LenderID, LenderName, and AvgMortAmt. Sort by AvgMortAmt in descending order. Consider using ROUND() to present at two decimal places.
g) How many appraisals did each appraiser perform? Provide AppraiserName and CountOfAppraisals.
h) Advanced queries that may require nesting or reference to earlier queries.
i) Which borrower requested the largest mortgage? Provide BorrowerID, LastName, and ReqMortAmount.
j) Which borrower requested the smallest mortgage? Provide BorrowerID, LastName, and ReqMortAmount.
