Need help on this Homework:

H10 Assignment

INLS 523_001

Instructions

H10 is an exercising in applying the SQL skills and knowledge we have learned in INLS523 in a practical manner. For this assignment we will use the HUSH VDI environment and HUSH database.

Data Dictionary

The HUSH database is based off the PCORnet (Patient-Centered Outcomes Research Network) common data model (CDM) and below is the link to the current data dictionary document. All HUSH tables are PCORnet tables with HUSH_ prefixing them. Be sure to only use tables that begin with HUSH_

https://pcornet.org/wp-content/uploads/2020/12/PCORnet-Common-Data-Model-v60-2020_10_221.pdf

Starter Code

SELECT * FROM HUSH_LAB_RESULT_CM WHERE LAB_LOINC = '85476-0'

The HUSH_LAB_RESULT_CM table contains clinical laboratory results for patients.

1)Using the PCORnet data dictionary, what is the column LAB_LOINC, and what data does it contain?

2)What laboratory test dose LOINC 85476-0 represent?

3)How many total test results are in the database for the given LOINC?

4)How many unique patients have this test result?

Modify the starter code to find only 'POSITIVE' test results

5)What column did you add to the WHERE clause to find only positive lab results?

6)How many unique patients have a positive test result?

7)When was the last result date?

Modify the code again to find the diagnoses for the given encounters from the patients with positive tests from the steps above.

8)What are the top 3 diagnosis codes (field dx) and count for each of the top 3 dx codes?

9)What does each of the top 3 dx codes represent? (hint, these are called ICD10 codes)

Modify the code BELOW as needed to complete the table below

SELECT * FROM

(

SELECT sex, hispanic, race FROM hush_demographic

)

PIVOT (

count(*)

FOR sex IN ('M' AS MALE, 'F' AS FEMALE));

10)The correct table will only have 7 rows and 4 columns

HISPANIC

RACE

MALE

FEMALE