Question
6. The chief of general surgical procedures needs a listing of patient names (last and first) who have received a service treatment General Panel; however,
6. The chief of general surgical procedures needs a listing of patient names (last and first) who have received a service treatment General Panel; however, you do not know the service_id for this service. Your query should have a subquery within a subquery using the patient, treatment, and service tables. Sort the output by patient last name, and then by patient first name. 7. The director of human resources for the hospital is concerned about employee retention. One of the hospital directors has suggested that the hospital has difficulty retaining employees except for registered nurses (hospital_title = R.N.). Produce a listing of staff members (last and first names) and the date they were hired to work at the hospital, but only list staff members who were hired before any nurse with a hospital title of R.N. Hint: Use the MIN aggregate function to determine the date hired for the oldest registered nurse based on the date_hired column. The result table must also list the employees hospital title and date hired. Format the output such that each column has a meaningful name and is an appropriate width such that each row fits on a single line of output. 8. The head of the hospitals accounting department is concerned about the prescribed charge for services versus the actual charges for services in the surgery category of services (service_cat_id = SUR). Produce a listing of surgery services listed in the treatment table by service_id where the prescribed service_charge differs from the actual_charge recorded in the treatment table. The result table should also include the service_charge and actual-charge. Use a subquery approach to produce a listing of service_id values from the service table that belong to the SUR category of services. Display the result table with the service and actual charges formatted to include the dollar sign ($). 9. Alter the above query to only list services where the difference between the service_charge and actual_charge is greater than the average difference between these two charges for services in the SUR category of services. The result table should only list the service_id and service_description column from the service table. Hint: Compute the average difference between these two charges for SUR services in a subquery. 10. The head of hospital auditing wants to know which patients were charged the most for a treatment. The result table should list three columns from the treatment table appropriately labeled: pat_id, actual_charge, and service _id .Only list rows where the patient was charged the most for a treatment. This may result in some rows where patients were charged identical amounts to other patients. Sort the rows by service_id. Use a correlated subquery. Format the output appropriately.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started