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 SmallBank, 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 UVA 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 maaaarvelous 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?  Finally 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

Use the software in the labs.  This homework will be hard to do on a Mac and require some tinkering on a window computer.   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!

Consult the first section of my very short introduction to SQL.

Connection information:

- Server: f-sg6m-s4.comm.virginia.edu (if on-grounds or using ADV, you do not need a VPN. If off grounds, you do).

- Authentication Type: Windows Authentication    <<< this is different than what is done in COMM3220

- Trust server certificate:  True

- Server group: leave the default

- DB: SmallbankDB

Getting started with Azure Data Studio (ADS)

Task

 VS is not required for this homework.  Create an ADS notebook containing (1) the number and text of each questions listed below and (2) the SQL query that produce the corresponding answer when run.  The filename for the notebook is "H06 SQL.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, and 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.
• All $ figures need to be formatted ($ symbol, comma separating thousands, and two decimals)
• 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 Customers. All info in the table. When they have the 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 $25K 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 6% rate? Format the result
7. How many customers do we have in Charlottesville?
8. What is our average premium?
9. What is our total exposure by rate? Order by rate. Note: total exposure = sum of unexpired principals at the time the query is run (see video).
10. What are our average rates and average principals by year of maturity? Order by year.
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. If too hard, leave it unanswered.

Avoid losing points on SQL!
There is a common error made by beginners and intermediate SQL coders alike.
When you format a column with format, it becomes a string (i.e., text).  If you then rename the formatted column with its original name and later use it to sort the results with 'order by', the sort will not work as expected (it will be an alphabetic sort, not a numeric sort).  In an alphabetic sort $100 is less than $20 (I know... weird, eh? think it through).
Easy fix: when you rename the formatted column with 'as', change a little the column name and use the original name in the 'order by'.
 

Getting started with ADS Notebooks and SQL

As always in this course, the video is not the solution. It will get you close, but you will have to modify it or add to it.