Esercizio no.8:soluzione

Diamo subito un possibile diagramma, dove abbiamo indicato con
T:tabella delle categorie
P:tabella dei rappresentanti
C:tabella dei clienti
F:tabella delle fatture

Le query di creazione sono le seguenti.

CREATE TABLE T( idT COUNTER PRIMARY KEY, Classe CHAR(1), Costo INTEGER);	CREATE TABLE P( idP COUNTER PRIMARY KEY, Cognome CHAR(20), Zona CHAR(20), xidT INTEGER REFERENCES T(idT));

CREATE TABLE C( idC COUNTER PRIMARY KEY, Luogo CHAR(20), piva CHAR(20), rs CHAR(20), xidP INTEGER REFERENCES P(idP));	CREATE TABLE F( idF COUNTER PRIMARY KEY, num CHAR(20), data DATE, importo INTEGER, xidC INTEGER REFERENCES C(idC));

Si ottiene il seguente diagramma logico:

Riempiamo le tabelle coi seguenti valori di test:

idT	Classe	Costo
1	A	14
2	B	12
3	C	10
4	D	8

idP	Cognome	Zona	xidT
1	Bianchi	Milano	1
2	Garavaglia	Como	3
3	Lanfranconi	Varese	3
4	Neri	Novara	3
5	Orlandi	Novara	2
6	Rossi	Milano	2
7	Verdi	Sondrio	4

idC	Luogo	piva	rs	xidP
1	Varese	1111111111	agusta	3
2	Varese	3334445551	air liquid	3
3	Orta	5555555555	c3	5
4	Gazzada	9999999999	connon	3
5	rho	1234567890	cit	1
6	erba	9078564523	coconut	2
7	milano	0987654321	dec	1
8	sondrio	676767677	mdw	7
9	novara	1231231231	mustek	5
10	nova	3232323232	nova sistemi	6
11	saronno	2143657890	operational	2
12	novara	2222222222	pavesi	4
13	turbigo	3333333333	recuperator	4
14	sondrio	565656565	sit	7
15	trezzano s.n	3213213213	yumax	6
16	bresso	2121212121	zincati	6

idF	num	data	importo	xidC
1	01/07	01/01/2007	2000	5
2	02/07	10/03/2007	1500	7
3	03/07	14/04/2007	1000	5
4	04/07	15/05/2007	2500	7
5	05/07	20/06/2007	3200	11
6	06/07	22/09/2007	800	15
7	08/07	27/10/2007	2100	14
8	09/07	01/11/2007	1000	13
9	10/07	05/12/2007	1300	5
10	01/08	10/01/2008	2199	3
11	02/08	15/03/2008	3100	6
12	03/08	20/05/2008	900	8
13	04/08	22/06/2008	900	4
14	05/08	25/09/2008	2000	12
15	06/08	30/10/2008	2800	14
16	07/08	01/11/2008	2350	16

q1:Elenco dei clienti di un singolo rappresentante. (nel nostro caso, inseriamo 'Bianchi' nella maschera di immissione)

SELECT C.rs
FROM C, P
WHERE P.idP=C.xidP AND
P.cognome=[Cognome:];

cit

dec

q2:numero dei clienti affidati ad un singolo rappresentante.
(anche in questo caso, inseriamo inseriamo 'Bianchi' nella maschera di immissione )

SELECT COUNT(*) AS TOT_CLIENTI
FROM C, P
WHERE P.idP=C.xidP AND
P.cognome=[Cognome:];

TOT_CLIENTI

q3:Fatturato elencato per singolo cliente

SELECT C.rs, SUM(F.importo) AS TOTALE
FROM F, C
WHERE C.idC=F.xidC
GROUP BY C.rs;

rs	TOTALE
c3	2199
cit	4300
coconut	3100
connon	900
dec	4000
mdw	900
operational	3200
pavesi	2000
recuperator	1000
sit	4900
yumax	800
zincati	2350

q4:Nome del cliente e data di emissione per la fattura con importo massimo.

SELECT C.rs, F.data, F.importo
FROM C,F
WHERE C.idC=F.xidC AND
F.Importo=(
SELECT MAX(F.Importo) FROM F
);

rs	data	importo
operational	20/06/2007	3200

q5:Nome del rappresentante corrispondente alla fatture con importo massimo.

SELECT P.cognome, C.rs, F.data,.importo FROM C, P, F WHERE C.idC=F.xidC AND P.idP=C.xidP AND F.Importo=( SELECT MAX(F.importo) FROM F );

cognome	rs	data	importo
Garavaglia	operational	20/06/2007	3200

q6:Nome dei rappresentanti per i quali il fatturato totale realizzato è maggiore di 5000.

SELECT P.cognome, SUM(F.importo)
AS TOTALE
FROM P, C, F
WHERE C.idC=F.xidC AND
P.idP=C.xidP
GROUP BY P.cognome
HAVING SUM(F.importo)>5000;

cognome	TOTALE
Bianchi	8300
Garavaglia	6300
Verdi	5800