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




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



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



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





SELECT SedeFROM FWHERE CodF='F1'

Sede del fornitore 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)


È possibile definire una formulazione equivalente con il join



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)






Clausola FROM (n.1)




FX

FY

Clausola FROM (n.1)


SELECT ... FROM F AS FX, F AS FY...



Condizione di join (n.1)




Condizione di join (n.1)


SELECT ... FROM F AS FX, F AS FYWHERE FX.Sede=FY.Sede...



Predicato di selezione (n.1)






SELECT ...FROM F AS FX, F AS FYWHERE FX.Sede=FY.Sede AND

FX.CodF='F1';



Clausola SELECT (n.1)


SELECT FY.CodF FROM F AS FX, F AS FYWHERE FX.Sede=FY.Sede AND

FX.CodF='F1';


Trovare il codice dei fornitori il cui numero di soci è minore del numero massimo di soci





SELECT MAX(NSoci) FROM F

Numeromassimodi soci



SELECT CodF FROM FWHERE NSoci < (SELECT MAX(NSoci)

FROM F);






FROM F);

È possibile definire una formulazione equivalente con il join?




FROM F);

Non è possibile definire una formulazione equivalente con il join




Operatore IN (n.1)

Trovare il nome dei fornitori che forniscono il prodotto P2



Operatore IN (n.1)


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'



Operatore IN (n.1)



Codicidei

fornitoridi P2F1

F2

F3

CodF

FP

200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodP

Operatore IN (n.1)


SELECT NomeF FROM FWHERE CodF (SELECT CodF

FROM FPWHERE CodP='P2')



Operatore IN (n.1)



FROM FPWHERE CodP='P2')

?

Operatore IN (n.1)


SELECT NomeF FROM FWHERE CodF IN (SELECT CodF

FROM FPWHERE CodP='P2');



Operatore IN (n.1)




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”



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)








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



Operatore IN (n.2)


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




Operatore IN (n.2)

SELECT CodF FROM FPWHERE CodP IN (SELECT CodP

FROM PWHERE Colore='Rosso')

Codici dei fornitoridi prodotti rossi


Operatore IN (n.2)


FROM FPWHERE CodP IN (SELECT CodP

FROM PWHERE Colore='Rosso'));












Clausola FROM (n.2)




Clausola FROM (n.2)


SELECT ...FROM F, FP, P...




Condizioni di join (n.2)

1





1


SELECT ...FROM F, FP, PWHERE P.CodF=F.CodF





2





SELECT ... FROM F, FP, PWHERE P.CodF=F.CodF AND

FP.CodP=P.CodP ...

2










SELECT ... FROM F, FP, PWHERE P.CodF=F.CodF AND

FP.CodP=P.CodP ANDColore='Rosso'




SELECT NomeF FROM F, FP, PWHERE P.CodF=F.CodF AND

FP.CodP=P.CodP ANDColore='Rosso'



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


SELECT CodP FROM P WHERE Colore='Rosso'

Codici dei prodotti rossi





SELECT CodFFROM FPWHERE CodP IN

(SELECT CodP FROM P WHERE Colore='Rosso')

Codici dei fornitori di prodotti rossi



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





SELECT CodF FROM FPWHERE CodP IN

(SELECT CodP FROM FPWHERE CodF 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 P WHERE Colore='Rosso'))));



Formulazione con il join (n.3)

Formulazione con il join (n.3)








Clausola FROM (n.3)






Clausola FROM (n.3)






FPA

FPB

FPC



Clausola FROM (n.3)

SELECT ... FROM F, FP AS FPA, FP AS FPB, FP AS FPC, P...







1

FPA




SELECT ... FROM F, FP AS FPA, FP AS FPB, FP AS FPC, PWHERE F.CodF=FPA.CodF

...1







2FPA

FPB




SELECT ... FROM F, FP AS FPA, FP AS FPB, FP AS FPC, PWHERE F.CodF=FPA.CodF AND

FPA.CodP=FPB.CodP...

2







3

FPB

FPC





FPA.CodP=FPB.CodP ANDFPB.CodF=FPC.CodF...

3







4

FPC





