Capitolo 6

Subquery

Se siamo in grado di padroneggiare con la maggior parte delle ‘sintassi’ viste nei capitoli precedenti, siamo a buon punto e possiamo realizzare interrogazioni molto complesse. Sebbene ciò però, è facile incappare in casi in cui non è possibile estrapolare i dati in maniera immediata e semplice. Per far fronte a situazione di questo tipo SQL ci mette a disposizione un altro potente strumento sintattico: la subquery.
Essa è una query che sta all’interno di un’altra interrogazione. La query interna passa i risultati alla query esterna che li verifica nella condizione che segue la clausola WHERE; vediamo i vari tipi di subquery.

Subquery che ci restituiscono un solo valore

Per gli esempi di questo paragrafo si farà riferimento alla tabella DIPENDENTI:

Vogliamo conoscere il nome dei dipendenti le cui ferie godute superino la media delle ferie godute da tutti. A qualcuno potrebbe venire in mente di scrivere una query di questo tipo:

SELECT NOME
FROM DIPENDENTI
WHERE FERIE_GODUTE > AVG(FERIE_GODUTE);

ma il risultato che si otterrebbe è un messaggio di errore; infatti non è possibile far seguire la calusola WHERE da funzioni di gruppo. Facciamo finta di fare un altro esperimento:

SELECT NOME
FROM DIPENDENTI
HAVING FERIE_GODUTE > AVG(FERIE_GODUTE);

anche in questo caso quello che si otterrebbe è un messaggio di errore: non è possibile usare la clausola HAVING in espressioni dove non compare la clausola GROUP BY e non è possibile, nella nostra interrogazione, eseguire raggruppamenti, dunque dobbiamo per forza usare dopo la clausola WHERE una subquery.

SELECT NOME
FROM DIPENDENTI
WHERE FERIE_GODUTE > (SELECT AVG(FERIE_GODUTE)
                                              FROM DIPENDENTI);

NOME
----------
BRUNI
VERDI
MANCINI
MARCHETTI

Sapendo a priori che il valore medio delle ferie godute da ogni dipendente è pari a 8,875 giorni possiamo verificare che l’espressione scritta è corretta e estrapola esattamente i dati che ci interessavano.

È evidente che il risultato della subquery è un unico valore; infatti non è possibile, con questo tipo di sintassi, estrapolare dalla subquery più di un valore e non è possibile usare le clausole GROUP BY e HAVING. Ricapitolando elenchiamo delle regole valide per l’utilizzo di questo tipo di subquery:

• La subquery deve restituire un unico valore
• Nella subquery non possono apparire le clausole GROUP BY e HAVING
• La subquery deve comparire alla destra dell’operatore di confronto
• Non si possono confrontare due subquery (conseguenza della regola precedente).

Subquery con IN

Questo operatore ci consente di estrapolare dalla subquery non un solo valore, ma più valori da cui verrà verificata la corrispondenza. Cerchiamo di capire con un esempio questa sintassi:

Nella tabella ISCRITTI sono presenti gli associati ad un club di cacciatori, nella tabella FAMILIARI sono registrati gli appartenenti alla famiglia di ogni associato. Vogliamo visualizzare gli associati che hanno almeno un famigliare:

SELECT *
FROM ISCRITTI
WHERE MATRICOLA
IN
(SELECT MATRICOLA_DEL_PARENTE_ISCRITTO FROM FAMILIARI);

Come possiamo vedere sono stati estrapolati solo i nominativi Antonio e Giovanni, gli unici che hanno familiari, dunque la subquery (quella tra parentesi) estrapola una serie di matricole le quali la dove c’è corrispondenza con le matricole della prima select la condizione è verificata. Adesso vogliamo visualizzare gli associati che hanno uno o più figlie.

SELECT *
FROM ISCRITTI
WHERE MATRICOLA
IN
    (SELECT MATRICOLA_DEL_PARENTE_ISCRITTO
    FROM FAMILIARI
    WHERE PARENTELA = 'FIGLIA');

Il ‘meccanismo’ che abbiamo usato è simile a quello della query precedente, con la differenza che abbiamo aggiunto una ulteriore condizione nella subquery. Possiamo aggiungere tante ulteriori condizioni quante ne servono; addirittura considerando una subquery come una query qualsiasi, nessuno ci impedisce di confrontare nella condizione i valori estrapolati da ‘subsubquery’. Vediamo nel prossimo paragrafo di comprendere meglio quanto detto.

Subquery annidate

Con il termine annidate si identificano quelle query che si trovano all’interno di subquery:

SELECT nome campi
FROM nome tabella
WHERE (SUBQUERY condizione (SUBQUERY condizione (SUBQUERY )));

nello schema soprastante abbiamo inserito tre subquery nella prima select una dentro l’altra come se fossero scatole cinesi, ma avremo potuto inserirne anche più. La potenza di una simile struttura è notevole anche se la sua complessità richiede nella sua applicazione una particolare attenzione e tecnica di ‘costruzione’. Cercheremo con un esempio di comprendere questo tipo di struttura sintattica. La struttura sottostante rappresenta il database che conserva le informazioni inerenti all’attività che andremo ad analizzare:

Si ha una azienda che noleggia auto. L’azienda opera su tutto il territorio nazionale tramite agenzie presenti nelle maggiori città. Le automobili non possono essere associabili alle singole agenzie e il loro costo di noleggio dipende oltre che dalla categoria dell’auto (utilitaria, sport, gran turismo, ecc.) anche dall’agenzia da dove è stata noleggiata: una automobile noleggiata all’agenzia di Milano costa di più della stessa automobile noleggiata tramite l’agenzia di Catania.

Nello schema si vedono tre tabelle; la tabella Agenzie è associata alla tabella Auto tramite la tabella Unione. Questo tipo di struttura è necessaria per far fronte al tipo di relazione, molti a molti, che si ha tra una agenzia e un’auto. Infatti la stessa auto viene associata a tutte le agenzie e ad una agenzia associamo tutte le auto; in questo modo per mezzo dell’attributo tariffa sappiamo il costo di ogni auto noleggiata per ogni agenzia.

Supponiamo ora che vogliamo conoscere il nome delle agenzie che applicano una tariffa inferiore alle 100.000 di lire per automobili di categoria sport.

Scomponendo il problema in più moduli che poi risolveremo con delle singole select, arriveremo alla soluzione in modo semplice:

Iniziamo dalla selezione delle automobili di tipo sport:

SELECT TARGA
FROM AUTO
WHERE CATEGORIA = ‘sport’ ;

Questa query la chiameremo Q1.

Continuiamo selezionando le tariffe che soddisfano le condizioni del quesito:

SELECT ID_AGENZIA
FROM UNIONE
WHERE TARGA IN (Q1)
AND TARIFFA < 100.000;

Questa query la chiamiamo Q2.

A questo punto abbiamo a disposizione i codici delle agenzie che soddisfano il nostro quesito, ma non abbiamo ancora a disposizione i nomi di tali agenzie. Inoltre i codici delle agenzie sono inutilmente ripetuti più volte, dunque la successiva query che soddisfa completamente il quesito è:

SELECT NOME_AGENZIA
FROM AGENZIE
WHERE ID_AGENZIA IN (Q2);

Vediamo ora la query scritta in modo completo:

SELECT NOME_AGENZIA
FROM AGENZIE
WHERE ID_AGENZIA IN
           (SELECT ID_AGENZIA
            FROM UNIONE
            WHERE TARGA IN
                          (SELECT TARGA
                            FROM AUTO
                            WHERE CATEGORIA = 'SPORT';)
             AND TARIFFA < 100000;);

Considerando il modo in cui abbiamo costruito questa interrogazione, possiamo renderci conto che la maniera migliore per effettuare la lettura e comprensione di una query di questo tipo, è iniziare ad analizzare le subquery più interne e man mano passare a quelle più esterne. Questa considerazione è importante ed è valida anche per la scrittura della query. In quest’ultimo caso però, decidere quale sarà la subquery più interna è difficile, comunque sia dobbiamo affidarci non tanto a delle eventuali regole assiomatiche, ma alla nostra logica che se utilizzata con rigore non può tradirci.

EXISTS

Vogliamo estrapolare tutti i dati di NOME e DIVISIONE da questa tabella solo se e soltanto se è presente il nominativo Neri:

SELECT *
FROM DIPENDENTI
WHERE EXISTS
         (SELECT *
          FROM DIPENDENTI
          WHERE NOME = 'NERI');

Da questa interrogazione i dati vengono estrapolati perché essendo presente la stringa ‘NERI’ la parola chiave EXISTS restituisce il valore true. Nel caso la stringa ‘NERI’ non fosse stato presente la parola chiave EXISTS avrebbe restituito false e in questo caso l’interrogazione non avrebbe visualizzato nessun valore.

SOME, ANY, ALL

Questi tre operatori svolgono funzioni simili alle parole chiave IN e EXSIST. Si consiglia di consultare altro testo per approfondire l’argomento.

ESERCIZI (capitolo 6)

1) La seguente query è giusta? e se errata perché?

SELECT COGNOME, NOME, MATRICOLA, ETA
FROM DIPENDENTI
WHERE (SELECT AVG(ETA) FROM DIPENDENTI) < ETA;

2) La seguente query è giusta? e se errata perché?

SELECT MATRICOLA
FROM DIPENDENTI
WHERE ETA > (SELECT ETA FROM DIPENDENTI);

3) Dalla tabella PERSONE scrivere una interrogazione che estrapoli tutti i dati delle persone che possiedono almeno un’auto. Usare la tecnica delle subquery.

4) Nella tabella ISCRITTI sono presenti gli associati ad un club, nella tabella FAMILIARI sono registrati gli appartenenti alla famiglia di ogni associato;

scrivere una select che dalle tabelle ISCRITTI e FAMILIARI, ci visualizzi gli iscritti che non hanno
nessun familiare.

5) La tabella MARCHE contiene dati riguardanti le industrie costruttrici di automobili; la tabella MODELLI
contiene dati riguardanti i vari modelli di auto esistenti. I nomi dei campi in neretto rappresentano le
chiavi primarie.

Scrivere una select che restituisca come risultato il codice della casa e il nome della casa che produce più di due modelli di automobili di tipo sport. Utilizzare la ‘tecnica’ delle subquery.

6) Data una tabella STUDENTI(matricola, esame, voto), scrivere una query che abbia come risultato la
matricola degli studenti che hanno effettuato più esami dello studente matricola 23.

Soluzioni agli esercizi del Capitolo 6

INDICE

pag. 11 di 20