Ordinamento
21/12/2015
Ordinamento del risultato
E‘ possibile dare un ordinamento del risultato di una select.
L‘ordinamento si può effettuare in base a un attributo, e
può essere crescente o decrescente. La sintassi è la
seguente:
SELECT lista_attributi
FROM nome_tabella
WHERE condizioni
ORDER BY Attributo [ASC/DESC]
Le righe vengono ordinate in base al campo Attributo in
maniera crescente o decrescente secondo se è data la
specifica ASC o DESC. ASC è il default. Secondo il tipo
dell’attributo, l’ordinamento è quello più naturale su quel
dominio.
21/12/2015
Esempio
Nome e reddito delle persone con meno di trenta anni
in ordine alfabetico
SELECT nome, reddito
FROM persone
WHERE eta < 30
ORDER BY nome
21/12/2015
SELECT nome, reddito
FROM persone
WHERE eta < 30
Persone
Nome
Andrea
Aldo
Filippo
21/12/2015
SELECT nome, reddito
FROM persone
WHERE eta < 30
ORDER BY nome
Persone
Reddito
21
15
30
Nome
Aldo
Andrea
Filippo
Reddito
15
21
30
Doppio ordinamento
Si può voler ordinare i dati in base a una certa chiave
(attributo) e poi ordinare i dati che che coincidono su
quella chiave in base a un’altra chiave (attributo).
21/12/2015
Ordinamento, Esempio
Ordinare gli studenti in base al loro cognome, in modo
tale che due persone con lo stesso cognome siano
ordinate in base al nome, e persone con lo stesso nome
e cognome siano ordinate in base all’ordine inverso
della data di nascita
Select *
From Studenti
Order by cognome [asc], nome [asc] , nascita desc
21/12/2015
Esercizi
Esercizio 1: Ordinare i negozi di Roma in base ai nomi.
Select * From Negozi
Where neg_citta=‘Roma’
Order by neg_nome
Esercizio 2: Ordinare i componenti per costi decrescenti
Select * From Componenti
Order by com_costo desc
Esercizio 3: Ordinare i componenti per costi decrescenti e, a parità
di costo, per codici crescenti
Select * From componenti
Order by com_costo desc, com_cod asc
21/12/2015
Operatori Aggregati
21/12/2015
Operatori aggregati


Nella target list possiamo avere anche espressioni che
calcolano valori a partire da insiemi di ennuple e che
restituiscono una tabella molto particolare, costituita
da un singolo valore scalare.
SQL-2 prevede 5 possibili operatori di aggregamento:






Conteggio (COUNT),
Minimo (MIN),
Massimo(MAX),
Media (AVG),
Somma (SUM)
Gli operatori di aggregamento NON sono
rappresentabili in Algebra Relazionale
21/12/2015
Operatori aggregati: COUNT
COUNT restituisce il numero di righe della tabella o il
numero di valori di un particolare attributo
Esempio: Il numero di figli di Franco:
SELECT count(*) as NumFigliDiFranco
FROM Paternita
WHERE Padre = 'Franco'
l’operatore aggregato (count) viene applicato al risultato
dell’interrogazione:
SELECT *
FROM Paternita
WHERE Padre = 'Franco'
21/12/2015
Paternità
Padre
Sergio
Luigi
Luigi
Franco
Franco
Figlio
Franco
Olga
Filippo
Andrea
Aldo
SELECT *
FROM Paternita
WHERE Padre = 'Franco'
Padre
Franco
Franco
Figlio
Andrea
Aldo
count
SELECT count(*)
as NumFigliDiFranco
FROM Paternita
WHERE Padre = 'Franco'
21/12/2015
NumFigliDiFranco
2
(*), ALL e DISTINCT
Mediante le specifiche (*), ALL e DISTINCT è possibile
contare
(*): tutte le righe selezionate;
ALL: tutti i valori non nulli delle righe selezionate;
DISTINCT: tutti i valori non nulli distinti delle righe
selezionate.
Se la specifica viene omessa, il default è ALL.
21/12/2015
EsamiBD
Studente
BD
LBD
012345
27
NULL
032456
25
23
035221
NULL NULL
033445
28
30
032441
NULL
30
Contare il numero di studenti iscritti al corso di BD e
Laboratorio
SELECT count(*) as “NumStud”
FROM EsamiBD
Contare il numero di esami di BD superati positivamente
SELECT count([ALL] BD) “ContaBD”
FROM EsamiBD
Numero di voti distinti dati all’esame di LBD
SELECT count(distinct LBD) “ContDistLBD”
FROM EsamiBD
NumStud
5
ContaBD
3
ContDistLBD
2
Esercizi
Esercizio 1: Calcolare il numero di codici presenti nella
tabella Articoli; spiegare perché in questo caso non ha
senso applicare le specifiche ALL e DISTINCT
SELECT count(art_cod)
FROM Articoli
Art_cod è una chiave primaria
Esercizio 2: calcolare il numero totale delle categorie,
basandosi sulla tabella categorie.
SELECT count(*)
FROM Categorie
21/12/2015
Esercizi
Esercizio 3: Calcolare il numero di categorie presenti
nella tabella Articoli
SELECT count (Distinct cat_cod)
FROM Articoli
Esercizio 4: Calcolare il numero di categorie presenti
nella tabella Articoli, per cui il prezzo dell’articolo
corrispondente sia inferiore a 400 euro.
SELECT count (Distinct cat_cod)
FROM articoli
WHERE art_prezzo<400
21/12/2015
Max e Min
Le funzioni MAX e MIN calcolano rispettivamente il
maggiore e il minore degli elementi di una colonna.
Esempio
L’età della persona più anziana nella tabella persone
SELECT max(eta)
FROM Persone
Il più basso dei voti assegnati all’esame di BD
SELECT min(BD)
FROM EsamiBD
21/12/2015
Esercizi
Esercizio 1: Quali sono la massima e la minima quantità di
articoli richiesti in un ordine?
SELECT MAX (ordart_qta)
FROM Ordart
SELECT MIN (ordart_qta)
FROM Ordart
Esercizio 2: Calcolare a quanto ammonterebbe il prezzo
massimo della tabella articoli se vi venisse apportato un
aumento del 12%
SELECT MAX (1,12 * art_prezzo)
FROM Articoli
21/12/2015
Esercizi
Esercizio 3: Determinare qual è il costo minimo di un
articolo, considerato il suo prezzo, l’iva e le spese di
trasporto
SELECT MIN ((1+art_iva/100)*Art_prezzo+art_spese_trasporto)
FROM Articoli
21/12/2015
Sum
La funzione SUM calcola la somma dei valori di una colonna.
Le specifiche ALL e DISTINCT permettono di sommare
tutti i valori non nulli o tutti i valori distinti. Il default in
mancanza di specifiche è ALL.
Esempio:
Calcolare la somma degli stipendi mensili degli impiegati
del settore Produzione.
SELECT SUM (ALL stipendio)
FROM Impiegati
WHERE Dipart=‘Produzione’
21/12/2015
Esercizi
Esercizio 1: Sommare i prezzi di tutti i componenti
SELECT SUM (com_prezzo)
FROM Componenti
Esercizio 2: Sommare i prezzi distinti di tutti i componenti
SELECT SUM (DISTINCT com_prezzo)
FROM Componenti
Esercizio 3: Sommare i prezzi di tutti i componenti
prodotti nel laboratorio dal codice ‘0050’
SELECT SUM (com_prezzo)
FROM Componenti
Where lab_cod=‘0050’
21/12/2015
Esercizi
Esercizio 4: Calcolare la somma dei prezzi dei due armadi
presenti nella tabella Articoli
SELECT SUM (Art_prezzo)
FROM Articoli
Where Art_descrizione LIKE ‘Armadio%’
Esercizio 5: Calcolare il numero totale di articoli ordinati
SELECT SUM (ordart_qta)
FROM Ordart
21/12/2015
AVG
La funzione AVG calcola la media (average) dei valori non
nulli di una colonna. Le specifiche ALL e DISTINCT
servono a calcolare la media fra tutti i valori o tra i valori
distinti. Il default è ALL.
Esempio: Calcolare la media degli stipendi degli impiegati
del dipartimento di Produzione e che hanno meno di 30 anni
SELECT AVG(stipendi)
FROM Impiegati
WHERE Dipart=‘Produzione’ AND eta<30
21/12/2015
Esercizi
Esercizio 1: Calcolare la media delle spese di
trasporto degli articoli
SELECT AVG (Art_spese_trasporto)
FROM Articoli
Esercizio 2: Calcolare la media dei prezzi dei componenti
SELECT AVG (Com_costo)
FROM Componenti
Esercizio 3: Calcolare la media dei prezzi lordi degli
articoli, inclusivi di spese di trasporto (per gli articoli
per cui prezzo, IVA e Spese trasporto sono definite)
Select AVG(Art_prezzo*(1+art_IVA/100)+Art_Spese_trasporto)
FROM Articoli
21/12/2015
Operatori aggregati e target
list
Non è possibile utilizzare in una stessa select una
proiezione su alcuni attributi della tabella
considerata e operatori aggregati sulla stessa tabella.
 un’interrogazione scorretta:
