pomoc oko upita prosek1>prosek2

[ Nuklearni Vojvoda @ 31.05.2008. 11:41 ] @

tabele:
PISAC (jmbg,ime, prezime)
AUTOR (jmbg,kid)
KNJIGA (kid,zanr,naslov)
IZDAVASTVO(kid,iid,izdanje,godina,tiraz,cena)
IZDAVAC (iid,naziv,sediste)

zadatak glasi ovako:
Ispisati ime i prezime pisca cija je prosecna cena knjiga izdatih u periodu
od 1969-1981 veca od prosecne cene tog pisca u periodu od 1985-1992.

Ja sam probao ovako da uradim ali nesto nece. Da li moze neki hint. Znam da u podupitu
ne smem da stavljam 'pisac.jmbg,sum(cena)' ali ne znam za drugaciji nacin.
Hvala unapred

Code:

select ime, prezime,avg(cena) as prosek1 from izdavastvo,autor,knjiga,pisac,izdavac
where pisac.jmbg=autor.jmbg and autor.kid=knjiga.kid and knjiga.kid=izdavastvo.kid and izdavastvo.iid=izdavac.iid
and godina>1968 and godina<1981 and pisac.jmbg  in

(select pisac.jmbg,avg(cena) as prosek2  from izdavastvo,autor,knjiga,pisac,izdavac
where pisac.jmbg=autor.jmbg and autor.kid=knjiga.kid and knjiga.kid=izdavastvo.kid and izdavastvo.iid=izdavac.iid  and
godina>1985 and godina<1992 group by pisac.jmbg

)  group by ime, prezime having prosek1>prosek2
;

Baza je napravljena sa seka.sql a podaci su u podaci.sql ako je slucajno potrebno. Inace ovo radim u sybase posto me je mysql mnogo nervirao jer nije mogao neke stvari @#&^*

_{[Ovu poruku je menjao Vladimir Starcevic dana 31.05.2008. u 13:12 GMT+1]}

[ goranvuc @ 31.05.2008. 12:19 ] @

Evo ti jedna varijanta resenja, izvini ako ne radi jer sam to onako "napamet" sastavio, ali valjda ce ti pomoci.

Code:

SELECT ime, prezime
FROM pisac
LEFT JOIN (
    SELECT AVG(izdavastvo.cena) AS cena_1, autor.jmbg
    FROM izdavastvo
    INNER JOIN knjiga ON izdavastvo.kid = knjiga.kid
    INNER JOIN autor ON knjiga.kid = autor.kid
    WHERE godina >= 1969 AND godina <= 1981
    GROUP BY autor.jmbg) AS upit_1 ON pisac.jmbg = upit_1.jmbg
LEFT JOIN (
    SELECT AVG(izdavastvo.cena) AS cena_2, autor.jmbg
    FROM izdavastvo
    INNER JOIN knjiga ON izdavastvo.kid = knjiga.kid
    INNER JOIN autor ON knjiga.kid = autor.kid
    WHERE godina >= 1985 AND godina <= 1992
    GROUP BY autor.jmbg) AS upit_2 ON pisac.jmbg = upit_2.jmbg
WHERE cena_1 > cena_2

[ jablan @ 31.05.2008. 12:58 ] @

Code:
select jmbg, avg(i1.cena) as a1, avg(i2.cena) as a2 from autor a
inner join knjiga k on a.kid = k.kid
left join izdavastvo i1 on i1.kid = k.kid and i1.godina between 1969 and 1981
left join izdavastvo i2 on i2.kid = k.kid and i2.godina between 1985 and 1992
group by jmbg
having avg(i1.cena) > avg(i2.cena) or (avg(i1.cena) is not null and avg(i2.cena) is null);

[ Nuklearni Vojvoda @ 31.05.2008. 13:08 ] @

Probacu sve ovo odmah i svakako mi je dragoceno i sa join ali da li postoji mogucnost bez koriscenja join zato sto su u pitanju ispitni zadaci pa im je profesor rekao da ne rade sa time, a i sve ostale sam vec uradio bez join pa da bude uniformno.

[ jablan @ 31.05.2008. 13:16 ] @

Citat:

Vladimir Starcevic: pa im je profesor rekao da ne rade sa time

A jel im je tako rekao? Pa onda neka im profesor budži đuntu... :D

BTW, jel dete studira da se osposobi nešto da radi, ili da dobije diplomu? ;)

