Homework 6

Business Intelligence at SmallBank

This homework is designed to give you some initial familiarity with SQL queries.

Using Visual studio, connect to the SMALLBANKDB database on the server f-sg6m-s4.comm.virginia.edu.  This vLab may help. You need to be logged in the School network to do this, either from the building or using a VPN. Use the Handout linked from the class schedule to learn more about SQL. The readings page lists a more extensive free online book.

Create queries that answer the business questions asked by your boss and listed below. Save all the queries (the SQL, NOT the results) in a single notepad file (not a word docx) and submit it. In other words, simply copy and paste all the queries in one notepad file. Name the file homework.sql. This is the only file that you need to submit. No zipping required. DO NOT add anything else to the file. No name, date, query number, comments, etc, UNLESS you use the SQL convention that

- -  two consecutive dashes make the text that follows a comment, and color it (often) in green

The last two queries are significantly more challenging that the first six. Consider skipping them if too hard. Each incorrect query gets a .25 deduction. The database might contain a little more tables than the ones shown in class. Ignore them. They are used in later exercises.

Business Intelligence Questions

  • Who are our customers in Austin, TX? Produce a list of our customers’ first names, last names and customer IDs, sorted by last name.
  • What rates did we offer on our loans? Produce a list of the loan IDs and their rates for the loans under $50,000,000
  • What is the first and last name of the of the customer whose last name begins by “Ze”?
  • How many loans over $100,000,000 do we have? Produce a count, not a list.
  • What is the average rate for the loans higher than $100,000,000?
  • What is our total exposure (total principal) per level of interest rate (hint: see handout on grouping query results)
  • Produce a list containing all customer last names and first names, their loan officers last name, and the office phone for that loan officer. Sort by Customer last name. Hint: some customers have multiple loan officers - list them all. Some customers have multiple loans with the same officer - list him/her only once per customer.
  • Who is managing the most money? Produce a list of loan officers’ last names and the total money they manage. Hint: sort it by the total they manage, largest first,

The readings page contains instructions on connecting to the DB from home.