[dbozarth@pssclabs server ~/dbTables]$ cat fiveQueries.sql

--

-- fiveQueries.sql

--

-- author: David Bozarth

-- created: 5 Nov 2003

--

-- MEANING & USEFULNESS: These queries are for skill development,

-- not for a real-life application; however, some thought was put

-- into retrieving sets of data that relate together, and into

-- moderating functional changes. Query# 4 minimizes deductibles

-- in plans that are not already fully loaded or when the customer

-- relationship has had some duration; Query#3 generates a

-- mailing list for customers for whom active work was done.

-- Query# 5 could be used in routine database maintenance.

--

-----------------------------------------------------------

--

-- Query# 1

--

-- get info on each customer who has Plans of all 4 types

-- (Default, Current, Proposed, Saved Working)

-- using set difference

--

select a.custName, a.enrollDate

from Account a

where not exists

( (select m.typeCode from ModelType m

)

minus

(select p.modelType from Plan p

where a.custNo = p.custNo

)

)

;

--

-- Query# 2

--

-- list each California customer and the customer's number of Plans

--

select a.custName, count(a.custName) as PLANS

from Account a, Contact c, Plan p

where a.custNo = p.custNo

and a.custName = c.contactName

and c.state = 'CA'

group by a.custName

order by PLANS desc

;

--

-- Query# 3

--

-- make a table (mailing list) of all "Professional" customers

-- who have more than 60% enrollment on any Plan

--

create table ProsWithEnrollment as

(select distinct

a.custName, c.addr1, c.addr2, c.city, c.state, c.zip, c.zip4

from Account a, Contact c, Industry i, Plan p

where a.custName = c.contactName

and a.custNo = p.custNo

and a.industry = 2

and p.pctEnrolled >= 60

)

;

--

-- display the mailing list

--

select custName from ProsWithEnrollment;

--

-- display deductibles before update

--

select t.level1 from Tier t where t.level1 > 40

--

-- Query # 4

--

-- ensure that all Proposed Type I In-Network deductibles are no greater than 40%

-- for plans that meet EITHER of the following criteria:

-- (1) the Plan's benefit Tiers are not fully Type 3,

-- OR (2) the Customer was enrolled in the 20th century

--

update Tier t

set level1 = 40

where t.networkOut = 'N'

and t.level1 > 40

and t.parameter = 'deductible'

and t.modelType = 2

and t.custNo IN

( (select a.custNo from Account a

where a.enrollDate < '01-JAN-00'

)

union

(select p.custNo from Plan p, ModelConfig m

where p.modelCfg = m.cfgCode

and (m.perio + m.endo + m.surg < 9)

)

)

;

--

-- display deductibles after update

--

select t.level1 from Tier t where t.level1 > 40

--

-- list all rows in the Plan table that have a modelType of 'current'

--

select custNo, modelType from Plan where modelType = 1;

--

-- Query# 5

--

-- ensure each customer has a Plan designated 'current'

-- if this plan is missing, copy the 'default' plan to 'current'

--

insert into Plan

(custNo, modelType, annMaxIn, annMaxOut, commBrok, commGA,

empContrib, pctEnrolled, dualChoice, orthoMax, modelCfg)

select p.custNo, 1, p.annMaxIn, p.annMaxOut, p.commBrok, p.commGA,

p.empContrib, p.pctEnrolled, p.dualChoice, p.orthoMax, p.modelCfg

from Plan p

where p.modelType = 0

and p.custNo in

(select a.custNo from Account a

where not exists

(select * from Plan p2

where a.custNo = p2.custNo

and p2.modelType = 1

)

)

;

--

-- list all rows in the Plan table that have a modelType of 'current'

--

select custNo, modelType from Plan where modelType = 1;

--

-- create View of each customer's current In-network coinsurance rates

--

create or replace view Current_InNetwork_Rates as

--select a.custName, a.zipIn, c.state, t.level1, t.level2, t.level3

select a.custName, a.zipIn, c.state,

t.level1, t.level2, t.level3

from Account a, Contact c, Tier t

where a.custName = c.contactName

and a.custNo = t.custNo

and t.modelType = 1

and t.parameter = 'coinsurance'

and t.networkOut = 'N'

order by c.state

;

select * from Current_InNetwork_Rates;

-- END OF SCRIPT