edutecnica



Esercizio no.10:soluzione

Per comodità indichiamo:
I=immobile
A=affitto
L=locatario
S=spesa

Decidiamo dunque, di implementare lo schema concettuale indicato.
Le query di creazione e i valori di test contenuti nelle tabelle sono i seguenti:

CREATE TABLE I(
idI COUNTER PRIMARY KEY,
tipo CHAR(1),
luogo CHAR(20));
idI tipo luogo
1 A varese
2 N como
3 U milano
4 A varese
5 A milano
6 U varese
7 A como

CREATE TABLE L(
idL COUNTER PRIMARY KEY,
cog CHAR(20));
idL cog
1 rossi
2 verdi
3 bianchi
4 neri

CREATE TABLE A(
idA COUNTER PRIMARY KEY,
ratax INTEGER,
durata INTEGER,
xidI INTEGER REFERENCES I(idI),
xidL INTEGER REFERENCES L(idL) );
idA ratax durata xidI xidL
1 1 5 2 1
2 3 3 3 1
3 0 2 4 2
4 2 2 5 2
5 1 1 6 3
6 1 3 7 4
7 0 2 7 4

CREATE TABLE S(
idS COUNTER PRIMARY KEY,
spesa INTEGER,
data DATE,
xidA INTEGER REFERENCES A(idA) );
idS spesa data
xidA
1 3000 01/04/2007
1
2 5000 01/07/2007
1
3 1500 14/02/2009
5
4 4500 03/05/2008
7
5 875 15/01/2009
7

Passiamo a risolvere le query di interrogazione:

q1:Visionare tutti i locatari che hanno un contratto > 2 anni.
   
SELECT DISTINCT L.cog
FROM L, A
WHERE L.idL=A.xidL AND A.durata>2;
cog
neri
rossi

q2:Dire quanti immobili sono affittati per ogni città.
   
SELECT luogo, COUNT(luogo) AS TOTALE
FROM I
GROUP BY luogo;
luogo TOTALE
como 2
milano 2
varese 3

q3:Individuare un contratto di affitto dopo il mancato pagamento di tre rate non pagate.
   
SELECT L.cog
FROM L, A
WHERE L.idL=A.xidL AND A.ratax>=3;
cog
rossi

q4:Visionare tutte le spese effettuate dal locatario Rossi effettuate nel 2007.
   
SELECT SUM(S.spesa)
FROM S, A, L
WHERE A.idA=S.xidA AND
L.idL=A.xidL AND L.cog='rossi';
Expr1000
8000

q5:Dire in che data Bianchi ha eseguito lavori per un importo maggiore di 3500€.
   
SELECT S.data, S.spesa
FROM S, A, L
WHERE L.idL=A.xidL AND
A.idA=S.xidA AND
L.cog='bianchi' AND S.spesa>1000;
data spesa
14/02/2009 1500

q6:Calcolare le spese registrate in un anno per tutti gli immobili, suddivisi per città.
   
SELECT DISTINCT I.luogo, SUM(S.spesa)
FROM I, A, S
WHERE I.idI=A.xidI AND
A.idA=S.xidA
GROUP BY I.luogo;
luogo Expr1001
como 13375
varese 1500

q7:Dire quanti uffici,negozi,appartamenti ci sono nel database
   
SELECT DISTINCT tipo, COUNT(tipo)
FROM I
GROUP BY tipo;
tipo Expr1001
A 4
N 1
U 2

 







edutecnica