Esercizio no.1:soluzione

Si profilano due entità: gli ordini (O) e l'entità impiegati (I) ; quest'ultima deve essere correlate alla precedente; ricordiamo che ogni impiegato può seguire più ordini ed ogni ordine può essere seguito da un solo impiegato. Si tratta di una relazione 1:n (uno-molti).

La tabella degli impiegati sarà caratterizzata dal seguente tracciato dei campi:

idI	contatore chiave primaria
nome	testo
livello	numerico

CREATE TABLE I(
idI COUNTER PRIMARY KEY,
nome CHAR(20),
livello INTEGER);

La tabella degli ordini dovrà contenere la chiave esterna che ci possa ricondurre al'impiegato che segue quello specifico ordine.

idO	contatore chiave primaria
cliente	testo
data	data
importo	numerico
xidI	numerico chiave esterna

CREATE TABLE O(
idO COUNTER PRIMARY KEY,
cliente CHAR(20),
data date,
importo INTEGER,
xidI INTEGER REFERENCES I(idI));

Ne deriva il seguente diagramma logico: tra le tabelle:

adesso popoliamo il database, per fortuna non sono molti record.

I
idI	nome	livello
1	bianchi	3
2	rossi	4
3	verdi	2
4	neri	2
5	ciano	3
6	rosa	5

O
idO	cliente	data	importo	xidI
1	VOLVO	01/01/2014	1000	1
2	BMW	01/02/2014	2400	1
3	FIAT	15/01/2014	1500	6
4	FORD	15/02/2014	1000	6
5	NISSAN	12/03/2014	1200	6
6	FIAT	07/02/2014	800	2
7	NISSAN	03/02/2014	2600	3
8	SKODA	04/02/2014	1400	3
9	FIAT	02/04/2014	1100	3
10	BMW	15/04/2014	2000	4
11	RENAULT	04/05/2014	1500	4
12	NISSAN	15/05/2014	1750	5
13	VOLVO	18/02/2014	3000	5

q1: Elenca i dipendenti che seguono uno specifico cliente ( nel nostro caso FIAT).

SELECT I.nome
FROM I, O
WHERE i.idI=O.xidI And O.cliente='FIAT';

nome

rosa

rossi

verdi

q2: Elenca gli ordini seguiti da uno specifico operatore (nel nostro caso 'bianchi').

SELECT O.cliente
FROM I, O
WHERE i.idI=O.xidI And I.nome='Bianchi';

cliente

VOLVO

BMW

q3: Fai un elenco dei clienti indicando la loro spesa complessiva in ordine decrescente.

SELECT O.cliente, SUM(O.importo)
AS TOT
FROM I, O
WHERE i.idI=O.xidI
GROUP BY O.cliente ORDER BY SUM(O.importo) DESC;

cliente	TOT
NISSAN	5550
BMW	4400
VOLVO	4000
FIAT	3400
RENAULT	1500
SKODA	1400
FORD	1000

q4: Fai un elenco dei livelli indicando per ogni livello quanti ordini sono seguiti.

SELECT I.livello, COUNT(O.xidI) AS CONTA
FROM I, O
WHERE i.idI=O.xidI
GROUP BY livello;

livello	CONTA
2	5
3	4
4	1
5	3

q5: Fai l'elenco degli ordini di febbraio e degli impiegati che li seguono.

SELECT O.cliente, O.data, I.nome FROM I, O WHERE i.idI=O.xidI AND data<#3/1/14# AND data>#1/31/14#;	N.B.come le date debbano riferirsi col formato mm.gg.aa

cliente	data	nome
BMW	01/02/2014	bianchi
FORD	15/02/2014	rosa
FIAT	07/02/2014	rossi
NISSAN	03/02/2014	verdi
SKODA	04/02/2014	verdi
VOLVO	18/02/2014	ciano

q6: Elenca gli impiegati e l'importo complessivo degli ordini che hanno gestito.

SELECT I.nome, SUM(O.importo) AS TOT
FROM I, O
WHERE i.idI=O.xidI
GROUP BY I.nome
ORDER BY SUM(O.importo) DESC;

nome	TOT
verdi	5100
ciano	4750
rosa	3700
neri	3500
bianchi	3400
rossi	800

q7: Fai un elenco dei livelli indicando per ogni livello la somma degli importi gestiti.

SELECT I.livello, SUM(O.importo) AS CONTA
FROM I, O
WHERE i.idI=O.xidI
GROUP BY livello
ORDER BY SUM(O.importo) DESC;

livello	CONTA
2	8600
3	8150
5	3700
4	800

q8: Elenca gli impiegati e la quantità di ordini che hanno gestito.

SELECT I.nome, COUNT(O.xidI) AS TOT
FROM I, O
WHERE i.idI=O.xidI
GROUP BY I.nome
ORDER BY COUNT(O.xidI) DESC;

nome	TOT
verdi	3
rosa	3
neri	2
ciano	2
bianchi	2
rossi	1