Esercitazione Algebra Relazionale ed SQLatzeni/didattica/BDN/20092010/EsercitazioneS… ·...

Basi di DatiBasi di Dati Esercitazione Algebra RelazionaleEsercitazione Algebra Relazionale

ed SQL10 Novembre 2009

Esercizio 1

Fornitori (CodiceFornitore, Nome, Indirizzo, Città)Prodotti (CodiceProdotto, Nome, Marca, Modello)Catalogo (Fornitore, Prodotto, Costo)

con vincoli di integrità referenzialef P d tt l hi di P d ttifra Prodotto e la chiave di Prodottifra Fornitore e la chiave di Fornitori

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

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

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

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

5 Trovare i nomi dei fornitori che forniscono tutti i prodotti IBM

Esercitazione Basi di Dati 2Algebra Relazionale e SQL

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

Le Relazioni

Nome CodiceFornitore Indirizzo Citta

L d i 01 Vi O ti RLadroni 01 Via Ostiense Roma

Risparmietti 02 Viale Marconi Roma

Teloporto 10 Via Roma MilanoFornitori

Teloporto 10 Via Roma Milano

TuttoIBM 13 Corso Italia Perugia

Nome CodiceProdotto Marca Modello

Notebook 01 IBM 390xProdotti

Desktop 02 IBM 510

Desktop 10 Acer 730

Prodotti


Le Relazioni

Fornitore Prodotto Costo

01 002 3200

01 003 2200

02 001 1900Catalogo02 002 2500

02 003 1800

10 001 2200

Catalogo

10 001 2200

10 003 2000

13 001 2600

13 002 2850


Esercizio 1.1

Fornitori (CF, Nome, Indirizzo, Città)

Prodotti (CP Nome Marca Modello)Prodotti (CP, Nome, Marca, Modello)

Catalogo (F, P, Costo)

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


Esercizio 1.1


Prodotti (CP, Nome, Marca, Modello)( , , , )


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

πNome, Marca, Modello, ,

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


Esercizio 1.1


Prodotti (CP, Nome, Marca, Modello)

1 T N M M d ll d i d tti i t bili

( , , , )


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

πNome, Marca, Modello(σCosto < 2000(Prodotti ⋈CP=P Catalogo))

select distinct nome, marca, modello

from prodotti, catalogo

where codiceProdotto = prodotto

and costo < 2000


Esercizio 1.1




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

Nome Marca Modello

Notebook IBM 390x

Desktop Acer 730


Esercizio 1.2




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


Esercizio 1.2



2.Trovare i nomi dei fornitori che distribuiscono prodotti IBM (IBM

( , , , )


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)))C CP,Marca


Esercizio 1.2