[ Nuklearni Vojvoda @ 31.05.2008. 13:27 ] @

Citat:

jablan: A jel im je tako rekao? Pa onda neka im profesor budži đuntu... :D
BTW, jel dete studira da se osposobi nešto da radi, ili da dobije diplomu? ;)

Pa znas kako, moja supruga studira da dobije diplomu, a ja ucim da se osposobim da radim nesto :-)) pa je dobro sto mi je dala zadatke da joj uradim taman i ja usput ucim :-)
A inace taj profesor je znao da da i neke neresive probleme za koje sam postavljao pitanje ovde http://www.elitesecurity.org/t233148-0 ali profesor je uvek u pravu :-)

[ goranvuc @ 31.05.2008. 13:45 ] @

Ako vec ucis i nesto znas o SQL-u ne bi trebalo da ti bude problem da prevedes upit sa JOIN klauzulama na upit bez toga. Nisi ni odgovorio da li si probao, ja ne mogu da probam jer nemam taj tvoj sybase

[ Nuklearni Vojvoda @ 31.05.2008. 13:49 ] @

E, hvala na pomoci upravo probavam ali mi treba malo vremena posto nece bas iz prve da radi...

[ jablan @ 31.05.2008. 14:05 ] @

Citat:

goranvuc: ne bi trebalo da ti bude problem da prevedes upit sa JOIN klauzulama na upit bez toga.

Može li LEFT JOIN tek tako da se prevede? Pitam jer ne znam (iskreno, nikad mi niko nije zakerao da li da upite radim sa ili bez JOIN-ova). BTW, izgleda da je u konkretnom slučaju profesor informatike sa filološkog fakulteta... Šta reći...

[ goranvuc @ 31.05.2008. 15:00 ] @

Citat:

jablan: Može li LEFT JOIN tek tako da se prevede?

Izvinjavam se na nepromišljenoj izjavi, tj lupetanju

Pošto koristim isključivo JOIN klauzule zaboravio sam da možemo bez JOIN predstaviti samo INNER JOIN ili CROSS JOIN relacije, a LEFT i RIGHT ne može (ako se neko u međuvremenu ne pojavi i ubedi me u suprotno) - hvala na primedbi.

[ Nuklearni Vojvoda @ 31.05.2008. 15:05 ] @

Ovo radi samo sam morao da dodam jos jednu tabelu jer mi treba ime i prezime, a i u sybase mora da pise left outer join e sad samo malo da proucim ove join posto se sa njima ne snalazim najbolje.

Hvala svima na pomoci

Code:

select ime, prezime,a.jmbg, avg(i1.cena) a1, avg(i2.cena) a2  from autor a
inner join knjiga k on a.kid = k.kid
left outer join izdavastvo i1 on i1.kid = k.kid and i1.godina>1969 and  i1.godina<1981
left outer join izdavastvo i2 on i2.kid = k.kid and i2.godina>1985 and  i2.godina<1992,
  pisac p inner join autor a on p.jmbg=a.jmbg
group by a.jmbg,ime,prezime
having avg(i1.cena) >= avg(i2.cena) or (avg(i1.cena) is not null and avg(i2.cena) is null) ;

_{[Ovu poruku je menjao Vladimir Starcevic dana 31.05.2008. u 16:22 GMT+1]}

[ Fitopatolog @ 31.05.2008. 23:15 ] @

Citat:

goranvuc: Izvinjavam se na nepromišljenoj izjavi, tj lupetanju ;) Pošto koristim isključivo JOIN klauzule zaboravio sam da možemo bez JOIN predstaviti samo INNER JOIN ili CROSS JOIN relacije, a LEFT i RIGHT ne može (ako se neko u međuvremenu ne pojavi i ubedi me u suprotno) - hvala na primedbi.

...levo (desno) spajanje se može dobiti unijom dva standardna upita...

uzgred, Oracle ima svoju notaciju za ova spajanja: http://forums.oracle.com/forums/thread.jspa?messageID=2120857

[ chachka @ 01.06.2008. 01:30 ] @

Prvo:
U upitu kojeg je dao Jablan, a kojeg je kasnije preradio Vladimir, nije potrebno spajanje sa tabelom 'knjige'.

Drugo:

Citat:

Vladimir Starcevic: ... ali da li postoji mogucnost bez koriscenja join zato sto su u pitanju ispitni zadaci pa im je profesor rekao da ne rade sa time...

