Assume you have a statistical database about inpatient data with the following s

Question

Assume you have a statistical database about inpatient data with the following schema. The only legal queries are those involving COUNT, SUM, MIN, MAX and AVG.

Inpatient (PatientID, Fname, Lname, AdmitDate, HomeCounty, TotalCost)

Assume that there is only one inpatient who resides in Lackawanna County. Write a legal SQL query to compute the total cost of that inpatient.

Assume that there are exactly two inpatients who reside in Pike County. Write a legal query that would allow you to deduce the total cost of each of the two inpatients.

Below is a legal query and its returned result:

Query:

SELECT MIN(TotalCost), MAX(TotalCost), SUM(TotalCost), COUNT(*)

FROM Inpatient

WHERE HomeCounty=’Wayne’;

Result:

Min

max

sum

Count

$800.00

$1200.00

$2900.00

3

Based on the returned result, can you infer the total cost for each inpatient from Wayne County? Explain your answer.

Min

max

sum

Count

$800.00

$1200.00

$2900.00

3

InsuranceComp ID Name PhoneNum Patient ID Fname Lname BirthYear Sex Zipcode PhoneNum OfficeVisit PatID DocID Month Day Year Outcome Charge HasPolicyWith Pat ID InsCompId StartDate Took PatID DocID Physician ID Fname Lname OfficeNum Specialty Result LabTest ID Name Charge Description

Explanation / Answer

1.

Assume that there is only one inpatient who resides in Lackawanna County. Write a legal SQL query to compute the total cost of that inpatient.

Select PatientID, SUM(TotalCost) from Inpatient group by PatientID having HomeCounty = 'Lackawanna';

2.

Assume that there are exactly two inpatients who reside in Pike County. Write a legal query that would allow you to deduce the total cost of each of the two inpatients.

Select PatientID, SUM(TotalCost) from Inpatient group by PatientID having HomeCounty = 'Pike';

3.

The total cost for each inpatient from Wayne County

count = 3; So there are 3 inpatients in the HomeCounty Wayne

Suppose Patient 1 has TotalCost which is minimum = $800.00

Suppose Patient 2 has TotalCost which is maximum = $1200.00

So Patient 3 has TotalCost = ($2900.00 - $800 -$1200) = $900.00

Do ask if any doubt. Please upvote.

Related Questions

Navigate

• Browse (All)
• Browse A
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.