CIS 442讲解、辅导SQL程序语言、database辅导、讲解SQL
- 首页 >> Database 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.
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.