Each month, our company bills the employers for their insurance coverage. Create
ID: 3691307 • Letter: E
Question
Each month, our company bills the employers for their insurance coverage. Create a View (named: yourLastNameView15) that will produce a listing that can be used to generate monthly bills. Include in the view the Employer Name, the employer contact name (first and last), the plan ID and description, the count of employees in that plan, the plan cost and the billing amount (count * plan cost). Order the view by Employer Name and PlanID. For Example – the first row in the results set (for Bakertime Mobile’s PlanID 001) would display as follows:
KeefeVIEW15 Bakrtme Mobile Martin Jackson Employer EmployerContact PlanID plnDescription NumberEnrolled PlanCost InvoiceAmt 72.0 $72.50 001 Single Basic Me Bakertime Mobile Martin Jackson001 Single Basic Medical1Explanation / Answer
Solution: See the queries below step by step:
1. Step-1: Select EmployerID, PlanID, Count(MemberID) As COUNT from members group by EmployerID, PlanID.
2. Step-2, 3, 4: Select EmployerName, CONCAT(EmployerContactFirstName,EmployerContactLastName) CONTACT, PlanID, PlanDescription, Count(MemberID) As COUNT, PlanCost, (COUNT*PlanCost) As InvoiceAmt from employer, plans, members where employer.EmployerID=members.EmployerID and members.PlanID=plans.PlanID group by EmployerID, PlanID, PlanCost order by EmployerID, PlanID.
3. View: CREATE VIEW yourLastNameView15 [EMPLOYER, CONTACT, PLANID, PLNDESCRIPTION, ENROLLED, COST, AMOUNT] AS Select EmployerName, CONCAT(EmployerContactFirstName,EmployerContactLastName) CONTACT, PlanID, PlanDescription, Count(MemberID) As COUNT, PlanCost, (COUNT*PlanCost) As InvoiceAmt from employer, plans, members where employer.EmployerID=members.EmployerID and members.PlanID=plans.PlanID group by EmployerID, PlanID, PlanCost order by EmployerID, PlanID.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.