UDA3 L5 - Hoepli Scuola

Le interrogazioni annidate
lezione
Lezione 5
5
Le interrogazioni
annidate
In questa lezione impareremo...
• a codificare operazioni insiemistiche su tabelle e relazioni
• a eseguire interrogazioni scalari
• a condizionare le subquery con gli operatori di quantificazione
semplice ed esistenziale
■■ Le operazioni insiemistiche
In questa lezione vengono esaminate le interrogazioni nidificate. Esse rappresentano uno strumento valido per effettuare interrogazioni complesse, che non possono essere realizzate con i comandi
SQL visti finora. In pratica, un’interrogazione nidificata (o subquery) non è altro che un’interrogazione che permette il confronto tra uno o più attributi e il risultato di una sottointerrogazione. Di
seguito elenchiamo i vantaggi offerti dall’uso delle subquery.
◗◗ Le subquery permettono interrogazioni strutturate, rendendo in tal modo possibile la separazione
di ogni singola parte significativa del comando SQL.
◗◗ Esse rappresentano un metodo alternativo alle congiunzioni per eseguire operazioni che altrimenti renderebbero necessario l’uso di queste complesse istruzioni.
◗◗ È opinione comune che l’introduzione delle subquery sia stata l’innovazione che ha consentito di
definire SQL un vero e proprio linguaggio strutturato.
Le operazioni insiemistiche vengono anche
chiamate query binarie o “set queries” e permettono di combinare insieme più SELECT
per ottenere un unico output. Si ottengono
concatenando due query SQL tramite operatori insiemistici di unione, intersezione e
differenza. La sintassi generale secondo SQL
standard è la seguente:
è necessario, però, osservare che sia MySQL
sia Access utilizzano il solo operatore UNION,
che permette di unire due tabelle mediante un
campo comune. Infatti, i due operatori insiemistici INTERSECT ed EXCEPT possono essere
sostituiti da interrogazioni equivalenti.
Query esterna SQL { UNION | INTERSECT | EXCEPT [ALL] Query interna SQL }
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
1
UdA 3
Il linguaggio SQL
La funzione di unione può essere espressa mediante la figura che segue, in cui si evidenzia come la
struttura possa essere ciclica, ovvero nidificata; inoltre, per ogni result-set si ottiene la somma con
la successiva.
Le principali caratteristiche dell’operatore UNION sono indicate brevemente di seguito.
◗◗ L’esecuzione di un insieme di più UNION (senza parentesi) avviene in ordine da sinistra verso destra.
◗◗ Le singole SELECT che partecipano alla UNION non possono avere loro clausole ORDER BY; un
unico ORDER BY può essere specificato dopo l’ultima SELECT e viene applicato al risultato finale
delle operazioni di UNION.
◗◗ Le clausole GROUP BY e HAVING possono essere specificate per le singole clausole SELECT, ma
non per il risultato finale.
◗◗ Mediante gli operatori insiemistici si ottiene un’eliminazione dei duplicati e anche delle proiezioni, a meno che non venga usata la clausola ALL.
Esempio14
Vediamo un esempio per comprendere meglio il significato di questo operatore. Utilizziamo le due
tabelle seguenti: nella prima, denominata Maternità, vi sono i nomi delle madri e dei relativi figli,
mentre nella tabella Paternità vi sono i nomi dei padri e dei relativi figli.
Tabella MaternitàTabella Paternità
La query insiemistica può essere vista come un’unione basata sugli attributi esposti nella target list
delle due SELECT. In pratica, vengono unite tutte le tuple delle due tabelle che hanno gli attributi
uguali, sia come tipo che come ordine sequenziale. Tale ordine viene chiamato notazione posizionale. Per esempio, si vogliono conoscere tutti i nomi dei genitori e dei relativi figli mediante la
seguente query:
Select nome_figlio, cognome_figlio, nome_madre, cognome_madre
FROM Maternità
UNION
Select nome_figlio, cognome_figlio, nome_padre, cognome_padre
FROM Paternità
2
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
Le interrogazioni annidate
Lezione 5
Tale interrogazione comprende da una prima query, il cui risultato viene confrontato con le tuple
ottenute dalla seconda. Nelle chermate che seguono sono indicati i passaggi che vengono eseguiti
per ottenere il risultato. Nel primo passaggio abbiamo il risultato della prima query, ovvero i nomi
delle madri con accanto i relativi figli. Nella schermata relativa al secondo passaggio vengono indicati i record della tabella Paternità che sono il risultato della seconda query indicata dopo la clausola
UNION.
 Primo passaggio intermedio:
