MP3: SmallBank (B)

The competency curse

Goal

Learning SQL joins.

Context

"That was faaaaan-tastic!"
Ms. Lendalot was clearly pleased with your SQL work. You felt that you were scoring points with your boss. It was a good day.
"So much useful information! Up to the minute! And produced so quickly! Thank you!"
"...Although... - continued Ms. Lendalot - seeing your data prompted even more questions about our operations..."

And there it was: the competency curse.  If you are good at something, people will ask you to do it more, and expectations about your performance will rise.
"...so I have a few more questions for you. They require to combine information from different sources, so they might be a little more challenging that the previous set, but I am sure that somebody with your skills can easily answer them."

It was your turn to speak. "I am on it, Boss. I think I..."

"Faaaaantastic! Send them as soon as you have them."

Task

Server: f-sg6m-s4.comm.virginia.edu (if outside grounds you might need a VPN).
DB: SmallBankDB
Pswd: the one given to you or your new one if you changed it
Link to the ERD of the Smallbank DB

Create a notebook that contains alternating text and code items with the number and text of the questions listed below, and the SQL query that produces the corresponding answer when run.

Name the notebook is "MP3 YourLastName.ipynb" and submit it in myFiles.

Tip for success

Appreciate that this assignment is more about writing queries than finding specific answers.  So, do not expect step-by-step instructions about how to write joins. Figuring that out *is* the assignment. Some struggle is normal, especially if you are a SQL beginner.  If I gave you precise step-by-step instructions on what commands to use, you would not learn much.  Take advantage of my brief note on SQL, the WWW School site, Google, and your favorite GenAI. You will succeed!

Note: when you use 'format' to format a number, you turn it into text. If you later need to sort the formatted number, SQL will use the text sorting order (i.e. alphabetical order).  In alphabetical order,  "$ 10" comes before "$ 2000". Make sure that that is what you want.  Else, sort on the original (e.g., non-formatted) column.

Criteria

Read carefully what is being asked.

• Your SQL must produce the correct answer and no unneeded extra data. If you are asked for a date, return a date (not an hour:minute, etc.), for a number, return a number, etc. You cannot say "But the answer is there if you look for it."
• One query per question
• If a question does not ask for specific columns, provide them all, except duplicates
• If asked to show data about a person or an item, always show their ID. For cities, use name and state
• Do not show duplicated records
• All result columns must have an easy-to-understand name
• All money figures are formatted with ‘$’, commas and no decimals.  If most figures in a column are less than $100 provide decimals.  Integers have commas and no decimals. Percentages have 2 decimals. Interest rates have three. For example, for percentages we will accept 3.23 or 4.34%.  For interest rates, 0.373, 2.430 or 3.882%.
• Specific instructions may override these bullets.

Business questions

Q1: List our customers who have a loan, where they live, their loan officers, and their phones. Alphabetical by city and then by customer last and first name. Column names specify who is who.
Q2: Who are  Carl Dyer's (id = 8) clients? Alphabetized list by last and first name.
Q3: We measure loan officer performance in terms of the amount they manage. Who manages the least money that is not yet past due as of the time the query is run?
Q4: List all customers alphabetically, including insurance information for those who have it. Print out 0 (zero) or '-' rather than 'Null' if they do not.
Q5: Create a report showing which loan officers has offered an average rate less than 13%. Order by rate ascending.
Q6: Who are our top quartile borrowers?  Show customers and total borrowed. Order by total principal borrowed, largest on top.
Q7: Who borrowed the most money in Richmond? (do not show the $ amount).
Q8: How many clients does Carl Dyer have? Assume you know his lo_id.
Q9: What is our insurance exposure by city? (Exposure = sum of coverages)
Q10: How many of our Charlottesville customers are not insured?
Q11: List all the loan officers and how many insured customers each has. Alphabetic by officer.
Q12: List all the loan officers who have customers in Charlottesville, order by total amount managed in that town, largest on top.