Approfondimenti SQL
Funzioni di aggregazione
• All’interno del comando SELECT possono
essere utilizzate funzioni predefinite che
agiscono sui valori contenuti in insiemi di righe
della tabella (da cui il nome f. di aggregazione).
• Le righe considerate per l’aggregazione sono
quelle prodotte dall’interrogazione, ad esempio
se compare la clausola WHERE, le righe
saranno quelle che soddisfano la condizione
specificata.
Funzioni di aggregazione:
 COUNT() conta il numero totale di righe di una tabella
 AVG() calcola la media dei valori assunti da una colonna di
una tabella
 MAX() calcola il massimo dei valori assunti da una colonna
di una tabella
 MIN() calcola il minimo dei valori assunti da una colonna
di una tabella
 SUM() calcola la somma dei valori assunti da una colonna
di una tabella
Funzione COUNT()
• La funzione COUNT conta il numero di
righe selezionate da un’interrogazione.
• La sintassi del linguaggio SQL è la
seguente:
SELECT COUNT (Nomecol o * asterisco)
FROM NomeTab;
• Data la tabella: Impiegati
Codice Cognome
2352 Cardelli
Nome
Livello
Codcapo
Stipendio
Dipartimento
Paolo
6
5234
2541
20
Andrea
3
25
2105
10
3456 Tarabba
Aldo
6
5234
2722,5
10
3461 Perugini
Maria
4
3456
1815
10
3462 Piraino
Carla
4
9876
1573
30
Sandro
4
2352
1512,5
20
Luca
7
0
2750
10
6530 Terranova
Mario
4
9876
1391,5
30
7461 Rossi
Mario
3
3456
1210
20
Cristiano
5
2352
2178
20
7896 Bertin
Francesca
5
3456
1391,5
10
9076 Rispoli
Alessandra
4
3456
1694
10
9871 Galli
Enzo
3
9876
1210
30
9875 Moi
Ester
5
9876
2750
30
2359 Lecca
3548 Mino
5234 Guiso
7467 Marazzita
Esempio
La query:
SELECT COUNT(*)
FROM Impiegati;
Restituisce il valore 14, lo stesso vale per la query:
SELECT COUNT(Dipartimento)
FROM Impiegati;
Esempio:
Con il seguente codice:
SELECT COUNT(Dipartimento)
FROM Impiegati
WHERE Livello = 4 ;
Invece si ottiene come risultato 5.
Si può ottenere anche il numero di righe diverse usando
DISTINCT
SELECT COUNT(DISTINCT Dipartimento)
FROM Impiegati
WHERE Livello = 4 ;
Invece si ottiene come risultato 3.
Funzione SUM()
• La funzione SUM restituisce la somma di
tutti i valori contenuti in una colonna
specificata come argomento della
funzione. Naturalmente l’attributo usato
per il calcolo deve essere di tipo numerico.
• La sintassi nel linguaggio SQL è:
SELECT SUM(nomecol)
FROM nometab;
Esempio
1.
SELECT SUM(Stipendio) As Somma
FROM Impiegati;
2.
SELECT SUM(Stipendio)
FROM Impiegati
WHERE Dipartimento = ’10’;
3.
SELECT SUM(DISTINCT Stipendio)
FROM Impiegati
WHERE Dipartimento = ’10’;
3.
SELECT SUM(PrezzoUnit * Quantità)
FROM Fattura;
Funzione AVG()
• La funzione AVG serve per calcolare la
media dei valori numerici contenuti in una
determinata colonna di una tabella. Anche
con tale funzione si può utilizzare la parola
DISTINCT.
• La sintassi nel linguaggio SQL è:
SELECT AVG(nomecol)
FROM nometab;
Esempio
1.
SELECT AVG(Stipendio) As Media
FROM Impiegati;
2.
SELECT AVG(Stipendio) As Media
FROM Impiegati
WHERE Dipartimento = ’10’;
3.
SELECT AVG(DISTINCT Stipendio) As Media
FROM Impiegati
WHERE Dipartimento = ’10’;
4.
SELECT AVG(Stipendio)
FROM Impiegati
WHERE Livello = 4;
Funzione MIN()
• La funzione MIN cerca il più piccolo dei
valori numerici contenuti in una
determinata colonna di una tabella.
• La sintassi nel linguaggio SQL è:
SELECT MIN(nomecol)
FROM nometab;
Esempi
1. SELECT MIN(Stipendio) As MinimoStip
FROM Impiegati;
2.
SELECT MIN(Stipendio) As MinimoStip
FROM Impiegati
WHERE Dipartimento = ’10’;
3.
SELECT MIN(Stipendio)
FROM Impiegati
WHERE Livello = 4 ;
Funzione MAX()
• La funzione MAX serve cerca il più piccolo
dei valori numerici contenuti in una
determinata colonna di una tabella.
• La sintassi nel linguaggio SQL è:
SELECT MAX(nomecol)
FROM nometab;
Esempi
1. SELECT MAX(Stipendio) As MassimoStip
FROM Impiegati;
2.
3.
SELECT MAX(Stipendio) As MassimoStip
FROM Impiegati
WHERE Dipartimento = ’10’;
SELECT MAX(Stipendio)
FROM Impiegati
WHERE Livello = 4 ;
Condizioni di ricerca
Le condizioni di ricerca sono utilizzate insieme alla
clausola WHERE per determinare i criteri di selezione
delle righe. Si possono utilizzare:
 Gli operatori di confronto: =,<, > ,<=, >=,<>;
 Gli operatori logici: AND, OR, NOT;
 L’operatore BETWEEN;
 L’operatore IN;
 L’operatore LIKE;
 Il predicato IS NULL, IS NOT NULL.
