Create the following queries: a. List the names of employees who have arranged f

Question

Create the following queries:

a. List the names of employees who have arranged for monthly premiums to be deducted by
their employer Baby Back Ribs. Name this query Q1

b. How many employees have deductions for a retirement plan? Name this query Q2 –

c. What is the average premium for a policy? Name this query Q3 –

d. Produce the total payments received by each business on behalf of their employees.
Name this query Q4 –

e. Customer Beth Nelsen called (Client Number 100919). She was informed that her
monthly deductions were not enough. Create a query to prompt the user for a Client
Number(10919) and produce the salary deduction along with the total premium deductions.
Include a calculated field named ‘Excess’ to subtract the total premium deductions from
the employee’s salary deduction. Name this query Q5 –

COMPANY Y COMPANYID COMPANY PARISHNAME Field: Table: Sort: Show: Criteria: 0r: CLIENT CLIENTNUM LASTNAME FIRSTNAME DATESTARTED COMPANY ID SalaryDeduction DEDUCTIONS ID CLIENTNU POLICITYPE POLICY POLICITYPE POLICYDESCRIPTIO PREMIUM

Explanation / Answer

Use these queries in Pivot model and generate queries.

No snapshot is shared as no data is available for the pivot tables.

-- Query 1 (Assuming all deduction are monthly)
SELECT FIRSTNAME,LASTNAME
FROM CLIENT C
INNER JOIN COMPANY CO
ON CO.COMPANYID = C.COMPANYID
WHERE CO.COMPANY = 'Baby Back Ribs'

-- Query 2
SELECT COUNT(CLIENTNUM) EmployeeCount
FROM CLIENT C
INNER JOIN DEDUCTIONS D
ON D.CLIENTNUM = C.CLIENTNUM
WHERE D.POLICYTYPE = 'Retirement'

-- Query 3
SELECT POLICYTYPE, AVG(PREMIUM)
FROM POLICY
GROUP BY POLICYTYPE

-- Query 4
SELECT CO.COMPANY, SUM(C.SalaryDeduction)
FROM CLIENT C
INNER JOIN COMPANY CO
ON CO.COMPANYID = C.COMPANYID
GROUP BY CO.COMPANY

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.