COMP 266 Lecture Notes - Lecture 2: Aggregate Function, Data Definition Language
1 Jul 2018
School
Department
Course
Professor
Question: Consider the following relations:
Patients(pid, na...
Bookmarked
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. SQL
A. Write the correct view (CREATE VIEW) that will satisfy the president’s request.
B. State which of the following queries and updates would be allowed in this view. If a particular
query or update would be allowed, show what the corresponding query or update on the base
relations would look like, and give its result when applied to the database.
Q2. SELECT D, C
FROM NURSE_SUMMARY
WHERE TOTAL_S > 100000;
Q3. SELECT D, AVERAGE_S
FROM NURSE_SUMMARY
WHERE C > (SELECT C FROM NURSE_SUMMARY WHERE D=4);
Q4. UPDATE NURSE_SUMMARY
SET D=3
WHERE D=4;
Q5. DELETE FROM NURSE_SUMMARY
WHERE C > 4;
Expert Answer
ď‚·
astha29 answered this
Was this answer helpful?
find more resources at oneclass.com
find more resources at oneclass.com
Document Summary
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. Sql: write the correct view (create view) that will satisfy the president"s request, state which of the following queries and updates would be allowed in this view. If a particular query or update would be allowed, show what the corresponding query or update on the base relations would look like, and give its result when applied to the database. Where c > (select c from nurse_summary where d=4); Select c. cid as d, count(nurse_charge_id) c, sum(n. salary) as total_s, Assuming d is care center id and c is count of nurses. Query will execute successfully below are the results: Update can only be done if columns to be updated are not part of group by clause or any aggregate function.
