Tasks.sql

-- Îòîáðàòü ñïèñîê ïàöèåíòîâ, ó êîòîðûõ ñåãîäíÿ äåíü ðîæäåíèÿ. Âûâåñòè ÔÈÎ ïàöèåíòà
select c.fullname
  from CLIENTS c
  where extract (day from c.bdate) = extract (day from sysdate)
    and extract (month from c.bdate) = extract (month from sysdate);

-- Âû÷èñëèòü êîëè÷åñòâî ïàöèåíòîâ, ó êîòîðûõ íå óêàçàíà äàòà ðîæäåíèÿ
select count(*)
  from CLIENTS c
  where c.bdate is null;
  
-- Âû÷èñëèòü êîëè÷åñòâî ïàöèåíòîâ, ó êîòîðûõ íå óêàçàíà äàòà ðîæäåíèÿ, íå èñïîëüçóÿ êàêèå-ëèáî óñëîâíûå êîíñòðóêöèè (âêëþ÷àÿ, íî íå îãðàíè÷èâàÿñü WHERE, HAVING, CASE, IIF)
select count(c.pcode) - count(c.bdate)
  from CLIENTS c;
  
-- Âû÷èñëèòü âòîðóþ ìàêñèìàëüíóþ äàòó ðîæäåíèÿ â òàáëèöå ïàöèåíòîâ
select max(c.bdate)
  from CLIENTS c
  where c.bdate != (select max(bdate) from CLIENTS);
  
-- Âû÷èñëèòü âòîðóþ ìàêñèìàëüíóþ äàòó ðîæäåíèÿ â òàáëèöå ïàöèåíòîâ, íå èñïîëüçóÿ àãðåãàòíûõ ôóíêöèé (ïî óñëîâèþ íå ìîæåò áûòü ïàöèåíòîâ ñ îäèíàêîâîé äàòîé ðîæäåíèÿ)
with bdates as (select bdate
                      ,row_number() over (order by bdate desc) as rn
                  from CLIENTS
                  where bdate is not null)
select bdate
  from bdates
  where rn = 2;

-- Âû÷èñëèòü âòîðóþ ìàêñèìàëüíóþ äàòó ðîæäåíèÿ â òàáëèöå ïàöèåíòîâ, íå èñïîëüçóÿ àãðåãàòíûõ ôóíêöèé (ïî óñëîâèþ ìîãóò áûòü ïàöèåíòû ñ îäèíàêîâîé äàòîé ðîæäåíèÿ)
with bdates as (select distinct bdate
                      ,row_number() over (order by bdate desc) as rn
                  from CLIENTS
                  where bdate is not null)
select bdate
  from bdates
  where rn = 2;
  
-- Âûâåñòè ñòðîêè òàáëèöû ïàöèåíòîâ, äëÿ êîòîðûõ íåò íè îäíîãî ëå÷åíèÿ
select *
  from CLIENTS c
  where c.pcode not in (select distinct pcode from TREAT);
  
-- Âûâåñòè ñòðîêè òàáëèöû ïàöèåíòîâ (CLIENTS.*), äëÿ êîòîðûõ åñòü õîòÿ áû îäíî ëå÷åíèå
select *
  from CLIENTS c
  where c.pcode in (select distinct pcode from TREAT);
  
-- Âûâåñòè ñòðîêè òàáëèöû ëå÷åíèé (TREAT.*), äëÿ êîòîðûõ ñóììà, íà÷èñëåííàÿ íà ïàöèåíòà, (TREAT.AMOUNTCL) ÿâëÿåòñÿ ìàêñèìàëüíîé â ðàìêàõ ýòîãî ïàöèåíòà
select *
  from TREAT t
  where t.amountcl = (select max(t1.amountcl)
                        from TREAT t1
                        where t1.pcode = t.pcode);
                        
-- Âûâåñòè ñïèñîê ïîëíûõ îäíîôàìèëüöåâ (ñîâïàäåíèå ÔÈÎ è äàòû ðîæäåíèÿ): ÔÈÎ, äàòà ðîæäåíèÿ, êîëè÷åñòâî.
select c.*
      ,count(*) over (partition by c.fullname, c.bdate)
  from CLIENTS c
  where 1 < (select count(*)
               from CLIENTS c1
               where c1.fullname = c.fullname
                 and c1.bdate = c.bdate);

-- Äëÿ âñåõ âðà÷åé êëèíèêè âûâåñòè ñëåäóþùèé ñïèñîê: ÔÈÎ äîêòîðà, êîëè÷åñòâî ëå÷åíèé çà ñåãîäíÿ, êîëè÷åñòâî ëå÷åíèé çà ñåãîäíÿ, ñòîèìîñòü êîòîðûõ áîëüøå 10000.
select d.dname
      ,(select count(*)
          from TREAT t
          where t.dcode = d.dcode
            and t.treatdate = sysdate)
      ,(select count(*)
          from TREAT t
          where t.dcode = d.dcode
            and t.treatdate = sysdate
            and t.amountcl + t.amountjp > 10000) 
  from DOCTOR d;

-- Äëÿ âñåõ âðà÷åé êëèíèêè âû÷èñëèòü àáñîëþòíîå èçìåíåíèå ñðåäíåé ñòîèìîñòè íàëè÷íîãî ïðè¸ìà (TREAT.AMOUNTCL) â 2019 ãîäó ïî îòíîøåíèþ ê 2018 ãîäó. Âûâåñòè ÔÈÎ äîêòîðà è óêàçàííûé ïîêàçàòåëü
select d.dname
      ,nvl(avg(case when extract (year from t.treatdate) = 2019 then t.amountcl end)
           -
           avg(case when extract (year from t.treatdate) = 2018 then t.amountcl end)
           , 0) as change
  from DOCTOR d
    -- Ïîëó÷èòü âñå ïðèåìû ïî ýòîìó âðà÷ó çà íóæíûå ãîäà
    left join TREAT t
    on t.dcode = d.dcode
    and extract (year from t.treatdate) in (2018, 2019)
  group by d.dname;

-- Âûâåñòè ñïèñîê ïðèêðåïëåíèé, äëÿ êîòîðûõ ñòîèìîñòü ãîäîâîãî ïðèêðåïëåíèÿ (CLHISTNUM.AMOUNTRUB) ìåíüøå ïîëîâèíû ñòîèìîñòè ôàêòè÷åñêè îêàçàííûõ ïî ýòîìó ïðèêðåïëåíèþ óñëóã (TREAT.AMOUNTJP). Óêàçàòü ÔÈÎ ïàöèåíòà, íîìåð äîãîâîðà (JPAGREEMENT.AGNUM), íîìåð ïîëèñà (CLHISTNUM.NSP), ïåðèîä ïðèêðåïëåíèÿ (CLHISTNUM.BDATE,FDATE)
select cl.fullname
      ,j.agnum
      ,c.nsp
      ,to_char(c.bdate, 'dd.mm.yyyy') || '-' || to_char(c.fdate, 'dd.mm.yyyy') as period
  from CLHISTNUM   c
      ,CLIENTS     cl
      ,JPAGREEMENT j
  where cl.pcode = c.pcode
    and j.agrid  = c.agrid
    and c.amountrub < (select sum(t.amountcl + t.amountjp) / 2
                         from TREAT t
                         where t.histid = c.histid);