è la marca di un prodotto).πNome(σMarca=‘IBM’((Fornitori⋈CF=FCatalogo)⋈P=CP(πCP,Marca(Prodotti)))

select distinct fornitori.nome

f d tti t l f it ifrom prodotti, catalogo, fornitori

where codiceProdotto = prodotto

and fornitore = codiceFornitore

and marca = 'IBM'


Esercizio 1.2




( , , , )


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

Nome

Ladroni

Risparmietti

T lTeloporto

TuttoIBM


Esercizio 1.3




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


Esercizio 1.3



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

( , , , )


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

πP ( σF ≠ F’(Catalogo ⋈P=P’ ρX’←X(Catalogo))P ( F ≠ F ( g P=P ρX ←X( g ))

ρX’←X indica una ridenominazione in cui ciascun attributo A viene cambiato in A’


Esercizio 1.3




( , , , )



πP ( σF ≠ F’(Catalogo ⋈P=P’ ρX’←X(Catalogo))P ( F ≠ F ( g P=P ρX ←X( g ))

select distinct c1 prodottoselect distinct c1.prodottofrom catalogo c1, catalogo c2where c1 prodotto = c2 prodottowhere c1.prodotto c2.prodottoand c1.fornitore > c2.fornitore


Esercizio 1.3




( , , , )



CodiceProdotto

001001

002

003003


Esercizio 1.4




4.Trovare i codici dei fornitori che distribuiscono tutti iprodotti presenti nel catalogo.p odott p ese t e cata ogo


Esercizio 1.4




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

πF(Catalogo) -πF((πF(Catalogo)⋈ πP(Catalogo))πF((πF(Catalogo)⋈ πP(Catalogo))

– π F,P(Catalogo) ))


Esercizio 1.4

4.Trovare i codici dei fornitori che distribuiscono tutti i prodottipresenti nel catalogo.πF(Catalogo) -πF((πF(Catalogo)⋈ πP(Catalogo)) – π F,P(Catalogo) ))

create view fornituraMancanteas select c1.fornitore, c2.prodotto

from catalogo c1, catalogo c2exceptexcept

select fornitore, prodotto from catalogo

select fornitorefrom catalogo

exceptselect fornitorefrom fornituraMancante;


Esercizio 1.4





Fornitore

2


Esercizio 1.4

4.Trovare i codici dei fornitori che distribuiscono tutti i prodottipresenti nel catalogo.πF(Catalogo) -πF((πF(Catalogo)⋈ πP(Catalogo)) – π F,P(Catalogo) ))

select distinct fornitorefrom catalogo c1where not existswhere not exists

(select * from catalogo c2gwhere not exists (select *from catalogo c3where c2.prodotto = c3.prodottoand c1 fornitore = c3 fornitore))


and c1.fornitore = c3.fornitore))

Esercizio 1.4


Prodotti (CP Nome Marca Modello)Prodotti (CP, Nome, Marca, Modello)



Fornitore

2


Esercizio 1.5




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


Esercizio 1.5



5 T i i d i f it i h f i t tti i d tti

( , , , )


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

In algebra, come interrogazione 4, usando, al posto dellaa geb a, co e te oga o e , usa do, a posto de arelazione Catalogo, relazione catalogoIBM:

CatalogoIBM := πP,F(

σ ’ ’(Catalogo ⋈ C Prodotti))σMarca=’IBM’(Catalogo ⋈P=CP Prodotti))


Esercizio 1.5create view catalogoIBMas select catalogo.fornitore, catalogo.prodotto,

from catalogo c, prodotti pwhere marca =‘IBM’and p.codiceProdotto=c.prodotto;

select distinct fornitorefrom catalogoIBM c1where not exists(select * from catalogoIBM c2where not exists ( l t *(select *from catalogoIBM c3where c2.prodotto = c3.prodottoand c1 fornitore = c3 fornitore));and c1.fornitore = c3.fornitore));


Esercizio 1.5




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

FornitoreFornitore

2

13


Esercizio 2




2. Trovare i codici ed i nomi dei fornitori che vendono più prodotti ed il numero di prodotti vendono più prodotti ed il numero di prodotti venduti.


Esercizio 2

2. Trovare il codice ed i nomi dei fornitori che vendono più prodotti ed il numero di prodotti p p pvenduti.

select f2.codiceFornitore, f2.nome, count(*) as SommaProdotti

from fornitori f2 catalogo c2from fornitori f2, catalogo c2

where f2.codicefornitore = c2.fornitore

group by f2.codiceFornitore, f2.nomegroup by f2.codiceFornitore, f2.nome

having count(*) >= all (

select count(*)

from catalogo c

group by c.fornitore );


Esercizio 2




2. Trovare i codici ed i nomi dei fornitori che vendono piùprodotti ed il numero di prodotti venduti.

CodiceFornitore Nome NumeroProdottiCodiceFornitore Nome NumeroProdotti

2 Risparmietti 3


Esercizio 3




3. Trovare per ogni prodotto il fornitore che lo vende a prezzo più bassoprezzo più basso.


Esercizio 3

3. Trovare per ogni prodotto il fornitore che lo vende a prezzo più basso.

select c.prodotto, c.fornitore, f.nomep

from ES.catalogo c, ES.fornitori f

where f.codiceFornitore = c.fornitore

and costo <= all (

select costo

from ES.catalogo c1

where c.prodotto = c1.prodotto);p p );


Esercizio 3




3. Trovare per ogni prodotto il fornitore che lo vende a prezzo più bassoprezzo più basso.

Prodotto Fornitore Nome

1 2 Risparmietti

2 2 Risparmietti

3 2 Risparmietti


Esercitazione Algebra Relazionale ed SQLatzeni/didattica/BDN/20092010/EsercitazioneS… ·...

Documents

Transcript of Esercitazione Algebra Relazionale ed SQLatzeni/didattica/BDN/20092010/EsercitazioneS… ·...