The company’s insurance policy is up for renewal and the insurance company’s underwriters need some updated information from us before they will issue a new policy.
Leverage your SQL skills to extract and analyze data from various tables inn the Maven Movies database to answer the underwriters’ questions. Each questions can be answered by querying just one table. Part of your job as an analyst is figuring out which table to use.
- We will need a list of all staff members, including their first and last names, email address, and the store identification number where they work
- We will need separate counts of inventory items held at each to stores
- We will need a count of active customers for each of your stores. Separately, please.
- In order to assess the liability of a data breach, we will need to provide a count of all customer email addresses stored in the database
- We are interested in how diverse your film offering is as a means of understanding how likely you are to keep customers engaged in the future. Please provide a count of the unique film titles you have in your inventory at each store and then provide a count of the unique categories of films you provide.
- We would like to understand the replacement cost of your films. Please provide the replacement cost for the film that is least expensive to replace, the most expensive to replace, and the average of all films you carry.
- We are interested in having you put payment monitoring systems and maximum payment processing restrictions in place in order to minimize the future risk of fraud by your staff. Please provide the average payment you process, as well as the maximum payment you have processed.
- We would like to better understand what your customer base looks like. Please provide a list of all customer identification values, with a count of rentals they have made all-time, with your highest volume customers at the top of the list.