edutecnica



Esercizio no.4:soluzione

Il contesto sembra chiaro un cliente può effettuare (nel tempo) più ordini.
Un ordine può fare riferimento ad un solo cliente.
In un ordine possono essere acquistati più prodotti.
Un prodotto può essere acquistato in molteplici ordini.

Essendoci una relazione n:n fra gli ordini e i prodotti, effettuiamo una ulteriore derivazione, introducendo la tabella Distinta (D).


Se volessimo definire i campi per la tabella Clienti (C).

idC Contatore
nome Testo 		CREATE TABLE C (
idC COUNTER PRIMARY KEY,
nome CHAR(20));

Se volessimo definire i campi per la tabella Prodotti (P)

idP Contatore
prz Numerico
prd Testo 		CREATE TABLE P (
idP COUNTER PRIMARY KEY,
prz INTEGER,
prd CHAR(20));

Se volessimo definire i campi per la tabella Ordini (O):

idO Contatore
data Date
xidC Numerico 		CREATE TABLE O(
idO COUNTER PRIMARY KEY,
data date,
xidC INTEGER REFERENCES C(idC));

Questa ultima è la chiave esterna per idC.
Se volessimo definire i campi per la tabella Distinta (D)

idD Contatore
xidP Numerico
xidO Numerico
qta Numerico 		CREATE TABLE D(
idD COUNTER PRIMARY KEY,
qta INTEGER ,
xidP INTEGER REFERENCES P(idP),
xidO INTEGER REFERENCES O(idO));


Si deriva il modello logico e poi possiamo popolare le tabelle con valori di test

 

idP prz prd
1 9 biella             
2 9 manovella          
3 2 ruota              
4 3 stelo              
5 3 capsula            
6 5 carter             
7 7 nipplo             
idC nome
1 BMW                
2 FIAT               
3 FORD               
4 VOLVO              


idO data
xidC
1 01/01/2014
1
2 01/02/2014
1
3 01/03/2014
1
4 07/01/2014
2
5 07/02/2014
2
6 15/01/2014
3
7 15/04/2014
3
8 15/05/2014
3
9 27/02/2014
4
10 27/03/2014
4
11 27/04/2014
4
12 27/05/2014
4
idD qta xidP xidO
1 3 1 1
2 2 4 1
3 3 2 2
4 2 6 2
5 6 7 3
6 5 3 3
7 12 2 4
8 6 3 5
9 3 4 6
10 4 6 6
11 3 1 7
12 3 2 7
13 2 1 8
14 2 5 9
15 4 7 10
16 2 3 11
17 2 1 12
18 1 2 12

q1: Elenco degli ordini effettuati dalla da uno specifico clinente ( BMW).
   
SELECT O.idO, O.data
FROM O, C
WHERE C.idC=O.xidC AND
C.nome='BMW';
idO data
1 01/01/2014
2 01/02/2014
3 01/03/2014

q2: Elenco dei clienti che comprano uno specifico prodotto (manovelle).
   
SELECT C.nome
FROM O, C, D, P
WHERE C.idC=O.xidC AND
O.idO=D.xidO AND
P.idP=D.xidP AND
P.prd='manovella';
nome
BMW                
FIAT               
FORD               
VOLVO              

q3: Elenco degli ordini e dei rispettivi clienti con importo complessivo organizzato per importo.
   
SELECT C.nome, O.idO, SUM(D.qta*P.prz) AS TOT
FROM O, C, D, P
WHERE C.idC=O.xidC AND
O.idO=D.xidO AND
P.idP=D.xidP
GROUP BY O.idO, C.nome
ORDER BY SUM(D.qta*P.prz) DESC;
nome idO TOT
FIAT  4 108
FORD 7 54
BMW  3 52
BMW  2 37
BMW  1 33
FORD  6 29
VOLVO  10 28
VOLVO 12 27
FORD 8 18
FIAT  5 12
VOLVO 9 6
VOLVO  11 4

q4: Indica il numero e la data di tutti gli ordini dove è stato venduto uno specifico prodotto (bielle) e il numero di pezzi venduti.
   
SELECT O.idO, O.data, D.qta
FROM O, D, P
WHERE O.idO=D.xidO AND
P.idP=D.xidP AND
P.prd='biella';
idO data
qta
1 01/01/2014
3
7 15/04/2014
3
8 15/05/2014
2
12 27/05/2014
2

q5: Elenca i clienti indicando quanti ordini hanno effettuato,
   
SELECT C.nome, COUNT(*) AS TOT
FROM O, C
WHERE C.idC=O.xidC
GROUP BY C.nome;
nome TOT
BMW                 3
FIAT                2
FORD                3
VOLVO               4

q6: Elenca i clienti indicando il quantitativo di pezzi acquistato.
   
SELECT C.nome, SUM(D.qta) AS TOT
FROM O, C, D
WHERE C.idC=O.xidC AND
O.idO=D.xidO
GROUP BY C.nome
ORDER BY SUM(D.qta) DESC;
nome TOT
BMW                 21
FIAT                18
FORD                15
VOLVO               11

 

 

 

 

 


 

 


 







edutecnica