MP1: SmallBank (A)

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

Goal

By completing this miniproject (MP), you will learn basic SQL commands, experience VS Code, and connect to a cloud-based instance of MS SQL Server.

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..."
- "Brilliant! I have a question for you. When I ask business questions to the other analysts they take forever to replay and then they send me answers in emails, or they put them in a presentation, or -worse- they print them out... which means that the data I see is always stale. I want answers based on live data. Is there a way to do it?"
- "Yes, Ms. Lendalot there is a tool called...."  you wanted to mention the Jupyter notebooks, but Ms. Lendalot was not interested in the details of the technology.
- "Eeeextra fantastic! Can you find the answers to these questions and send them to me in a couple of hours?"
She gives you a piece of paper with some scribbled lines.
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

Look for a message in Teams with your initial DBMS password.  When you find it, read the instructions to either install Visual Studio Code on your computer, OR use the copies installed on all computers in the McIntire labs. If you prefer having somebody else installing and managing the software, just go to the labs. It is easier.

Connection information:

• Server: f-sg6m-s4.comm.virginia.edu
• If off-grounds, you need a VPN
• DB: SmallbankDB
• UserID: use the 'SQL login' option and your UVA userID (e.g., "hsre4z")
• Initial password for the DBMS was sent to you in MS Teams. Change it asap (step 6).

Task

This is an *individual*, graded, and pledged assignment. Create a Jupiter-style notebook (step 4) that contains alternating text and code items.  Each text cell must contain the number and text of one question listed below, and the SQL cell immediately below it must contain the SQL queries that produce the corresponding answers when run.
When done, name the notebook "MP1 YourLastName.ipynb" and submit it in "myFiles" (top menu here). Deadline: beginning of next Thursday 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, and your favorite GenAI. You will succeed!

Criteria

Read carefully what is being asked. *Points will be taken out for failing to accurately 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.

Ms. Lendalot's business questions

1. Produce an alphabetized list of our customers who live in Dallas. Sort by last name, then first name
2. Show all 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. Tip: check the results!
5. What is our total exposure for non-expired loans as of today?
    (exposure = sum of borrowed principals; today=date in which we run the query).
6. What is the average loan size at the 5% rate?
7. Show customer ID and address for N. Grover. Do not assume you know the full first name.
8. How many customers do we have in each city?
9. What is the average loan size by rate? Order by rate, descending. Tip: check the results!
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.