What is the average time (days) between the first and second dose allocated in each jurisdiction?

here the instructors from the prof

COVID-19 Vaccine Allocation Dataset

Original Raw Data

Check how the raw data was provided in 3 separate CSV files imported to the database.

select * from hc848.COVID19_VACCINE_JANSSEN;

select * from hc848.COVID19_VACCINE_MODERNA;

select * from hc848.COVID19_VACCINE_PFIZER;

Data Model (Normalized)

Then, check how the normalized database was designed (above) and how data looks like now (below):

select * from hc848.COVID19_MAKER;

select * from hc848.COVID19_DOSE;

select * from hc848.COVID19_JURISDICTION;

select * from hc848.COVID19_ALLOCATION;

Questions

In SQL A, you can only use the normalized data model and you have to resolve each question with one SELECT statement only:

• hc848.COVID19_MAKER
• hc848.COVID19_DOSE
• hc848.COVID19_JURISDICTION
• hc848.COVID19_ALLOCATION

In SQL B (optional, extra-credit), you can only use the raw data tables, but you mioght need to use multiple SELECT statements:

• hc848.COVID19_VACCINE_JANSSEN
• hc848.COVID19_VACCINE_MODERNA

hc848.COVID19_VACCINE_PFIZER

here I every table.

  
 
 
 
 
 

Transcribed Image Text:

COVID19 MAKER MAKER_ID MAKER_NAME integer varchar2(50) COVID19 ALLOCATION MAKER ID JURISDICTION_ID ALLOCATION DATE DOSE ID DOSE_ALLOCATIONS PK integer PK FK integer PK FK date PK integer PK FK integer COVID19 JURISDICTION integer JURISDICTION_ID JURISDICTION_NAME varchar2(50) PK COVID19 DOSE integer DOSE_ID DOSE DESCRIPTION varchar2(50) PK COVID19 MAKER MAKER_ID MAKER_NAME integer varchar2(50) COVID19 ALLOCATION MAKER ID JURISDICTION_ID ALLOCATION DATE DOSE ID DOSE_ALLOCATIONS PK integer PK FK integer PK FK date PK integer PK FK integer COVID19 JURISDICTION integer JURISDICTION_ID JURISDICTION_NAME varchar2(50) PK COVID19 DOSE integer DOSE_ID DOSE DESCRIPTION varchar2(50) PK

Answer rating: 100% (QA)

To calculate the average time in days between the first and second dose allocations in each jurisdiction you can use the normalized data model and SQL ... View the full answer