2 Secondo passaggio intermedio:
Per poter realizzare un’unione è necessario che le due tabelle da unire possiedano lo stesso numero di colonne, e
che i domini degli attributi siano uguali.
L’unione delle due tabelle produce una
nuova tabella che contiene tutte le righe
delle tabelle di partenza, escludendo le
righe doppie. In questo caso non vi sono
righe doppie, per cui si ottiene:
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
3
UdA 3
Il linguaggio SQL
Quali nomi vengono associati agli attributi del risultato? In realtà gli attributi risultanti possiedono
il nome che è stato assegnato dalla prima query.
È quindi buona regola assegnare un nome con alias ai campi risultato. Infatti, nell’esempio precedente si notano alcune incongruenze: per esempio, esistono nomi di padri nella colonna nome_madre.
Vediamo come riscrivere in modo più corretto la query precedente, ottenendo così i campi Alias
e nome genitore al posto di cognome e nome madre:
SELECT nome_figlio, cognome_figlio, nome_madre AS “nome genitore”,
cognome_madre AS “cognome genitore”
FROM Maternità
UNION
SELECT nome_figlio, cognome_figlio, nome_padre, cognome_padre
FROM Paternità
Poniamo di voler conoscere tutti coloro che hanno ancora almeno un genitore.
Per fare questo è sufficiente ricavare un elenco di tutti i figli dalla tabella Maternità, unendolo all’elenco di tutti i figli della tabella Paternità:
SELECT nome_figlio, cognome_figlio
FROM Maternità
UNION
SELECT nome_figlio, cognome_figlio
FROM Paternità;
Ottenendo così: ▶
Un utilizzo di tale clausola è legato alla possibilità di unire, appunto, le tuple risultanti da più query. Per esempio, se vogliamo conoscere il numero di figli complessivo per ciascun genitore, si deve
ricorrere alla clasuola UNION che consente di visualizzare prima il totale dei figli dei padri e poi il
totale di figli delle madri. Vediamo il codice in SQL:
SELECT P.cognome_padre AS cognome, P.nome_padre AS nome, COUNT(*)
AS “totale figli”
FROM Paternità P
GROUP BY P.cognome_padre, P.nome_padre
UNION
SELECT M.cognome_madre, M.nome_madre, COUNT(*) AS “totale figli”
FROM Maternità M
GROUP BY M.cognome_madre, M.nome_madre
4
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
Le interrogazioni annidate
Lezione 5
Con il seguente risultato:
Prova adesso!
APRI IL FILE esempio17
• Utilizzare le operazioni
insiemistiche
• Applicare i raggruppamenti alle
UNION
• Utilizzare GROUP BY
e HAVING
 Crea una query che mostri tutti i cognomi disponibili in ordine crescente.
2 Crea una query che mostri tutti i cognomi presenti che iniziano per “C” o per “V”. Per fare
questo crea quattro interrogazioni unite da UNION e all’interno di alcune di esse utilizza la
clausola WHERE.
 Crea una query che mostri tutti i nomi dei genitori (padri o madri) che hanno più di un figlio;
per fare questo utilizza la clausola HAVING, che selezioni solo i gruppi di cognomi che hanno
contato più di un figlio.
 Confronta la tua soluzione con quella contenuta nel file esempio17_solux