FPA.CodP=FPB.CodP ANDFPB.CodF=FPC.CodF ANDFPC.CodP=P.CodP...

4











FPA.CodP=FPB.CodP ANDFPB.CodF=FPC.CodF ANDFPC.CodP=P.CodP ANDColore='Rosso'


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




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)


non è possibile esprimere l'interrogazione mediante il join


AND CodP<>'P2';






3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoSede

AndreaF1NomeFCodF

FFP

200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodP




3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoSede

AndreaF1NomeFCodF

FFP

200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodP





FFP

200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodP

AndreaLuca

Gabriele

NomeFR


3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoSede

AndreaF1NomeFCodF



AND CodP<> 'P2';

A che interrogazione corrisponde?





AND CodP<> 'P2';

Trovare il nome dei fornitori che forniscono almeno un prodotto diverso da P2







Occorre escludere dal risultatoi fornitori che forniscono il prodotto P2




Codici dei fornitori che forniscono P2








?

Operatore NOT IN (n.1)


SELECT NomeF FROM FWHERE CodF NOT IN (SELECT CodF










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



NOT IN e algebra relazionale (n.1)



-

πNomeF

πCodF

F

FP

πCodF

σCodP='P2'

F





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 forniscono solo il prodotto P2




Trovare il nome dei fornitori di P2 che non hanno mai fornito prodotti diversi da P2



Trovare il nome dei fornitori di P2 che non hanno mai fornito prodotti diversi da P2

Insieme da escluderefornitori di prodotti diversi da P2






SELECT CodF FROM FPWHERE CodP<>'P2'

Codici dei fornitori che forniscono

almeno un prodotto diverso

da P2



FROM FPWHERE CodP<>'P2')

...





