Appunti ed esercizi SQL

Formattazione del testo

GENERALE… CITTA …PAVIA CITTA NUM_ABMILANO PAVIA 2PADOVA MILANO 2PAVIA PADOVA 1NAPOLI NAPOLI 2MILANONAPOLIEsercizi :Trovare quanti sono i maschi e quante le femmine

SELECT UCASE(SESSO), COUNT(*)FROM GENERALEGROUP BY UCASE(SESSO)Mettiamo UCASE per evitare che GROUP BY mi faccia 4 sotto tabelle nel caso in cui ci siano M ed F siamaiuscole che minuscole. QuandoSi vogliono confrontare i pesi delle persone alte(>170) con quelli delle persone basse(<=170).

si vogliono confrontare dei parametri vuol dire che bisogna sempre trovare la media (AVG), ladeviazione standard (SD) e il numero/conteggio (COUNT).SELECT ALTEZZA > 170 AS “-1=ALTI 0=BASSI”, AVG(PESO) AS PESO_MEDIO, STDEV(PESO) AS DS,COUNT(PESO) AS CONTEGGIOFROM VISITE GROUP BY ALTEZZA > 170 (-1:VERO, 0:FALSO) due sotto tabelle“-1=ALTI 0=BASSI PESO_MEDIO SD CONTEGGIO-1 80,2 10 20500 65,3 6 2205Si vogliono confrontare diverse classi di altezza e ottenere la tabella seguente, con le

seguenti classi

• di altezza

CLASSE DI ALTEZZA PESO_MEDIO SD CONTEGGIO

0 (0 - <50)	1(>=50 - <100)	2 (>=100 - <150)	3 (>=150 - <200)	4 (>=200 - <250)

SELECT INT(ALTEZZA/50) AS “0:<50, 1:50<100, 2:…..”, AVG(PESO) AS PESO_MEDIO, STDEV(PESO) ASDS, COUNT(PESO) AS CONTEGGIO

FROM VISITE

GROUP BY INT(ALTEZZA/PESO)

SELECT SESSO, CITTA, COUNT(*) … SESSO … CITTAF MILANO

FROM GENERALE F MILANO

GROUP BY SESSO, CITTA, COUNT(*) F MILANO

M MILANO

Si creeranno 4 sotto tabelle con tutte le F PAVIA

combinazioni: M PAVIAF – PAVIA M - PAVIA M PAVIAF – MILANO M – MILANO

Quando si usa la GROUP BY, nella SELECT posso mettere qualunque funzione di aggregazione, qualunque

costante, ma, come attributi singoli, SOLO quelli utilizzati nella GROUP BY, perché sono gli unici che,

all’interno delle sotto tabelle, sicuramente hanno lo stesso valore in tutte le righe.

La GROUP BY può contenere anche più attributi. Ad esempio

La seguente query:

<SELECT PROVINCIA, SESSO, COUNT(*) AS NUMERO
FROM GENERALE
GROUP BY PROVINCIA, SESSO>

Fornirà il numero di maschi e di femmine per ogni provincia di provenienza.

Si vuole controllare se le persone provenienti dalle diverse città hanno età simili (considerando le città da cui provengono almeno venti persone).

Si tratta di un confronto e quindi ci serve AVG, STDEV e la COUNT.

In output mi aspetto una tabella del genere:

<table>
  <tr>
    <th>CITTA</th>
    <th>ETA MEDIA</th>
    <th>DEVSTD</th>
    <th>NUMERO</th>
  </tr>
  <tr>
    <td>PAVIA</td>
    <td>60.5</td>
    <td>22</td>
    <td>3520</td>
  </tr>
  <tr>
    <td>PADOVA</td>
    <td>59.3</td>
    <td>15.1</td>
    <td>1532</td>
  </tr>
</table>

La e faccio un'altra query:

<SELECT *
FROM Q
WHERE NUM >= 20>

Oppure:

<SELECT CITTA, AVG((DATE()-NASCITA)/365.25) AS ETA_MEDIA, STDEV((DATE()-NASCITA)/365.25) AS DEVSTD, COUNT(*) AS NUMERO
FROM GENERALE
GROUP BY CITTA
HAVING COUNT(*) >= 20>

HAVING: quando si usa la GROUP BY, è possibile selezionare le sotto

CF	PESO	NUM.VISITE
CF23	70	3
CF41	35	2

Limiterò il risultato a chi ha più di una visita.

VISITA	CF	PESO
1	CF23	70
2	CF23	70
3	CF23	70
4	CF41	35
5	CF41	35
6	CF10	50
7	CF10	51
8	CF10	52
9	CF3	42
10	CF3	40

