Appunti di Basi di dati

FROM F, FP, P

WHERE P.CodF=F.CodF AND FP.CodP=P.CodP AND Colore=’Rosso’;

Esempio #3

Trovare il nome dei fornitori che forniscono almeno un prodotto fornito da fornitori di prodotti rossi.

Scomponiamo:

1. Trovare il codice dei prodotti rossi.

2. Trovare il codice dei fornitori di tali prodotto.

3. Trovare i codici dei prodotti forniti da tali fornitori.

4. Trovare i codici di fornitori dei prodotti con tale codice.

5. Trovare i nomi dei fornitori con tale codice.

Risolviamo la prima:

SELECT CodP

FROM P

WHERE Colore=’Rosso’;

Uniamo alla seconda:

SELECT CodF

FROM FP

WHERE CodP IN ( SELECT CodP

FROM P

WHERE Colore=’Rosso’;)

Terza:

SELECT CodP

FROM FP

WHERE CodF IN ( SELECT CodF

FROM FP

WHERE CodP IN ( SELECT CodP

FROM P

WHERE Colore=’Rosso’;)

Quarta:

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

Infine, la quinta:

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

Operatore NOT IN

Esempio #1

Trovare il nome dei fornitori che non forniscono il prodotto P2.

SELECT NomeF

FROM F, FP

WHERE F.CofF=FP.CodF AND CodP<>’P2’;

Questa soluzione è errata perché risponde alla query di trovare i nome dei fornitori che hanno fornito

almeno un prodotto diverso da P2. È obbligatorio usare le query annidate.

La soluzione corretta è la seguente:

SELECT NomeF

FROM F

WHERE CodF NOT IN ( SELECT CodF

FROM FP

WHERE CodP=’P2’);

Esempio #2

Trovare il nome dei fornitori che forniscono solo il prodotto P2.

Suddividiamolo:

1. Trovare i codici dei fornitori che forniscono il prodotto P2.

2. Trovare i nomi dei fornitori che non fanno parte di tali fornitori.

SELECT NomeF

FROM F, FP

WHERE F.CodF NOT IN ( SELECT CodF

FROM FP

WHERE CodP <> ‘P2’)

AND F.CodF=FP.CodF;

Nota: se un fornitore non ha fornito nulla, non viene compreso nell’insieme di fornitori da escludere; per

questo, è necessario utilizzare anche un’istanza della tabella FP per vedere se il fornitori preso in

considerazione ha fornito almeno il prodotto P2.

Esempio #3

Trovare il nome dei prodotti dei fornitori che non forniscono prodotti rossi.

SELECT NomeF

FROM F

WHERE CodF NOT IN ( SELECT CodF

FROM FP

WHERE CodP IN ( SELECT CodP

FROM P

WHERE Colore=’Rosso’ ));

Costruttore di tupla

Consente di definire la struttura temporanea di una tupla. La sintassi prevede che si elenchino gli attributi

tra parentesi tonde. ( , , … , )

1 2

Esempio #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)

SELECT LuogoPartenza, LuogoArrivo

FROM Viaggio

WHERE (LuogoPartenza, LuogoArrivo) NOT IN ( SELECT LuogoPartenza, LuogoArrivo

FROM Viaggio

Costruttore di tupla WHERE OraArrivo-OraPartenza>2);

Lezione 9 – 18 Marzo 2021

Operatore EXIST

Esempio #1

Trovare il nome dei fornitori del prodotto P2.

Questa query può essere riformulata così:

Trovare il nome dei fornitori per cui esiste una fornitura del prodotto P2.

SELECT NomeF

FROM F

WHERE EXIST ( SELECT *

FROM FP

WHERE CodP=’P2’ AND FP.CodF=F.CodF);

Se esiste una tupla che soddisfa la query annidata, l’operatore restituisce vero, altrimenti falso.

exist

Esempio #2

Trovare il nome dei fornitori che non forniscono il prodotto P2.

Riformulazione:

Trovare il nome dei fornitori per cui non esiste una fornitura del prodotto P2.

SELECT NomeF

FROM F

WHERE NOT EXIST ( SELECT *

FROM FP

WHERE CodP=’P2’ AND FP.CodF=F.CodF);

Esercitazione

Esercizio #1

Trovare il codice ed il nome delle riviste che hanno pubblicato almeno un articolo di argomento

‘motociclismo’.

Le tabelle coinvolte sono quella delle riviste e quella degli articoli da esse pubblicati. Utilizzeremo un join

per risolvere l’esercizio.

SELECT R.CodR, NomeR

FROM RIVISTA R, ARTICOLO A

WHERE R.CodR=A.CodR AND Argomento=’motociclismo’;

è la clausola del Join.

R.CodR=A.CodR

Nota: in molti DBMS la parola AS per definire il nome dell’alias di una tabella non è obbligatorio.

Nota: se l’attributo di una tabella non presenta ambiguità è possibile omettere il prefisso che indica la

tabella cui appartiene tale attributo, come in questo caso.

Argomento

In questo esempio, uniamo con un prodotto cartesiano le due tabelle e, per quelle tuple, che soddisfano il

predicato, consideriamo l’argomento, se esso è uguale a “motociclismo”, allora lo aggiungiamo alla

relazione risultante.

Soluzione alternativa (query annidata)

SELECT CodR, NomeR

FROM RIVISTA

WHERE CodR IN ( SELECT CodR

FROM ARTICOLO

WHERE Argomento = ‘motociclismo’);

Selezioniamo quei codici di riviste che hanno pubblicato almeno un articolo che ha come argomento

“motociclismo” e li poniamo in una tabella risultato. La query principale prenderà quei codici e quei nomi

delle riviste che si trovano in tale tabella.

Esercizio #2

Trovare il codice e il nome delle riviste che pubblicano articoli di motociclismo oppure di auto.

SELECT R.CodR, NomeR

FROM RIVISTA R, ARTICOLO A

WHERE R.CodR=A.CodR AND (Argomento=’motociclismo’ OR Argomento=’auto’);

Esercizio #3

Trovare il codice e il nome delle riviste che pubblicano sia di motociclismo sia di auto.

Per risolvere la query dobbiamo intersecare i due insiemi contenti le riviste che pubblicano auto e quelle

che pubblicano motociclismo. L’intersezione costituisce la soluzione.

SELECT R.CodR, NomeR

FROM ARTICOLO A, RIVISTA R

WHERE Argomento = ’motociclismo’ AND A.CodR IN ( SELECT CodR

FROM ARTICOLO

WHERE Argomento=’auto’)

AND R.CodR=A.CodR;

è la condizione di join.

R.CodR=A.CodR

Esercizio #4

Trovare il codice e il nome delle riviste che hanno pubblicato almeno due articoli di motociclismo.

SELECT R.CodR, NomeR

FROM ARTICOLO A, RIVISTA R;

WHERE R.CodR=A.CodR AND Argomento=’motociclismo’

GROUP BY R.CodR, NomeR

HAVING COUNT(*)>=2;

Nota: la chiave agisce solo sul raggruppamento (cioè va posto dopo). Inoltre, essa può lavorare

HAVING

solamente su funzioni aggregate.

Lezione 10 – 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. Tale legame è espresso da una o più condizioni di correlazione.

Condizione di correlazione

Una condizione di correlazione è indicata nella clausola dell’interrogazione nidificata che la richiede

WHERE

e consiste in un predicato che lega attributi di tabelle nella dell’interrogazione nidificata con attributi

FROM

di tabelle nella di interrogazioni più esterne.

FROM

Non è possibile esprimere condizioni di correlazione in interrogazioni allo stesso livello di nidificazione, né

contenenti riferimenti ad attributi di una tabella nella di un’interrogazione nidificata.

FROM

Esempio #1

Per ogni prodotto, trovare il codice del fornitore che ne fornisce la quantità massima.

SELECT CodP, CodF

FROM FP AS FPX

WHERE Qta = ( SELECT MAX (Qta)

FROM FP AS FPY

WHERE FPY.CodP=FPX.CodP);

Allora il ragionamento è il seguente: se io non mettessi il nella query annidata, troverei il massimo

WHERE

globale relativo all’attributo della tabella ma io voglio trovare la massima quantità di forniture per

Qta FP,

un dato prodotto, quindi, creando 2 istanze della tabella io seleziono la quantità massima solamente se

FP,

le tupla considerata, generata dal prodotto cartesiano tra e hanno i campi e

FPX FPY, FPX.CodP

identici.

FPY.CodP

Esempio #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 ed arrivo).

VIAGGIO (CodV, LuogoPartenza, LuogoArrivo, OraPartenza, OraArrivo)

SELECT CodV

FROM VIAGGIO AS VA

WHERE OraArrivo-OraPartenza < ( SELECT AVG(OraPartenza-OraArrivo)

FROM VIAGGIO AS VB

WHERE VA.LuogoPartenza=VB.LuogoPartenza

AND VA.LuogoArrivo=VA.LuogoArrivo);

Anche in questo caso il ragionamento è lo stesso: considero la media del viaggio considerato solamente se

la condizione di correlazione è verificata.

Operazione di divisione

Esempio #1

Trovare il codice dei fornitori che forniscono tutti i prodotti.

Useremo l’operatore di divisione.

In questo esempio, tutti i prodotti che possono essere forniti sono contenuti nella tabella P, perciò un

fornitore fornisce tutti i prodotti solamente se fornisce un numero di prodotti diversi pari alla cardinalità di

P. SELECT CodF

FROM FP

GROUP BY CodF

HAVING COUNT(*)=( SELECT COUNT(*)

FROM P);

Immagina di creare tante tabelle quanti sono i codici fornitori all’interno della

tabella FP, poi conta quanti record sono contenuti all’interno di esse e confronta tale numero con il numero

di record nella tabella P. Se esso è uguale, hai trovato un fornitore che fornisce tutti i prodotti.

Esempio #2

Trovare il codice dei fornitori che forniscono almeno tutti i prodotti forniti dal fornitore F2.

In primis, eseguiamo il conteggio dei prodotti forniti da F2, poi contiamo il numero di prodotti forniti da un

fornitore arbitrario e anche da F2; tali

SELECT CodF conteggi devono essere uguali.

FROM FP Il ragionamento è il seguente: raggruppa i

WHERE CodP IN ( SELECT CodP per codice del fornitore, immaginando di

FROM FP creare tante tabelle quante sono i codici di

fornitori; fatto questo, conta i record in cui

WHERE CodF=’F2’) compaiono quei codici prodotto che ha

GROUP BY CodF fornito F2; se il numero risultante è pari al

HAVING COUNT(*)=( SELECT COUNT(*) numero di prodotti di prodotti forniti da F2,

allora quel fornitore fa parte della soluzione.

FROM FP

WHERE CodF=’F2’); Table functions

Esempio #1

STUDENTE (Matricola, AnnoIscri