Classificazione delle interrogazioni complesse

Le interrogazioni o query sono uno strumento fondamentale dei DBMS e le possiamo suddividere in:
• Query con ordinamento
• Query con aggregazioni
• Query con raggruppamento
• Query binarie
• Query nidificate

Esempio: gestione ordini

Istanza di ordine

Ordinamento
• La clausola order by, che compare in coda all’interrogazione, ordina le righe del risultato
• Sintassi:
order by AttributoOrdinamento [ asc | desc ]
{, AttributoOrdinamento [ asc | desc ] }
• Le condizioni ordinamento vengono valutate in ordine
– a pari valore del primo attributo, si considera l’ordinamento sul secondo, e così via.

Query con ordinamento

Select *
From Ordine
Where Importo > 100.000
Order by Data

Order by CodCli

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Funzioni aggregate

Le interrogazioni con funzioni aggregate non possono essere rappresentate in algebra relazionale.
Il risultato di una query con funzioni aggregate dipende dalla valutazione del contenuto di un insieme di righe.
• SQL offre cinque operatori aggregati:
– count cardinalità
– sum sommatoria
– max massimo
– min minimo
– avg media

Operatore count

• count restituisce il numero di righe o valori distinti;
sintassi:
count(< * | [ distinct | all ] ListaAttributi >)
• Estrarre il numero di ordini:
select count(*)
from Ordine
• Estrarre il numero di valori distinti dell’attributo CodCli per tutte le righe di Ordine:
select count(distinct CodCli)
from Ordine
• Estrarre il numero di righe di Ordine che posseggono un valore non nullo per l’attributo CodCli:
select count(all CodCli)
from Ordine

sum, max, min, avg
• Sintassi:
< sum | max | min | avg > ([ distinct | all ] AttrEspr )
• L’opzione distinct considera una sola volta ciascun valore
– utile solo per le funzioni sum e avg
• L’opzione all considera tutti i valori diversi da null.

Query con massimo
• Estrarre l’importo massimo degli ordini
select max(Importo) as MaxImp
from Ordine.

Query con sommatoria
• Estrarre la somma degli importi degli ordini relativi al cliente numero 1
select sum(Importo) as SommaImp
from Ordine
where CodCliente = 1

Funzioni aggregate con join

• Estrarre l’ordine massimo tra quelli contenenti il prodotto con codice ‘ABC’ :
select max(Importo) as MaxImportoABC
from Ordine, Dettaglio
where Ordine.CodOrd = Dettaglio.CodOrd and
CodProd = ’ABC’

Funzioni aggregate e target list

• Query scorretta:
select Data, max(Importo)
from Ordine, Dettaglio
where Ordine.CodOrd = Dettaglio.CodOrd and
CodProd = ’ABC’
• La data di quale ordine? La target list deve essere omogenea
• Estrarre il massimo e il minimo importo degli ordini:
select max(Importo) as MaxImp,
min(Importo) as MinImp
from Ordine.

Funzioni aggregate e target list
• Estrarre il massimo e il minimo importo degli ordini:
select max(Importo) as MaxImp,
min(Importo) as MinImp
from Ordine.

Query con raggruppamento

• Nelle interrogazioni si possono applicare gli operatori aggregati a sottoinsiemi di righe
• Si aggiungono le clausole
– group by (raggruppamento)
– having (selezione dei gruppi)
select …
from …
where …
group by …
having …

Query con raggruppamento

Estrarre la somma degli importi degli ordini successivi al 10‐6‐2007 per quei clienti che hanno emesso almeno 2 ordini.

select CodCli, sum(Importo)
from Ordine
where Data > 2007‐06‐10
group by CodCli
having count(Importo) >= 2

Passo 1: Valutazione where

Passo 2 : Raggruppamento

• si valuta la clausola group by

Passo 3 : Calcolo degli aggregati

• si calcolano sum(Importo) e count(Importo) per ciascun gruppo.

Passo 4 : Estrazione dei gruppi

• si valuta il predicato count(Importo) >= 2

 

Passo 5 : Produzione del risultato

Query con group by e target list

• Query scorretta:
select Importo
from Ordine
group by CodCli
• Query scorretta:
select O.CodCli, count(*), C.Città
from Ordine O join Cliente C
on (O.CodCli = C.CodCli)
group by O.CodCli
• Query corretta:
select O.CodCli, count(*), C.Città
from Ordine O join Cliente C
on (O.CodCli = C.CodCli)
group by O.CodCli, C.Città

Where o having?

• Soltanto i predicati che richiedono la valutazione di funzioni aggregate dovrebbero comparire nell’argomento della clausola having
• Estrarre i dipartimenti in cui lo stipendio medio degli impiegati che lavorano nell’ufficio 20 è maggiore di 25:
select Dipart
from Impiegato
where Ufficio = 20
group by Dipart
having avg(Stipendio) > 25

Query con raggruppamento e ordinamento

È possibile ordinare il  risultato delle query con raggruppamento
select …..
from …..
[ where ….. ]
group by …..
[ having ….. ]
order by ….

Raggruppamento e ordinamento

• Estrarre la somma degli importi degli ordini successivi al 10‐6‐ 97 per quei clienti che hanno emesso almeno 2 ordini, in ordine decrescente di somma di importo.
select CodCli, sum(Importo)
from Ordine
where Data > 10‐6‐97
group by CodCli
having count(Importo) >= 2
order by 2 desc
Risultato dopo la clausola di ordinamento

 

Doppio raggruppamento

• Estrarre la somma delle quantità dei dettagli degli ordini emessi da ciascun cliente per ciascun prodotto, purché la somma superi 50
select CodCli, CodProd, sum(Qta)
from Ordine as O, Dettaglio as D
where O.CodOrd = D.CodOrd
group by CodCli CodProd
CodCli, having sum(Qta) > 50
Situazione dopo il join e il raggruppamento

Estrazione del risultato

• si valuta la funzione aggregata sum(Qta) e il predicato having