DB
MG
Linguaggio SQL: fondamenti
DB
MG
Interrogazioni nidificate
Introduzione Operatore IN Operatore NOT IN Costruttore di tupla Operatore EXISTS Operatore NOT EXISTS Correlazione tra interrogazioni Operazione di divisione
Table functions
DB
MG
Interrogazioni nidificate
DB
MG
Introduzione
Un'interrogazione nidificata è un'istruzioneSELECT contenuta all'interno di un'altra interrogazione
la nidificazione di interrogazioni permette di suddividere un problema complesso in sottoproblemi più semplici
È possibile introdurre istruzioni SELECT in un predicato nella clausola WHERE in un predicato nella clausola HAVING nella clausola FROM
DB
MG
DB forniture prodotti (1/2)
P (CodP, NomeP, Colore, Taglia, Magazzino) F (CodF, NomeF, NSoci, Sede)
FP (CodF, CodP, Qta)
DB
MG
DB forniture prodotti (2/2)
CodF NomeF NSoci Sede F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
F
FP
CodF CodP Qta F1 P1 300 F1 P2 200 F1 P3 400 F1 P4 200 F1 P5 100 F1 P6 100 F2 P1 300 F2 P2 400 F3 P2 200 F4 P3 200 F4 P4 300 F4 P5 400 CodP NomeP Colore Taglia Magazzino
P1 Maglia Rosso 40 Torino P2 Jeans Verde 48 Milano
P3 Camicia Blu 48 Roma
P4 Camicia Blu 44 Torino
P5 Gonna Blu 40 Milano
P6 Bermuda Rosso 42 Torino
P
DB
MG
Interrogazioni nidificate (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1La formulazione mediante interrogazioni nidificate consente di separare il problema in due
sottoproblemi
sede del fornitore F1
codici dei fornitori con la stessa sede
DB
MG
Interrogazioni nidificate (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1SELECT CodF FROM F
WHERE Sede = (SELECT Sede FROM F
WHERE CodF='F1');
È possibile utilizzare '=' esclusivamente se è noto a priori che il risultato della SELECT nidificata è sempre un solo valore
DB
MG
Formulazione equivalente (n.1)
Trovare il codice dei fornitori che hanno sedenella stessa città di F1
È possibile definire una formulazione equivalente con il join
DB
MG
Formulazione equivalente
La formulazione equivalente con il join ècaratterizzata da
Clausola FROM contenente le tabelle referenziate nelle FROM di tutte le SELECT
Opportune condizioni di join nella clausola WHERE Eventuali predicati di selezione aggiunti nella clausola WHERE
DB
MG
Clausola FROM (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1SELECT CodF FROM F
WHERE Sede = (SELECT Sede FROM F
WHERE CodF='F1');
FX
FY
DB
MG
Clausola FROM (n.1)
Trovare il codice dei fornitori che hanno sede nella stessa città di F1SELECT ...
FROM F AS FX, F AS FY ...
DB
MG
Condizione di join (n.1)
Trovare il codice dei fornitori che hanno sedenella stessa città di F1
SELECT CodF FROM F
WHERE Sede = (SELECT Sede FROM F
WHERE CodF='F1');
DB
MG
Condizione di join (n.1)
Trovare il codice dei fornitori che hanno sedenella stessa città di F1
SELECT ...
FROM F AS FX, F AS FY WHERE FX.Sede=FY.Sede ...
DB
MG
Predicato di selezione (n.1)
Trovare il codice dei fornitori che hanno sedenella stessa città di F1
SELECT CodF FROM F
WHERE Sede = (SELECT Sede FROM F
WHERE CodF='F1');
DB
MG
Clausola SELECT (n.1)
Trovare il codice dei fornitori che hanno sedenella stessa città di F1
SELECT FY.CodF FROM F AS FX, F AS FY WHERE FX.Sede=FY.Sede AND FX.CodF='F1';
DB
MG
Formulazione equivalente (n.2)
Trovare il codice dei fornitori il cui numero di soci è minore del numero massimo di sociSELECT CodF FROM F
WHERE NSoci < (SELECT MAX(NSoci) FROM F);
È possibile definire una formulazione equivalente con il join?
DB
MG
Formulazione equivalente (n.2)
Trovare il codice dei fornitori il cui numero di soci è minore del numero massimo di sociSELECT CodF FROM F
WHERE NSoci < (SELECT MAX(NSoci) FROM F);
Non è possibile definire una formulazione equivalente con il join
DB
MG
Interrogazioni nidificate
DB
MG
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono ilprodotto P2
Scomposizione del problema in due sottoproblemi codici dei fornitori del prodotto P2
nome dei fornitori aventi quei codici
DB
MG
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono ilprodotto P2
SELECT CodF FROM FP
WHERE CodP='P2'
Codici dei fornitori
di P2 CodF
F1 F2 F3
FP
CodF CodP Qta F1 P1 300 F1 P2 200 F1 P3 400 F1 P4 200 F1 P5 100 F1 P6 100 F2 P1 300 F2 P2 400 F3 P2 200 F4 P3 200 F4 P4 300 F4 P5 400
DB
MG
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono ilprodotto P2
SELECT NomeF FROM F
WHERE CodF (SELECT CodF FROM FP
WHERE CodP='P2')
DB
MG
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono ilprodotto P2
SELECT NomeF FROM F
WHERE CodF (SELECT CodF FROM FP
WHERE CodP='P2')
?
DB
MG
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono ilprodotto P2
SELECT NomeF FROM F
WHERE CodF IN (SELECT CodF FROM FP
WHERE CodP='P2');
Appartenenza all'insieme
DB
MG
Operatore IN
Esprime il concetto di appartenenza ad uninsieme di valori
NomeAttributo IN (InterrogazioneNidificata)
Permette di scrivere l'interrogazione scomponendo il problema in sottoproblemi seguendo un procedimento “bottom-up”
DB
MG
Formulazione equivalente
La formulazione equivalente con il join ècaratterizzata da
clausola FROM contenente le tabelle referenziate nelle FROM di tutte le SELECT
opportune condizioni di join nella clausola WHERE eventuali predicati di selezione aggiunti nella clausola WHERE
DB
MG
Operatore IN (n.1)
Trovare il nome dei fornitori che forniscono ilprodotto P2
SELECT NomeF FROM F
WHERE CodF IN (SELECT CodF FROM FP
WHERE CodP='P2');
DB
MG
Formulazione equivalente (n.1)
Trovare il nome dei fornitori che forniscono ilprodotto P2
SELECT NomeF FROM F, FP
WHERE F.CodF=FP.CodF AND CodP='P2';
DB
MG
Operatore IN (n.2)
Trovare il nome dei fornitori che fornisconoalmeno un prodotto rosso
Scomposizione del problema in sottoproblemi codici dei prodotti rossi
codici dei fornitori di quei prodotti nomi dei fornitori aventi quei codici
DB
MG
Operatore IN (n.2)
SELECT NomeF FROM F
WHERE CodF IN (SELECT CodF FROM FP
WHERE CodP IN (SELECT CodP
FROM P
WHERE Colore='Rosso'));
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
DB
MG
Formulazione equivalente (n.2)
Trovare il nome dei fornitori che fornisconoalmeno un prodotto rosso
SELECT NomeF FROM F
WHERE CodF IN (SELECT CodF FROM FP
WHERE CodP IN (SELECT CodP
FROM P
WHERE Colore='Rosso'));
DB
MG
SELECT NomeF FROM F
WHERE CodF IN (SELECT CodF FROM FP
WHERE CodP IN (SELECT CodP
FROM P
WHERE Colore='Rosso'));
Clausola FROM (n.2)
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
DB
MG
Clausola FROM (n.2)
Trovare il nome dei fornitori che fornisconoalmeno un prodotto rosso
SELECT ...
FROM F, FP, P ...
DB
MG
SELECT NomeF FROM F
WHERE CodF IN (SELECT CodF FROM FP
WHERE CodP IN (SELECT CodP
FROM P
WHERE Colore='Rosso'));
Condizioni di join (n.2)
1
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
DB
MG
Condizioni di join (n.2)
1
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
SELECT ...
FROM F, FP, P
WHERE P.CodF=F.CodF
DB
MG
Condizioni di join (n.2)
SELECT NomeF FROM F
WHERE CodF IN (SELECT CodF FROM FP
WHERE CodP IN (SELECT CodP
FROM P
WHERE Colore='Rosso'));
2
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
DB
MG
Condizioni di join (n.2)
SELECT ...
FROM F, FP, P
WHERE P.CodF=F.CodF AND FP.CodP=P.CodP ...
2
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
DB
MG
Predicato di selezione (n.2)
SELECT NomeF FROM F
WHERE CodF IN (SELECT CodF FROM FP
WHERE CodP IN (SELECT CodP
FROM P
WHERE Colore='Rosso'));
Trovare il nome dei fornitori che forniscono almeno un prodotto rosso
DB
MG
Clausola SELECT (n.2)
Trovare il nome dei fornitori che fornisconoalmeno un prodotto rosso
SELECT NomeF FROM F, FP, P
WHERE P.CodF=F.CodF AND FP.CodP=P.CodP AND Colore='Rosso'
DB
MG
Esempio complesso (n.3)
Trovare il nome dei fornitori che fornisconoalmeno un prodotto fornito da fornitori di prodotti rossi
DB
MG
Esempio complesso (n.3)
Trovare il nome dei fornitori che fornisconoalmeno un prodotto fornito da fornitori di prodotti rossi
DB
MG
Esempio complesso (n.3)
Trovare il nome dei fornitori che fornisconoalmeno un prodotto fornito da fornitori di prodotti rossi
La formulazione con il join è difficile è più semplice scomporre il problema in
sottoproblemi mediante interrogazioni nidificate
DB
MG
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
DB
MG
Esempio complesso (n.3)
SELECT CodF FROM FP WHERE 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
DB
MG
Esempio complesso (n.3)
SELECT CodP FROM FP WHERE CodF IN
(SELECT CodF FROM FP WHERE 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
DB
MG
Esempio complesso (n.3)
SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM FP WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM P
WHERE Colore='Rosso'))) Codici dei fornitori
di prodotti forniti da fornitori di
prodotti rossi
DB
MG
Interrogazione completa (n.3)
SELECT NomeF FROM F WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM FP WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM P
WHERE Colore='Rosso'))));
DB
MG
Formulazione con il join (n.3)
DB
MG
Formulazione con il join (n.3)
SELECT NomeF FROM F WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM FP WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM P
WHERE Colore='Rosso'))));
DB
MG
Clausola FROM (n.3)
SELECT NomeF FROM F WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM FP WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM P
WHERE Colore='Rosso'))));
DB
MG
Clausola FROM (n.3)
SELECT NomeF FROM F WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM FP WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM P
WHERE Colore='Rosso'))));
FPA
FPB
FPC
DB
MG
Clausola FROM (n.3)
SELECT ...
FROM F, FP AS FPA, FP AS FPB, FP AS FPC, P ...
DB
MG
Condizioni di join (n.3)
SELECT NomeF FROM F WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM FP WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM P
WHERE Colore='Rosso'))));
1
FPA
DB
MG
Condizioni di join (n.3)
SELECT ...
FROM F, FP AS FPA, FP AS FPB, FP AS FPC, P WHERE F.CodF=FPA.CodF
...
1
DB
MG
Condizioni di join (n.3)
SELECT NomeF FROM F WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM FP WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM P
WHERE Colore='Rosso'))));
2 FPA
FPB
DB
MG
Condizioni di join (n.3)
SELECT ...
FROM F, FP AS FPA, FP AS FPB, FP AS FPC, P WHERE F.CodF=FPA.CodF AND
FPA.CodP=FPB.CodP ...
2
DB
MG
Condizioni di join (n.3)
SELECT NomeF FROM F WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM FP WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM P
WHERE Colore='Rosso'))));
3 FPB
FPC
DB
MG
Condizioni di join (n.3)
SELECT ...
FROM F, FP AS FPA, FP AS FPB, FP AS FPC, P WHERE F.CodF=FPA.CodF AND
FPA.CodP=FPB.CodP AND FPB.CodF=FPC.CodF ...
3
DB
MG
Condizioni di join (n.3)
SELECT NomeF FROM F WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM FP WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM P
WHERE Colore='Rosso'))));
4 FPC
DB
MG
Condizioni di join (n.3)
SELECT ...
FROM F, FP AS FPA, FP AS FPB, FP AS FPC, P WHERE F.CodF=FPA.CodF AND
FPA.CodP=FPB.CodP AND FPB.CodF=FPC.CodF AND FPC.CodP=P.CodP
... 4
DB
MG
Predicato di selezione (n.3)
SELECT NomeF FROM F WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM FP WHERE CodF IN
(SELECT CodF FROM FP WHERE CodP IN
(SELECT CodP FROM P
WHERE Colore='Rosso'))));
DB
MG
Predicato di selezione (n.3)
SELECT ...
FROM F, FP AS FPA, FP AS FPB, FP AS FPC, P WHERE F.CodF=FPA.CodF AND
FPA.CodP=FPB.CodP AND FPB.CodF=FPC.CodF AND FPC.CodP=P.CodP AND Colore='Rosso'
DB
MG
Clausola SELECT (n.3)
SELECT NomeF
FROM F, FP AS FPA, FP AS FPB, FP AS FPC, P WHERE F.CodF=FPA.CodF AND
FPA.CodP=FPB.CodP AND FPB.CodF=FPC.CodF AND FPC.CodP=P.CodP AND Colore='Rosso';
DB
MG
Interrogazioni nidificate
DB
MG
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, FP
WHERE F.CodF=FP.CodF AND CodP<>'P2';
DB
MG
Soluzione errata (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2non è possibile esprimere l'interrogazione mediante il join
SELECT NomeF FROM F, FP
WHERE F.CodF=FP.CodF AND CodP<>'P2';
DB
MG
Soluzione errata (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2F
FP
CodF CodP Qta F1 P1 300 F1 P2 200 F1 P3 400 F1 P4 200 F1 P5 100 F1 P6 100 F2 P1 300 F2 P2 400 F3 P2 200 F4 P3 200 F4 P4 300 F4 P5 400 NomeF
Andrea Luca Gabriele
R
CodF NomeF NSoci Sede F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
DB
MG
Soluzione errata (n.1)
SELECT NomeF FROM F, FP
WHERE F.CodF=FP.CodF AND CodP<> 'P2';
A che interrogazione corrisponde?
DB
MG
Soluzione errata (n.1)
SELECT NomeF FROM F, FP
WHERE F.CodF=FP.CodF AND CodP<> 'P2';
Trovare il nome dei fornitori che forniscono almeno un prodotto diverso da P2
DB
MG
Concetto di esclusione (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2Occorre escludere dal risultato
i fornitori che forniscono il prodotto P2
DB
MG
Concetto di esclusione (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2SELECT CodF FROM FP
WHERE CodP='P2'
Codici dei fornitori che forniscono P2
DB
MG
Concetto di esclusione (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2SELECT NomeF FROM F
WHERE CodF (SELECT CodF FROM FP
WHERE CodP='P2');
Codici dei fornitori che forniscono P2
?
DB
MG
Operatore NOT IN (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2SELECT NomeF FROM F
WHERE CodF NOT IN (SELECT CodF FROM FP
WHERE CodP='P2');
Codici dei fornitori che forniscono P2 Non appartiene
DB
MG
Operatore NOT IN
Esprime il concetto di esclusione da un insieme di valoriNomeAttributo NOT IN (InterrogazioneNidificata)
Richiede di individuare in modo appropriato l’insieme da escludere
definito dall’interrogazione nidificata
DB
MG
NOT IN e algebra relazionale (n.1)
-
pNomeF
pCodF F
FP pCodF sCodP='P2'
F
Trovare il nome dei fornitori che non forniscono il prodotto P2
DB
MG
NOT IN e algebra relazionale (n.1)
F
FP p
sCodP='P2' pNomeF
p: F.CodF=FP.CodF
-
pNomeF
pCodF F
FP pCodF sCodP='P2'
F
Trovare il nome dei fornitori che non forniscono il prodotto P2
DB
MG
Operatore NOT IN (n.2)
Trovare il nome dei fornitori di P2 che non hanno mai fornito prodotti diversi da P2
Insieme da escludere
fornitori di prodotti diversi da P2
Trovare il nome dei fornitori che forniscono solo il prodotto P2
DB
MG
Operatore NOT IN (n.2)
Trovare il nome dei fornitori che forniscono solo il prodotto P2SELECT CodF FROM FP
WHERE CodP<>'P2'
Codici dei fornitori che forniscono
almeno un prodotto diverso
da P2
DB
MG
Operatore NOT IN (n.2)
SELECT NomeF FROM F
WHERE CodF NOT IN (SELECT CodF FROM FP
WHERE CodP<>'P2') ...
Trovare il nome dei fornitori che forniscono solo il prodotto P2
DB
MG
Operatore NOT IN (n.2)
SELECT NomeF FROM F, FP
WHERE F.CodF NOT IN (SELECT F.CodF FROM FP
WHERE CodP<>'P2') AND F.CodF=FP.CodF;
Trovare il nome dei fornitori che forniscono solo il prodotto P2
DB
MG
Soluzione alternativa (n.2)
Trovare il nome dei fornitori che forniscono solo il prodotto P2SELECT NomeF FROM F
WHERE F.CodF NOT IN (SELECT CodF FROM FP
WHERE CodP<>'P2') AND F.CodF IN (SELECT CodF
FROM FP);
DB
MG
Operatore NOT IN (n.3)
Trovare il nome dei fornitori che non forniscono prodotti rossiDB
MG
Operatore NOT IN (n.3)
Trovare il nome dei fornitori che non forniscono prodotti rossiInsieme da escludere?
i fornitori di prodotti rossi, identificati dal loro codice
DB
MG
Operatore NOT IN (n.3)
(SELECT CodF FROM FP
WHERE CodP IN (SELECT CodP FROM P
WHERE Colore='Rosso') Codici dei fornitori
di prodotti rossi
Trovare il nome dei fornitori che non forniscono prodotti rossi
DB
MG
Operatore NOT IN (n.3)
SELECT NomeF FROM F
WHERE CodF NOT IN (SELECT CodF FROM FP
WHERE CodP IN (SELECT CodP FROM P
WHERE Colore='Rosso'));
Trovare il nome dei fornitori che non forniscono prodotti rossi
DB
MG
Alternativa (corretta?) (n.3)
SELECT CodF FROM FP
WHERE CodP NOT IN (SELECT CodP FROM P
WHERE Colore='Rosso') Codici dei fornitori
che forniscono almeno un prodotto non rosso
Trovare il nome dei fornitori che non forniscono prodotti rossi
DB
MG
Alternativa (corretta?) (n.3)
SELECT NomeF FROM F
WHERE CodF IN (SELECT CodF FROM FP
WHERE CodP NOT IN (SELECT CodP FROM P
WHERE Colore='Rosso'));
Trovare il nome dei fornitori che non forniscono prodotti rossi
DB
MG
Alternativa errata (n.3)
SELECT NomeF FROM F
WHERE CodF IN (SELECT CodF FROM FP
WHERE CodP NOT IN (SELECT CodP FROM P
WHERE Colore='Rosso'));
Trovare il nome dei fornitori che non forniscono prodotti rossi
DB
MG
Alternativa errata (n.3)
SELECT NomeF FROM F
WHERE CodF IN (SELECT CodF FROM FP
WHERE CodP NOT IN (SELECT CodP FROM P
WHERE Colore='Rosso'));
Codici dei fornitori di prodotti
non rossi
Trovare il nome dei fornitori che non forniscono prodotti rossi
DB
MG
Alternativa errata (n.3)
CodF NomeF NSoci Sede F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
F
FP
CodF CodP Qta
F1 P1 300
F1 P2 200
F1 P3 400
F1 P4 200
F1 P5 100
F1 P6 100
F2 P1 300
F2 P2 400
F3 P2 200
F4 P3 200
F4 P4 300
F4 P5 400
CodP NomeP Colore Taglia Magazzino P1 Maglia Rosso 40 Torino P2 Jeans Verde 48 Milano
P3 Camicia Blu 48 Roma
P4 Camicia Blu 44 Torino
P5 Gonna Blu 40 Milano
P6 Bermuda Rosso 42 Torino
P
Trovare il nome dei fornitori che non forniscono prodotti rossi
DB
MG
Alternativa errata (n.3)
CodF NomeF NSoci Sede F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
FP
CodF CodP Qta
F1 P1 300
F1 P2 200
F1 P3 400
F1 P4 200
F1 P5 100
F1 P6 100
F2 P1 300
F2 P2 400
F3 P2 200
F4 P3 200
F4 P4 300
F4 P5 400
F
P CodP NomeP Colore Taglia Magazzino P1 Maglia Rosso 40 Torino P2 Jeans Verde 48 Milano
P3 Camicia Blu 48 Roma
P4 Camicia Blu 44 Torino
P5 Gonna Blu 40 Milano
P6 Bermuda Rosso 42 Torino
Trovare il nome dei fornitori che non forniscono prodotti rossi
DB
MG
Alternativa errata (n.3)
SELECT NomeF FROM F
WHERE CodF IN (SELECT CodF FROM FP
WHERE CodP NOT IN (SELECT CodP FROM P
WHERE Colore='Rosso'));
L'insieme di elementi da escludere non è corretto Trovare il nome dei fornitori che non forniscono prodotti rossi
DB
MG
Interrogazioni nidificate
DB
MG
Costruttore di tupla
Permette di definire la struttura temporanea di una tuplasi elencano gli attributi che ne fanno parte tra ()
Permette di estendere il poter espressivo degli operatori IN e NOT IN
(NomeAttributo1, NomeAttributo2, ...)
DB
MG
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)
DB
MG
Esempio (n.1)
SELECT LuogoPartenza, LuogoArrivo FROM VIAGGIO
WHERE (LuogoPartenza, LuogoArrivo) NOT IN (SELECT LuogoPartenza, LuogoArrivo FROM VIAGGIO
WHERE OraArrivo-OraPartenza>2);
Costruttore di 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)
DB
MG
Interrogazioni nidificate
DB
MG
Operatore EXISTS (n.1)
Trovare il nome dei fornitori del prodotto P2Trovare il nome dei fornitori per cui esiste una fornitura del prodotto P2
DB
MG
Condizione di correlazione (n.1)
Trovare il nome dei fornitori del prodotto P2SELECT NomeF FROM F
WHERE EXISTS (SELECT * FROM FP
WHERE CodP='P2'
AND FP.CodF=F.CodF );
Condizione di correlazione
DB
MG
Funzionamento di EXISTS (n.1)
CodF CodP Qta F1 P1 300 F1 P2 200 F1 P3 400 F1 P4 200 F1 P5 100 F1 P6 100 F2 P1 300 F2 P2 400 F3 P2 200 F4 P3 200 F4 P4 300 F4 P5 400 CodF NomeF NSoci Città
F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
F FP
SELECT * FROM FP
WHERE CodP='P2' AND FP.CodF='F1'
Valore di CodF nella riga corrente di F
Trovare il nome dei fornitori del prodotto P2
DB
MG
Funzionamento di EXISTS (n.1)
CodF CodP Qta F1 P1 300 F1 P2 200 F1 P3 400 F1 P4 200 F1 P5 100 F1 P6 100 F2 P1 300 F2 P2 400 F3 P2 200 F4 P3 200 F4 P4 300 F4 P5 400 CodF NomeF NSoci Città
F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
F FP
Il predicato con EXISTS è vero per F1 poiché esiste una fornitura di P2 per F1
F1 fa parte del risultato dell'interrogazione
Trovare il nome dei fornitori del prodotto P2
DB
MG
Funzionamento di EXISTS (n.1)
CodF CodP Qta F1 P1 300 F1 P2 200 F1 P3 400 F1 P4 200 F1 P5 100 F1 P6 100 F2 P1 300 F2 P2 400 F3 P2 200 F4 P3 200 F4 P4 300 F4 P5 400 CodF NomeF NSoci Città
F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
F FP
Il predicato con EXISTS è falso per F4 poiché non esiste una fornitura di P2 per F4
F4 non fa parte del risultato dell'interrogazione
Trovare il nome dei fornitori del prodotto P2
DB
MG
Risultato dell’interrogazione (n.1)
NomeF Andrea Luca Antonio
R
Trovare il nome dei fornitori del prodotto P2
DB
MG
Predicati con EXISTS
Il predicato contenente EXISTS è
vero se l'interrogazione interna restituisce almeno una tupla
falso se l'interrogazione interna restituisce l'insieme vuoto
DB
MG
Predicati con EXISTS
Il predicato contenente EXISTS è
vero se l'interrogazione interna restituisce almeno una tupla
falso 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
La condizione di correlazione lega l'esecuzione dell'interrogazione interna al valore di attributi della tupla corrente nell'interrogazione esterna
DB
MG
Visibilità degli attributi
Un'interrogazione nidificata può far riferimento ad attributi definiti in interrogazioni più esterne Un'interrogazione non può far riferimento ad attributi referenziatiin un'interrogazione nidificata al suo interno in un'interrogazione allo stesso livello
DB
MG
Interrogazioni nidificate
DB
MG
Operatore NOT EXISTS (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2Trovare il nome dei fornitori per cui non esiste una fornitura del prodotto P2
DB
MG
Operatore NOT EXISTS (n.1)
Trovare il nome dei fornitori che non forniscono il prodotto P2SELECT NomeF FROM F
WHERE NOT EXISTS (SELECT * FROM FP
WHERE CodP='P2'
AND FP.CodF=F.CodF );
Condizione di correlazione
DB
MG
Trovare il nome dei fornitori che non forniscono il prodotto P2
Funzionamento di NOT EXISTS (n.1)
SELECT * FROM FP
WHERE CodP='P2' AND
FP.CodF='F1' Valore di CodF nella riga corrente di F CodF NomeF NSoci Città
F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
F
DB
MG
CodF NomeF NSoci Città F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
CodF CodP Qta
F1 P1 300
F1 P2 200
F1 P3 400
F1 P4 200
F1 P5 100
F1 P6 100
F2 P1 300
F2 P2 400
F3 P2 200
F4 P3 200
F4 P4 300
F4 P5 400
Trovare il nome dei fornitori che non forniscono il prodotto P2
Funzionamento di NOT EXISTS (n.1)
SELECT * FROM FP
WHERE CodP='P2' AND FP.CodF='F1'
F FP
DB
MG
Trovare il nome dei fornitori che non forniscono il prodotto P2
CodF NomeF NSoci Città F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
CodF CodP Qta
F1 P1 300
F1 P2 200
F1 P3 400
F1 P4 200
F1 P5 100
F1 P6 100
F2 P1 300
F2 P2 400
F3 P2 200
F4 P3 200
F4 P4 300
F4 P5 400
Funzionamento di NOT EXISTS (n.1)
Il predicato con NOT EXISTS è falso per F1 perché esiste una fornitura di P2 per F1
F1 non fa parte del risultato dell'interrogazione
F FP
DB
MG
Trovare il nome dei fornitori che non forniscono il prodotto P2
CodF NomeF NSoci Città F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
Funzionamento di NOT EXISTS (n.1)
CodF CodP Qta
F1 P1 300
F1 P2 200
F1 P3 400
F1 P4 200
F1 P5 100
F1 P6 100
F2 P1 300
F2 P2 400
F3 P2 200
F4 P3 200
F4 P4 300
F4 P5 400
F FP
DB
MG
Trovare il nome dei fornitori che non forniscono il prodotto P2
CodF NomeF NSoci Città F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
Funzionamento di NOT EXISTS (n.1)
F
DB
MG
Trovare il nome dei fornitori che non forniscono il prodotto P2
Funzionamento di NOT EXISTS (n.1)
CodF NomeF NSoci Città F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
CodF CodP Qta
F1 P1 300
F1 P2 200
F1 P3 400
F1 P4 200
F1 P5 100
F1 P6 100
F2 P1 300
F2 P2 400
F3 P2 200
F4 P3 200
F4 P4 300
F4 P5 400
F FP
DB
MG
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 esiste una fornitura di P2 per F4
F4 fa parte del risultato dell'interrogazione
CodF NomeF NSoci Città F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
CodF CodP Qta
F1 P1 300
F1 P2 200
F1 P3 400
F1 P4 200
F1 P5 100
F1 P6 100
F2 P1 300
F2 P2 400
F3 P2 200
F4 P3 200
F4 P4 300
F4 P5 400
F FP
DB
MG
Funzionamento di NOT EXISTS (n.1)
CodF NomeF NSoci Città F1 Andrea 2 Torino
F2 Luca 1 Milano
F3 Antonio 3 Milano F4 Gabriele 2 Torino F5 Matteo 3 Venezia
F CodF CodP Qta
F1 P1 300
F1 P2 200
F1 P3 400
F1 P4 200
F1 P5 100
F1 P6 100
F2 P1 300
F2 P2 400
F3 P2 200
F4 P3 200
F4 P4 300
F4 P5 400
FP
Trovare il nome dei fornitori che non forniscono il prodotto P2
DB
MG
Risultato dell’interrogazione (n.1)
NomeF Gabriele Matteo
R
Trovare il nome dei fornitori che non forniscono il prodotto P2
DB
MG
Predicato con NOT EXISTS
Il predicato contenente NOT EXISTS è
vero se l'interrogazione interna restituisce l'insieme vuoto
falso se l'interrogazione interna restituisce almeno una tupla
La condizione di correlazione lega l'esecuzione dell'interrogazione interna al valore di attributi della tupla corrente nell'interrogazione esterna
DB
MG
Interrogazioni nidificate
DB
MG
Correlazione tra interrogazioni
Può essere necessario legare la computazione di un'interrogazione nidificata al valore di uno o più attributi in un'interrogazione più esternail legame è espresso da una o più condizioni di correlazione
DB
MG
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 nidificazione contenenti riferimenti ad attributi di una tabella nella FROM di un'interrogazione nidificata
DB
MG
Correlazione tra interrogazioni (n.1)
Per ogni prodotto, trovare il codice del fornitore che ne fornisce la quantità massimaDB
MG
Correlazione tra interrogazioni (n.1)
Per ogni prodotto, trovare il codice del fornitore che ne fornisce la quantità massimaSELECT CodP, CodF FROM FP AS FPX WHERE Qta = (...
)
Quantità massima per il prodotto
corrente
DB
MG
Correlazione tra interrogazioni (n.1)
Per ogni prodotto, trovare il codice del fornitore che ne fornisce la quantità massimaSELECT CodP, CodF FROM FP AS FPX
WHERE Qta = (SELECT MAX(Qta) FROM FP AS FPY ... )
Quantità massima
DB
MG
Correlazione tra interrogazioni (n.1)
Per ogni prodotto, trovare il codice del fornitore che ne fornisce la quantità massimaSELECT CodP, CodF FROM FP AS FPX
WHERE Qta = (SELECT MAX(Qta) FROM FP AS FPY
WHERE FPY.CodP=FPX.CodP);
Quantità massima
per il prodotto corrente
DB
MG
Correlazione tra interrogazioni (n.1)
Per ogni prodotto, trovare il codice del fornitore che ne fornisce la quantità massimaSELECT CodP, CodF FROM FP AS FPX
WHERE Qta = (SELECT MAX(Qta) FROM FP AS FPY
WHERE FPY. CodP=FPX.CodP);
Condizione di correlazione
DB
MG
Correlazione tra interrogazioni (n.2)
SELECT CodV FROM VIAGGIO AS VA
WHERE OraArrivo-OraPartenza < (...
) VIAGGIO (CodV, LuogoPartenza, LuogoArrivo,
OraPartenza, OraArrivo)
Durata media dei viaggi sul 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)
DB
MG
Correlazione tra interrogazioni (n.2)
SELECT CodV FROM VIAGGIO AS VA
WHERE OraArrivo-OraPartenza <
(SELECT AVG(OraArrivo-OraPartenza) FROM VIAGGIO AS VB
... )
VIAGGIO (CodV, LuogoPartenza, LuogoArrivo, OraPartenza, OraArrivo)
Durata media 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)
DB
MG
Correlazione tra interrogazioni (n.2)
SELECT CodV FROM VIAGGIO AS VA
WHERE OraArrivo-OraPartenza <
(SELECT AVG(OraArrivo-OraPartenza) FROM VIAGGIO AS VB
WHERE 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)