SELECT NomeF FROM F, FPWHERE F.CodF NOT IN (SELECT F.CodF


AND F.CodF=FP.CodF;


Soluzione alternativa (n.2)


SELECT NomeF FROM FWHERE F.CodF NOT IN (SELECT CodF


AND F.CodF IN (SELECT CodFFROM FP);




Trovare il nome dei fornitori che non forniscono prodotti rossi



Insieme da escludere?





Insieme da escludere?i fornitori di prodotti rossi, identificati dal loro codice


(SELECT CodF FROM FPWHERE CodP IN (SELECT CodP


Codici dei fornitori di prodotti rossi









Alternativa (corretta?) (n.3)





SELECT CodPFROM PWHERE Colore='Rosso'

Codici dei prodotti

rossi



SELECT CodF FROM FPWHERE CodP NOT IN (SELECT CodP


Codici dei fornitori che forniscono

almeno un prodotto non rosso






FROM FPWHERE CodP NOT IN (SELECT CodP



Alternativa errata (n.3)











Codici dei fornitori di prodotti non rossi




3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoSede

AndreaF1NomeFCodF

F

FP

200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodP


Rosso

VerdeRossoColore

42

4840Taglia

MilanoJeansP2

TorinoBermudaP6

TorinoMagazzino

MagliaP1NomePCodPP






3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoSede

AndreaF1NomeFCodF

FP

200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodP

F

P


Rosso

VerdeRossoColore

42

4840Taglia

MilanoJeansP2

TorinoBermudaP6

TorinoMagazzino

MagliaP1NomePCodP






L'insieme di elementi da escludere non è corretto





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, ...)



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


(SELECT LuogoPartenza, LuogoArrivoFROM VIAGGIOWHERE OraArrivo-OraPartenza>2)




Esempio (n.1)

SELECT LuogoPartenza, LuogoArrivoFROM VIAGGIOWHERE (LuogoPartenza, LuogoArrivo) NOT IN

(SELECT LuogoPartenza, LuogoArrivoFROM VIAGGIOWHERE OraArrivo-OraPartenza>2);



Esempio (n.1)

SELECT LuogoPartenza, LuogoArrivoFROM VIAGGIOWHERE (LuogoPartenza, LuogoArrivo) NOT IN

(SELECT LuogoPartenza, LuogoArrivoFROM VIAGGIOWHERE OraArrivo-OraPartenza>2);

Costruttoredi tupla






Operatore EXISTS (n.1)

Trovare il nome dei fornitori del prodotto P2





Trovare il nome dei fornitori per cui esisteuna fornitura del prodotto P2



SELECT NomeF FROM FWHERE EXISTS (...);





SELECT NomeF FROM FWHERE EXISTS (SELECT *

FROM FPWHERE CodP='P2'

...





AND FP.CodF=F.CodF );



Condizione di correlazione (n.1)





Condizione di correlazione

Funzionamento di EXISTS (n.1)


3

12NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoCittà

AndreaF1NomeFCodF

FTrovare il nome dei fornitori del prodotto P2





3

12NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoCittà

AndreaF1NomeFCodF

F

SELECT *FROM FPWHERE CodP='P2'

AND FP.CodF='F1'

Valore di CodF nella riga corrente di F



200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodP


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





200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodP


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



200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodP


3

12NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoCittà

AndreaF1NomeFCodF

F FPTrovare il nome dei fornitori del prodotto P2




200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodP


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


Risultato dell’interrogazione (n.1)

AndreaLuca

Antonio

NomeFR




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





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




Operatore NOT EXISTS (n.1)






Trovare il nome dei fornitori per cui non esisteuna fornitura del prodotto P2



SELECT NomeF FROM FWHERE NOT EXISTS (…);





SELECT NomeF FROM FWHERE NOT EXISTS (SELECT *


...















3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoCittà

AndreaF1NomeFCodF


Funzionamento di NOT EXISTS (n.1)

F





SELECT *FROM FPWHERE CodP='P2' AND

FP.CodF='F1'Valore di CodF nella

riga corrente di F


3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoCittà

AndreaF1NomeFCodF

F


3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoCittà

AndreaF1NomeFCodF

200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodP



SELECT *FROM FPWHERE CodP='P2' AND

FP.CodF='F1'

F FP





3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoCittà

AndreaF1NomeFCodF

200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodP


Il predicato con NOT EXISTS èfalso per F1 perché esiste unafornitura di P2 per F1

F1 non fa parte del risultatodell'interrogazione

F FP



3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoCittà

AndreaF1NomeFCodF


F





3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoCittà

AndreaF1NomeFCodF


200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodPF FP



3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoCittà

AndreaF1NomeFCodF


F






3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoCittà

AndreaF1NomeFCodF

200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodPF FP



Il predicato con NOT EXISTS èvero per F4 perché non esisteuna fornitura di P2 per F4

F4 fa parte del risultatodell'interrogazione


3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoCittà

AndreaF1NomeFCodF

200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodPF FP






3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoCittà

AndreaF1NomeFCodF

F



3

12

NSoci

MilanoLucaF2

VeneziaMatteoF5

TorinoCittà

AndreaF1NomeFCodF

F

200P4F1400P3F1200P2F1300P1F1

400P2F2300P1F2100P6F1100P5F1

400P5F4300P4F4200P3F4200P2F3

CodF QtaCodPFP




Risultato dell’interrogazione (n.1)

GabrieleMatteo

NomeFR


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




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




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


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



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





SELECT CodP, CodF FROM FP AS FPXWHERE Qta = (SELECT MAX(Qta)

FROM FP AS FPY... )

Quantitàmassima




FROM FP AS FPYWHERE FPY.CodP=FPX.CodP);

Quantitàmassima

per il prodottocorrente






FROM FP AS FPYWHERE FPY. CodP=FPX.CodP);


Schema di esempio (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)



SELECT CodVFROM VIAGGIO AS VAWHERE OraArrivo-OraPartenza < (...

)


Duratamedia

dei viaggisul percorso

corrente





SELECT CodVFROM VIAGGIO AS VAWHERE OraArrivo-OraPartenza <

(SELECT AVG(OraArrivo-OraPartenza)FROM VIAGGIO AS VB... )


Duratamedia

dei viaggi




(SELECT AVG(OraArrivo-OraPartenza)FROM VIAGGIO AS VBWHERE VB.LuogoPartenza=VA.LuogoPartenza

AND VB.LuogoArrivo=VA.LuogoArrivo);







(SELECT AVG(OraArrivo-OraPartenza)FROM VIAGGIO AS VBWHERE VB.LuogoPartenza=VA.LuogoPartenza

AND VB.LuogoArrivo=VA.LuogoArrivo);

Condizioni di correlazione






Operazione di divisione (n.1)

Trovare il codice dei fornitori che forniscono tuttii prodotti


Trovare il codice dei fornitori che forniscono tuttii prodottiIn algebra si utilizza l’operatore di divisione




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






Osservazionetutti i prodotti che possono essere forniti sonocontenuti nella tabella P


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







SELECT COUNT(*)FROM P





SELECT COUNT(*)FROM P

Numero totale di prodotti



SELECT CodF FROM FPGROUP BY CodF… (SELECT COUNT(*)

FROM P)





SELECT CodF FROM FPGROUP BY CodFHAVING COUNT(*)=(SELECT COUNT(*)

FROM P);



Trovare il codice dei fornitori che forniscono almeno tutti i prodotti forniti dal fornitore 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 F2



il conteggio del numero di prodotti forniti da F2il conteggio del numero di prodotti forniti da un fornitore arbitrario e anche da F2





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







SELECT COUNT(*)FROM FPWHERE CodF='F2'



SELECT COUNT(*)FROM FPWHERE CodF='F2'

Numero di prodotti

forniti da F2






FROM FPWHERE CodF='F2')

GROUP BY CodF... (SELECT COUNT(*)






GROUP BY CodFHAVING COUNT(*)=(SELECT COUNT(*)

FROM FPWHERE CodF='F2');




Schema di esempio

STUDENTE (Matricola, AnnoIscrizione)ESAME-SUPERATO (Matricola, CodC, Data, Voto)CORSO (CodC, NomeC)



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)



Risoluzione in 2 passitrovare la media per ogni studentetrovare il valore massimo della media





SELECT Matricola, AVG(Voto) AS MediaStudentiFROM ESAME-SUPERATOGROUP BY Matricola



passo 1: media per ogni studente


(SELECT Matricola, AVG(Voto) AS MediaStudentiFROM ESAME-SUPERATOGROUP BY Matricola) AS MEDIE



passo 1: media per ogni studente




SELECT ...FROM (SELECT Matricola, AVG(Voto) AS MediaStudenti

FROM ESAME-SUPERATOGROUP BY Matricola) AS MEDIE



passo 2: valore massimo della media


SELECT MAX(MediaStudenti)FROM (SELECT Matricola, AVG(Voto) AS MediaStudenti

FROM ESAME-SUPERATOGROUP BY Matricola) AS MEDIE;



passo 2: valore massimo della media



Table functions (n.1)

SELECT MAX(MediaStudenti)FROM (SELECT Matricola, AVG(Voto) AS MediaStudenti

FROM ESAME-SUPERATOGROUP BY Matricola) AS MEDIE;


Table function


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




Per ogni anno di iscrizione, trovare la media massima (conseguita da uno studente)




Risoluzione in 2 passitrovare la media per ogni studenteraggruppare gli studenti per anno di iscrizione e calcolare la media massima





(SELECT Matricola, AVG(Voto) AS MediaStudenteFROM ESAME-SUPERATOGROUP BY Matricola) AS MEDIE



passo 1


SELECT ...FROM STUDENTE,


WHERE STUDENTE.Matricola=MEDIE.Matricola...


Table function


passo 2






WHERE STUDENTE.Matricola=MEDIE.Matricola...


Condizione di join


passo 2




WHERE STUDENTE.Matricola=MEDIE.MatricolaGROUP BY AnnoIscrizione



passo 2




SELECT AnnoIscrizione, MAX(MediaStudente)FROM STUDENTE,


WHERE STUDENTE.Matricola=MEDIE.MatricolaGROUP BY AnnoIscrizione;



passo 2

Linguaggio SQL: fondamenti - polito.itcorsiadistanza.polito.it/on-line/sistemi_info/pdf/U3_3.pdf ·...

Documents

Transcript of Linguaggio SQL: fondamenti - polito.itcorsiadistanza.polito.it/on-line/sistemi_info/pdf/U3_3.pdf ·...