Consider the following relations: Patients(pid, name, address, telephone, care_c
ID: 3800555 • Letter: C
Question
Consider the following relations:
Patients(pid, name, address, telephone, care_centre_id)
Care_centres(cid, name, location, nurse_charge_id)
Treatments(tid, patient_id, physician_id, treatment_name, date)
Nurses(nid, name, care_centre_id, certificate_type, telephone, salary)
Physicians(phid, name, pager_number, specialization, salary).
For some strategic decisions, the president of the hospital needs summary data about the care centres. For each care centre, s/he needs to know the number of nurses holding an RN certificate, as well as their total and average salaries. Write the correct view that will satisfy the president’s request. SQL
Explanation / Answer
select count(nid),sum(salary),avg(salary)
from nurses join care_centres
group by care_centres.cid
having
nurse.certificate_type=RN;