Question

I need SQL queries for these statements

17. Display the contracts and payment methods associated with each referral Patient Last Name, Physician Last Name, Referral Start Date, Contract Start Date, Payment Method Sort Order: Payment Method - ascending Physician Last Name – ascending Patient Last Name – ascending Referral Start Date – ascending Contract Start Date – ascending

18. Display the number of contracts whose payment method is Insurance Number of contracts (This is a single value)

19. Display the number of contracts whose payment method is Insurance, broken out by Insurance Company Insurance Company Name, number of contracts Sort order: Insurance company name

20. List the Employees who are Nurses Employee First Name, followed by a space, followed by Employee Middle Initial, followed by a space, followed by Employee Last Name (call this whole field “Nurses”)

21. Display the average hourly wage for all employees who are aides. Average hourly wage (single value)

22. Display the average hourly wage for all hourly employees broken out by level. Skill level, average wage Sort order: Skill Level

23. Display the total salary for all salaried employees. Total salaries (single value)

Answer 1

NOTE: Here Table names are not given so I've provided the assumptions before each query

------17----
--Assumption: Table names->Contracts,Payments
SELECT Payment_Method,Patient_Last_Name,Physician_Last_Name,Referral_Start_Date,Contract_Start_Date
FROM Contracts,Payments
ORDER BY Payment_Method ASC,Physician_Last_Name ASC,Patient_Last_Name ASC,Referral_Start_Date ASC,Contract_Start_Date ASC

------18----
--Assumption: Table name is Payments
SELECT COUNT(*)
FROM Payments
WHERE Payment_Method = 'Insurance'

-----19----
--Assumption: Table names are-> Company,Payments
SELECT Company_Name,COUNT(*)
FROM Company,Payments
WHERE Payment_Method = 'Insurance'

-----20------
--Assumption: table name Employee having attribute Employee_type value 'Nurse'
SELECT concat(First_Name,' ',Middle_Initial,' ',Last_Name) as "Nurses"
FROM Employee
WHERE Employee_type = 'Nurse'

------21------------
--Assumption: table name Employee having attribute Employee_type value 'aides' and hourly_wage
SELECT AVG(hourly_wage)
FROM Employee
WHERE Employee_type = 'aides'

------22-------------
--Assumption: table Employee having hourly_wage as attribute and Employee_type attribute with value 'hourly_wage'
SELECT Skill_level,AVG(hourly_wage)
FROM Employee
WHERE Employee_type = 'hourly_wage'
ORDER BY Skill_level

-------23------------
--Assumption: table Employee having Employee_type attribute with value 'salaried' and attribute salary
SELECT SUM(salary)
FROM Employee
WHERE Employee_type = 'salaried'