edutecnica



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:];

rs
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
2

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

 







edutecnica