Comm 3220: DM4DM

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! And produced so quickly! Thank you!"
"...Although... - continued Ms. Lendalot - seeing that 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.
"...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 an ADS notebook containing the number and text of the questions listed below and the SQL queries that produce the corresponding answers when run.

The filename for the notebook is "MP3 YourLastname YourFirstName.ipynb"

Please submit it as an assignment in the Box folder with the link that was sent to you by the TA..

Criteria

• Read carefully what is being asked. SQL must produce the correct answer and no additional data
• Answer each question with a single query. Simpler queries are better.
  
• All columns must have easy-to-understand names
• Format all $ numbers.
• If a question does not ask for specific columns, provide them all, except duplicate columns.
• When you are asked to show people, show first name, last name, and id, unless otherwise specified.
• When ordering alphabetically, do so by last name and within that by first name.
• Do not show duplicate records.

If some queries are too challenging, skip them for partial credit.

NOTE: This assignment is less about finding specific answers than it is about writing queries.  So, do not expect step-by-step instructions about how to write the queries. 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 the resources listed in the syllabus: my brief notes on SQL, the WWW school site, Google, your favorite GenAI (but be careful about possible bad advice!). If you get stuck, ask me via Teams. You will succeed. 

Business questions

Q1: List our customers, where they live, their loan officers, and their phones. Alphabetical by city and then by customer. Column names specify who is who.

Q2: Who are  Mary Park's (id = 6) clients? Alphabetized list.

Q3: We measure loan officer performance in terms of the amount they manage. Who manages the least money that is not yet due? (hint: getdate())
Q4: List all customers alphabetically, including insurance information for those who have it.
Q5: Create a report showing which loan officer has given 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 Mary Park have?
Q9: What is our insurance exposure by city? Format the numbers.  (Exposure = sum of coverages)
Q10: How many of our Charlottesville customers are insured?

Q11: List all the Loan Officers who have customers who are not insured and their customers. Alphabetic by officer.

Q12: List all the loan officers who have customers in Charlottesville, order by total amount managed.

Back home