Informatica 3 Liceo Scientifico Scienze Applicate/SubQuery e Group By

Query attraverso l'istruzione select parte 2

Vediamo l'uso del comando select con la clausola Group By e il caso di query annidate (subquery)

Le tabelle utilizzate sono :

studenti

IDStudente	Nome	Cognome	DataNascita	CittaNascita	Altezza
1	Marco	Rossi	21/1/1980	Rovigo	178
2	Luca	Verdi	25/12/1978	Padova	180
3	Silvia	Blu	13/9/1997	Rovigo	173
4	Michele	Giallo	14/9/1997	Padova	167
5	Anna	Rossi	23/2/1995	Venezia	180

compiti

IDCompito	Materia	Data	Voto	Tipo	IDstudente
1	matematica	13/10/2014	7	scritto	1
2	storia	23/10/2014	8	scritto	3
3	matematica	13/11/2014	6	orale	1
4	matematica	13/11/2014	7	orale	4
5	geografia	15/5/2013	10	orale	4
6	inglese	13/4/2013	2	scritto	2
7	storia	3/10/2016	4	scritto	1

Group By

1) e' possibile selezionare le righe di una tabella e raggrupparle per un particolare campo, ad esempio possiamo prendere la tabella dei compiti e decidere di raggrupparli per materia per poterne visualizzare il voto medio per ciascuna materia scriviamo allora

select Materia, avg(Voto)
from compiti
group by Materia

dopo il select se utilizziamo il group by possono esserci solo i campi oggetto del raggruppamento e funzioni di aggregazione sui campi della tabella

quindi NON SI PUO' scrivere

select IDstudente, materia, avg(voto)
from compiti
group by materia

posso anche decidere una selezione a livello di raggruppamento ad esempio calcolare il voto medio dei compiti per ciascuna materia limitatamente alle materie con piu' di 3 valutazioni

select materia, avg(Voto)
from compiti
group by materia
having count(*)>3

fate attenzione che il where lavora sulle righe prima del raggruppamento, mentre l' having sui raggruppamenti mediante funzione di aggregazione relative ai campi della tabella

ad esempio voglio il voto medio per ciascuna materia dello studente con codice 7 escludendo le materie con meno di 5 prove

select materia, avg(Voto)
from compiti
where IDStudente = 7
group by materia
having count(*)>=5

calcolare il voto max e contare quanti compiti sono stati fatti per le materie : storia, geografia e matematica

select materia, max(Voto) as votomassimo, count(*) as numeroprove
from compiti
where Materia in ('storia','geografia','matematica')
group by materia

contare quante prove ci sono suddivise per materia e valutazione ( i 6 con i sei, i 7 con i sette etc)

select materia,voti,count(*)
from compiti
group by materia,voti

Subquery

2) Talvolta c'e' la necessita' piu' di una query per risolvere un particolare quesito, inserendo il risultato di una query fra i parametri di una seconda query, in questo caso abbiamo una subquery .

visualizzare i nomi degli studenti che hanno in storia un voto superiore alla media di tutti gli studenti in quella materia

ora per risolvere il quesito bisogna prima calcolare il voto medio in storia di tutti gli studenti, cioe'

select avg(voto)
from Compiti
where materia='storia'

e poi utilizzare la query appena scritta al posto del valore medio dei compiti in storia, quindi

select Studenti.idstudente
from Studenti, Compiti
where materia='storia' AND Studenti.Idstudente = Compiti.idstudente
group by Studenti.idstudente
having avg(voto) > (select avg(voto)
                   from Compiti
                   where materia='storia');

oppure pensiamo a dove risolvere il quesito: stampare il nome e cognome degli studenti nati in una citta' con piu' di 10 iscritti nel nostro istituto ora per trovare le citta' dobbiamo

select CittaNascita
from Studenti
group by CittaNascita
having count(*) >10

che inseriamo nella

select cognome ,nome
from Studenti
where CittaNascita in (select CittaNascita
                      from Studenti
                      group by CittaNascita
                      having count(*) >10);