Operatore BETWEEN
•
•
Questo operatore controlla se un valore è compreso
all’interno di un intervallo di valori, inclusi gli
estremi.
Esempio: Cognome di tutti i dipendenti con
stipendio compreso tra 1500 e 2000 euro.
SELECT Cognome
FROM Impiegati
WHERE Stipendio BETWEEN 1500 AND 2000
;
Operatore BETWEEN
• L’operatore BETWEEN può essere utilizzato con
l’operatore logico NOT, nel caso in cui si voglia
controllare se un il valore non rientra in un intervallo
di valori. In questo caso prima si deve utilizzare
l’operatore NOT e poi l’operatore BETWEEN.
• Esempio: Cognome di tutti i dipendenti con stipendio
non compreso tra 1000 e 2000 euro.
SELECT Cognome
FROM Impiegati
WHERE Stipendio NOT BETWEEN 1000 AND
2000 ;
Operatore IN
• L’operatore IN controlla se un un valore appartiene
ad uno dei valori di una lista che viene precisata dopo
la parola IN all’interno della condizione.
• Esempio: Cognome di tutti i dipendenti che hanno
come capo uno dei seguenti: 5234, 2352, 3456.
SELECT Cognome
FROM Impiegati
WHERE Codcapo IN (“5234”, “2352”, “3456”);
Osservazione: Anche con l’operatore IN si può usare
l’operatore NOT.
Operatore LIKE
L’operatore LIKE confronta il valore di un attributo di tipo
carattere con un modello di stringa che può contenere dai caratteri
jolli. Tali caratteri jolli sono:
• _ (trattino basso) o ? serve per indicare uno ed un solo carattere
qualsiasi nella stringa in una determinata posizione;
• % o * serve per indicare una stringa qualsiasi.
Esempi:
LIKE “xyz*” rappresenta una stringa che inizia con xyz
LIKE “*xyz” rappresenta una stringa che termina con xyz
LIKE “?xyz” rappresenta una stringa di quattro caratteri, che termina
con xyz
Operatore LIKE
Esempi:
• Cognome e stipendio dei dipendenti il cui
cognome che inizia con la lettera p:
SELECT Cognome, Stipendio
FROM Impiegati
WHERE Cognome LIKE “p*”;
Operatore LIKE
• Cognome e stipendio di tutti gli impiegati il
cui cognome inizia con le lettere A, B o C:
SELECT Cognome, Stipendio
FROM Impiegati
WHERE Cognome LIKE “[A,B,C]*”;
Operatore LIKE
• Anche con l’operatore LIKE si può usare l’operatore
NOT.
• Inoltre se occorre ricercare un carattere jolli in una stringa
si deve usare uno speciale carattere, detto carattere escape
per qualificare il carattere come carattere da ricercare e
non come un carattere jolli. Il carattere escape viene
precisato con la dichiarazione ESCAPE.
• Per esempio per ricercare una stringa che possiede il
carattere * (asterisco), occorre scrivere:
LIKE “?$*” ESCAPE “$”.
Il carattere “$” è il carattere escape e, pertanto, la sequenza
“$*” precisa che il carattere * è il carattere da ricercare
nella stringa e non un carattere jolli.
PREDICATO IS NULL
• Il predicato IS NULL confronta il valore in una colonna con il
valore NULL. L’uso di questo predicato è il solo modo per
controllare la presenza del valore NULL in una colonna.
Anche in questo caso si può utilizzare l’operatore NOT: il
predicato così prodotto è IS NOT NULL.
• L’operatore IS può essere utilizzato solo con la parola NULL e
NOT NULL.
• Esempio: cognome e dipartimento dei dipendenti per i quali è
indicato lo stipendio.
SELECT Cognome, Dipartimento
FROM Impiegati
WHERE Stipendio IS NOT NULL;
Ordinamenti
• A differenza del modello relazionale, in cui
le tuple non sono ordinate, le righe di una
tabella possono esserlo - anche se solo al
momento della presentazione all’utente.
• Talvolta la possibilità di ordinare il risultato
di un’interrogazione è importante. Ad
esempio, se si vogliono gli stipendi in
ordine dal minore al maggiore.
Ordinamenti
SQL mette a disposizione la clausola ORDER BY
L’ordinamento può essere:
• Crescente (ASC) - Le stringhe dalla A alla Z, e i
numeri dal più piccolo al più grande e i valori Null,
compaiono all’inizio della sequenza;
• Decrescente (DESC) - Le stringhe dalla Z alla A, e i
numeri dal più grande al più piccolo e i valori Null,
compaiono alla fine della sequenza.
• L’ordinamento è crescente per default
Esempi:
1.
SELECT Cognome, Stipendio
FROM Impiegati
WHERE Dipartimento = 10
ORDER BY Stipendio DESC;
2.
SELECT Cognome, Stipendio
FROM Impiegati
WHERE Dipartimento = 10
ORDER BY Cognome;
Esempi:
3. SELECT Cognome, Stipendio
FROM Impiegati
WHERE Dipartimento = 10
ORDER BY Stipendio DESC, Cognome;
Esercizi
1. Visualizzare in ordine alfabetico i nomi di
tutti gli impiegati;
2. Visualizzare in ordine alfabetico i nomi di
tutti gli impiegati del livello 7;
3. Visualizzare in ordine alfabetico i nomi di
tutti gli impiegati del dipartimento N. 20;
4. Visualizzare il cognome e lo stipendio
degli impiegati, in ordine decrescente in
base allo stipendio;
Esempio:
Query: per ogni dipartimento, trovare la somma
degli stipendi
 Per prima cosa occorre selezionare le
