Congratulations! You are a BA for SmallBank. Now what?
Graded pledged individual HW#1
Goal
Learning 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..."
"Faaaantastic! 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 them 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 well-respected manager. He recommended you to satisfy her request promptly. So, you went to work.
Prep work
Wait for a message in Teams confirming that your account is ready for you to use. We will let you know as soon as we have it. Until your account is activated, you cannot use the DB.
Install Azure Data Studio (ADS) on your computer, or use the ADS copy installed on all computers in the McIntire labs.
Watch getting started with ADS.
Note: Microsoft continuously updates this software. The interface you might see may be slightly different from the one in the
video. No worries! When in doubt, accept the defaults. Also, the data in the DB shown in the video is updated every year. You
might get different results than the video. Again, no worries!
Connection information:
- Server: f-sg6m-s4.comm.virginia.edu (if on-grounds or using ADV, you will not need a VPN. In all other cases, you do).
- DB: SmallbankDB
- UserID: use the 'SQL login' option and your UVA userID
- Initial password: p@ssword123
The video will show you how to change the initial password. Do it asap.
Task
Create an ADS notebook containing (1) the number and text of the questions listed below and (2) the SQL queries that produce the corresponding answers when run. The filename for the notebook is "MP1 YourLastName YourFirstName.ipynb". Please submit it as an assignment in the Box folder with the link that was sent to you by the TA.
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.
TIPS FOR SUCCESS: Take advantage of the resources listed in the syllabus: my brief notes on SQL, the WWW school site, Google, your favorite GenAI. If you get stuck, ask me via Teams. You will succeed.
Criteria
SQL must produce the correct answer and no additional information. Read carefully what is being asked. If you are asked for a date, return a date, for an number, return a number etc.
One query per answer.
All result columns must have an easy-to-understand name.
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.
Do not show duplicate records.
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 above $75K do we have in the DB?
4. Show id, rate, and principal for our ten smalles loan. Smallest on top. Format the principal.
5. What is our total exposure for non-expired loans? (exposure = sum of borrowed principals). Format the principal.
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. Format the $ figures.
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. Format the principals so that they print nicely.
Note: Q12 is more challenging than the others. You will need to figure out on your own how to extract year information from a date.