Linguaggio SQL: fondamenti - polito.itcorsiadistanza.polito.it/on-line/sistemi_info/pdf/U3_3.pdf ·...
Transcript of Linguaggio SQL: fondamenti - polito.itcorsiadistanza.polito.it/on-line/sistemi_info/pdf/U3_3.pdf ·...
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 1
Linguaggio SQL: fondamenti
Interrogazioni nidificate
IntroduzioneOperatore INOperatore NOT INCostruttore di tuplaOperatore EXISTSOperatore NOT EXISTSCorrelazione tra interrogazioniOperazione di divisioneTable functions
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 2
Interrogazioni nidificate
Introduzione
Un'interrogazione nidificata è un'istruzione SELECT contenuta all'interno di un'altra interrogazione
la nidificazione di interrogazioni permette di suddividere un problema complesso in sottoproblemi più semplici
È possibile introdurre istruzioni SELECTin un predicato nella clausola WHEREin un predicato nella clausola HAVINGnella clausola FROM
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 3
DB forniture prodotti (1/2)
P (CodP, NomeP, Colore, Taglia, Magazzino)F (CodF, NomeF, NSoci, Sede)FP (CodF, CodP, Qta)
DB forniture prodotti (2/2)
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoSede
AndreaF1NomeFCodF
F
FP
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodP
Roma48BluCamiciaP3Torino44BluCamiciaP4Milano40BluGonnaP5
Rosso
VerdeRossoColore
42
4840
Taglia
MilanoJeansP2
TorinoBermudaP6
TorinoMagazzino
MagliaP1NomePCodP
P
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 4
Interrogazioni nidificate (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1
Interrogazioni nidificate (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1
La formulazione mediante interrogazioni nidificate consente di separare il problema in due sottoproblemi
sede del fornitore F1codici dei fornitori con la stessa sede
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 5
Interrogazioni nidificate (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1
SELECT SedeFROM FWHERE CodF='F1'
Sede del fornitore F1
Interrogazioni nidificate (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1
SELECT CodF FROM FWHERE Sede = (SELECT Sede
FROM FWHERE CodF='F1');
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 6
Interrogazioni nidificate (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1
SELECT CodF FROM FWHERE Sede = (SELECT Sede
FROM FWHERE CodF='F1');
È possibile utilizzare '=' esclusivamente se è noto a priori che il risultato della SELECT nidificata èsempre un solo valore
Formulazione equivalente (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1
È possibile definire una formulazione equivalente con il join
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 7
Formulazione equivalente
La formulazione equivalente con il join ècaratterizzata da
Clausola FROM contenente le tabelle referenziate nelle FROM di tutte le SELECTOpportune condizioni di join nella clausola WHEREEventuali predicati di selezione aggiunti nella clausola WHERE
Clausola FROM (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1
SELECT CodF FROM FWHERE Sede = (SELECT Sede
FROM FWHERE CodF='F1');
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 8
Clausola FROM (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1
SELECT CodF FROM FWHERE Sede = (SELECT Sede
FROM FWHERE CodF='F1');
FX
FY
Clausola FROM (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1
SELECT ... FROM F AS FX, F AS FY...
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 9
Condizione di join (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1
SELECT CodF FROM FWHERE Sede = (SELECT Sede
FROM FWHERE CodF='F1');
Condizione di join (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1
SELECT ... FROM F AS FX, F AS FYWHERE FX.Sede=FY.Sede...
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 10
Predicato di selezione (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1
SELECT CodF FROM FWHERE Sede = (SELECT Sede
FROM FWHERE CodF='F1');
Predicato di selezione (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1
SELECT ...FROM F AS FX, F AS FYWHERE FX.Sede=FY.Sede AND
FX.CodF='F1';
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 11
Clausola SELECT (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1
SELECT FY.CodF FROM F AS FX, F AS FYWHERE FX.Sede=FY.Sede AND
FX.CodF='F1';
Interrogazioni nidificate (n.2)
Trovare il codice dei fornitori il cui numero di soci è minore del numero massimo di soci
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 12
Interrogazioni nidificate (n.2)
Trovare il codice dei fornitori il cui numero di soci è minore del numero massimo di soci
SELECT MAX(NSoci) FROM F
Numeromassimodi soci
Interrogazioni nidificate (n.2)
Trovare il codice dei fornitori il cui numero di soci è minore del numero massimo di soci
SELECT CodF FROM FWHERE NSoci < (SELECT MAX(NSoci)
FROM F);
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 13
Formulazione equivalente (n.2)
Trovare il codice dei fornitori il cui numero di soci è minore del numero massimo di soci
SELECT CodF FROM FWHERE NSoci < (SELECT MAX(NSoci)
FROM F);
È possibile definire una formulazione equivalente con il join?
Formulazione equivalente (n.2)
Trovare il codice dei fornitori il cui numero di soci è minore del numero massimo di soci
SELECT CodF FROM FWHERE NSoci < (SELECT MAX(NSoci)
FROM F);
Non è possibile definire una formulazione equivalente con il join
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 14
Interrogazioni nidificate
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono il prodotto P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 15
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono il prodotto P2
Scomposizione del problema in due sottoproblemicodici dei fornitori del prodotto P2nome dei fornitori aventi quei codici
Codicidei
fornitoridi P2
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono ilprodotto P2
SELECT CodF FROM FPWHERE CodP='P2'
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 16
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono ilprodotto P2
SELECT CodF FROM FPWHERE CodP='P2'
Codicidei
fornitoridi P2F1
F2
F3
CodF
FP
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodP
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono ilprodotto P2
SELECT NomeF FROM FWHERE CodF (SELECT CodF
FROM FPWHERE CodP='P2')
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 17
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono ilprodotto P2
SELECT NomeF FROM FWHERE CodF (SELECT CodF
FROM FPWHERE CodP='P2')
?
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono ilprodotto P2
SELECT NomeF FROM FWHERE CodF IN (SELECT CodF
FROM FPWHERE CodP='P2');
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 18
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono ilprodotto P2
SELECT NomeF FROM FWHERE CodF IN (SELECT CodF
FROM FPWHERE CodP='P2');
Appartenenza all'insieme
Operatore IN
Esprime il concetto di appartenenza ad un insieme di valori
NomeAttributo IN (InterrogazioneNidificata)
Permette di scrivere l'interrogazionescomponendo il problema in sottoproblemiseguendo un procedimento “bottom-up”
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 19
Formulazione equivalente
La formulazione equivalente con il join ècaratterizzata da
clausola FROM contenente le tabelle referenziate nelle FROM di tutte le SELECTopportune condizioni di join nella clausola WHEREeventuali predicati di selezione aggiunti nella clausola WHERE
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono ilprodotto P2
SELECT NomeF FROM FWHERE CodF IN (SELECT CodF
FROM FPWHERE CodP='P2');
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 20
Formulazione equivalente (n.1)
Trovare il nome dei fornitori che forniscono il prodotto P2
SELECT NomeF FROM F, FPWHERE F.CodF=FP.CodF
AND CodP='P2';
Operatore IN (n.2)
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 21
Operatore IN (n.2)
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
Scomposizione del problema in sottoproblemicodici dei prodotti rossicodici dei fornitori di quei prodottinomi dei fornitori aventi quei codici
Operatore IN (n.2)
SELECT CodP FROM PWHERE Colore='Rosso'
Codici deiprodotti rossi
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 22
Operatore IN (n.2)
SELECT CodF FROM FPWHERE CodP IN (SELECT CodP
FROM PWHERE Colore='Rosso')
Codici dei fornitoridi prodotti rossi
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
Operatore IN (n.2)
SELECT NomeF FROM FWHERE CodF IN (SELECT CodF
FROM FPWHERE CodP IN (SELECT CodP
FROM PWHERE Colore='Rosso'));
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 23
Formulazione equivalente (n.2)
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
SELECT NomeF FROM FWHERE CodF IN (SELECT CodF
FROM FPWHERE CodP IN (SELECT CodP
FROM PWHERE Colore='Rosso'));
SELECT NomeF FROM FWHERE CodF IN (SELECT CodF
FROM FPWHERE CodP IN (SELECT CodP
FROM PWHERE Colore='Rosso'));
Clausola FROM (n.2)
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 24
Clausola FROM (n.2)
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
SELECT ...FROM F, FP, P...
SELECT NomeF FROM FWHERE CodF IN (SELECT CodF
FROM FPWHERE CodP IN (SELECT CodP
FROM PWHERE Colore='Rosso'));
Condizioni di join (n.2)
1
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 25
Condizioni di join (n.2)
1
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
SELECT ...FROM F, FP, PWHERE P.CodF=F.CodF
Condizioni di join (n.2)
SELECT NomeF FROM FWHERE CodF IN (SELECT CodF
FROM FPWHERE CodP IN (SELECT CodP
FROM PWHERE Colore='Rosso'));
2
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 26
Condizioni di join (n.2)
SELECT ... FROM F, FP, PWHERE P.CodF=F.CodF AND
FP.CodP=P.CodP ...
2
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
Predicato di selezione (n.2)
SELECT NomeF FROM FWHERE CodF IN (SELECT CodF
FROM FPWHERE CodP IN (SELECT CodP
FROM PWHERE Colore='Rosso'));
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 27
Predicato di selezione (n.2)
SELECT ... FROM F, FP, PWHERE P.CodF=F.CodF AND
FP.CodP=P.CodP ANDColore='Rosso'
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
Clausola SELECT (n.2)
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
SELECT NomeF FROM F, FP, PWHERE P.CodF=F.CodF AND
FP.CodP=P.CodP ANDColore='Rosso'
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 28
Esempio complesso (n.3)
Trovare il nome dei fornitori che forniscono almeno un prodotto fornito da fornitori di prodotti rossi
Esempio complesso (n.3)
Trovare il nome dei fornitori che forniscono almeno un prodotto fornito da fornitori di prodotti rossi
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 29
Esempio complesso (n.3)
Trovare il nome dei fornitori che forniscono almeno un prodotto fornito da fornitori di prodotti rossi
La formulazione con il join è difficileè più semplice scomporre il problema in sottoproblemi mediante interrogazioni nidificate
Esempio complesso (n.3)
SELECT CodP FROM P WHERE Colore='Rosso'
Codici dei prodotti rossi
Trovare il nome dei fornitori che forniscono almeno un prodotto fornito da fornitori di prodotti rossi
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 30
Esempio complesso (n.3)
SELECT CodFFROM FPWHERE CodP IN
(SELECT CodP FROM P WHERE Colore='Rosso')
Codici dei fornitori di prodotti rossi
Trovare il nome dei fornitori che forniscono almeno un prodotto fornito da fornitori di prodotti rossi
Esempio complesso (n.3)
SELECT CodP FROM FPWHERE CodF IN
(SELECT CodFFROM FPWHERE CodP IN
(SELECT CodP FROM P WHERE Colore='Rosso'))
Codici dei prodotti forniti da fornitori di prodotti rossi
Trovare il nome dei fornitori che forniscono almeno un prodotto fornito da fornitori di prodotti rossi
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 31
Esempio complesso (n.3)
SELECT CodF FROM FPWHERE CodP IN
(SELECT CodP FROM FPWHERE CodF IN
(SELECT CodFFROM FPWHERE CodP IN
(SELECT CodP FROM P WHERE Colore='Rosso')))
Codici dei fornitori di prodotti forniti
da fornitori di prodotti rossi
Interrogazione completa (n.3)
SELECT NomeF FROM FWHERE CodF IN
(SELECT CodF FROM FPWHERE CodP IN
(SELECT CodP FROM FPWHERE CodF IN
(SELECT CodFFROM FPWHERE CodP IN
(SELECT CodP FROM P WHERE Colore='Rosso'))));
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 32
Formulazione con il join (n.3)
Formulazione con il join (n.3)
SELECT NomeF FROM FWHERE CodF IN
(SELECT CodF FROM FPWHERE CodP IN
(SELECT CodP FROM FPWHERE CodF IN
(SELECT CodFFROM FPWHERE CodP IN
(SELECT CodP FROM P WHERE Colore='Rosso'))));
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 33
Clausola FROM (n.3)
SELECT NomeF FROM FWHERE CodF IN
(SELECT CodF FROM FPWHERE CodP IN
(SELECT CodP FROM FPWHERE CodF IN
(SELECT CodFFROM FPWHERE CodP IN
(SELECT CodP FROM P WHERE Colore='Rosso'))));
Clausola FROM (n.3)
SELECT NomeF FROM FWHERE CodF IN
(SELECT CodF FROM FPWHERE CodP IN
(SELECT CodP FROM FPWHERE CodF IN
(SELECT CodFFROM FPWHERE CodP IN
(SELECT CodP FROM P WHERE Colore='Rosso'))));
FPA
FPB
FPC
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 34
Clausola FROM (n.3)
SELECT ... FROM F, FP AS FPA, FP AS FPB, FP AS FPC, P...
Condizioni di join (n.3)
SELECT NomeF FROM FWHERE CodF IN
(SELECT CodF FROM FPWHERE CodP IN
(SELECT CodP FROM FPWHERE CodF IN
(SELECT CodFFROM FPWHERE CodP IN
(SELECT CodP FROM P WHERE Colore='Rosso'))));
1
FPA
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 35
Condizioni di join (n.3)
SELECT ... FROM F, FP AS FPA, FP AS FPB, FP AS FPC, PWHERE F.CodF=FPA.CodF
...1
Condizioni di join (n.3)
SELECT NomeF FROM FWHERE CodF IN
(SELECT CodF FROM FPWHERE CodP IN
(SELECT CodP FROM FPWHERE CodF IN
(SELECT CodFFROM FPWHERE CodP IN
(SELECT CodP FROM P WHERE Colore='Rosso'))));
2FPA
FPB
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 36
Condizioni di join (n.3)
SELECT ... FROM F, FP AS FPA, FP AS FPB, FP AS FPC, PWHERE F.CodF=FPA.CodF AND
FPA.CodP=FPB.CodP...
2
Condizioni di join (n.3)
SELECT NomeF FROM FWHERE CodF IN
(SELECT CodF FROM FPWHERE CodP IN
(SELECT CodP FROM FPWHERE CodF IN
(SELECT CodFFROM FPWHERE CodP IN
(SELECT CodP FROM P WHERE Colore='Rosso'))));
3
FPB
FPC
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 37
Condizioni di join (n.3)
SELECT ... FROM F, FP AS FPA, FP AS FPB, FP AS FPC, PWHERE F.CodF=FPA.CodF AND
FPA.CodP=FPB.CodP ANDFPB.CodF=FPC.CodF...
3
Condizioni di join (n.3)
SELECT NomeF FROM FWHERE CodF IN
(SELECT CodF FROM FPWHERE CodP IN
(SELECT CodP FROM FPWHERE CodF IN
(SELECT CodFFROM FPWHERE CodP IN
(SELECT CodP FROM P WHERE Colore='Rosso'))));
4
FPC
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 38
Condizioni di join (n.3)
SELECT ... FROM F, FP AS FPA, FP AS FPB, FP AS FPC, PWHERE F.CodF=FPA.CodF AND
FPA.CodP=FPB.CodP ANDFPB.CodF=FPC.CodF ANDFPC.CodP=P.CodP...
4
Predicato di selezione (n.3)
SELECT NomeF FROM FWHERE CodF IN
(SELECT CodF FROM FPWHERE CodP IN
(SELECT CodP FROM FPWHERE CodF IN
(SELECT CodFFROM FPWHERE CodP IN
(SELECT CodP FROM P WHERE Colore='Rosso'))));
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 39
Predicato di selezione (n.3)
SELECT ... FROM F, FP AS FPA, FP AS FPB, FP AS FPC, PWHERE F.CodF=FPA.CodF AND
FPA.CodP=FPB.CodP ANDFPB.CodF=FPC.CodF ANDFPC.CodP=P.CodP ANDColore='Rosso'
Clausola SELECT (n.3)
SELECT NomeF FROM F, FP AS FPA, FP AS FPB, FP AS FPC, PWHERE F.CodF=FPA.CodF AND
FPA.CodP=FPB.CodP ANDFPB.CodF=FPC.CodF ANDFPC.CodP=P.CodP ANDColore='Rosso';
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 40
Interrogazioni nidificate
Concetto di esclusione (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 41
Concetto di esclusione (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
è possibile esprimere l'interrogazione mediante il join?
Concetto di esclusione (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
è possibile esprimere l'interrogazione mediante il join?
SELECT NomeF FROM F, FPWHERE F.CodF=FP.CodF ...
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 42
Concetto di esclusione (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
è possibile esprimere l'interrogazione mediante il join?
SELECT NomeF FROM F, FPWHERE F.CodF=FP.CodF
AND CodP<>'P2';
Soluzione errata (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
non è possibile esprimere l'interrogazione mediante il join
SELECT NomeF FROM F, FPWHERE F.CodF=FP.CodF
AND CodP<>'P2';
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 43
Soluzione errata (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoSede
AndreaF1NomeFCodF
FFP
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodP
Soluzione errata (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoSede
AndreaF1NomeFCodF
FFP
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodP
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 44
Soluzione errata (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
FFP
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodP
AndreaLuca
Gabriele
NomeFR
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoSede
AndreaF1NomeFCodF
Soluzione errata (n.1)
SELECT NomeF FROM F, FPWHERE F.CodF=FP.CodF
AND CodP<> 'P2';
A che interrogazione corrisponde?
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 45
Soluzione errata (n.1)
SELECT NomeF FROM F, FPWHERE F.CodF=FP.CodF
AND CodP<> 'P2';
Trovare il nome dei fornitori che forniscono almeno un prodotto diverso da P2
Concetto di esclusione (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 46
Concetto di esclusione (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
Occorre escludere dal risultatoi fornitori che forniscono il prodotto P2
Concetto di esclusione (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
SELECT CodF FROM FPWHERE CodP='P2'
Codici dei fornitori che forniscono P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 47
Concetto di esclusione (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
SELECT NomeF FROM FWHERE CodF (SELECT CodF
FROM FPWHERE CodP='P2');
Codici dei fornitori che forniscono P2
?
Operatore NOT IN (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
SELECT NomeF FROM FWHERE CodF NOT IN (SELECT CodF
FROM FPWHERE CodP='P2');
Codici dei fornitori che forniscono P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 48
Operatore NOT IN (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
SELECT NomeF FROM FWHERE CodF NOT IN (SELECT CodF
FROM FPWHERE CodP='P2');
Codici dei fornitori che forniscono P2
Non appartiene
Operatore NOT IN
Esprime il concetto di esclusione da un insieme di valori
NomeAttributo NOT IN (InterrogazioneNidificata)
Richiede di individuare in modo appropriato l’insieme da escludere
definito dall’interrogazione nidificata
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 49
NOT IN e algebra relazionale (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
NOT IN e algebra relazionale (n.1)
-
πNomeF
πCodF
F
FP
πCodF
σCodP='P2'
F
Trovare il nome dei fornitori che non forniscono il prodotto P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 50
NOT IN e algebra relazionale (n.1)
F
FP
p
σCodP='P2'
πNomeF
p: F.CodF=FP.CodF
-
πNomeF
πCodF
F
FP
πCodF
σCodP='P2'
F
Trovare il nome dei fornitori che non forniscono il prodotto P2
Operatore NOT IN (n.2)
Trovare il nome dei fornitori che forniscono solo il prodotto P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 51
Operatore NOT IN (n.2)
Trovare il nome dei fornitori di P2 che non hanno mai fornito prodotti diversi da P2
Trovare il nome dei fornitori che forniscono solo il prodotto P2
Operatore NOT IN (n.2)
Trovare il nome dei fornitori di P2 che non hanno mai fornito prodotti diversi da P2
Insieme da escluderefornitori di prodotti diversi da P2
Trovare il nome dei fornitori che forniscono solo il prodotto P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 52
Operatore NOT IN (n.2)
Trovare il nome dei fornitori che forniscono solo il prodotto P2
SELECT CodF FROM FPWHERE CodP<>'P2'
Codici dei fornitori che forniscono
almeno un prodotto diverso
da P2
Operatore NOT IN (n.2)
SELECT NomeF FROM FWHERE CodF NOT IN (SELECT CodF
FROM FPWHERE CodP<>'P2')
...
Trovare il nome dei fornitori che forniscono solo il prodotto P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 53
Operatore NOT IN (n.2)
SELECT NomeF FROM F, FPWHERE F.CodF NOT IN (SELECT F.CodF
FROM FPWHERE CodP<>'P2')
AND F.CodF=FP.CodF;
Trovare il nome dei fornitori che forniscono solo il prodotto P2
Soluzione alternativa (n.2)
Trovare il nome dei fornitori che forniscono solo il prodotto P2
SELECT NomeF FROM FWHERE F.CodF NOT IN (SELECT CodF
FROM FPWHERE CodP<>'P2')
AND F.CodF IN (SELECT CodFFROM FP);
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 54
Operatore NOT IN (n.3)
Trovare il nome dei fornitori che non forniscono prodotti rossi
Operatore NOT IN (n.3)
Trovare il nome dei fornitori che non forniscono prodotti rossi
Insieme da escludere?
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 55
Operatore NOT IN (n.3)
Trovare il nome dei fornitori che non forniscono prodotti rossi
Insieme da escludere?i fornitori di prodotti rossi, identificati dal loro codice
Operatore NOT IN (n.3)
(SELECT CodF FROM FPWHERE CodP IN (SELECT CodP
FROM PWHERE Colore='Rosso')
Codici dei fornitori di prodotti rossi
Trovare il nome dei fornitori che non forniscono prodotti rossi
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 56
Operatore NOT IN (n.3)
SELECT NomeF FROM FWHERE CodF NOT IN (SELECT CodF
FROM FPWHERE CodP IN (SELECT CodP
FROM PWHERE Colore='Rosso'));
Trovare il nome dei fornitori che non forniscono prodotti rossi
Alternativa (corretta?) (n.3)
Trovare il nome dei fornitori che non forniscono prodotti rossi
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 57
Alternativa (corretta?) (n.3)
SELECT CodPFROM PWHERE Colore='Rosso'
Codici dei prodotti
rossi
Trovare il nome dei fornitori che non forniscono prodotti rossi
Alternativa (corretta?) (n.3)
SELECT CodF FROM FPWHERE CodP NOT IN (SELECT CodP
FROM PWHERE Colore='Rosso')
Codici dei fornitori che forniscono
almeno un prodotto non rosso
Trovare il nome dei fornitori che non forniscono prodotti rossi
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 58
Alternativa (corretta?) (n.3)
SELECT NomeF FROM FWHERE CodF IN (SELECT CodF
FROM FPWHERE CodP NOT IN (SELECT CodP
FROM PWHERE Colore='Rosso'));
Trovare il nome dei fornitori che non forniscono prodotti rossi
Alternativa errata (n.3)
SELECT NomeF FROM FWHERE CodF IN (SELECT CodF
FROM FPWHERE CodP NOT IN (SELECT CodP
FROM PWHERE Colore='Rosso'));
Trovare il nome dei fornitori che non forniscono prodotti rossi
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 59
Alternativa errata (n.3)
SELECT NomeF FROM FWHERE CodF IN (SELECT CodF
FROM FPWHERE CodP NOT IN (SELECT CodP
FROM PWHERE Colore='Rosso'));
Codici dei fornitori di prodotti non rossi
Trovare il nome dei fornitori che non forniscono prodotti rossi
Alternativa errata (n.3)
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoSede
AndreaF1NomeFCodF
F
FP
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodP
Roma48BluCamiciaP3Torino44BluCamiciaP4Milano40BluGonnaP5
Rosso
VerdeRossoColore
42
4840Taglia
MilanoJeansP2
TorinoBermudaP6
TorinoMagazzino
MagliaP1NomePCodPP
Trovare il nome dei fornitori che non forniscono prodotti rossi
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 60
Alternativa errata (n.3)
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoSede
AndreaF1NomeFCodF
FP
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodP
F
P
Roma48BluCamiciaP3Torino44BluCamiciaP4Milano40BluGonnaP5
Rosso
VerdeRossoColore
42
4840Taglia
MilanoJeansP2
TorinoBermudaP6
TorinoMagazzino
MagliaP1NomePCodP
Trovare il nome dei fornitori che non forniscono prodotti rossi
Alternativa errata (n.3)
SELECT NomeF FROM FWHERE CodF IN (SELECT CodF
FROM FPWHERE CodP NOT IN (SELECT CodP
FROM PWHERE Colore='Rosso'));
L'insieme di elementi da escludere non è corretto
Trovare il nome dei fornitori che non forniscono prodotti rossi
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 61
Interrogazioni nidificate
Costruttore di tupla
Permette di definire la struttura temporanea di una tupla
si elencano gli attributi che ne fanno parte tra ()
Permette di estendere il poter espressivo degli operatori IN e NOT IN
(NomeAttributo1, NomeAttributo2, ...)
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 62
Esempio (n.1)
Trovare le coppie luogo di partenza e luogo di arrivo per cui nessun viaggio dura più di 2 ore
VIAGGIO (CodV, LuogoPartenza, LuogoArrivo, OraPartenza, OraArrivo)
Esempio (n.1)
Percorsi per cui esistono viaggi che
durano più di 2 ore
Trovare le coppie luogo di partenza e luogo di arrivo per cui nessun viaggio dura più di 2 ore
(SELECT LuogoPartenza, LuogoArrivoFROM VIAGGIOWHERE OraArrivo-OraPartenza>2)
VIAGGIO (CodV, LuogoPartenza, LuogoArrivo, OraPartenza, OraArrivo)
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 63
Esempio (n.1)
SELECT LuogoPartenza, LuogoArrivoFROM VIAGGIOWHERE (LuogoPartenza, LuogoArrivo) NOT IN
(SELECT LuogoPartenza, LuogoArrivoFROM VIAGGIOWHERE OraArrivo-OraPartenza>2);
Trovare le coppie luogo di partenza e luogo di arrivo per cui nessun viaggio dura più di 2 ore
VIAGGIO (CodV, LuogoPartenza, LuogoArrivo, OraPartenza, OraArrivo)
Esempio (n.1)
SELECT LuogoPartenza, LuogoArrivoFROM VIAGGIOWHERE (LuogoPartenza, LuogoArrivo) NOT IN
(SELECT LuogoPartenza, LuogoArrivoFROM VIAGGIOWHERE OraArrivo-OraPartenza>2);
Costruttoredi tupla
Trovare le coppie luogo di partenza e luogo di arrivo per cui nessun viaggio dura più di 2 ore
VIAGGIO (CodV, LuogoPartenza, LuogoArrivo, OraPartenza, OraArrivo)
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 64
Interrogazioni nidificate
Operatore EXISTS (n.1)
Trovare il nome dei fornitori del prodotto P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 65
Operatore EXISTS (n.1)
Trovare il nome dei fornitori del prodotto P2
Trovare il nome dei fornitori per cui esisteuna fornitura del prodotto P2
Operatore EXISTS (n.1)
Trovare il nome dei fornitori del prodotto P2
SELECT NomeF FROM FWHERE EXISTS (...);
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 66
Operatore EXISTS (n.1)
Trovare il nome dei fornitori del prodotto P2
SELECT NomeF FROM FWHERE EXISTS (SELECT *
FROM FPWHERE CodP='P2'
...
Operatore EXISTS (n.1)
Trovare il nome dei fornitori del prodotto P2
SELECT NomeF FROM FWHERE EXISTS (SELECT *
FROM FPWHERE CodP='P2'
AND FP.CodF=F.CodF );
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 67
Condizione di correlazione (n.1)
Trovare il nome dei fornitori del prodotto P2
SELECT NomeF FROM FWHERE EXISTS (SELECT *
FROM FPWHERE CodP='P2'
AND FP.CodF=F.CodF );
Condizione di correlazione
Funzionamento di EXISTS (n.1)
Milano3AntonioF3Torino2GabrieleF4
3
12NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
FTrovare il nome dei fornitori del prodotto P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 68
Funzionamento di EXISTS (n.1)
Milano3AntonioF3Torino2GabrieleF4
3
12NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
F
SELECT *FROM FPWHERE CodP='P2'
AND FP.CodF='F1'
Valore di CodF nella riga corrente di F
Trovare il nome dei fornitori del prodotto P2
Funzionamento di EXISTS (n.1)
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodP
Milano3AntonioF3Torino2GabrieleF4
3
12NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
F FP
SELECT *FROM FPWHERE CodP='P2'
AND FP.CodF='F1'
Valore di CodF nella riga corrente di F
Trovare il nome dei fornitori del prodotto P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 69
Funzionamento di EXISTS (n.1)
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodP
Milano3AntonioF3Torino2GabrieleF4
3
12NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
F FP
Il predicato con EXISTS èvero per F1 poiché esiste unafornitura di P2 per F1
F1 fa parte del risultatodell'interrogazione
Trovare il nome dei fornitori del prodotto P2
Funzionamento di EXISTS (n.1)
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodP
Milano3AntonioF3Torino2GabrieleF4
3
12NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
F FPTrovare il nome dei fornitori del prodotto P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 70
Funzionamento di EXISTS (n.1)
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodP
Milano3AntonioF3Torino2GabrieleF4
3
12NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
F FP
Il predicato con EXISTS èfalso per F4 poiché non esisteuna fornitura di P2 per F4
F4 non fa parte del risultatodell'interrogazione
Trovare il nome dei fornitori del prodotto P2
Risultato dell’interrogazione (n.1)
AndreaLuca
Antonio
NomeFR
Trovare il nome dei fornitori del prodotto P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 71
Predicati con EXISTS
Il predicato contenente EXISTS èvero se l'interrogazione interna restituisce almeno una tuplafalso se l'interrogazione interna restituisce l'insieme vuoto
Predicati con EXISTS
Il predicato contenente EXISTS èvero se l'interrogazione interna restituisce almeno una tuplafalso se l'interrogazione interna restituisce l'insieme vuoto
Nell’interrogazione interna a EXISTS, la clausola SELECT è obbligatoria, ma irrilevante, perchè gli attributi non sono visualizzati
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 72
Predicati con EXISTS
Il predicato contenente EXISTS èvero se l'interrogazione interna restituisce almeno una tuplafalso se l'interrogazione interna restituisce l'insieme vuoto
Nell’interrogazione interna a EXISTS, la clausola SELECT è obbligatoria, ma irrilevante, perchè gli attributi non sono visualizzatiLa condizione di correlazione lega l'esecuzione dell'interrogazione interna al valore di attributi della tupla corrente nell'interrogazione esterna
Visibilità degli attributi
Un'interrogazione nidificata può far riferimento ad attributi definiti in interrogazioni più esterne Un'interrogazione non può far riferimento ad attributi referenziati
in un'interrogazione nidificata al suo internoin un'interrogazione allo stesso livello
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 73
Interrogazioni nidificate
Operatore NOT EXISTS (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 74
Operatore NOT EXISTS (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
Trovare il nome dei fornitori per cui non esisteuna fornitura del prodotto P2
Operatore NOT EXISTS (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
SELECT NomeF FROM FWHERE NOT EXISTS (…);
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 75
Operatore NOT EXISTS (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
SELECT NomeF FROM FWHERE NOT EXISTS (SELECT *
FROM FPWHERE CodP='P2'
...
Operatore NOT EXISTS (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
SELECT NomeF FROM FWHERE NOT EXISTS (SELECT *
FROM FPWHERE CodP='P2'
AND FP.CodF=F.CodF );
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 76
Operatore NOT EXISTS (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
SELECT NomeF FROM FWHERE NOT EXISTS (SELECT *
FROM FPWHERE CodP='P2'
AND FP.CodF=F.CodF );
Condizione di correlazione
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
Trovare il nome dei fornitori che non forniscono il prodotto P2
Funzionamento di NOT EXISTS (n.1)
F
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 77
Trovare il nome dei fornitori che non forniscono il prodotto P2
Funzionamento di NOT EXISTS (n.1)
SELECT *FROM FPWHERE CodP='P2' AND
FP.CodF='F1'Valore di CodF nella
riga corrente di F
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
F
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodP
Trovare il nome dei fornitori che non forniscono il prodotto P2
Funzionamento di NOT EXISTS (n.1)
SELECT *FROM FPWHERE CodP='P2' AND
FP.CodF='F1'
F FP
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 78
Trovare il nome dei fornitori che non forniscono il prodotto P2
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodP
Funzionamento di NOT EXISTS (n.1)
Il predicato con NOT EXISTS èfalso per F1 perché esiste unafornitura di P2 per F1
F1 non fa parte del risultatodell'interrogazione
F FP
Trovare il nome dei fornitori che non forniscono il prodotto P2
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
Funzionamento di NOT EXISTS (n.1)
F
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 79
Trovare il nome dei fornitori che non forniscono il prodotto P2
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
Funzionamento di NOT EXISTS (n.1)
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodPF FP
Trovare il nome dei fornitori che non forniscono il prodotto P2
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
Funzionamento di NOT EXISTS (n.1)
F
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 80
Trovare il nome dei fornitori che non forniscono il prodotto P2
Funzionamento di NOT EXISTS (n.1)
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodPF FP
Trovare il nome dei fornitori che non forniscono il prodotto P2
Funzionamento di NOT EXISTS (n.1)
Il predicato con NOT EXISTS èvero per F4 perché non esisteuna fornitura di P2 per F4
F4 fa parte del risultatodell'interrogazione
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodPF FP
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 81
Funzionamento di NOT EXISTS (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
F
Funzionamento di NOT EXISTS (n.1)
Milano3AntonioF3Torino2GabrieleF4
3
12
NSoci
MilanoLucaF2
VeneziaMatteoF5
TorinoCittà
AndreaF1NomeFCodF
F
200P4F1400P3F1200P2F1300P1F1
400P2F2300P1F2100P6F1100P5F1
400P5F4300P4F4200P3F4200P2F3
CodF QtaCodPFP
Trovare il nome dei fornitori che non forniscono il prodotto P2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 82
Risultato dell’interrogazione (n.1)
GabrieleMatteo
NomeFR
Trovare il nome dei fornitori che non forniscono il prodotto P2
Predicato con NOT EXISTS
Il predicato contenente NOT EXISTS èvero se l'interrogazione interna restituisce l'insiemevuotofalso se l'interrogazione interna restituisce almenouna tupla
La condizione di correlazione lega l'esecuzione dell'interrogazione interna al valore di attributi della tupla corrente nell'interrogazione esterna
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 83
Interrogazioni nidificate
Correlazione tra interrogazioni
Può essere necessario legare la computazione di un'interrogazione nidificata al valore di uno o piùattributi in un'interrogazione più esterna
il legame è espresso da una o più condizioni di correlazione
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 84
Condizione di correlazione
Una condizione di correlazioneè indicata nella clausola WHERE dell'interrogazione nidificata che la richiedeè un predicato che lega attributi di tabelle nella FROM dell'interrogazione nidificata con attributi di tabelle nella FROM di interrogazioni più esterne
Condizione di correlazione
Una condizione di correlazioneè indicata nella clausola WHERE dell'interrogazione nidificata che la richiedeè un predicato che lega attributi di tabelle nella FROM dell'interrogazione nidificata con attributi di tabelle nella FROM di interrogazioni più esterne
Non si possono esprimere condizioni di correlazione
in interrogazioni allo stesso livello di nidificazionecontenenti riferimenti ad attributi di una tabella nella FROM di un'interrogazione nidificata
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 85
Correlazione tra interrogazioni (n.1)
Per ogni prodotto, trovare il codice del fornitore che ne fornisce la quantità massima
Correlazione tra interrogazioni (n.1)
Per ogni prodotto, trovare il codice del fornitore che ne fornisce la quantità massima
SELECT CodP, CodF FROM FP AS FPXWHERE Qta = (...
)
Quantità massimaper il prodotto
corrente
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 86
Correlazione tra interrogazioni (n.1)
Per ogni prodotto, trovare il codice del fornitore che ne fornisce la quantità massima
SELECT CodP, CodF FROM FP AS FPXWHERE Qta = (SELECT MAX(Qta)
FROM FP AS FPY... )
Quantitàmassima
Correlazione tra interrogazioni (n.1)
Per ogni prodotto, trovare il codice del fornitore che ne fornisce la quantità massima
SELECT CodP, CodF FROM FP AS FPXWHERE Qta = (SELECT MAX(Qta)
FROM FP AS FPYWHERE FPY.CodP=FPX.CodP);
Quantitàmassima
per il prodottocorrente
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 87
Correlazione tra interrogazioni (n.1)
Per ogni prodotto, trovare il codice del fornitore che ne fornisce la quantità massima
SELECT CodP, CodF FROM FP AS FPXWHERE Qta = (SELECT MAX(Qta)
FROM FP AS FPYWHERE FPY. CodP=FPX.CodP);
Condizione di correlazione
Schema di esempio (n.2)
VIAGGIO (CodV, LuogoPartenza, LuogoArrivo, OraPartenza, OraArrivo)
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 88
Correlazione tra interrogazioni (n.2)
Trovare il codice dei viaggi che hanno una durata inferiore alla durata media dei viaggi sullo stesso percorso (caratterizzato dallo stesso luogo di partenza e di arrivo)
VIAGGIO (CodV, LuogoPartenza, LuogoArrivo, OraPartenza, OraArrivo)
Correlazione tra interrogazioni (n.2)
SELECT CodVFROM VIAGGIO AS VAWHERE OraArrivo-OraPartenza < (...
)
VIAGGIO (CodV, LuogoPartenza, LuogoArrivo, OraPartenza, OraArrivo)
Duratamedia
dei viaggisul percorso
corrente
Trovare il codice dei viaggi che hanno una durata inferiore alla durata media dei viaggi sullo stesso percorso (caratterizzato dallo stesso luogo di partenza e di arrivo)
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 89
Correlazione tra interrogazioni (n.2)
SELECT CodVFROM VIAGGIO AS VAWHERE OraArrivo-OraPartenza <
(SELECT AVG(OraArrivo-OraPartenza)FROM VIAGGIO AS VB... )
VIAGGIO (CodV, LuogoPartenza, LuogoArrivo, OraPartenza, OraArrivo)
Duratamedia
dei viaggi
Trovare il codice dei viaggi che hanno una durata inferiore alla durata media dei viaggi sullo stesso percorso (caratterizzato dallo stesso luogo di partenza e di arrivo)
Correlazione tra interrogazioni (n.2)
SELECT CodVFROM VIAGGIO AS VAWHERE OraArrivo-OraPartenza <
(SELECT AVG(OraArrivo-OraPartenza)FROM VIAGGIO AS VBWHERE VB.LuogoPartenza=VA.LuogoPartenza
AND VB.LuogoArrivo=VA.LuogoArrivo);
VIAGGIO (CodV, LuogoPartenza, LuogoArrivo, OraPartenza, OraArrivo)
Trovare il codice dei viaggi che hanno una durata inferiore alla durata media dei viaggi sullo stesso percorso (caratterizzato dallo stesso luogo di partenza e di arrivo)
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 90
Correlazione tra interrogazioni (n.2)
SELECT CodVFROM VIAGGIO AS VAWHERE OraArrivo-OraPartenza <
(SELECT AVG(OraArrivo-OraPartenza)FROM VIAGGIO AS VBWHERE VB.LuogoPartenza=VA.LuogoPartenza
AND VB.LuogoArrivo=VA.LuogoArrivo);
Condizioni di correlazione
VIAGGIO (CodV, LuogoPartenza, LuogoArrivo, OraPartenza, OraArrivo)
Trovare il codice dei viaggi che hanno una durata inferiore alla durata media dei viaggi sullo stesso percorso (caratterizzato dallo stesso luogo di partenza e di arrivo)
Interrogazioni nidificate
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 91
Operazione di divisione (n.1)
Trovare il codice dei fornitori che forniscono tuttii prodotti
Operazione di divisione (n.1)
Trovare il codice dei fornitori che forniscono tuttii prodottiIn algebra si utilizza l’operatore di divisione
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 92
Operazione di divisione (n.1)
πCodF,CodPπCodP
/
FP P
R
Trovare il codice dei fornitori che forniscono tuttii prodottiIn algebra si utilizza l’operatore di divisione
Divisione in SQL (n.1)
Trovare il codice dei fornitori che forniscono tuttii prodotti
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 93
Divisione in SQL (n.1)
Trovare il codice dei fornitori che forniscono tuttii prodotti
Osservazionetutti i prodotti che possono essere forniti sonocontenuti nella tabella P
Divisione in SQL (n.1)
Osservazionetutti i prodotti che possono essere forniti sonocontenuti nella tabella P
un fornitore fornisce tutti i prodotti se fornisce un numero di prodotti diversi pari alla cardinalità di P
Trovare il codice dei fornitori che forniscono tuttii prodotti
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 94
Divisione in SQL (n.1)
Trovare il codice dei fornitori che forniscono tuttii prodotti
Divisione in SQL (n.1)
SELECT COUNT(*)FROM P
Trovare il codice dei fornitori che forniscono tuttii prodotti
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 95
Divisione in SQL (n.1)
SELECT COUNT(*)FROM P
Numero totale di prodotti
Trovare il codice dei fornitori che forniscono tuttii prodotti
Divisione in SQL (n.1)
SELECT CodF FROM FPGROUP BY CodF… (SELECT COUNT(*)
FROM P)
Trovare il codice dei fornitori che forniscono tuttii prodotti
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 96
Divisione in SQL (n.1)
SELECT CodF FROM FPGROUP BY CodFHAVING COUNT(*)=(SELECT COUNT(*)
FROM P);
Trovare il codice dei fornitori che forniscono tuttii prodotti
Divisione in SQL (n.2)
Trovare il codice dei fornitori che forniscono almeno tutti i prodotti forniti dal fornitore F2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 97
Divisione in SQL: procedimento (n.2)
Trovare il codice dei fornitori che forniscono almeno tutti i prodotti forniti dal fornitore F2Si esegue
il conteggio del numero di prodotti forniti da F2
Divisione in SQL: procedimento (n.2)
Trovare il codice dei fornitori che forniscono almeno tutti i prodotti forniti dal fornitore F2Si esegue
il conteggio del numero di prodotti forniti da F2il conteggio del numero di prodotti forniti da un fornitore arbitrario e anche da F2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 98
Divisione in SQL: procedimento (n.2)
Trovare il codice dei fornitori che forniscono almeno tutti i prodotti forniti dal fornitore F2Si esegue
il conteggio del numero di prodotti forniti da F2il conteggio del numero di prodotti forniti da un fornitore arbitrario e anche da F2
I due conteggi devono essere uguali
Divisione in SQL (n.2)
Trovare il codice dei fornitori che forniscono almeno tutti i prodotti forniti dal fornitore F2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 99
Divisione in SQL (n.2)
Trovare il codice dei fornitori che forniscono almeno tutti i prodotti forniti dal fornitore F2
SELECT COUNT(*)FROM FPWHERE CodF='F2'
Divisione in SQL (n.2)
Trovare il codice dei fornitori che forniscono almeno tutti i prodotti forniti dal fornitore F2
SELECT COUNT(*)FROM FPWHERE CodF='F2'
Numero di prodotti
forniti da F2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 100
Divisione in SQL (n.2)
Trovare il codice dei fornitori che forniscono almeno tutti i prodotti forniti dal fornitore F2
SELECT CodF FROM FPWHERE CodP IN (SELECT CodP
FROM FPWHERE CodF='F2')
GROUP BY CodF... (SELECT COUNT(*)
FROM FPWHERE CodF='F2')
Divisione in SQL (n.2)
Trovare il codice dei fornitori che forniscono almeno tutti i prodotti forniti dal fornitore F2
SELECT CodF FROM FPWHERE CodP IN (SELECT CodP
FROM FPWHERE CodF='F2')
GROUP BY CodFHAVING COUNT(*)=(SELECT COUNT(*)
FROM FPWHERE CodF='F2');
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 101
Interrogazioni nidificate
Schema di esempio
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)CORSO (CodC, NomeC)
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 102
Calcolo di aggregati a due livelli (n.1)
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)
Trovare la media massima (conseguita da uno studente)
Calcolo di aggregati a due livelli (n.1)
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)
Risoluzione in 2 passitrovare la media per ogni studentetrovare il valore massimo della media
Trovare la media massima (conseguita da uno studente)
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 103
Calcolo di aggregati a due livelli (n.1)
SELECT Matricola, AVG(Voto) AS MediaStudentiFROM ESAME-SUPERATOGROUP BY Matricola
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)
Trovare la media massima (conseguita da uno studente)
passo 1: media per ogni studente
Calcolo di aggregati a due livelli (n.1)
(SELECT Matricola, AVG(Voto) AS MediaStudentiFROM ESAME-SUPERATOGROUP BY Matricola) AS MEDIE
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)
Trovare la media massima (conseguita da uno studente)
passo 1: media per ogni studente
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 104
Calcolo di aggregati a due livelli (n.1)
SELECT ...FROM (SELECT Matricola, AVG(Voto) AS MediaStudenti
FROM ESAME-SUPERATOGROUP BY Matricola) AS MEDIE
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)
Trovare la media massima (conseguita da uno studente)
passo 2: valore massimo della media
Calcolo di aggregati a due livelli (n.1)
SELECT MAX(MediaStudenti)FROM (SELECT Matricola, AVG(Voto) AS MediaStudenti
FROM ESAME-SUPERATOGROUP BY Matricola) AS MEDIE;
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)
Trovare la media massima (conseguita da uno studente)
passo 2: valore massimo della media
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 105
Table functions (n.1)
SELECT MAX(MediaStudenti)FROM (SELECT Matricola, AVG(Voto) AS MediaStudenti
FROM ESAME-SUPERATOGROUP BY Matricola) AS MEDIE;
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)
Table function
Trovare la media massima (conseguita da uno studente)
Table function
Definisce una tabella temporanea che può essere utilizzata per ulteriori operazioni di calcoloLa table function
ha la struttura di una SELECTè definita all'interno di una clausola FROMpuò essere referenziata come una normale tabella
La table function permette dicalcolare più livelli di aggregazioneformulare in modo equivalente le interrogazioniche richiedono la correlazione
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 106
Table functions (n.2)
Per ogni anno di iscrizione, trovare la media massima (conseguita da uno studente)
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)
Table functions (n.2)
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)
Risoluzione in 2 passitrovare la media per ogni studenteraggruppare gli studenti per anno di iscrizione e calcolare la media massima
Per ogni anno di iscrizione, trovare la media massima (conseguita da uno studente)
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 107
Table functions (n.2)
(SELECT Matricola, AVG(Voto) AS MediaStudenteFROM ESAME-SUPERATOGROUP BY Matricola) AS MEDIE
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)
Per ogni anno di iscrizione, trovare la media massima (conseguita da uno studente)
passo 1
Table functions (n.2)
SELECT ...FROM STUDENTE,
(SELECT Matricola, AVG(Voto) AS MediaStudenteFROM ESAME-SUPERATOGROUP BY Matricola) AS MEDIE
WHERE STUDENTE.Matricola=MEDIE.Matricola...
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)
Table function
Per ogni anno di iscrizione, trovare la media massima (conseguita da uno studente)
passo 2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 108
Table functions (n.2)
SELECT ...FROM STUDENTE,
(SELECT Matricola, AVG(Voto) AS MediaStudenteFROM ESAME-SUPERATOGROUP BY Matricola) AS MEDIE
WHERE STUDENTE.Matricola=MEDIE.Matricola...
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)
Condizione di join
Per ogni anno di iscrizione, trovare la media massima (conseguita da uno studente)
passo 2
Table functions (n.2)
SELECT ...FROM STUDENTE,
(SELECT Matricola, AVG(Voto) AS MediaStudenteFROM ESAME-SUPERATOGROUP BY Matricola) AS MEDIE
WHERE STUDENTE.Matricola=MEDIE.MatricolaGROUP BY AnnoIscrizione
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)
Per ogni anno di iscrizione, trovare la media massima (conseguita da uno studente)
passo 2
Linguaggio SQL: fondamenti Interrogazioni nidificate
©2007 Politecnico di Torino 109
Table functions (n.2)
SELECT AnnoIscrizione, MAX(MediaStudente)FROM STUDENTE,
(SELECT Matricola, AVG(Voto) AS MediaStudenteFROM ESAME-SUPERATOGROUP BY Matricola) AS MEDIE
WHERE STUDENTE.Matricola=MEDIE.MatricolaGROUP BY AnnoIscrizione;
STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)
Per ogni anno di iscrizione, trovare la media massima (conseguita da uno studente)
passo 2