informazioni di interesse, ovvero il
dipartimento e gli stipendi;
 poi calcoliamo la somma degli stipendi
raggruppati per il dipartimento;
Osservazione:
Questa è la codifica?
SELECT Dipartimento, SUM(Stipendio)
FROM Impiegati;
L’SQL non consente di mischiare nel comando
Select attributi e funzioni di aggregazione a meno
che non si usi la clausola GROUP BY
La clausola GROUP BY permette di potenziare
ulteriormente le interrogazioni sulle tabelle,
consentendo di raggruppare le righe di una tabella
in base ai valori di uno o più attributi, potendo così
applicare le funzioni di aggregazione ai singoli
gruppi di righe.
Codifica:
Query: per ogni dipartimento, trovare la somma
degli stipendi
SELECT Dipartimento, SUM(Stipendio) as
TotaleStip
FROM Impiegati
GROUP BY Dipartimento;
GROUP BY
• La clausola GROUP BY,
permette di raggruppare
un insieme di righe
aventi lo stesso valore
nelle colonne indicate
(Dipartimento).
• L’opzione produce una
riga di risultati per ogni
raggruppamento.
Dipartimento
Totalestip
10
13446
20
7441,5
30
5142,5
Raggruppamenti
Le funzioni di aggregazione vengono applicate
ad un insieme di righe restituendo un solo
valore, come per esempio la somma di tutti gli
stipendi dei dipendenti.
• Spesso esiste l’esigenza di applicare funzioni di
aggregazione distintamente a particolari
sottoinsiemi delle righe di una tabella
• In altre parole si possono sintetizzare i valori per
classi omogenee, secondo opportuni criteri di
raggruppamento.
Esempio 2:
SELECT Livello, SUM(Stipendio) AS TotaleStipLiv
FROM Impiegati
GROUP BY Livello;
Livello
TotaleStipLiv
3
4
5
6
7
4525
7986
6594,5
5263,5
2750
Esempio 3:
SELECT Dipartimento As Dip, COUNT(*) AS
NumImpDip, AVG(Stipendio) AS MediaStipDip
FROM Impiegati
GROUP BY Dipartimento;
Dip
NumImpDip
MediaStipDip
10
6
2241
20
4
1860,375
30
4
1557,875
Esempio 4:
SELECT Dipartimento AS Dip, COUNT(*) AS
NImpDip, AVG(Stipendio) AS MStipDipL4
FROM Impiegati
WHERE Livello=4
GROUP BY Dipartimento;
Dip
NImpDip
10
20
30
MStipDipL4
2
1
2
1754,5
1512,5
1482,25
Esercizi
1. Visualizzare la media degli stipendi per ogni livello;
2. Visualizzare lo stipendio massimo e quello minimo per
ogni dipartimento;
3. Visualizzare il numero dei dipendenti per ogni livello .
Condizioni sui gruppi
• La struttura del comando SELECT con
raggruppamenti può essere ulteriormente
ampliata con la clausola HAVING con la
quale è possibile sottoporre al controllo di
una o più condizioni i gruppi creati con la
clausola GROUP BY. In altre parole si può
utilizzare la clausola HAVING quando le
condizioni sono però da calcolare sui
raggruppamenti di tuple.
Esempio:
SELECT Dipartimento As Dip,
SUM(Stipendio) As SommaStip
FROM Impiegati
GROUP BY Dipartimento
HAVING SUM(Stipendio) > 10000;
Dip
10
SommaStip
13446
WHERE o HAVING?
Per decidere se specificare le condizioni nella
clausola WHERE o tramite HAVING, la regola è
semplice:
 Se bisogna utilizzare una funzione di
aggregazione, significa che la condizione
concerne gli insiemi di tuple: HAVING
 In caso contrario: WHERE
Esempio:
I dipartimenti per cui la media degli stipendi degli
impiegati del livello 4 è superiore a 1500 euro.
Soluzione:
SELECT Dipartimento AS Dip, AVG(Stipendio) AS
MediaStip
FROM Impiegati
WHERE Livello = 4
GROUP BY Dipartimento
HAVING AVG(Stipendio) > 1500;
Dip
MediaStip
10
1754,5
20
1512,5
Riassumendo:
SELECT Lista_Attributi_o_Espressioni
FROM Lista_Tabelle
[WHERE Condizioni_Semplici]
[GROUP BY
Lista_Attributi_Di_Raggruppamento]
[HAVING Condizioni_Aggregate]
[ORDER BY Lista_Attributi_Di_Ordinamento]