联系方式

  • QQ:99515681
  • 邮箱:99515681@qq.com
  • 工作时间:8:00-21:00
  • 微信:codinghelp

您当前位置:首页 >> Database作业Database作业

日期:2020-04-04 10:22

CIS 442 Spring B - HW #2

Due Monday, April 6 @ 5:30 PM

Please upload your completed assignment to Blackboard. For the work done in MySQL Workbench,

please copy and paste your work into a document and upload a .pdf of your work. For handwritten

work, you may take a picture and upload a .jpeg. The easier it is for us to find and follow all of your

work, the easier/quicker we can grade your assignments. Thanks!

Questions 1 – 2. Below is a portion of a report generated by the Eastgate Animal Clinic, showing the

data they store about office visits for animals that they treat.

1. Create in MySQL the vets DB that you designed in HW 1. Please paste the SQL code into a

document and upload as a .pdf or .doc file.

2. Create a SQL script that inserts the records from the report shown above into the database

tables that you created in Question 1. Then, write a SELECT statement whose results replicate

the data in the report. Please paste the SQL code for your SELECT statement into your

homework submission and upload as a .pdf or .doc file. Also include a copy (screenshot, etc.) of

your results grid.

Questions 3 – 9. The create_insurance.sql script creates a database which contains the 2 tables

described below. The policyholders table contains 150 policy holders for a medium sized insurance

company. The claims table file contains all claims filed by these 150 policy holders (some policy holders

may not have filed any claims) between 1/1/1990 and 12/31/2017. Data for each claim is included in

the claims table and policy holder demographic information is included in the policyholders table.

The policyholders table also contains the annual premium for each policy holder and the calendar year

in which they began carrying insurance. You may assume that each policy holder has been continually

covered over the time period since their enrollment year and that they have paid a constant annual

premium (so someone who started a policy in 2004 would have been insured for 14 years and would

have paid a total premium of 14 * [their annual premium] over that time).

Claims Field Description

Claim_Number Unique ID for insurance claim

Policy_Holder_Id Unique ID for policy holder

Claim_Type Indicator (Accident or Theft)

Claim_Amount $$ amount of claim

Claim_Date_Filed Date claim was filed

Claim_Date_Settled Date claim was settled

Policyholders Field Description

PolicyHolderId Unique ID for policy holder

YearEnrolled Year policy holder enrolled in insurance

AnnualPremium $$ amount policy holder pays yearly for coverage

Gender Indicator (Male or Female)

Age Age of policy holder

HomeDemo Demographic of policy holder’s area of residence

For questions 3 & 4, you do not need to include the SQL code. Only the answer to the question is

necessary.

3. DaysToSettle is a kpi that measures the number of days between the claim being filed and the

claim being settled. What was the mean DaysToSettle for Accident claims vs. Theft claims?

Accident claims = ______________________________

Theft claims = _________________________________

4. How many policy holders in the policyholders table did not file any claims?

For questions 5 & 6, please cut and paste the SQL statement that creates the results grid shown.

5. A frequency table (i.e., counts by category) of ACCIDENT claims by policyholder gender age

group (under 30, 30 – 40, 40 – 50, etc.).

6. A frequency table of number of policyholders by number of claims. For example, 29

policyholders had exactly 1 claim in the time period covered by the data. 5 policyholders had

exactly 9 claims.

For questions 7 - 9, please cut and paste (or include a screenshot) the results grid that is described by

the question.

Loss Ratio is a kpi that measures the total $$ amount of claims paid over a period of time divided by the

total $$ amount of premium collected over the same time period.

7. Calculate the Loss Ratio for each policy holder.

(Total $$ Claims Paid for PolicyHolder) / (Total $$ Premium Collected for PolicyHolder)

Create a report showing the policyholders that had the 10 largest Loss Ratios. Include

PolicyHolderID, Gender, Age, HomeDemo, Total Premium, Total Claims and Loss Ratio, sorted by

Loss Ratio. Round Loss Ratio to 4 decimal places.

8. Calculate the Loss Ratio for male policyholders and for female policyholders. Create a report

(two lines) showing the Total Premium, Total Claims and Loss Ratio for each gender. Round Loss

Ratio to 4 decimal places.

9. Similar to question 8, calculate the Loss Ratio for policyholders who pay an annual premium less

than or equal to $1150 and the loss ratio for policyholders whose annual premium is greater

than $1150. Create a report (two lines) showing the Total Premium, Total Claims and Loss Ratio

for each category. Round Loss Ratio to 4 decimal places.


版权所有:编程辅导网 2021 All Rights Reserved 联系方式:QQ:99515681 微信:codinghelp 电子信箱:99515681@qq.com
免责声明:本站部分内容从网络整理而来,只供参考!如有版权问题可联系本站删除。 站长地图

python代写
微信客服:codinghelp