Business Intelligence  Practice

Goal

This homework is designed to refresh your familiarity with SQL queries, or to get you started with SQL if you have not yet taken a SQL class.

Context

Congratulations! You have just been hired as a business analyst at Small Bank, and this is your first day on the job. The finance director, Penny Lendalot, is very excited that you have accepted, and even more that you have some SQL skills because that will enable you to interrogate directly the database that runs the bank operations.

"Are you the new hire?"
"Yes Madam!" 
"Faaaan-tastic! Welcome to SmallBank! You just graduated from that fancy program, right?"- said she, not really giving you time to answer her question - "Faaaan-tastic! We truly need somebody with your talents. Do you know how to work with databases?"
"Of course. I..."

"Faaan-tastic! Use our new tool" said she, "it is called ADS - Azure Data Studio. It is a marvelous new technology from Microsoft. You will love it!"
"Whew!" thought you, "good thing I took that class at McIntire. I know about ADS and SQL."
"I am familiar with ADS, Ms. Lendalot."
"Oh, are you?" said the CFO "Fiiiiiinally the people at HR are sending me somebody capable... this is EXCELLENT!  Welcome to the Bank!  These are the reports I need."
She hands you a list of Business Intelligence questions.

You were about to say "Faaan-tastic! I will do it."  But decided to stick for a more sober  "No problem, Boss. I am on it."

Shortly thereafter, a colleague reassured you that, despite her annoying use of exclamatory words, Ms. Lendalot is a well-respected leader.  He recommended you to satisfy her request promptly. So, you went to work.

Prep work

Install ADS on your computer or use the copies in the labs.  Watch the videos below on how to get started with ADS and how to get started with notebooks. 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 changes every year. You might get different results when you run the same queries. 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).

- Authentication Type: Windows Authentication

- DB: SmallbankDB

Task

 VS is not required for this homework.  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 "H06 YourLastName YourFirstName.ipynb". Zip it and submit it in MyCommSite as you normally do.

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

• Submit a zipped ADS notebook that includes the questions below and the SQL queries that compute the answers. ADS notebooks have an .ipynb extension.
  
• Each question must be answered by a single query.
• Each query must answer the question asked. Do not provide more information that requested.
• Make sure that all result columns 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 an alphabetic list of our loan officers, inclusive of first and last names (list by last names first, then first names), lo_id, and phone numbers. When they have same last names, alphabetize by fist names
2. Produce a list of all the loans inclusive of all the info on the LOAN table. Order by principal, descending.
3. How many loans above 50K do we have in the database?
4. List the full info on the unexpired 6% loans (see video about GetDate() )
5. What are our three largest loans?  ID, principal, rate and expiration.
6. What is the average loan size at the 8% rate? Format the result
7. How many customers do we have in each city?
8. What is our average premium? Format it so that it prints nicely.
9. What is our total exposure by rate? Order by rate. Note: total exposure = sum of unexpired principals (see video).
10. What are our average rates and average principals by year of maturity? Order by year. Format the principals so that they print nicely.
Note: Q10 is a bit more challenging than the others.  You will need to figure out on your own how to extract year information from a column. Use the listed resources and/or Google if you get stuck.

Getting started with Azure Data Studio (ADS)

Getting started with ADS Notebooks