■■ Le query annidate di tipo scalare
L’opportunità di eseguire un’interrogazione (query) secondo un determinato ordine temporale risulta di notevole importanza nello schema relazionale.
Le ◀ subquery scalari ▶ annidate producono un result-set costituito da una sola tupla con un solo
attributo, utilizzabile ovunque sia assegnabile un valore scalare singolo.
◀ Subquery scalari Le subquery scalari rendono le query dinamiche e rispondenti ai dati presenti nel database, piuttosto che a valori esplicitamente impostati nel codice. ▶
Per eseguire due query in modo annidato (una dentro l’altra, per intenderci) è necessario prima di
tutto conoscere la sintassi generale delle SELECT nidificate.
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
5
UdA 3
Il linguaggio SQL
Tale forma è sintetizzata dallo schema che segue, dove si evidenziano le parti che la compongono:
prima la query esterna, poi l’operatore relazionale e quindi la subquery o query annidata.
query esterna
SELECT ...
FROM ...
WHERE espressione
Operatore
=
!=
>
<
!>
!<
>=
<=
query interna:
restituisce solo un attributo
(SELECT ...
FROM ...
WHERE espressione)
Il suo funzionamento è abbastanza semplice. La query che si trova tra le parentesi tonde, chiamata
anche select interna o query annidata, viene eseguita per prima; quindi, sulla base del risultato
ottenuto, viene eseguita l’altra query, chiamata anche select esterna o query principale. È necessario inoltre ricordare che le sottointerrogazioni non possono contenere operatori insiemistici. La
procedura risulterà più chiara con qualche esempio. Si vogliono conoscere i nomi delle madri che
hanno figlie con lo stesso nome delle figlie di “Colombo Piero”. L’interrogazione è quindi formata da
una query interna che cerca il nome dei figli di “Colombo Piero”, e da una esterna che confronta il
nome dei figli delle madri con quello trovato nella query più interna.
Vediamo il codice SQL:
SELECT cognome_madre, nome_madre
FROM Maternità
WHERE nome_figlio =
( SELECT nome_figlio
FROM Paternità
WHERE cognome_padre=”Colombo” and nome_padre=”Piero”);
Query esterna
Query interna
In quest’altro esempio si vede come è possibile usare una query annidata per verificare quali padri
hanno avuto meno figli di “Corelli Lina”:
SELECT cognome_padre, nome_padre, COUNT(*) AS figli
FROM paternità
GROUP BY cognome_padre, nome_padre
HAVING COUNT(*)<
( SELECT COUNT(*)
FROM Maternità
WHERE cognome_madre=”Corelli” and nome_madre=”Lina”);
L’operazione di nidificazione può anche essere effettuata in riferimento a differenti condizioni nella
stessa query.
A questo proposito, vediamo un altro esempio: si vuole conoscere il nome e cognome della nonna da
parte di padre di “Valli Mario”. Per fare questo è sufficiente selezionare il nome del padre di “Valli
Mario” in una query interna.
La query più esterna cerca nella tabella Maternità la madre che ha nome e cognome del figlio uguali
a quelli risultati dalla query più interna. L’aspetto problematico deriva dal fatto che la query più
esterna deve confrontare due attributi, il cognome e il nome. In tal caso è necessario scrivere due
query interne: la prima viene confrontata con il cognome, mentre la seconda con il nome.
Per migliorarne la leggibilità è consigliabile indentare le query più interne.
6
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
Le interrogazioni annidate
Lezione 5
Vediamo il codice:
SELECT cognome_madre AS “cognome nonna”, nome_madre AS “nome nonna”
FROM Maternità
WHERE cognome_figlio=
( SELECT cognome_padre
FROM Paternità
WHERE cognome_figlio=”Valli” and nome_figlio=”Mario”)
and nome_figlio=
( SELECT nome_padre
FROM Paternità
WHERE cognome_figlio=”Valli” and nome_figlio=”Mario”);
Esempio15
In questo esempio abbiamo una base di dati formata da due tabelle: Dipendenti e Mansioni. Per
ciascun dipendente è associata una mansione.
Tale mansione è presente in una tabella separata, chiamata appunto Mansioni, che è in relazione 1
a N con la tabella Dipendenti. Vediamo di seguito i dati contenuti nelle due tabelle.
Tabella DipendentiTabella Mansioni
Se volessimo conoscere il cognome e il nome di tutti i dipendenti che hanno stipendio inferiore
alla media di quelli con mansione 9, ma superiore alla media di quelli con mansione 10, dovremmo
applicare le conoscenze acquisite in campo di condizioni multiple, viste nell’esempio precedente:
SELECT cognome, nome, stipendio
FROM Dipendenti
WHERE stipendio <
(SELECT AVG(stipendio) FROM Dipendenti
WHERE mansione=9)
and stipendio >
(SELECT AVG(stipendio) FROM Dipendenti
WHERE mansione=10)
Si ottiene:
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
7
UdA 3
Il linguaggio SQL
■■ Query annidate e join
Anche in questo caso utilizziamo il database dell’esempio 18. Supponiamo di voler ottenere la denominazione delle mansioni e i nominativi dei dipendenti che hanno stipendio superiore alla media.
Allo scopo di facilitare la comprensione della query, sono necessarie alcune considerazioni.
La ricerca dell’attributo della mansione, in questo esempio, associato a un valore calcolato con
un operatore di aggregazione (AVG), si può ottenere mediante nidificazione, in modo analogo agli
esempi fin qui trattati. Per poter rispondere al quesito, però, è necessario considerare che i dati
utili a una delle due SELECT si trovano su tabelle separate, quindi si deve ricorrere anche a una
congiunzione (join).
La soluzione al quesito viene esposta suddividendo le tre query e valutandone i risultati separatamente. La prima query calcola la media dello stipendio della tabella Dipendenti.
SELECT AVG(stipendio)
FROM Dipendenti
La query seguente invece, mediante l’uso di una join, calcola la media degli stipendi, suddivisi per
mansione mediante la clausola GROUP BY:
SELECT M.nome, AVG(D.stipendio) AS media
FROM Mansioni M INNER JOIN Dipendenti D ON D.mansione=M.ID_mansione
GROUP BY M.nome
Tale query produce un elenco di tuple, contraddistinte dal nome della
mansione e dalla media di stipendio calcolata per ciascuna di esse, sempre
che vi siano dipendenti per quella mansione, visto che è stata usata una
inner join: ▶
Per conoscere invece quali mansioni hanno una media di stipendio superiore alla media complessiva è necessario annidare la query vista all’inizio. Infine, utilizzando la clausola HAVING è possibile
stabilire una condizione sull’attributo media delle tuple sopra indicate.
Vediamo il codice completo e il risultato:
SELECT M.nome, AVG(D.stipendio) AS media
FROM Mansioni M INNER JOIN Dipendenti D ON D.mansione=M.ID_mansione
GROUP BY M.nome
HAVING AVG(D.stipendio) >
(SELECT AVG(stipendio)
FROM Dipendenti)
Le considerazioni svolte precedentemente sulle tre query e il risultato ottenuto con la figura indicata sopra possono così riassumersi: il raccordo tra le due SELECT, quella interna e quella esterna,
è stato ottenuto imponendo una condizione sul raggruppamento mediante clausola HAVING nell’interrogazione esterna.
8
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
Le interrogazioni annidate
Lezione 5
Vediamo un altro esempio su come utilizzare le join all’interno di query annidate. In realtà è molto
semplice, in quanto abbiamo visto che ciascuna query, anche se annidata, produce una tupla risultato che viene poi confrontata con quelle prodotte dalla query più esterna.
In questo ulteriore esempio si vogliono conoscere i nomi dei dipendenti che guadagnano più del Top
manager; per fare questo si utilizza una congiunzione interna (inner join) tra le tabelle Dipendenti
e Mansioni mediante il campo ID_mansione:
SELECT matricola, cognome, nome, stipendio
FROM Dipendenti
WHERE stipendio >
(SELECT stipendio
FROM (Dipendenti D INNER JOIN Mansioni M ON D.mansione=M.ID_mansione)
WHERE M.nome=”top manager”);
Ottenendo le seguenti tuple risultato: ▶
Una proprietà notevole delle query annidate è quella di essere equivalenti, in alcuni casi, alle self join.
Per esempio, la query di cui sopra avrebbe potuto essere scritta mediante una self join come segue:
SELECT D1.matricola, D1.cognome, D1.nome, D1.stipendio
FROM Dipendenti D1, dipendenti D2, Mansioni M
WHERE D1.stipendio > D2.stipendio
and D2.mansione=M.ID_mansione
and M.nome=”top manager”;
Ottenendo il medesimo risultato!
Nel caso in cui le tuple risultanti dalla query interna siano più di una, il DBMS fornisce un messaggio di errore
inequivocabile, che significa che la query interna ha restituito più diuna riga, rendendo così di fatto impossibile il confronto con un attributo della query esterna:
Zoom su...
Le suBquerY Con operatori aggregati
Sempre riguardo al database dell’esempio 18 vogliamo risolvere il problema seguente: vogliamo conoscere il nome dei dipendenti che guadagnano più della media di tutti i dipendenti.
Escludendo il ricorso a interrogazioni che elenchino l’attributo nome insieme a una funzione di
gruppo, escluso qualunque raggruppamento di tuple, si eseguono due query distinte. Mediante la prima si calcola la media degli stipendi:
SELECT AVG(stipendio)
FROM Dipendenti;
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
9
uda 3
Il linguaggio SQL
viene utilizzato come elemento di confronto per la query più esterna, il cui codice è:
SELECT cognome, nome
FROM Dipendenti
WHERE stipendio > 2435,56;
Il difetto riscontrabile in questo tipo di approccio è legato alla non generalizzazione della seconda query, in quanto una variazione dei dati della tabella Dipendenti comporta anche una sua
modifica. La nidificazione delle query evita questo inconveniente per la modifica della condizione stabilita nell’esempio citato sopra. Si tratta di unire le query precedenti, legandole mediante
un operatore di confronto (>):
SELECT cognome, nome
FROM Dipendenti
WHERE stipendio >
(SELECT AVG(stipendio)
FROM Dipendenti);
Ottenendo così: ▶
In questo esempio abbiamo visto come esprimere, mediante la nidificazione, espressioni condizionali in cui si confronta uno scalare con un valore restituito da un operatore aggregato.
Prova adesso!
• Applicare le subquery
• Utilizzare i raggruppamenti
nelle subquery
APRI IL FILE esempio18
 Si vogliono conoscere i nomi, la mansione e la paga oraria dei dipendenti che hanno stipendio superiore a quello dell’autista che guadagna di più. Ti consiglio di calcolare innanzitutto