SELECT CF_PAZIENTE, (MIN(PESO) o MAX(PESO) o AVG(PESO)), COUNT(*) AS NUM.VISITE
FROM VISITE
GROUP BY CF_PAZIENTE
HAVING STDEV(PESO) = 0 per definizione la deviazione standard è nulla quando i pesi sono tutti uguali(HAVING

MAX(PESO) = MIN(PESO))AND COUNT(*) >= 2(MIN(PESO) o MAX(PESO) o AVG(PESO)) : scrivo così perché ogni paziente ha più di un peso e possiamo scrivere tutte e tre le funzioni indistintamente perché nel caso in cui i pesi sono tutti uguali saranno uguali anche minimo, massimo e media. Trovare i pazienti che sono stati operati sempre dallo stesso chirurgo. SELECT CODICE_FISCAL FROM INTERVENTI GROUP BY CODICE_FISCHAVING MIN(CF_CHIRURGO) = MAX(CF_CHIRURGO) FUNZIONI SULLE DATE NOW() data – ore : minuti : secondi DATE() data YEAR(DATE()) – 2019 MONTH(data) – mese(1 -12) DAY(data) – giorno(1-31) Esempio : Quanti pazienti sono stati ricoverati, anno per anno, negli ultimi 10 anni. SELECT YEAR(DATA_INGRESSO) AS ANNO, COUNT(*) AS NUM_RICOVERI FROM RICOVERI WHERE YEAR(DATA_INGRESSO) > YEAR(DATE()) – 10 GROUP BY YEAR(DATA_INGRESSO) FATTORI DI RISCHIO : BMI > 26 +2 FC > 90 +1 P_SIST > 140 +3 P_DIAS >90 +4 SELECT CF_PAZIENTE, DATA,

-2*(PESO/(ALTEZZA*ALTEZZA))>26 - (FC>90) - 3*(P_SIST>140) - 4*(P_DIAS>90)AS IR

FROM VISTE

SUBQUERY: è una query che è messa all'interno di una query principale.

SELECT *

FROM GENERALE

WHERE CITTA (SELECT CITTA FROM CITTAIN subqueryWHERE PROVINCIA = "PV")

Esercizi:

Trovare i pazienti (CF, nome, cognome) che sono stati operati.

SELECT G.CODICE_FISC, G.NOME, G.COGNOME

FROM GENERALE G

WHERE IN (SELECT FROM INTERVENTIG.CODICE_FISC I.CODICE_FISC I)

Se ci interessano i pazienti visitati e non ricoverati scriviamo:

SELECT G.CODICE_FISC, G.NOME, G.COGNOME

FROM GENERALE G

WHERE IN (SELECT FROM VISITEG.CODICE_FISC V.CF_PAZIENTE V)

AND NOT IN (SELECT FROM RICOVERIG.CODICE_FISC R.CODICE_FISC R)

Trovare i pazienti che hanno 5 ricoveri.

SELECT G.CODICE_FISC, G.NOME, G.COGNOME

FROM GENERALE G

WHERE 5 = (SELECT COUNT(*) FROM RICOVERI R  subquery correlataWHERE R.CODICE_FISC = G.CODICE_FISC) : contiene uno o più attributi della query

EXISTS – NOT EXISTS: restituisce TRUE se la subquery seguente restituisce una riga come risultato, altrimenti restituisce FALSE.

Esempio precedente:

SELECT G.CODICE_FISC, G.NOME, G.COGNOME
FROM GENERALE G
WHERE EXISTS (SELECT * FROM INTERVENTI I
              WHERE G.CODICE_FISC = I.CODICE_FISC)

Si trovino i pazienti che nelle loro visite non hanno mai avuto il loro peso misurato. (meglio ragionare in negativo con i SEMPRE e i MAI)

SELECT G.CODICE_FISC, G.NOME, G.COGNOME
FROM GENERALE G
WHERE G.CODICE_FISC NOT IN (SELECT V.CF_PAZIENTE FROM VISITE V
                            WHERE V.PESO IS NOT NULL)

Oppure

WHERE NOT EXISTS (SELECT * FROM VISITE V
                  WHERE V.CF_PAZIENTE = G.CODICE_FISC AND V.PESO IS NOT NULL)

Si trovino i pazienti che nelle loro visite hanno sempre avuto il peso misurato.

SELECT G.CODICE_FISC, G.NOME, G.COGNOME
FROM GENERALE G
WHERE G.CODICE_FISC NOT IN (SELECT V.CF_PAZIENTE FROM VISITE V
                            WHERE V.PESO IS NULL)

Oppure

