Question

Provider	Department	Hospitalization	MaxAmount	MinAmount
Pro1	Dep1	12	12	10
Pro1	Dep1	10	12	10
Pro1	Dep1	11	12	10
Pro2	Dep2	22	32	19
Pro2	Dep2	32	32	19
Pro2	Dep2	19	32	19
Pro3	Dep3	34	34	10
Pro3	Dep3	10	34	10
Pro3	Dep3	17	34	10
Pro4	Dep4	23	23	23

Using Windows Functions

a) Write a query that will produce the table above as an outcome

b) Why are all the values equal in the last tuple of the table

c) Write a second query that will add a column to the table that will also provide the max amount of hospitalization for all providers.

Answer 1

Considering this table as Table2, parent table of Table2 is Table1 and child table of Table2 is Table3.

(A)

SELECT Provider, Department, Hospitalization,MAX(Hospitalization) Over(PARTITION BY Department ORDER BY provider) AS MaxAmount, MIN(Hospitalization) Over(PARTITION BY Department ORDER BY provider) AS MinAmount

FROM Table1

Where Provider = 'Pro1' AND Department= 'Dep1' OR Provider = 'Pro2' AND Department= 'Dep2' OR Provider = 'Pro3' AND Department= 'Dep3' OR Provider = 'Pro4' AND Department= 'Dep4';

(B) Why are all the values equal in the last tuple of the table

Because for last tuple, their is only one entry in the parent table where

Provider	Department	Hospitalization
Pro4	Dep4	23

so, for single entry minimum andd maximum value will alway same .

ie. MIN(Hospitalization) = 23 = MAX(Hospitalization) = Hospitalization value of Dep4 and Pro4

(C) Write a second query that will add a column to the table that will also provide the max amount of hospitalization for all providers.

ALTER TABLE Table2 ADD Maximum_Amount NUMBER;

SELECT Provider, Department, Hospitalization,MAX(Hospitalization) Over(PARTITION BY Department ORDER BY provider) AS MaxAmount, MIN(Hospitalization) Over(PARTITION BY Department ORDER BY provider) AS MinAmount, MAX(Hospitalization) AS Maximum_Amount

FROM Table1

Where Provider = 'Pro1' AND Department= 'Dep1' OR Provider = 'Pro2' AND Department= 'Dep2' OR Provider = 'Pro3' AND Department= 'Dep3' OR Provider = 'Pro4' AND Department= 'Dep4';