use the accompanying Fitchwood Insurance Company logical data model to write all
ID: 3583374 • Letter: U
Question
use the accompanying Fitchwood Insurance Company logical data model to write all the necessary queries. You may abbreviate the names as they might be assigned in the physical model 1. For the last 2 years (2014 and 2015), list the total dollar sales and total number of policies sold, by quarter, territory and policy type, but only for policy type code values in the range 1-6. 2. over the last 5 years (2011 thru 015), which 5 agents, whose territories are in zip code 02134 thru 04693, earned the most commissions? 3. Which type of policy has the highest face value amount for policies no longer in force over theExplanation / Answer
1.
SELECT d.[Quarter Number],t.[Territory ID],p.[Policy Type Code]
,sum(s.[Total Sales Amount]) TOTAL_SALES
,sum(s.[Total Policies Number]) TOTAL_POL_NUMBER
FROM Date d,Sales s,Territory t,[Policy Type] p
WHERE d.[Date Skey]=s.[Date Skey]
AND t.[Territory Skey]=s.[Territory Skey]
AND p.[Policy Type Skey]=s.[Policy Type Skey]
AND p.[Policy Type Code] between 1 and 6 -- Policy type filter
AND d.[Year Number] in (2015,2016) -- Year filter
2.
SELECT * FROM (
SELECT a.[Agent Name],sum(s.[Total Commission Earned Amount]) as TOTAL_SUM
FROM Date d,Sales s,Territory t,Agent a
WHERE d.[Date Skey]=s.[Date Skey]
AND t.[Territory Skey]=s.[Territory Skey]
AND a.[Agent Skey]=s.[Agent Skey]
AND t.[Zip Code] between '02134' and '04693'
AND d.[Year Number] between 2011 and 2015
ORDER BY TOTAL_SUM DESC) T --- Inner query gets the agent name with highest to lowest commision earned. Outer query to filter first 5.
WHERE ROWNUM<=5
3.
select * from (SELECT p.[Policy Type Code],max(s.[Total Face Value Amount]) MAX_FACE
FROM Date d,Sales s,[Policy Type] p
WHERE d.[Date Skey]=s.[Date Skey]
AND p.[Policy Type Skey]=s.[Policy Type Skey]
AND s.[In-Force Flag]=0
AND d.[Year Number] =2016 -- 2016 year filter
AND d.[Month Number] between 7 and 12 --- Jul to Dec filter
ORDER BY MAX_FACE DESC) T -- Inner query sort the policy type from highest to lowest face value
WHERE ROWNUM<=1 --- Take the top row
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.