WHERE NOT EXISTS (SELECT * FROM VISITE V
                  WHERE V.CF_PAZIENTE = G.CODICE_FISC AND V.PESO IS

Trovare i medici che non hanno mai visitato nessun paziente.

GENERALE PROFESSIONI

CODICE	DESCRIZIONE	CF	NOME	COGNOME
2.4.1	MEDICI	1.3CF2
2.4.1.1	----
2.4.1.2	----	CODICE_FISC	CODICE_PROF

SELECT G.CODICE_FISC, G.NOME, G.COGNOME

FROM GENERALE G

WHERE G.CODICE_FISC IN (SELECT E.CODICE_FISC FROM ESERCITA_PROF E

WHERE LEFT(E.CODICE_PROF,5) = "2.4.1")

AND G.CODICE_FISC IN (SELECT D.CODICE_FISC FROM DIPENDENTI D)

AND G.CODICE_FISC NOT IN (SELECT V.CF_MED FROM VISITE V)

JOIN : prodotto cartesiano tra tabelle (tutte le combinazioni possibili fra le righe della tabella) con eventuale selezione in base al valore di uno o più attributi.

Serve a recuperare dati che sono in tabelle diverse, e che possono essere messe in relazione attraverso le chiavi esterne.

A1	B1	C1	A2	B2
0	3	5	1	2
A1	B1	C1	PRODOTTO CARTESIANO
A2	B2	0	3	5
0	3	5	1	2
1	4	8	1	2
1	4	8	3	4
1	4	8	3	4
6	0	2	1	2
6	0	2	3	4

Esercizi:

Elencare i pazienti, le date delle loro visite e la loro

pressione.

    
CF NOME COGNOME DATA_VISITA PRES_SIST PRES_DIASSELECT G.CODICE_FISC, G.NOME, G.COGNOME, V.DATA, V-P_DIASTOLICA, V_SISTOLICAFROM GENERALE G, VISITE V  CONDIZIONE DI JOINWHERE G.CODICE_FISC = V.CF_PAZIENTE : sfrutta gli attributi comuni alle due tabelle

Elencare i pazienti, le date dei loro ricoveri e le pressioni misurate durante il ricovero, riportando

    
anche la data della misura. In uscita voglio la tabella seguente :


    

        
CF
        
NOME
        
COGNOME
        
DATA_VIS
        
P_SISTOLICA
        
P_DIASTOLICA
        
DATA_ING
        
DATA_USC
    
    

        
G.CODICE_FISC
        
G.NOME
        
G.COGNOME
        
V.DATA_VIS
        
V.P_SISTOLICA
        
V.P_DIASTOLICA
        
R.DATA_ING
        
R.DATA_USC
    

SELECT G.CODICE_FISC, G.NOME, G.COGNOME, V.DATA_VIS, V.P_SISTOLICA, V.P_DIASTOLICA,
R.DATA_ING, R.DATA_USC
FROM GENERALE G, VISITE V, RICOVERI V
WHERE G.CODICE_FISC = V.CF_PAZIENTE AND G.CODICE_FISC = R.CODICE_FISC
AND V.DATA_VIS BETWEEN R.DATA_ING AND R.DATA_USC
ORDER BY G.CODICE_FISC, V.DATA_VIS

Se ho N tabelle dovrò mettere N-1 condizioni!!

Riprodurre la tabella RICOVERI riportando al posto dei codici le loro descrizioni ( mantenendo come

    
codice solo il CF).

SELECT R.ID_RICOVERO,

G.CODICE_FISC AS PAZIENTE, G.NOME, G.COGNOME, R.DATA_INGRESSO,R.DATA_USCITA, REP.NOME, D.DESCRIZIONE, I.DIAGNOSI_RICOVERO, R.CF_MEDICO AS MEDICO,GM.MEDICO, GM.COGNOME FROM GENERALE G, RICOVERI R, DRG D, ICD9 I, REPARTI REP, GENERALE GM WHERE R.CODICE_FISC = G.CODICE_FISC AND R.REPARTO = REP.CODICE AND R.DRG = D.CODICE AND R.DIAGNOSI_RICOVERO = I.CODICE AND R.CF_MEDICO = GM.CODICE_FISC Un prodotto cartesiano può contenere anche varie 'copie' della stessa tabella, basta usare alias differenti. T1 T1.A T1.B T1.A T1.B A B 0 1 0 10 1 0 1 3 5 3 5 0 13 5 3 5 SELECT * FROM TABELLA T1, TABELLA T2 PROVA D'ESAME 18-11-2019 1. Considerando gli interventi chirurgici degli ultimi sette giorni, si vuole produrre un elenco, in un'unica colonna così formattato : INTERVENTI DEGLI ULTIMI SETTE GIORNI Il sig. Mario Rossi è stato operato da Luigi Neri in data 17/11/2019 La sig.ra Maria Rossi è stata operata da ..... SELECT "IL SIG." +Il testo formattato con i tag HTML sarebbe il seguente: “ “ + G.NOME + “ “ + G.COGNOME + “ “ + “E’ STATO OPERATO DA” + &