Query:
For every area, find the average monthly salary and an average annual bonus of the doctors in that area. give the new columns aliases and sort the results according to the area in descending order.
Instructions
The following relational schema forms part of a hospital database. The primary keys are underlined and foreign keys are in italics.
Tables:
Doctor (docID, docName, dateHired, monthlySalary, area, chargePerAppt, annualBonus)
Patient (patID, patName, dateOfBirth, nextAppDate, lastAppDate, docID)
Billing (patID, balance, due date, phone, address, city, street, postcode, insurance)
Here is the query to find the average monthly salary and an average annual bonus of the doctors in the area.
Select area, avg(monthlySalary) as AVG_SAL,
Avg(annualbonus) as Yearly_Bonus
From doctor
Group by area
Order by area desc;
Leave a reply