U kom veku živi taj profesor kad još ne upotrebljava JOIN?!? I nije problem što on ne upotrebljava JOIN, problem je što maltretira studente da ne upotrebljavaju JOIN!

Treće:
Sumnjiva mi je zamena originalnog Jablanovog dela

Code:
i1.godina between 1969 and 1981
...
i2.godina between 1985 and 1992

sa Vladimirovim delom

Code:
i1.godina>1969 and i1.godina<1981
...
i2.godina>1985 and i2.godina<1992

jer to naprosto nije isto! Po postavci zadatka bih rekao da je ipak Jablan u pravu.
Usput

Code:
a BETWEEN b AND c
je ekvivalentno sa
a >= b AND a <= c

Četvrto:

Citat:

Fitopatolog: ...levo (desno) spajanje se može dobiti unijom dva standardna upita...

Evo i prepravljenog upita kojeg je dao Vladimir, bez nepotrebnog spajanja sa tabelom 'knjige', bez upotrebe INNER JOIN i LEFT OUTER JOIN konstrukcija, a sa upotrebom UNION operacije.

Code:
SELECT p.ime, p.prezime, p.jmbg, SUM(c.cena_1), SUM(c.cena_2)
  FROM pisac p,
       (SELECT a.jmbg, AVG(i.cena) AS cena_1, 0.0 AS cena_2
          FROM autor a, izdavastvo i
         WHERE a.kid = i.kid
           AND i.godina > 1969
           AND i.godina < 1981
         GROUP BY a.jmbg
         UNION ALL
        SELECT a.jmbg, 0.0 AS cena_1, AVG(i.cena) AS cena_2
          FROM autor a, izdavastvo i
         WHERE a.kid = i.kid
           AND i.godina > 1985
           AND i.godina < 1992
         GROUP BY a.jmbg
       ) c
WHERE p.jmbg = c.jmbg
GROUP BY p.ime, p.prezime, p.jmbg
HAVING SUM(c.cena_1) > SUM(c.cena_2)

[ jablan @ 01.06.2008. 06:48 ] @

Citat:

chachka: U upitu kojeg je dao Jablan, a kojeg je kasnije preradio Vladimir, nije potrebno spajanje sa tabelom 'knjige'.

Vaistinu, moja greška...

BTW, jel ovaj fancy indenting radiš ručno ili nekim editorom?

[ chachka @ 02.06.2008. 09:57 ] @

PSPad editor i modifikovana skripta za formatiranje SQL-a, mada još neke stvari odradim ručno

[ Getsbi @ 02.06.2008. 12:53 ] @

Evo Zidar je pre desetak dana na Access forumu ukazao na link gde se nalazi dobar SQL formatter.
http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

[ escape... @ 19.06.2008. 01:06 ] @

Ja videh ovu temu relativno skoro, pa sa zakašnjenjem dajem svoj skromni doprinos (za slučaj da nekoga ovo još uvek zanima).

Jeste da join najlakše (makar za čoveka) rešava sve probleme ovog tipa, ali on, tj. neka varijanta spajanja, mora se koristiti jedino u slučaju potrebe prikazivanja podataka iz više tabela.

Pretpostavljam da je taj profesor mislio na korišćenje tzv. ugnježdenih upita. U ovom slučaju to je moguće, a mislim da je i poželjno, budući da treba prikazati podatke samo iz jedne tabele, tj. relacije, a to su ime i prezime pisca.

Moj predlog upita bi bio:

Code:

SELECT ime, prezime
FROM Pisac
WHERE
      (SELECT AVG(cena)
       FROM Izdavastvo
       WHERE godina BETWEEN 1969 AND 1981
       AND kid IN
                  (SELECT kid
                   FROM Autor
                   WHERE Autor.jmbg = Pisac.jmbg)) >
      (SELECT AVG(cena)
       FROM Izdavastvo
       WHERE godina BETWEEN 1985 AND 1992
       AND kid IN
                  (SELECT kid
                   FROM Autor
                   WHERE Autor.jmbg = Pisac.jmbg));

[ Nuklearni Vojvoda @ 19.06.2008. 22:53 ] @

To je to, on je poceo da im radi tako na tabli valjda ali nije zavrsio do kraja. U medjuvremenu je moja supruga dobila 8 na pismenom i kao krajnju ocenu posle usmenog dobila 10 :-) sad sam miran do sledece godine kad moram da ucim awk i ko zna sta jos...
Hvala svima jos jednom.