4.3 Query con aggregazioni
4.3.2 Operatore di aggregazione
Per l’utilizzo degli operatori di aggregazione ci basiamo sull’istanza della base di datiGestione-risultati-esami. DaGestione-risultati-esamiestrarremo le seguenti informazioni, gi`a elencate nell’introduzione di questo capitolo:
“Si vogliono gestire i risultati di esami, sostenuti da studenti, in diversi corsi. Tipiche informazioni da estrarre sono:
1. . . .
2. La matricola dei corsi e la relativa media dei voti;
3. La matricola di ogni studente, contenuta in un dato intervallo, e la relativa media dei voti;
4. Le matricole, contenute in un dato intervallo, che individuano studenti la cui media sia maggiore di un dato valore;
5. . . . ”
Query CorsoEMedia
Definiamo la query CorsoEMedia che, alla matricola di ogni corso, associa la media di tutti i voti, assegnati nel tempo, agli studenti che hanno superato l’esame del corso considerato. Il risultato atteso di CorsoEMedia `e:
118 CAPITOLO 4. ESTRAZIONE DI INFORMAZIONE
in cui il significato di ogni campo `e evidente dal nome.
Per progettare una query che produca tale risultato occorre immaginare che debbano essere compiuti i seguenti passi:
1. riorganizzare le istanze della tabella Registrare in gruppi. Ogni gruppo `e individuato da istanze con lo stesso valore del campo Corso:
4.3. QUERY CON AGGREGAZIONI 119 2. calcolare la media, relativamente ad ogni gruppo di valori omogenei del campo Corso. Nel caso del valore A00, occorrer`a sommare i 6 voti ad esso corrispondente, per poi dividere il risultato per 6.
I passi evidenziati sono integrati nella definizione di un’unica query CorsoEMedia, definita come segue:
Definizione 18 (Query CorsoEMedia.)
Nella parte inferiore della finestra:
• il passo1 si realizza, usando l’operatore Raggruppamento come valore del campo Formula: in corrispondenza del campo Corso;
• il passo2 si realizza indicando che la funzione aggregata Media va usata su ogni gruppo, quest’ultimo individuato da valori omogenei del campo Corso. Il risultato `e ottenibile specificando il valore Media come valore del campo Formula: in corrispondeza del campo Voto.
Esercizio 65 (Query NomeCorsoNumeroEsami.) Definire una query che, ad ogni corso, individuato per nome, associ il numero di esami per esso registrati.
Query StudenteMatricola<005EMedia
Definiamo la query StudenteMatricola<005EMedia che elenca le matricole de-gli studenti, inferiori al valore 005, associando, a ciascuna di esse, la media di tutti gli esami superati dallo studente con la matricola considerata. Il risultato atteso di StudenteMatricola<005EMedia `e:
120 CAPITOLO 4. ESTRAZIONE DI INFORMAZIONE
in cui il significato di ogni campo `e evidente dal nome.
Per progettare una query che produca tale risultato occorre immaginare che debbano essere compiuti i seguenti passi:
1. selezionare da Registrare solo le istanze i cui valori di Studente siano inferiori al valore 005;
2. riorganizzare le istanze della tabella Registrare in gruppi. Ogni gruppo `e individuato da istanze con lo stesso valore del campo Studente;
3. calcolare la media, relativamente ad ogni gruppo. Nel caso del valore 001, del campo Studente, occorrer`a sommare 3 voti e dividere il risultato per 3.
I passi evidenziati sono integrati nella definizione di un’unica query Studen-teMatricola<005EMedia, definita come segue:
Definizione 19 (Query StudenteMatricola<005EMedia.)
Nella parte inferiore della finestra:
• il passo 1 si realizza usando l’operatore Dove come valore del campo Formula:, in corrispondenza della prima occorrenza del campo Studente.
4.3. QUERY CON AGGREGAZIONI 121 Nota 10 (Uso dell’operatore Dove.)
Nella definizione di una query che sfrutti gli operatori di raggruppamento l’operatore Dove va necessariamente usato nei casi in cui occorra selezio-nare istanze prima di raggrupparle.
• il passo2 si realizza usando l’operatore Raggruppamento come valore del campo Formula:, in corrispondenza della seconda occorrenza del campo Studente;
• il passo3si realizza indicando che la funzione aggregata Media, va usata su ogni gruppo, individuato da valori identici nel campo Studente. Questo si ottiene, automaticamente, specificando il valore Media come valore del campo Formula: in corrispondeza del campo Voto.
Esercizio 66 (Query NomeCorso>LVotoMassimo.) Definire una query che, ad ogni corso, il cui nome inizi con la lettera L, o superiore, associ il massimo voto registrato.
Query StudenteMatricola<005EMedia>26
Definiamo la query StudenteMatricola<005EMedia>26 che elenca le matricole degli studenti, inferiori al valore 005, associando, a ciascuna di esse, la media di tutti gli esami superati dallo studente con la matricola considerata. Dall’elenco, tuttavia, occorre escludere tutti gli studenti la cui media non sia superiore al 26. Il risultato atteso di StudenteMatricola<005EMedia>26 `e:
in cui il significato di ogni campo `e evidente dal nome.
Per progettare una query che produca tale risultato occorre immaginare che debbano essere compiuti i seguenti passi:
1. selezionare da Registrare solo le istanze i cui valori di Studente siano inferiori al valore 005;
2. riorganizzare le istanze della tabella Registrare in gruppi. Ogni gruppo `e individuato da istanze con lo stesso valore del campo Studente;
3. calcolare la media, relativamente ad ogni gruppo. Nel caso del valore 001, del campo Studente, occorrer`a sommare 3 voti e dividere il risultato per 3;
4. selezionare dalle tuple, risultato dai passi precedenti, quelle in cui il valore della media supera il valore 26.
122 CAPITOLO 4. ESTRAZIONE DI INFORMAZIONE
I passi evidenziati sono integrati nella definizione di un’unica query Studen-teMatricola<005EMedia>26, definita come segue:
Definizione 20 (Query StudenteMatricola<005EMedia>26.)
Nella parte inferiore della finestra:
• il passo 1si realizza usando la funzione di selezione Dove come valore del campo Formula:, in corrispondenza della prima occorrenza del campo Studente;
• il passo2 si realizza usando l’operatore Raggruppamento come valore del campo Formula:, in corrispondenza della seconda occorrenza del campo Studente;
• il passo3si realizza indicando che la funzione aggregata Media sia usata su ogni gruppo, individuato da valori omogenei del campo Studente. Questo si ottiene specificando il valore Media come valore del campo Formula: in corrispondeza del campo Voto.
• il passo4`e una selezione per mezzo dell’espressione booleana >¨26¨, usa-to come valore del campo Criteri:, in corrispondenza del campo che contiene le medie dei voti.
Nota 11
`
E fondamentale osservare la differenza tra gli scopi dei passi1e4.
Il passo 1 seleziona le istanze della tabella Registrare prima del raggrup-pamento.
Il passo 4 seleziona le istanze del risultato dopo che sia l’operatore di rag-gruppamento, sia la funzione aggregata Media, sono state applicate alla tabella Registrare.
La differenza sarebbe evidente usando il linguaggio SQL per definire le query che `e pi`u espressivo di quello grafico, detto query by examples (QBE). Per
4.3. QUERY CON AGGREGAZIONI 123
i curiosi, a puro titolo informativo, la query SQL, equivalente ad una query definita nel linguaggio QBE del DBMS di riferimento `e si ottiene con un click sul “pulsante” SQL Visualizzazione SQL, come indicato nella seguente figura:
Esercizio 67 (Query StudenteMatricola<005EMedia>26DuePassi.) Definire una query equivalente a StudenteMatricola<005EMedia>26, ovvero che produca lo stesso risultato, ma che non usi la funzione di selezione Dove come valore del cam-po Formula: per selezionare le tuple di Registrare, prima del raggruppamento. (Suggerimento: usare due query.)
Esercizio 68 (Query CognomeMatricola<005EMedia>26.) Modificare la que-ry soluzione dell’esercizio precedente per elencare i cognomi degli studenti la cui matricola sia inferiore a 005 e la cui media sia superiore a 26.