SQL upiti - Pomoć

[romanah]

Molim pomoć oko ovih zadataka... Rješavala sam, ali nisam sigurna da sam dobro riješila. Prvi zadatak možda radi, a drugi ne rade najbolje, najteži je četvrti zadatak. Svaka pomoć je dobro došla! Hvala.

Tablice:
ORG (SIFRA_ORG, NAZIV_ORG, ADRESA)
PUT (SIFRA_PUT, SIFRA_ORG, POLAZISTE, ODREDISTE, PRIJEVOZ, TRAJANJE, CIJENA, PRETHODI)

1. Za svako polazište iz Zagreba prikazati odredište, vrstu prijevoza i naziv organizatora.

SELECT p.odrediste, p.prijevoz, o.naziv_org
FROM org AS o INNER JOIN put AS p
ON o.sifra_org=p.sifra_org
WHERE p.polaziste='Zagreb'

2. Prikazati sve podatke o organizatorima koji imaju barem 3 putovanja koja traju duže od 2 noćenja.

SELECT o.*, COUNT (o.naziv_org) AS broj_putovanja
FROM org AS o INNER JOIN put AS p
ON o.sifra_org=p.sifra_org
WHERE p.trajanje >2
GROUP BY o.naziv_org
HAVING broj_putovanja > 2

3. Koja putovanja su jeftinija od prosječne cijene putovanja po organizatoru?

SELECT p.*
FROM org AS o INNER JOIN put AS p
ON o.sifra_org=p.sifra_org
WHERE p.cijena < (SELECT AVG (p.cijena)
FROM org AS o INNER JOIN put AS p
ON o.sifra_org=p.sifra_org)

4. Prikazati odredište i cijenu svakog putovanja, kao i odredište i cijenu putovanja koju mu neposredno prethodi, za putovanja koja su jeftinija od svojeg prethodnika.

SELECT p.odrediste, p.cijena cijena1, p.cijena cijena2, p.prethodi, p.prethodi
ON o.sifra_org=p.sifra_org
WHERE cijena1 < cijena2

5. Prikazati vrstu prijevoza, trajanje i cijenu za sva ona putovanja koja slijede nakon putovanja brodom u Veneciju.

SELECT p.prijevoz, p.trajanje, p.cijena
FROM put AS p
WHERE p.sifra_put > (SELECT p.sifra_put FROM put AS p
WHERE p.odrediste='Venecija'
AND p.prijevoz='brod')

6. Prikazati trajanje i cijenu onih putovanja koje organizira agencija Atlas, a skuplja su od prosječnog putovanja u Rim.

SELECT p.trajanje, p.cijena
FROM org AS o INNER JOIN put AS p
ON o.sifra_org=p.sifra_org
WHERE o.naziv_org='Atlas'
AND p.cijena > (SELECT AVG (p.cijena)
FROM put AS p
WHERE odrediste='Rim')

[Apostrof]

Upali MS Office Access mozes tu da isprobas i vezbas, napravi tabele, napravi querry i prebaci na SQL view.

[Remedy]

Malo da provezbamo SQL, ionako sve redje imam prilike da ga pisem. Morao sam da promenim imena tabela i kolona u nesto sto se standardnije koristi.

Agencije: id, ime, adresa.
Aranzmani: id, agencija_id, polaziste, odrediste, prevoz, trajanje, cena, prethodni_aranzman_id (uz pretpostavku da je PUT.PRETHODI SIFRA_PUT prethodnog puta, tj aranzmana).

1.
SELECT odrediste, prevoz, ime
FROM agencije INNER JOIN aranzmani
ON agencije.id = aranzmani.agencija_id
WHERE polaziste = 'Zagreb';

2.
SELECT agencije.*
FROM agencije INNER JOIN aranzmani
ON agencije.id = aranzmani.agencija_id
WHERE trajanje > 2
GROUP BY agencije.id
HAVING COUNT(*) > 2;

