dCNMuhaR

1-- Esercizio 1 (10 punti)
2-- Considerate le sole visite otorinolaringoiatriche, scrivere una query che 
3-- restituisca il numero di pazienti, ad oggi maggiorenni, che sono stati visitati
4-- solo da otorini di Firenze durante il primo trimestre del 2015.
5
6create or replace view PazientiCittaMedici as 
7select  V.Paziente, M.Citta 
8from    Visita V 
9        inner join Medico M on V.Medico = M.Matricola
10        inner join Paziente P on V.Paziente = P.CodFiscale
11where   M.Specializzazione = 'otorinolaringoiatria' and -- Le sole visite otorinolaringoiatriche
12        P.DataNascita + interval 18 year >= current_date -- Paz ad oggi maggiorenni
13        and year(V.Data) = 2015             -- durante il primo trimestre del 2015
14        and month(V.Data) between 1 and 3 
15group by V.Paziente, M.Citta;
16
17select  count(distinct PCM.Paziente)
18from    PazientiCittaMedici PCM 
19where   not exists
20            ( -- Non esistono visite da otorini non di firenze --
21                select *
22                from PazientiCittaMedici PCM2
23                where PCM2.Paziente = PCM.Paziente 
24                      and PCM2.Citta <> 'Firenze'
25            )
26        and exists
27            ( -- Esiste almeno una visita da un otorino di firenze --
28                select *
29                from PazientiCittaMedici PCM2
30                where PCM2.Paziente = PCM.Paziente 
31                      and PCM2.Citta = 'Firenze'
32            );
33
34-- Esercizio 2 (10 punti)
35-- Implementare una stored function therapy_failures() che riceva in ingresso
36-- il codice fiscale di un paziente e il nome commerciale di un farmaco e 
37-- restituisca, solo se esiste, il settore medico con il più alto numero di terapie
38-- iniziate dal paziente nel mese scorso con quel farmaco, 
39-- terminate senza guarigione nello stesso mese.
40
41drop function if exists therapy_failures;
42delimiter $$
43create function therapy_failures(codFiscale varchar(100), nomeFarmaco varchar(100))
44returns varchar(100) not deterministic
45reads sql data
46begin    
47    set @spec_max = (
48        select  D.SettoreMedico
49        from    (
50                    select  PA.SettoreMedico, count(*) as NumTerapie
51                    from    Terapia T natural join Esordio E 
52                            inner join Patologia PA on PA.Nome = E.Patologia
53                    where   T.Paziente = codFiscale
54                            and T.Farmaco = nomeFarmaco
55                            and year(T.DataInizioTerapia) = year(current_date)
56                            and month(T.DataInizioTerapia) = month(current_date) - 1
57                            and month(T.DataFineTerapia) = month(current_date) - 1
58                            and (
59                                    E.DataGuarigione is null 
60                                    or
61                                    E.DataGuarigione > T.DataFineTerapia
62                                ) -- senza guarigione 
63                    group by PA.SettoreMedico
64                ) as D
65        where D.NumTerapie > all
66            (
67                select  D2.NumTerapie
68                from    (
69                            select  PA.SettoreMedico, count(*) as NumTerapie
70                            from    Terapia T natural join Esordio E 
71                                    inner join Patologia PA on PA.Nome = E.Patologia
72                            where   T.Paziente = codFiscale
73                                    and T.Farmaco = nomeFarmaco
74                                    and year(T.DataInizioTerapia) = year(current_date)
75                                    and month(T.DataInizioTerapia) = month(current_date) - 1
76                                    and month(T.DataFineTerapia) = month(current_date) - 1
77                                    and (
78                                            E.DataGuarigione is null 
79                                            or
80                                            E.DataGuarigione > T.DataFineTerapia
81                                        ) -- senza guarigione 
82                            group by PA.SettoreMedico
83                        ) as D2
84                where D2.SettoreMedico <> D.SettoreMedico -- Deve esistere unica
85            ));
86    return @spec_max; -- Se non esiste o ne esiste più di una torna null
87end $$
88delimiter ;
89select therapy_failures('aaa1', 'Aspirina');
90
91-- Versione 'pulita' se le temporary table funzionassero come dovrebbero
92drop function if exists therapy_failures;
93delimiter $$
94create function therapy_failures(codFiscale varchar(100), nomeFarmaco varchar(100))
95returns varchar(100) not deterministic
96begin
97    create temporary table _TerapieTarget
98        select  PA.SettoreMedico, count(*) as NumTerapie
99        from    Terapia T natural join Esordio E 
100                inner join Patologia PA on PA.Nome = E.Patologia
101        where   T.Paziente = codFiscale
102                and T.Farmaco = nomeFarmaco
103                and year(T.DataInizioTerapia) = year(current_date)
104                and month(T.DataInizioTerapia) = month(current_date) - 1
105                and month(T.DataFineTerapia) = month(current_date) - 1
106                and (
107                        E.DataGuarigione is null 
108                        or
109                        E.DataGuarigione > T.DataFineTerapia
110                    ) -- senza guarigione 
111        group by PA.SettoreMedico;
112    
113    set @spec_max = (
114        select T.SettoreMedico
115        from _TerapieTarget T 
116        where T.NumTerapie > all
117            (
118                select T2.NumTerapie
119                from _TerapieTarget T2
120                where T2.SettoreMedico <> T.SettoreMedico -- Deve esistere unica
121            )
122    );
123    return @spec_max; -- Se non esiste o ne esiste più di una torna null
124end $$
125delimiter ;