query SQL standard

[pedra_89]

Ciao a tutti, devo risolvere una query sql usando solo codice sql standard, e non riesco a venirne a capo . spero qualcuno mi avvicini alla soluzione. questa è la parte di modello relazionale interessata
ANNUNCIO (id_annuncio, data_pubblicazione, data_scadenza, prezzo, occupazione, id_immobile, username, data_disponibilità)
IMMOBILE (id_immobile, tipo, regione, citta, via, numciv , cap, metratura, privcomm, descrizione, piano, numlocali)

e nella query devo trovare gli annunci di vendita di immobili il cui costo al metro quadro è inferiore al 20% del costo medio al metro quadro degli annunci attivi per immobili della stessa città e della stessa tipologia.
Sono arrivato al punto di trovare per ogni città e tipo il suo costo medio al metro quadro, e fin qua era facile

SELECT citta,tipo, AVG(prezzo/metratura)
FROM annuncio a JOIN immobile i ON a.id_immobile=i.id_immobile
WHERE current_date<data_scadenza
GROUP BY citta, tipo

*(tipo può assumere alcuni valori, come "appartamento", "villa" e altri simili)

ora però dovrei confrontare ogni singolo annuncio con la sua rispettiva media e stampare solo quelli il cui costo al metro quadro è inferiore al 20% della media calcolata nel codice sopra.
grazie ciao

[nicola75ss]

Posta un dump delle due tabelle con qualche record di esempio e indica pure con quale db ti stai esercitando, come da regolamento.

[pedra_89]

scusa cosa intendi con dump? sono nuovo nell'ambiente
per quanto riguarda il db, non l'ho messo perchè devo fare la query col codice sql standard, quindi deve funzionare ovunque, è più un esercizio teorico che una necessità per un sito, ma cmq se può essere utile, uso postgres

[nicola75ss]

Per dump si intende il codice sql col quale si creano le tabelle e si popolano.
Quindi gentilmente incolla sia le create che le insert di esempio e il risultato che la query dovrebbe produrre.

[pedra_89]

ho semplificato un pelo togliendo attributi inutili o che erano chiavi esterne per tabelle che ora non interessano
CREATE TABLE immobile
( id_immobile numeric(5,0) NOT NULL,
tipo character varying(30) NOT NULL,
regione character varying(25) NOT NULL,
citta character varying(20) NOT NULL,
via character varying(25) NOT NULL,
numCiv character varying(4) NOT NULL,
cap character varying(5) NOT NULL,
metratura numeric(4,0) NOT NULL,
CONSTRAINT chiaveimm PRIMARY KEY (id_immobile));

CREATE TABLE annuncio (
ID_annuncio numeric(5,0) NOT NULL,
data_pubblicazione date NOT NULL,
data_scadenza date NOT NULL,
prezzo numeric(11,0),
ID_immobile numeric(5,0) NOT NULL,
CONSTRAINT chiaveannuncio PRIMARY KEY (ID_annuncio),
CONSTRAINT esternaimmobile FOREIGN KEY (ID_immobile)
REFERENCES immobile (id_immobile) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE CASCADE );

INSERT INTO immobile VALUES ('1', 'ristorante', 'puglia','taranto','via edison', '13','89567','285');
INSERT INTO immobile VALUES ('2', 'ristorante', 'puglia','taranto','via festa', '245','89467','300');
INSERT INTO immobile VALUES ('3', 'ristorante', 'puglia','taranto','via lorenzi', '2','89153','250');
INSERT INTO immobile VALUES ('4', 'ristorante', 'puglia','bari','via edison', '13','89567','285');
INSERT INTO immobile VALUES ('5', 'ristorante', 'puglia','bari','via festa', '245','89467','300');
INSERT INTO immobile VALUES ('6', 'ristorante', 'puglia','bari','via lorenteggio', '2','89153','250');

INSERT INTO annuncio VALUES (1,'2010-07-01','2010-08-23', 800000.00, '1');
INSERT INTO annuncio VALUES (2,'2010-05-12','2010-08-30', 1000000.00, '2');
INSERT INTO annuncio VALUES (3,'2010-06-16','2010-08-12', 105000.00, '3');
INSERT INTO annuncio VALUES (4,'2010-07-01','2010-08-23', 950000.00, '4');
INSERT INTO annuncio VALUES (5,'2010-05-12','2010-08-30', 1000000.00, '5');
INSERT INTO annuncio VALUES (6,'2010-06-16','2010-08-12', 100000.00, '6');

per quanto riguarda l'output mi basta che esca l'attributo id_annuncio. con questi dati dovrebbero uscire gli annunci 3 e 6.
p.s.: insistendo forse mi è venuta dopo tanti tentativi, a questo punto sarebbe sufficiente verificarne la correttezza. ho fatto così:

CREATE OR REPLACE VIEW queryF (cittaF,tipoF,mediaF) as
SELECT citta,tipo, AVG(prezzo/metratura)
FROM annuncio NATURAL JOIN immobile
WHERE current_date < data_scadenza
GROUP BY citta, tipo;

SELECT id_annuncio
FROM annuncio natural join immobile i
GROUP BY id_annuncio,prezzo,metratura,citta,tipo
HAVING (prezzo/metratura) <= (SELECT ((mediaf/100)*20)
FROM queryF q
WHERE i.citta=q.cittaF and i.tipo=q.tipoF);

c'è un modo più semplice (sempre che sia giusta)?
grazie ancora

[nicola75ss]

A occhiometro mi sembra corretta.
Anche io l'avrei risolta seguendo quella logica. Qui in ufficio non ho postgresql a disposizione. Stasera se vuoi una conferma la provo, sempre che qualcuno non intervenga nel frattempo.