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));

CREATE TABLE L(
idL COUNTER PRIMARY KEY,
cog CHAR(20));

CREATE TABLE A(
idA COUNTER PRIMARY KEY,
ratax INTEGER,
durata INTEGER,
xidI INTEGER REFERENCES I(idI),
xidL INTEGER REFERENCES L(idL) );

CREATE TABLE S(
idS COUNTER PRIMARY KEY,
spesa INTEGER,
data DATE,
xidA INTEGER REFERENCES A(idA) );

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;

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;

q7:Dire quanti uffici,negozi,appartamenti ci sono nel database

SELECT DISTINCT tipo, COUNT(tipo)
FROM I
GROUP BY tipo;