SELECT nome, max(reddito)
FROM persone

di chi sarebbe il nome? La target list deve essere
omogenea. E’ corretta invece la seguente:
SELECT min(eta), avg(reddito)
FROM persone
21/12/2015
Raggruppamenti
21/12/2015
Group by
A volte può essere richiesto di calcolare operatori aggregati non
per l’intera tabella, ma raggruppando le righe i cui valori coincidono
su un certo attributo.
Per esempio, vogliamo sapere la media degli stipendi degli impiegati
per ogni dipartimento. In tal caso si può utilizzare la clausola
GROUP BY.
SELECT Dipart, AVG(stipendio)
FROM Impiegati
GROUP BY Dipart
Nella colonna AVG(stipendio)
c’è la media di tutti gli
stipendi degli impiegati di ogni
reparto
21/12/2015
Dipart
AVG(stipendio)
Produzione
1330
Amministrazione
1505
Distribuzione
1810
Direzione
2500
Semantica degli operatori di
raggruppamento (1)

La query è innanzitutto eseguita senza operatori aggregati e
senza GROUP BY:
SELECT Dipart, stipendio
FROM Impiegati
21/12/2015
Dipart
Stipendio
Amministrazione
1560
Produzione
1340
Amministrazione
1450
Distribuzione
1750
Produzione
1400
Direzione
2500
Produzione
1250
Distribuzione
1870
Semantica degli operatori di
raggruppamento (2)
Quindi il risultato è diviso in sottoinsiemi aventi gli stessi valori per gli
attributi indicati nel GROUP BY (Dept nel nostro caso)
Quindi l’operatore aggregato è calcolato su ogni sottoinsieme:
Dipart
Stipendio
Amministrazione
1560
Amministrazione
1450
Distribuzione
1750
Distribuzione
1870
Direzione
Dipart
AVGstipendio
Amministrazione
1505
Distribuzione
1810
2500
Direzione
2500
Produzione
1340
Produzione
1330
Produzione
1400
Produzione
1250
21/12/2015
AVG
Esercizi
Esercizio 1: Calcolare il prezzo più alto degli articoli per
ciascuna categoria.
SELECT cat_cod, MAX (art_prezzo)
FROM Articoli
GROUP BY cat_cod
Esercizio 2: Calcolare il numero di articoli appartenenti a
ciascuna categoria
SELECT cat_cod, count(art_cod)
FROM Articoli
GROUP BY cat_cod
21/12/2015
Esercizi
Esercizio 3: Calcolare il numero di componenti di ciascun
articolo.
SELECT art_cod, sum(compart_qta)
FROM compart
GROUP BY art_cod
Esercizio 4: Calcolare quanti articoli sono richiesti in
ciascun ordine
SELECT ord_cod, SUM(ordart_qta)
FROM ordart
GROUP BY ord_cod
21/12/2015
Condizioni sui gruppi, clausola
HAVING


