Run the given ipps.sql file to build the IPPS database from Database Assignment 02. For this second part of the IPPS assignment you are asked to use the provided database schema. Use the LOAD DATA commands (embedded in the SQL file) to populate your tables from the following CSV files that you will have to download:
● DRGs.csv
● HRRs.csv
● Providers.csv
● ChargesAndPayments.csv
Your task in this assignment is to answer the following queries using SQL. Write your answers to all queries updating the ipps.sql file. T
How you should interpret the following fields in ChargesAndPayments:
● totalDischarges refers to “the number of discharges billed by the provider for inpatient hospital services,”
● avgCoveredCharges refers to “the provider's average charge for services covered by Medicare for all discharges in the DRG,”
● avgTotalPayments refers to “the average charge for services by the provider for the correspondent DRG,”
● avgMedicarePayments refers to “the average of Medicare payments to the provider for the DRG.”
a) List all diagnostic names in alphabetical order.
b) List the names and correspondent states (including Washington D.C.) of all of the providers in alphabetical order (state first, provider name next, no repetition).
c) List the total number of providers.
d) List the total number of providers per state (including Washington D.C.) in alphabetical order (also printing out the state).
e) List the total number of hospital referral regions (HRR).
f) List the total number of HRRs per state (also printing out the state).
g) List all of the providers in the state of Pennsylvania in alphabetical order.
h) List the top 10 providers (with their correspondent state) that charged (as described in avgTotalPayments) the most for the diagnose with code 308. Output should display the provider, their state, and the average charged amount in descending order.
i) List the average charges (as described in avgTotalPayments) of all providers per state for the clinical condition with code 308. Output should display the state and the average charged amount per state in descending order (of the charged amount) using only two decimals.
j) Which provider and clinical condition pair had the highest difference between the amount charged (as described in avgTotalPayments) and the amount covered by Medicare (as described in avgMedicarePayments)?
