Informatica 3 Liceo Scientifico Scienze Applicate/SubQuery e Group By

Indice del libro

Query attraverso l'istruzione select parte 2

modifica

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 DataNascitaCittaNascitaAltezza
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 VotoTipoIDstudente
1 matematica 13/10/2014 7scritto 1
2 storia 23/10/2014 8scritto 3
3 matematica 13/11/2014 6orale 1
4 matematica 13/11/2014 7orale 4
5 geografia 15/5/2013 10orale 4
6 inglese 13/4/2013 2scritto 2
7 storia 3/10/2016 4scritto 1

Group By

modifica

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

modifica

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)