Raggruppamenti e target list
scorretta
select padre, avg(f.reddito), p.reddito
from persone f join paternita on figlio = nome
join persone p on padre = p.nome
group by padre
corretta
select padre, avg(f.reddito), p.reddito
from persone f join paternita on figlio = nome
join persone p on padre = p.nome
group by padre, p.reddito
Condizioni sui gruppi
Può essere anche necessario restringere i gruppi attraverso
l’applicazione di condizioni.
Se le condizioni sono verificabili a livello delle singole righe, basta
utilizzare la clausola where, altrimenti si aggiunge una
condizione alla group by attraverso l’estensione having
select Dipart, sum(Stipendio)as SommaStipendi
from Impiegati
group by Dipart
having sum(Stipendio) > 100
Se non si specifica group by e si usa having da solo la
condizione è applicata a tutte le righe. Il problema è che se la
condizione non è verificata, il risultato sarà vuoto.
Condizioni sui gruppi
• I padri i cui figli hanno un reddito medio maggiore di
25
select padre, avg(f.reddito)
from persone f join paternita on
figlio = nome
group by padre
having avg(f.reddito) > 25
WHERE o HAVING?
• I padri i cui figli sotto i 30 anni hanno un reddito medio
maggiore di 20
select padre, avg(f.reddito)
from persone f join paternita on
figlio = nome
where eta < 30
group by padre
having avg(f.reddito) > 25
Select
La forma di select cui siamo arrivati dopo le estensioni
viste è quindi:
SelectSQL ::= select ListaAttributiOEspressioni
from ListaTabelle
[ where CondizioniSemplici ]
[group by ListaAttributiDiRaggruppamento]
[ having CondizioniAggregate]
[ order by ListaAttributiDiOrdinamento]
Interrogazioni di tipo insiemistico
SQL fornisce anche gli operatori di tipo insiemistico
union,
intersect,
except (minus)
con la seguente sintassi:
SelectSQL
{< union|intersect|except >[all]} SelectSQL
NB
• intersect e except potrebbero anche essere derivati
attraverso opportune query
• gli operatori insiemistici eseguono per default una eliminazione
dei duplicati; all specifica di non farla
• gli schemi su cui si opera non devono essere identici ma avere
uguale numero di attributi, con domini compatibili. La
corrispondenza è per posizione.
Interrogazioni di tipo insiemistico
Estrarre nomi e cognomi degli impiegati
select Nome
from Impiegato
union
select Cognome
from Impiegato
Estrarre i cognomi degli impiegati che sono anche nomi
select Nome
from Impiegato
intersect
select Cognome
from Impiegato
Interrogazioni nidificate
E’ possibile anche realizzare clausole where in cui il
confronto non avviene fra predicati semplici o fra valori, ma
fra valori e insiemi, ottenuti come risultato di un’altra
interrogazione.
Sorge il problema del confronto fra un valore e un insieme di
valori.
SQL offre 2 possibilità di estendere gli operatori di confronto
al caso del confronto valore/insieme (risultato query):
all specifica che il risultato del confronto è vero se è vero
per tutte le righe del risultato dell’interrogazione.
any specifica che il risultato del confronto è vero se è vero
per una qualunque riga del risultato dell’interrogazione.
Interrogazioni nidificate
select *
from Impiegato
where Dipart = any (select Nome
from Dipartimento
where Città=‘Firenze’)
Il risultato è una tabella che comprende tutte le righe di
IMPIEGATO per cui il valore Dipart è uguale ad almeno uno
dei valori di Nome in DIPARTIMENTO, limitatamente alle
tuple per cui Città=‘Firenze’, cioè l’elenco degli impiegati
che lavorano in dipartimenti con sede a Firenze.
Lo stesso risultato si poteva ottenere con un join, ma così,
specialmente per interrogazioni complesse, è più leggibile.
Nome e reddito del padre di Franco
select Nome, Reddito
from Persone, Paternita
where Nome = Padre and
Figlio = 'Franco'
select Nome, Reddito
from Persone
where Nome = (select Padre
from Paternita
where Figlio = 'Franco')
Interrogazioni nidificate
L’uso delle interrogazioni nidificate può anche eliminare la
necessità degli alias.
Es.
select
from
where
and
and
I1.Nome
Impiegato
I1.Nome =
I1.Dipart
I2.Dipart
I1, Impiegato I2
I2. Nome
=‘Produz’
<> ‘Produz’
equivale a
select Nome
from Impiegato
where Nome = any (select Nome
from Impiegato
where Dipart=‘Produz’)
and Dipart <> ‘Produz’
Interrogazioni nidificate
Non tutte le interrogazioni nidificate corrispondono però ad un join
select Nome
from Dipartimento
where Nome <> all ( select Dipart
from Impiegato
where Cognome=‘Rossi’)
La condizione è verificata per le righe che NON contengono un certo
valore, quindi non è esprimibile mediante un join, che richiede una
corrispondenza fra valori. Però è equivalente a :
PNome(DIPARTIMENTO) - PDipart (s Cognome=‘Rossi’(IMPIEGATO)))
e quindi le due query potevano essere unite da except
NB: =any e <>all si possono anche scrivere in e not in
Interrogazioni nidificate
Un’altra equivalenza può essere evidenziata con operatori
aggregati.
select Dipart
from Impiegato
where Stipendio =
(select max(Stipendio)
from Impiegato)
equivale a
select Dipart
from Impiegato
where Stipendio >= all ( select Stipendio
from Impiegato )
Massimo e nidificazione
• La persona (o le persone) con il reddito massimo
select *
from persone
where reddito = ( select max(reddito)
from persone)
Interpretazione delle query nidificate
Per analizzare il risultato di una interrogazione nidificata si può
supporre di valutare prima il risultato dell’interrogazione nidificata
e poi quella della interrogazione che la contiene.
Questo va anche a favore dell’efficienza, in quanto
l’interrogazione nidificata viene eseguita una sola volta.
Talvolta però esiste un riferimento tramite variabile (passaggio di
binding) fra l’interrogazione nidificata e quella che la contiene.
In questo caso bisogna usare la definizione ‘procedurale’ di query,
che calcola il prodotto cartesiano fra le tabelle e poi verifica la
condizione where separatamente per ciascuna riga.
Quindi per ogni riga della query esterna si valuta prima la query
nidificata per poi calcolare il predicato a livello di riga sulla query
esterna.
Interrogazioni nidificate
Le variabili SQL sono utilizzabili solo nell’ambito della
query in cui sono definite o nell’ambito di una query
nidificata all’interno di essa.
Se due query sono allo stesso livello non possono
condividere variabili.
exists
è un operatore logico applicabile a query nidificate.
Restituisce vero se la query dà un risultato non nullo, falso
se è nullo.
E’ utilizzabile in modo significativo solo se esiste un
passaggio di binding fra interrogazione esterna e
interrogazione nidificata.
Le persone che hanno almeno un figlio
select *
from Persone
where
exists ( select *
from Paternita
where Padre = Nome)
or
exists ( select *
from Maternita
where Madre = Nome)
exists
select *
from Persona P
where exists ( select *
from Persona P1
where P1.Nome = P.Nome
and P1.Cognome = P.Cognome
and P1.CFiscale<>P.CFiscale)
In questo caso non è possibile eseguire prima la query
nidificata, in quanto è indeterminata se non si risolve il
riferimento. Quindi per ogni riga dell’interrogazione
esterna dovrà essere valutata l’interrogazione nidificata.
Costruttore di tuple
Quando esiste un’uguaglianza fra un insieme di
attributi è possibile sintetizzare la query inserendo
gli attributi all’interno di una parentesi tonda.
Es.
Select *
from Persona P
where (Nome, Cognome) in
(select Nome, Cognome
from Persona Q
where P.CFiscale <> Q.CFiscale)