MP1: SmallBank (A)

Congratulations! You are a Business Analyst for SmallBank. Now what?

Goal

After completing this miniproject (MP), you will have learned basic SQL.

Context

- "Are you the new hire?"
- "Yes Madam!" You recognize Ms. Lendalot, the finance director at SmallBank.
- "Faaantastic! Welcome to SmallBank! You just graduated from that fancy Comm School, right?" said she, not really giving you time to answer her question.
"Faaantastic! We truly need somebody with your talents. Do you know how to work with databases?"
- "Of course. I took a course in..."
- "Extra faaantastic! I have a few quick questions for you. If I asked the IT people downstairs they will take a week to answer... can you have the answers on my desk in a couple of hours?"
You wanted to reply "Faaantastic! I will do it." but decided to stick to a more sober: "I am on it, Ms. Lendalot."

Shortly thereafter, a colleague reassured you that, despite her annoying use of exclamatory words, Ms. Lendalot is a competent, well-respected manager. So, you get to work.

Prep work

Wait for a message in Teams confirming that your database account is ready for you to use.  When it is ready, install Azure Data Studio (ADS) on your Windows or MacOS computer, or use the copies installed on all computers in the McIntire labs.
Fall 2025: Microsoft has announced that it will discontinue ADS in favor of another product called CODE.  CODE is needlessly complex to use for beginners, and lacks some cool features that we will use. For the Fall '25 we will stick with ADS. I hope that Microsoft will change its mind on this.

Next, watch getting started with ADS.
Microsoft continuously updates this software. The interface you might see in the video may be slightly different. No worries! When in doubt, accept the defaults. Also, the data shown in the video is updated every year. You might see different data in the videos. Again, no worries!

Connection information:

• Server: f-sg6m-s4.comm.virginia.edu
  (if on-grounds, you do not need a VPN. Otherwise, you do).
• DB: SmallbankDB
• UserID: use the 'SQL login' option and your UVA userID
• Initial password for the SmallBankDB: sent to you in MS Teams.
  The video will show you how to change the initial password. Do it asap.

Task

This is a graded and pledged assignment. Watch getting started with ADS notebooks and create a notebook that contains alternating text and code items with (1) numbers and texts of the questions listed below, and (2) the SQL queries that produce the corresponding answers when run.
Name the notebook is "MP1 YourLastName.ipynb" and submit it in the "my files". Deadline: beginning of class.

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 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 my brief note on SQL, the WWW School site, Google, and your favorite GenAI. You will succeed!

Criteria

Read carefully what is being asked. Points will be taken out for failing to fully satisfy these requirements.

• Your SQL must produce the correct answer and no unneeded extra data. If you are asked for a date, return a date, 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.
• Specific instructions may override these bullets.

Business questions

1. Produce a list of our customers who live in Dallas. All columns. Sort by last name, then first name
2. Produce a list of the loans above $200K, print out all the info we have on them from that table. Order by principal, descending
3. How many loans below $75K do we have in the DB?
4. Show id, rate, and principal for our ten smallest loans. Smallest on top.
5. What is our total exposure for non-expired loans as of today? (exposure = sum of borrowed principals).
6. What is the average loan size at the 5% rate?
7. Show customer ID and address for N. Grover. There might be more than one!
8. How many customers do we have in each city?
9. What is the average loan size by rate? Order by rate, descending.
10. Do we have a customer who has the initials NM? (print out all the info we have in the customer table on them)
11. How many customers last-named "Echevarria" do we have?
12. What are our average rates and average principals by year of maturity? Order by year.

Note: Q12 is a little more challenging than the others.  You will need to figure out on your own how to extract year information from a date.