CIS 220, Business Information Systems and Analytics

Simon Business School University of Rochester

CIS 220, Business Information Systems and Analytics

Fall 2020

Database Assignment #2, due Monday, November 3rd, at 11:59pm

NOTE: The databases for this assignment (MBA and IT 100 v4) and the Database II Answers Excel spreadsheet are available on Blackboard and should already be in your CIS 220 folder if you are using Microsoft Access through the virtual machine. ALSO NOTE: You should submit both databases with the queries labeled as specified below AND the Database II Answers spreadsheet, with your answers filled in in the appropriate cell. To submit files on Blackboard, you should open up a browser on the virtual machine, login to Blackboard, and then upload the files (or alternatively email them to yourself, save them locally, and then upload them to Blackboard with a local browser…). It may be easier to store the Database II Answers spreadsheet on the virtual machine and fill it in there.

PART 1 (IT 100 Database)

Design and run (execute) the following queries. You should hand in your database with the queries saved as Q1 – 5 and fill out the Database II Answers spreadsheet with your answers to the questions.

1. Show the average revenue growth by industry. Which industry has the highest average revenue growth?

2. Show the average return on sales by industry (return on sales is a field in the database and in general is profits / revenues). Simply do an unweighted average, i.e, just average all the firms in an industry. Which industry has the highest return on sales?

3. Show the number of companies, total revenues, and average revenue by country. How many countries had a sum of revenue greater than $100,000 million, i.e. $100 billion?

4. Create a new table that classifies each country as part of one of the following regions: Africa, Americas (North and South), Asia Pacific (including Australia), or Europe. While it is fine and perhaps simplest to create this table and populate the records manually, the results of a query are themselves a table, so if you group by country you can copy and paste all the countries into your new table and then just enter the region for each country. After creating this table, use it to show the number of firms in each region. Note that for our purposes, Israel, Kuwait, and Turkey are all part of the Asia Pacific region. How many companies are from Europe?

5. Show the average revenue growth by region (as in 2, do an unweighted average, i.e, just average all the firms in a region). Which region has the highest average revenue growth?

Part 2 (MBA Database)

Design and run (execute) the following queries. You should again hand in your database with the queries saved as Q1 – 7 and fill out the Database II Answers spreadsheet with your answers to the questions.

Note that the data has been developed from actual data (now old), although the number of applications has been reduced by a factor of ten.

1. Show an alphabetical list of school names, rankings, starting salaries, and tuition, for all schools ranked in the top 30 whose tuition is less than $27,000. How many schools meet the criteria?

2. Show a list of the total number of schools per state for all states that have 2 or more schools, sorted in descending order of the number of schools. Note that when using Count with group by, Count shows you the total number of records no matter what field you count, e.g., you could even do Count of the State field if it appeared twice in the query. How many states have four or more schools?

3. Show the School Name, Starting Salary, Tuition, and Starting Salary minus Tuition, for all schools whose Starting Salary exceeds the Tuition by at least $80,000. You will need to create an expression to do the calculation. To do so, use the expression builder icon. Show the schools in decreasing order of the difference between the Starting Salary and the Tuition. What school has the biggest value of Starting Salary minus tuition?

4. For this question you only need to save the query for part d), along with answer the question in part d).

a) Create a list of Station Names and Max, for all climate data records that have a Max below 32 (i.e., it’s a cold month).

Note that the number of records in the query result (number of rows) is different if you show Station Name than if you show Climate Data Station ID. This is because with the former case only Climate Data records that have a corresponding weather station show up. There are Climate Data records for Stations that do not have a Weather Station record.

b) Now use group by to produce a list of Station Names that have at least one month with the Max below 32. Under the Max column, you’ll need to change “Group By” to “Where.” When you use Where, the item will not be shown.

c) Now Group By the Station State instead of the Station Name.

d) Now Look for a Minimum below zero instead of a Max below 32. How many states have at least one month with an average Minimum below zero?

5. Create a list of the names of schools that have more than 400 corresponding application records, in descending order of the number of applications. How many schools have at least 400 applications?

6. For this question you only need to save the query for part c), along with answer the question in part c).

a) Create a list of school names and average GMAT scores of their applicants.

b) Now do the same thing, only restrict the applications to those that were Accepted (Status = Accept). You will again need to use Where.

c) Now create a list of School Names and average GMAT Score of accepted students where the Average GMAT score is greater than 650 for schools that are not ranked in the top 10. How many such schools are there?

7. Create a list of school names, average January high, and month, for schools that have an average January high of greater than 60 or less than 32 in descending order of average high. Note that showing month should be redundant, but I’m asking you to do it to avoid having you make the same mistake I initially did. Also note that average monthly high is what is stored in the Max field of the Climate Data table, i.e., you don’t need to calculate an average (since you already have it). How many school have an average January high temperature greater than 60?