3.
SELECT aranzmani.*, prosecne_cene.prosek
FROM aranzmani LEFT JOIN (
  SELECT agencija_id, AVG(cena) AS prosek
  FROM aranzmani
  GROUP BY agencija_id
) AS prosecne_cene
ON aranzmani.agencija_id = prosecne_cene.agencija_id
WHERE cena < prosek;

4.
SELECT aranzmani.odrediste, aranzmani.cena,
prethodni_aranzmani.odrediste AS prethodni_odrediste, 
prethodni_aranzmani.cena AS prethodni_cena
FROM aranzmani LEFT JOIN aranzmani AS prethodni_aranzmani
ON aranzmani.prethodni_aranzman_id = prethodni_aranzmani.id
WHERE aranzmani.cena < prethodni_aranzmani.cena;

5.
SELECT prevoz, trajanje, cena
FROM aranzmani
WHERE prethodni_aranzman_id >= (
  SELECT id
  FROM aranzmani
  WHERE prevoz = 'brod' AND odrediste = 'Venecija'
  LIMIT 1
);

6.
SELECT trajanje, cena
FROM aranzmani INNER JOIN agencije
ON aranzmani.agencija_id = agencije.id
WHERE ime = 'Atlas' AND cena > (
  SELECT AVG(cena)
  FROM aranzmani
  WHERE odrediste = 'Rim'
);

Ja bih otprilike tako napisao. A ti nam javi kako izgledaju "zvanicna" resenja kada ih dobijes.

[ivan90BG]

Mislim da rešenje petog zadaka nije dobro. Meni je najlogičnije ovako:

SELECT aranzmani.prevoz, aranzmani.trajanje, aranzmani.cena
FROM aranzmani JOIN aranzmani as prethodni
ON aranzmani.prethodni_aranzman_id = prethodni.id
WHERE prethodni.odrediste = 'Venecija' AND prethodni.prevoz = 'brod'

[Remedy]

To je problem neprecizno formulisanog zadatka. Ja sam pretpostavio da treba izlistati podatke za sva putovanja koja slede nakon prvog putovanja brodom u Veneciju (uzimajuci u obzir i kako je OP pokusao da resi). Tvoj odgovor prikazuje podatke za svako putovanje kome prethodi neko putovanje brodom u Veneciju (eto kako bih ja formulisao pitanje gde se trazi takav odgovor). I jedno i drugo moze da bude ispravno, zavisi sta se trazi .

[ivan90BG]

Postavka zadatka uopšte ne implicira da šifre imaju neko značenje, to jest da veća vrednost šifre znači vremenski skoriji trenutak. Sasvim je moguće da je svaka šifra nasumično generisan broj. Tako da mislim da nema logike porediti šifre.

[Remedy]

To je dobar argument. Ali sta je znacenje poslednje kolone "pethodi" u tabeli "put"? Ja sam pretpostavio da je u pitanju sifra (id) prethodnog aranzmana (puta) i da se aranzmani unose odredjenim redosledom. U praksi sifra (id) najcesce ima AUTO_INCREMENT atribut, ali kao sto kazes ne mora da znaci, nije eksplicitno receno (atributi kolona u tabelama nam nisu poznati) - veca sifra ne mora da znaci da je aranzman kasnije ubacen (ali prethodni aranzman mora vec da bude u tabeli). Vrlo je moguce da tvoj odgovor jeste ono sto se trazi, jer sva ostala resenja zahtevaju JOIN (npr trenutno to obradjuju u skoli/kursu). U tom slucaju mi se formulacija zadatka "prikazati podatke za sva ona putovanja koja slede nakon putovanja brodom u Veneciju" uopste ne svidja (da je bar receno neposredno nakon). Mnogo jasnije bi bilo "prikazati podatke za sva putovanja kojima prethodi neko putovanje brodom u Veneciju". Problem bi trebalo da bude resiti zadatak, a ne protumaciti zadatak.