Esercizio no.4:soluzione

Il contesto sembra chiaro un cliente pò 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