-- · Determine the state that has the most number of occurrences where Compared to National has the value “Number of Cases Too Small”.

select * from (
   select hl.state, count(*) as occurrences from HOSP_LIST hl where hl.PRVDR_ID in
         ( select distinct hnc.PRVDR_ID from HOSP_NATNL_COMPR hnc
            where hnc.COMP_TO_NATNL = 'Number of Cases Too Small'
         )
         group by hl.state
         order by occurrences desc
   )
   where rownum <= 1 ;

   -- · For the state with the most number of occurrences where the Compared to National has the value “Number of Cases Too Small”,
   -- list the Measure ID and Measure Name.

select hml.* from HOSP_MEASURE_LIST hml
inner join
(
   select distinct hnc.MEASURE_ID from HOSP_NATNL_COMPR hnc
   where hnc.PRVDR_ID in
         (
            select hl.PRVDR_ID from HOSP_LIST hl
               where hl.state = (
               select state from (
                     select hl2.state, count(*) as occurrences from HOSP_LIST hl2 where hl2.PRVDR_ID in
                        ( select distinct hnc2.PRVDR_ID from HOSP_NATNL_COMPR hnc2
                           where hnc2.COMP_TO_NATNL = 'Number of Cases Too Small'
                        )
                        group by hl2.state
                        order by occurrences desc
                     )
                     where rownum <= 1
            )
         )
   and hnc.COMP_TO_NATNL = 'Number of Cases Too Small'
   order by hnc.MEASURE_ID
) s on hml.MEASURE_ID = s.MEASURE_ID

Order ID Order Date Customer ID Customer Name Product ID Pr...