Si possono applicare condizioni sul valore aggregato per ogni
gruppo. Si può realizzare mediante la clausola HAVING.
Esempio: I dipartimenti la cui media degli stipendi è maggiore
di 1700 euro
Select dipart, AVG(stipendio)
FROM Impiegati
Group by Dipart
HAVING AVG(stipendio)>1700
21/12/2015
Dipart
AVG(stipendio)
Distribuzione
1810
Direzione
2500
Dipart
AVG(stipendio)
Amministrazione
1505
Distribuzione
1810
Direzione
2500
Produzione
1330
HAVING AVG(stipendio)>1700
Where o Having
In generale se la condizione coinvolge un attributo, si
usa la clausola where, mentre se coinvolge un operatore
aggregato si usa la clausola having.
EsamiBD (matricola,nome, cognome, città, voto, età)
Le città per cui la media dei voti dei suoi studenti di
meno di 21 anni è maggiore di 26
SELECT città, avg(voto)
FROM EsamiBD
WHERE eta < 21
GROUP BY città
HAVING avg(voto) > 26
21/12/2015
Sintassi, riassumiamo
SelectSQL ::=
select ListaAttributi O Espressioni
from ListaTabelle
[ where CondizioniSemplici ]
[ group by ListaAttributiDiRaggruppamento ]
[ having CondizioniAggregate ]
[ order by ListaAttributiDiOrdinamento ]
21/12/2015
Esercizio 1
Quanti laboratori sono presenti in ogni città, escludendo
le città con un solo laboratorio?
Select lab_citta, count(lab_cod) as “numero laboratori”
From Laboratori
Group by lab_citta
Having count(lab_cod)>1
21/12/2015
Esercizio 2
Calcolare da quanti componenti è costituito ciascun
articolo, escludendo gli articoli costituiti da meno di
10 componenti.
Select art_cod, sum(com_cod)
From Compart
Group by art_cod
Having sum(com_cod)>10
21/12/2015
Esercizio 3
Calcolare da quanti tipi di componenti è costituito
ciascun articoli, escudendo gli articoli costituiti
da meno di 5 tipi di componenti
Select art_cod, count(com_cod)
From Compart
Group by art_cod
Having count (com_cod)>5
21/12/2015
Esercizio 4
Calcolare da quanti componenti è costituito ciascun
articolo, escludendo gli articoli costituiti da meno di
5 tipi di componenti.
Select art_cod, sum(compart_qta)
From Compart
Group by art_cod
Having count (com_cod)>5
21/12/2015
Esercizio 5
Per ogni categoria di mobili, calcolare la media dei
prezzi degli articoli le cui spese di trasporto sono
superiori a 50 euro, e la cui media non supera i 400
euro
Select cat_cod, avg(art_prezzo)
From Articoli
Where art_spese_trasporto>50
Group by cat_cod
Having AVG(art_prezzo)<400
21/12/2015
Esercizio 6
Calcolare la media dei prezzi delle categorie che
compendono almeno un articolo per il quale il campo
art_prezzo è inferiore a 300 euro e ordinare il risultato
secondi i valori della media.
Select cat_cod, avg(art_prezzo)
From Articoli
Group by cat_cod
Having min(art_prezzo)<300
Order by avg(art_prezzo)
21/12/2015