SQL (Structured Query Language)

Un corso completo sul linguaggio SQL (Structured Query Language): definizioni, esempi ed esercizi

Subquery SQL

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:

DIPENDENTI
NOME DIVISIONE STIPENDIO GIORNI_MUTUA FERIE_GODUTE
ROSSI VENDITE 2 000 000 33 5
BIANCHI VENDITE 2 100 000 1 0
BRUNI RICERCA 3 300 000 0 9
VERDI ACQUISTI 1 800 000 32 20
GIALLI RICERCA 4 800 000 0 0
NERI RICERCA 3 400 000 2 1
MANCINI AMMINISTRAZIONE 2 400 000 9 24
MARCHETTI VENDITE 2 000 000 99 12

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:

ISCRITTI
NOME MATRICOLA
GIOVANNI 1
ANTONIO 21
RICCARDO 9

 

FAMILIARI
NOME PARENTELA MATRICOLA_DEL_PARENTE_ISCRITTO ETA
GIULIA FIGLIA 21 5
MARIA MOGLIE 21 35
RUGERO FIGLIO 1 21

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);

NOME MATRICOLA
ANTONIO 21
GIOVANNI 1

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’);

NOME MATRICOLA
ANTONIO 21

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:

subquery

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

DIPENDENTI
NOME DIVISIONE STIPENDIO GIORNI_MUTUA FERIE_GODUTE
ROSSI VENDITE 2 000 000 33 5
BIANCHI VENDITE 2 100 000 1 0
BRUNI RICERCA 3 300 000 0 9
VERDI ACQUISTI 1 800 000 32 20
GIALLI RICERCA 4 800 000 0 0
NERI RICERCA 3 400 000 2 1
MANCINI AMMINISTRAZIONE 2 400 000 9 24
MARCHETTI VENDITE 2 000 000 99 12

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’);

NOME DIVISIONE
ROSSI VENDITE
BIANCHI VENDITE
BRUNI RICERCA
VERDI ACQUISTI
GIALLI RICERCA
NERI RICERCA
MANCINI AMMINISTRAZIONE
MARCHETTI VENDITE

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.

PERSONE AUTO
NOME PATENTE TARGA PROPRIETARIO
ANTONIO 123 VT AC73949 156
GIOVANNI 156 ROMA J1003 172
ARTURO 172 MI GH3434 300
NA G666223 301

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

ISCRITTI
NOME MATRICOLA
GIOVANNI 1
ANTONIO 21
RICCARDO 9

 

FAMILIARI
NOME PARENTELA MATRICOLA_DEL_PARENTE_ISCRITTO ETA
GIULIA FIGLIA 21 5
MARIA MOGLIE 21 35
RUGERO FIGLIO 1 21

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.

MARCHE   MODELLI
COD_CASA NOME_AUTO
NOME_CASA COD_CASA
STATO TIPO
CITTA CILINDRATA

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

Autore: Art Net

Condividi questo articolo su

15 Commenti

  1. SAlve, quindi non esiste una funzione come p.e. in Excel, che estragga alcuni caratteri a destra o sinistra?
    Ho provato cn right(nome, 3) ma non lo riconosce

    Rispondi al Commento
  2. SELECT COUNT (*)
    Buongiorno

    FROM IMPIEGATO
    WHERE DIPARTIMENTO = ‘PRODUZIONE’

    genera il seguente errore:
    “The data types text and varchar are incompatible in the equal to operator”.

    Invece, sostituendo con WHERE DIPARTIMENTO LIKE ‘PRODUZIONE’, la query funziona.

    Rispondi al Commento
  3. Ho imparato alcune cose buone qui. Certamente merita il bookmarking per la rivisitazione.

    Mi chiedo quanti sforzi hai impostato per creare questo tipo di fantastico sito informativo.

    Rispondi al Commento
  4. guida molto interessante grazie se ci fossero anche le istruzioni per creare la tabella sarebbe completa secondo me

    Rispondi al Commento
  5. Grazie per la guida. Semplice, interessante ed utile.
    Vorremmo segnalare due cose. Un errore ed un dubbio.
    Errore:
    SELECT DIVISIONE, AVG(STIPENDIO)
    FROM DIPENDENTI
    HAVING AVG(STIPENDIO) > 2200000;

    manca la “group by DIVISIONE” prima della HAVING.

    Di conseguenza il dubbio sul punto 6.
    HAVING non è necessario che venga posta dopo la clausola GROUP BY.
    Sicuri?

    Rispondi al Commento
  6. Guida Molto utile, segnalo un errore nella query 9, capitolo 2, delle soluzioni:
    PrezzoIngrosso è moltiplicato * 1,5 ma si chiedeva un aumento del 15%, ciò significa moltiplicare * 1,15

    Rispondi al Commento
  7. Veramente complimenti, una guida dettagliata, semplice e adatta a tutti 🙂

    FortyZone

    Rispondi al Commento
  8. Davvero complimenti ragazzi. La prima guida che per chi parte da zero è manna dal cielo…PERFETTA!!

    Rispondi al Commento
  9. nel punto 8 la soluzione non dovrebbe essere con HAVING MIN(IMPORTO) > 400 000; cioè: SELECT BENEFICIARI FROM ASSEGNI GROUP BY BENEFICIARIO HAVING MIN(IMPORTO) > 400 000;

    Rispondi al Commento
  10. un ottimo Manuale unico errore (se vogliamo chiamarlo cosi) i tipi di dati Char(5) number(5,2) cosa sono e come si usano

    Rispondi al Commento
  11. Complimenti ottimo sito! fatto davvero bene. Grazie!

    Rispondi al Commento

Invia commento

Il tuo indirizzo email non sarà pubblicato.