3) possiamo usare gli operatori

in per verificare se il valore di un campo appartiene a un certo gruppo di valori

 eta in ('33','35','37')

not in per verificare se il valore di un campo non appartiene a un certo gruppo di valori

 eta  not in ('33','35','37')

all per verificare se il valore di un campo e' minore o maggiore di tutti i valori di un certo gruppo

 eta  > all ('33','35','37')

any per verificare se il valore di un campo e' minore o maggiore uguale o diverso da almeno uno dei valori di un certo gruppo

     eta   > any ('33','35','37') 
     eta   < any ('33','35','37')
     eta  <> any ('33','35','37')

exists per verificare se una interrogazione restituisce un numero di righe diverso da zero

 SELECT romanzi, titolo
 FROM romanzi, film
 WHERE romanzi. ID romanzo= film. ID romanzo AND EXIST (SELECT nome. autore
                                                        FROM autori
                                                        WHERE romanzi. nome autore= autori. nome autore AND nazione= "Italia")

4) quando si opera con le subquery si puo salvare una query con un nome e poi utilizzarla come una ulteriore tabella accessibile mediante quel nome ad esempio

salvo

select CittaNascita
from Studenti
group by CittaNascita
having count(*) >10

come tabellacittastudentinumerosi

e poi la utilizzo in

select cognome ,nome
from Studenti
where CittaNascita in tabellacittastudentinumerosi

5) le subquery possono restituire :

• un solo valore,

ad esempio per visualizzare tutti gli studenti che hanno preso il voto massimo fra i voti della tabella compiti

 select cognome, nome
 from studenti,compiti 
 where studenti.idstudente= compiti.idstudente AND
       voto= ( select max(voto)
               from compiti
              )  

• una riga di valori

visualizzare i nomi e i cognomi degli studenti la cui altezza è pari all'altezza media degli studenti e che siano i più vecchi della classe

select nome,cognome
 from studenti
 where ROW(altezza,eta) = ( select avg(altezza),max(eta)
                          from studenti
                           ) 

• una colonna di valori

visualizzare i nomi e i cognomi degli studenti che sono nati in città in cui sono nati più di 3 studenti

select nome,cognome
 from studenti
 where città IN  ( select  città
                   from studenti
                   group by città
                   having count(*) >3
                  )  

• una tabella

tutte le informazioni sui compiti degli studenti con una eta>23

  select *
  from ( select cognome,idstudente
         from studenti
         where eta>23
        ) as tabella1, compiti
  where tabella1.idstudente = compiti.idstudente

6)Query correlate

Nelle soluzioni proposte per evitare di allungare troppo la query si può dare un alias al nome delle tabelle nel FROM, ad esempio

SELECT DISTINCT s.Nome
FROM Attori a, Recita r, Film f, Proiezioni p, Sale s
WHERE p.DataProiezione =25/12/04 and s.Città=”Milan” and a.Nome = “Gigi Proietti”
      and a.CodAttore = r.CodAttore and r.CodFilm = f.CodFilm and f.CodFilm=p.CodFilm
      and p.CodSala=s.CodSala 

Nel caso di una query annidata, la query più interna può fare riferimento ai campi delle tabelle della query più esterna,in questo caso si parla di subquery correlata alla query principale (quella più esterna) come nel seguente esempio

Il titolo dei film di Fellini in cui non recitano attori italiani

SELECT f.Titolo
FROM Film f
WHERE f.Regista = “Fellini” 
      and Not Exists (SELECT *
                      FROM Attori a, Recita r
                      WHERE f.CodFilm = r.CodFilm and r.CodAttore = a.CodAttore  
                            and a.Nazionalità = “Italiana”
                      )

In questo caso la query interna viene rielaborata per ciascuna riga della query più esterna

Per prepararvi al 2^compito rivedete l'uso dei comandi sql e poi esercitatevi nella creazione dei modelli relazionali e nelle interrogazioni sql con questa dispensaEserciziSQL.pdf (la dispensa è dell'Università di Pisa Prof.Leoni)