Basi di Dati Esercitazione SQL 17 Novembre 2014. Esercitazione Basi di Dati2 Fornitori...

Basi di Dati Esercitazione SQL

17 Novembre 2014

Esercitazione Basi di Dati 2

Fornitori (CodiceFornitore, Nome, Indirizzo, Città)

Prodotti (CodiceProdotto, Nome, Marca, Modello)

Catalogo (Fornitore, Prodotto, Costo)

con vincoli di integrità referenziale

fra Prodotto e la chiave di Prodottifra Fornitore e la chiave di Fornitori

1. Trovare Nome, Marca e Modello dei prodotti acquistabili con meno di 2000 €.

2. Trovare i nomi dei fornitori che distribuiscono prodotti IBM (IBM è la marca di un prodotto).

3. Trovare i codici di tutti i prodotti che sono forniti da almeno due fornitori.

4. Trovare i codici dei fornitori che distribuiscono tutti i prodotti presenti nel catalogo.

5. Trovare i nomi dei fornitori che forniscono tutti i prodotti IBM presenti nel catalogo.

6. Trovare il costo medio dei prodotti forniti in ciascuna città (visualizzare costo e città).


Le Relazioni

Fornitori

Nome CodiceFornitore Indirizzo Città

Ladroni 001 Via Ostense RomaRisparmietti 002 Viale Marconi RomaTeloporto 010 Via Roma Milano

CodiceProdotto Nome Marca Modello

0001 Notebook IBM 390 x0002 Desktop IBM 5100003 Desktop ACER 730

Prodotti

Catalogo

Fornitore Prodotto Costo

001 0002 € 3.200001 0003 € 2.200002 0001 € 1.900002 0002 € 2.500002 0003 € 1.800010 0001 € 2.200010 0003 € 2.000



Esercizio 1.1

Fornitori (CF, Nome, Indirizzo, Città)

Prodotti (CP, Nome, Marca, Modello)

Catalogo (F, P, Costo)



Nome, Marca, Modello

(Costo < 2000(Prodotti ⋈CP=P Catalogo))

Esercizio 1.1





select *

From prodotti, catalogo

Where codiceProdotto = prodotto

Esercizio 1.1


select *



and costo < 2000

Esercizio 1.1


Select distinct nome, marca, modello



and costo < 2000

Esercizio 1.1


2. Trovare i nomi dei fornitori che distribuiscono prodotti IBM (IBM è la marca di un prodotto).

Nome (

Marca =‘IBM’((Fornitori⋈CF=F Catalogo)

⋈P=CP(CP,Marca(Prodotti)) )

Esercizio 1.2





Select *

From prodotti, catalogo, fornitori

Where codiceProdotto = prodotto and fornitore = codiceFornitore

Esercizio 1.2


Select *


Where codiceProdotto = prodotto and fornitore = codiceFornitore and

marca ='IBM'

Esercizio 1.2


Select distinct fornitori.nome


Where codiceProdotto = prodotto and fornitore = codiceFornitore and marca

='IBM'

Esercizio 1.2


3. Trovare i codici di tutti i prodotti che sono forniti da almeno due fornitori.

P ( F F’(

Catalogo ⋈P=P’ X’←X(Catalogo))

X’←X indica una ridenominazione in cui ciascun attributo A

viene cambiato in A’

Esercizio 1.3





Select *From catalogo c1, catalogo c2Where c1.prodotto = c2.prodotto

Esercizio 1.3


Select *From catalogo c1, catalogo c2Where c1.prodotto = c2.prodotto c1.fornitore != c2.fornitore

Esercizio 1.3


Select distinct c1.prodottoFrom catalogo c1, catalogo c2where c1.prodotto = c2.prodotto c1.fornitore != c2.fornitore

Esercizio 1.3


Esercizio 1.4


F(Catalogo) -

F((F(Catalogo)⋈ P(Catalogo))

– F,P(Catalogo) ))


F(Catalogo) - Fornitura Mancate

Select distinct fornitore

From catalogo

Esercizio 1.4


F(Catalogo)⋈ P(Catalogo)

Fornitore

001

002

010

Prodotto

0001

0002

0003

⋈

Prodotto Fornitore

0001 001

0002 002

0003 010

0001 002

0002 010

0003 001

0001 010

0002 001

0003 002

Tutte le coppie Fornitore/Prodotto

Esercizio 1.4


F(Catalogo)⋈ P(Catalogo) – F,P(Catalogo)

Prodotto Fornitore

0001 001

0002 002

0003 010

0001 002

0002 010

0003 001

0001 010

0002 001

0003 002

Tutte le coppie Fornitore/Prodotto – Fornitore/Prodotto

Prodotto Fornitore

0002 001

0003 001

0001 002

0002 002

0003 002

0001 010

0003 010

Le forniture mancate

Esercizio 1.4


Fornitura Mancate

Select c1.fornitore, c2.prodotto From catalogo c1, catalogo c2 Except Select fornitore, prodotto From catalogo

Esercizio 1.4


Fornitura Mancate

create view fornituraMancante as select c1.fornitore, c2.prodotto from catalogo c1, catalogo c2 except select fornitore, prodotto from catalogo

Esercizio 1.4


F(Catalogo) - Fornitura Mancate

Select distinct fornitore

From catalogo

Esercizio 1.4

select fornitore from fornituraMancante

except

-


Esercizio 1.4


F(Catalogo) -

F((F(Catalogo)⋈ P(Catalogo)) – F,P(Catalogo) ))

select distinct fornitorefrom catalogo c1where not exists (select * from catalogo c2 where not exists (select * from catalogo c3 where c2.prodotto = c3.prodotto and c1.fornitore = c3.fornitore))


5. Trovare i nomi dei fornitori che forniscono tutti i prodotti IBM presenti nel catalogo.

La soluzione è identica a quella della interrogazione 4, con, al posto della relazione Catalogo la vista catalogoIBM :

CatalogoIBM := P,F(

Marca=’IBM’(Catalogo ⋈P=CP

Prodotti))

Esercizio 1.5


Esercizio 1.5

create view catalogoIBMas select prodotto, fornitore from catalogo, prodotti

where prodotto = codiceProdotto and

marca = ‘IBM’


Esercizio 1.5

create view fornituraMancanteIBM as select c1.fornitore, c2.prodotto from catalogoIBM c1, catalogoIBM c2 except select fornitore, prodotto from catalogoIBM


Trovare il costo medio dei prodotti forniti in ciascuna città (visualizzare costo e città).

Esercizio 1.6

Fornitori (CodiceFornitore, Nome, Indirizzo, Città)

Prodotti (CodiceProdotto, Nome, Marca, Modello)

Catalogo (Fornitore, Prodotto, Costo)

con vincoli di integrità referenziale

fra Prodotto e la chiave di Prodottifra Fornitore e la chiave di Fornitori



SELECT *

FROM catalogo AS C, fornitori AS F

WHERE C.fornitore = F.codiceFornitore

Esercizio 1.6


Esercizio 1.6


SELECT avg(costo) AS CostoMedio, F.citta

FROM catalogo AS C, fornitori AS F

WHERE C.fornitore = F.codiceFornitore

GROUP BY F.citta

Basi di Dati Esercitazione SQL 17 Novembre 2014. Esercitazione Basi di Dati2 Fornitori...

Documents

Transcript of Basi di Dati Esercitazione SQL 17 Novembre 2014. Esercitazione Basi di Dati2 Fornitori...