lo stipendio massimo degli autisti come dato scalare nella subquery, quindi nella query più
esterna ricerca tutti quei dipendenti che hanno stipendio superiore a 1650.
2 Confronta la tua soluzione con quella contenuta nel file esempio18_solux.
■■ Condizioni su valori non scalari con i quantificatori
ALL, ANY e SOME
Una delle caratteristiche innovative di SQL fu quella di rendere possibile l’effettuazione di query
annidate in grado di confrontare più valori restituiti dalla query più interna. Finora abbiamo ipotizzato che la query interna generasse sempre valori scalari, cioè unici sia come numero di tuple che
come attributi.
In questo paragrafo ci occupiamo di confrontare su un unico attributo valori non scalari, cioè liste
di valori.
10
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
Le interrogazioni annidate
Lezione 5
Le clausole ALL, ANY e SOME rendono possibile il confronto tra un attributo e un insieme di valori.
Per comprenderne il meccanismo di funzionamento svilupperemo un esempio.
Supponiamo di voler conoscere i nomi dei dipendenti che guadagnano meno di 1500 euro con
la stessa mansione di quelli che ne guadagnano di più. Il risultato si ottiene calcolando, con una
nidificazione, le tipologie di mansione dei dipendenti che guadagnano meno di 1500 euro, confrontate con le mansioni di coloro che guadagnano più di 1500 euro. Purtroppo la query seguente
è errata:
SELECT cognome, nome, stipendio
FROM Dipendenti
WHERE stipendio <=1500
AND mansione =
(SELECT mansione
FROM Dipendenti
WHERE stipendio>1500);
La subquery, infatti, ha cardinalità maggiore di uno, quindi il confronto non può essere valido.
Questo si verifica in quanto la condizione tra le due query è di tipo “ambiguo”: questa, infatti, deve
valere per uno o per tutti gli scalari calcolati dall’interrogazione interna?
La specificazione di una delle tre clausole ◀ ALL/ANY/SOME ▶ risolve l’ambiguità, in quanto permette di definire un set di valori. Esaminiamo meglio queste clausole.
◀ ALL signica che la condizione deve essere valida “per una qualsiasi tupla” del risult-set interno; SOME
è equivalente a ad ANY, ed è stato introdotto in MySQL per correttezza linguistica; ALL identifica “tutte le
tuple” della relazione più interna. ▶
Vediamo ora come applicarle nella query vista sopra, correggendo in tal modo l’errore sul confronto:
SELECT cognome, nome, stipendio
FROM Dipendenti
WHERE stipendio <=1500
AND mansione = ANY
(SELECT mansione FROM Dipendenti
WHERE stipendio>1500);
La query deve essere letta in questo modo: “elencare tutti i dipendenti che hanno stipendio minore
o uguale a 1500 con mansione uguale a uno qualsiasi tra i dipendenti con la stessa mansione ma
con stipendio maggiore di 1500”.
Vediamo il risultato ottenuto:
La prossima query mostra con un esempio quando ricorrere alla clausola ALL. Si vuole conoscere
il nome e la mansione dei dipendenti con uno stipendio più alto di quello di tutti i dipendenti con
mansione “autista”.
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
11
UdA 3
Il linguaggio SQL
In questo caso, è la stessa espressione “maggiore di tutti” a facilitare la comprensione della clausola
di relazione con la subquery, che riportiamo di seguito insieme al risultato ottenuto:
SELECT M.nome AS mansione, D.cognome, D.nome, D.stipendio
FROM Dipendenti D INNER JOIN Mansioni M ON M.ID_mansione=D.mansione
WHERE D.stipendio > ALL
(SELECT D.stipendio
FROM Dipendenti D, Mansioni M
WHERE M.nome=”autista”
and M.ID_mansione=D.mansione)
Oppure ancora, nell’esempio che segue si vogliono elencare tutti i dipendenti che hanno la paga oraria superiore alla media della paga oraria degli impiegati di primo, secondo e terzo livello. Per fare
questo si calcola la media della paga oraria riferita agli impiegati (mediante LIKE), quindi la query
esterna ricerca tutte le mansioni, con il relativo dipendente con paga oraria maggiore del risultato
della subquery interna. Vediamo la codifica, prima per MySQL:
SELECT M.nome AS mansione, D.cognome, D.nome, M.tariffa_oraria
FROM Dipendenti D INNER JOIN Mansioni M ON M.ID_mansione=D.mansione
WHERE M.tariffa_oraria > ALL
(SELECT AVG(tariffa_oraria)
FROM Mansioni
WHERE nome LIKE “impiegato%”)
Ottenendo: ▶
Esistono
corrispondenze
di aggregazione.
Si ricorda
chedelle
il carattere
jolly “%”,tra
inalcune
Accessclausole
è sostituito
dal carattere Per
“*”.esempio, la clausola (=
ANY) è del tutto equivalente a (IN), mentre (<> ALL) è del tutto equivalente a (NOT IN).
■■ La quantificazione esistenziale
La quantificazione esistenziale permette di verificare l’esistenza di almeno una tupla nel result-set
della subquery. La proiezione restituita dalla subquery è assolutamente irrilevante, quindi può essere opportuno indicare sempre una proiezione di tipo star (*). La sua sintassi è la seguente:
SELECT attributo1
FROM tabella1
WHERE [NOT] EXISTS
(SELECT * FROM tabella2);
12
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
Le interrogazioni annidate
Lezione 5
Se una subquery restituisce almeno una tupla, EXISTS fornisce un valore True, altrimenti avrà un
valore False. Come si può notare, se la tabella 2 contiene almeno una riga, la condizione WHERE
della query esterna è True.
Esempio16
In questo esempio utilizziamo come esempio le tabelle che seguono.
Tabella Studenti
Tabella Esami
Vogliamo conoscere l’elenco degli studenti che non hanno superato alcun esame. Per fare questo,
si crea un’interrogazione esterna che elenca tutti gli studenti con proiezione sui dati anagrafici,
mentre la congiunzione prevede l’uso del quantificatore esistenziale negato con NOT, che significa
“non esiste nemmeno una tupla nella subquery”. In questo esempio è stata usata la correlazione per
legare tra loro le tabelle Studenti (nella query esterna) con Esami.
SELECT ID_studente,cognome, nome
FROM Studenti S
WHERE NOT EXISTS
(SELECT * FROM Esami
WHERE S.ID_studente=id_Studente)
Per verificare, invece, quali sono gli studenti che hanno sostenuto almeno un esame, si utilizza il
quantificatore EXISTS. Vediamone la codifica e il risultato:
SELECT ID_studente,cognome, nome
FROM Studenti S
WHERE EXISTS
(SELECT * FROM Esami
WHERE S.ID_studente=id_Studente)
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
13
uda 3
Il linguaggio SQL
Verifichiamo le conoscenze
g Esercizi a scelta multipla
1 Quale dei seguenti non è un operatore insiemistico di SQL?
a) UNION
b) INTERSECT
c) POWERSET
d) EXCEPT
2 In SQL, volendo dividere le righe di una tabella in insiemi caratterizzati dallo stesso valore degli attribuiti
che compaiono come argomento, si usa l’operatore:
a) ORDER BY
b) GROUP BY
c) SELECT BY
3 Dato il seguente schema relazionale:
Ordini(ID_ord, articolo, cliente, data, quantità, id_venditore)
Venditori(ID_venditore, nome, qualifica)
indica quale tra le seguenti query rappresenta i nomi dei venditori che non hanno ancora venduto nulla
nell’anno 2006.
a) SELECT V.NOME FROM Venditori V
WHERE NOT EXISTS (SELECT * FROM Ordini O
WHERE O.id_venditore=V.ID_venditori AND year(O.data)=’2006’)
b) SELECT * FROM Venditori V
WHERE NOT IN (SELECT * FROM Ordini O
WHERE O.id_venditore=V.ID_venditori AND year(O.data)=’2006’)
c) SELECT V.nome FROM Venditori V
WHERE EXISTS (SELECT DISTINCT * FROM Ordini O
WHERE O.id_venditore=V.ID_venditori AND year(O.data)=’2006’)
d) SELECT V.nome FROM Venditori V
WHERE year(O.data)=2006
and NOT EXISTS (SELECT * FROM
Venditori V INNER JOIN Ordini O ON O.id_venditore=V.ID_venditori)
3 Indica a quale categoria appartiene la seguente query:
SELECT cognome, nome
FROM Amici
WHERE nome=
(SELECT nome
FROM Amici
WHERE cognome=“Verdini” and nome=“Pio”);
a)
b)
c)
d)
query derivata
query correlata
subquery
query scalare
4 Indica a quale categoria appartiene la seguente query:
SELECT cognome, nome, stipendio
FROM Amici
WHERE stipendio <
14
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
Le interrogazioni annidate
Lezione 5
(SELECT MAX(stipendio) FROM Amici)
a)
b)
c)
d)
query insiemistica
query calcolata
query scalare
query correlata
g Test vero/falso
1 Le clausole HAVING e GROUP BY possono essere specificate per il risultato finale
di una UNION.
2 È possibile ottenere query multiple unite con UNION.


g Esercizi
1 Illustra come avviene l’operazione di UNION tra due query.
2 Perché è di norma utile assegnare un nome come alias agli attributi risultato della prima query di una
UNION?
3 Individua l’errore nella seguente query annidata:
SELECT * FROM Correntisti C
WHERE CC.cognome<>
(SELECT cognome FROM ContiCorrenti CC
WHERE year(data)>1986);
4 Riscrivi la seguente query utilizzando le tecniche di annidamento corrette:
SELECT D.cognome, D.nome, D.stipendio
FROM
Impiegati D INNER JOIN Impiegati D1 ON D.ID_superiore=D1.ID_impiegato
WHERE D.stipendio>D1.stipendio
5 Riscrivi la seguente query utilizzando le tecniche di annidamento corrette:
SELECT cognome, nome, stipendio
FROM Dipendenti D, Aziende A
WHERE D.id_azienda=A.ID_azienda and A.nome=”FIAT”;
6 Riscrivi la seguente query senza utilizzare le tecniche di annidamento, con e senza l’uso della congiunzione:
SELECT *
FROM Clienti
WHERE cognome =ANY
(SELECT cognome FROM
Appuntamenti WHERE id_trattamento=ANY
(SELECT ID_trattamento
FROM Trattamenti
WHERE descrizione LIKE “%lamp%”));
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014
15
uda 3
Il linguaggio SQL
Verifichiamo le competenze
g Problemi
1 Considera una tabella a piacere contenente un attributo di valori numerici qualsiasi. Si chiede di scrivere la
query che calcola il dato immediatamente superiore al minimo per quell’attributo.
2 Dato lo schema relazionale costituito dalle seguenti tabelle:
Persone(nome, reddito, età, sesso, indirizzo, città)
Genitori_figli(ID, nome_genitore, nome_figlio, ordine_figlio)
esprimi in SQL le seguenti interrogazioni:
a) Trovare l’elenco ordinato dei genitori i cui figli guadagnano tutti più di 10.000
b) Elencare per ogni persona l’età media dei suoi figli
c) Elencare i figli la cui età è maggiore della media delle età dei figli dello stesso genitore
d) Elencare per ogni persona il primo figlio
e) Elencare i genitori senza figli
3 Dato lo schema relazionale costituito dalle seguenti tabelle:
Pilota Pilota (ID_pilota, nome, cognome, num_mondiali_vinti, nazione)
Gara (ID_gara, nome, data, id_vincitore)
esprimi in SQL le seguenti interrogazioni:
a) Elencare i nomi dei piloti che, a partire dal 1970, hanno vinto almeno 2 gare
b) Elencare il nome di tutti i piloti che hanno vinto almeno un mondiale ma non hanno mai vinto il Gran Premio
di San Marino
c) Elencare quale pilota ha vinto più GP del Brasile
d) Elencare quali piloti non hanno mai vinto alcun mondiale pur avendo vinto almeno una gara
e) Elencare il pilota italiano che ha vinto più gare della media di tutti i piloti tedeschi
4 Dato lo schema relazionale costituito dalle seguenti tabelle:
Programma(ID_programma, nome, tipo, durata)
Orario(canale_tv, data, ora, minuti, id_programma)
esprimi in SQL le seguenti interrogazioni:
a) Elencare i canali televisivi che non hanno mai trasmesso programmi di tipo “intrattenimento” con durata
inferiore a 20 minuti nel periodo dal 20/12/2006 al 05/01/2005
b) Elencare il codice, nome, e il canale di programmazione, data e ora di inizio dei relativi programmi di tipo
“sport”, trasmessi da più di 2 canali televisivi diversi
c) Elencare quali canali televisivi hanno trasmesso, nel periodo gennaio 2005, almeno un programma di genere
“sport” ma nessun programma di “cultura”
d) Elencare i programmi televisivi con durata inferiore alla media di quelli sportivi e superiore a quella del
programma culturale più lungo
16
P. Camagni, R. Nikolassy, Progettare i database, Nuova Edizione OPENSCHOOL, © Ulrico Hoepli Editore S.p.A. 2014