[ MaelstroM @ 15.01.2012. 14:35 ] @
Pokusavam da uradim neke pripremne zadatke za kolokvijum vezane za SQL upite. Napisao sam upite, pa mi recite je li ispravno i predlozite neki jednostavniji nacin za njihovo ispisivanje (ako postoji). Unaprijed hvala

Date su sledeće relacione šeme:

Grad(Naziv, Drzava, BrojStanovnika)
Dvorana(DID, Kapacitet, Naziv references Grad)
Koncert(KID, JMBG references Izvodjac, Trajanje, DID refences Dvorana)
Izvodjac(JMBG, Ime, Adresa, Starost)
Ulaznica(UID, KID references Koncert, Cijena, Tip)

Napomena. Primarni ključevi su podvučeni. Atribut Ime u relacionoj šemi Dvorana je spoljnji ključ na relacionu šemu Grad i označava u kom gradu se nalazi dvorana. Atribut JMBG u relacionoj šemi Koncert je spoljnji ključ na relacionu šemu Izvođač i označava koji izvođač je nastupao na koncertu. U relacionoj šemi Koncert atribut DID je spoljnji ključ na relacionu šemu Dvorana i označava u kojoj dvorani je održan koncert.

a) Izlistati ime izvođača, kao i broj različitih gradova u kojima je izvođač održao koncerte pod uslovom da je taj broj veći od 10.

SELECT i.Ime, COUNT(DISTINCT d.Naziv) as BrGradova FROM Izvodjac i, Koncert k, Dvorana d
WHERE k.JMBG = i.JMBG AND k.DID = d.DID
GROUP BY i.Ime
HAVING BrGradova > 10

b) Naći koncerte za koji je prodato više VIP ulaznica tipa nego ostalih karata. Napomena. VIP ulaznice su one karte čiji je Tip jednak VIP.

SELECT k.* FROM Koncert k
WHERE (SELECT COUNT(*) FROM ULAZNICA u1
WHERE u1.KID = k.KID AND u1.Tip = 'VIP') > (SELECT COUNT(*) FROM Ulaznica u2
WHERE u2.KID = k.KID and u2.Tip != 'VIP')

c) Naći izvođače koji nastupaju isključivo u dvoranama koje imaju kapacitet veći od kapaciteta najveće dvorane u Podgorici.

SELECT i.* FROM Izvodjac i, Koncert k, Dvorana d
WHERE NOT EXISTS(SELECT * FROM Koncert k, Dvorana d
WHERE k.JMBG = i.JMBG AND k.DID = d.DID AND d.Kapacitet < (SELECT MAX(d1.Kapacitet) FROM Dvorana d1
WHERE d1.Grad = 'Podgorica'))


d) Naći parove JMBG, Naziv tako da izvođač koji je identifikovan atributom JMBG nikada nije održao koncert u gradu koji je identifikovan atributom Naziv.

SELECT i.JMBG, g.Naziv FROM Izvodjac i, Grad g
WHERE NOT EXISTS(SELECT * FROM Koncert k, Dvorana d
WHERE k.JMBG = i.JMBG AND k.DID = d.DID AND d.Naziv = g.Naziv)

e) Naći ime grada u kome je nastupao svaki izvođač.

SELECT g.Naziv FROM Grad g
WHERE NOT EXISTS (SELECT * FROM Izvodjac i
WHERE NOT EXISTS (SELECT * FROM Koncert k, Dvorana d
WHERE k.JMBG = i.JMBG AND k.DID = d.DID AND d.Naziv = g.Naziv))

f) Naći koncert za koji je prodato najviše ulazica.

SELECT u.KID, COUNT(*) as ProdateUlaznice FROM Ulaznica u
GROUP BY u.KID
HAVING ProdateUlazince = (SELECT MAX(PrUlazn) FROM (SELECT COUNT(*) as PrUlazn FROM Ulaznica u1
GROUP BY u1.KID))

g) Naći države u kojima je nastupao izvođač koji je do sada imao najveći broj koncerata.

SELECT g.Drzava FROM Grad g, Izvodjac i, Koncert k, Dvorana d
WHERE k.JMBG = i.JMBG AND k.DID = d.DID AND d.Naziv = g.Naziv AND (SELECT COUNT(*) FROM Koncert k1
WHERE k1.JMBG = i.JMBG) >= ALL (SELECT BrKoncerata FROM (SELECT k2.JMBG, COUNT(*) as BrKoncerata FROM Koncert k2 GROUP BY k2.JMBG)

h) Naći grad takav da je u njemu održano više od deset koncerata i nijedan izvođač nije nastupao dva puta u tom gradu.

SELECT d.Naziv, COUNT(*) FROM Dvorana d, Koncert k,
WHERE k.DID = d.DID AND NOT EXIST (SELECT i.JMBG, COUNT(*) FROM Izvodjac i, Koncert k1, Dvorana d1
WHERE k1.JMBG = i.JMBG AND k1.DID = d1.DID AND d1.Naziv = d.Naziv
GROUP BY i.JMBG
HAVING COUNT(*) >= 2)
GROUP BY d.Naziv
HAVING COUNT(*) > 10

i) Naći izvođača koji je imao najviše rasprodatih koncerata. Napomena. Koncert je rasprodat ako je broj prodatih ulaznica jednak kapacitetu dvorane u kojoj je održan koncert.

SELECT k.JMBG, COUNT(*) from Koncert k, Dvorana d
WHERE k.DID = d.DID AND (SELECT COUNT(*) FROM Ulaznica u WHERE u.KID = k.KID) = d.Kapacitet
GROUP BY k.JMBG
HAVING COUNT(*) = (SELECT MAX(COUNT(*)) FROM (SELECT k1.JMBG, COUNT(*) FROM Koncert k1, Dvorana d1
WHERE k1.DID = d1.DID AND (SELECT COUNT(*) FROM Ulaznica u1 WHERE u1.KID = k1.KID) = d1.Kapacitet)
GROUP BY k1.JMBG)

j) Naći izvođača koji je u svakoj dvorani imao makar dva koncerta.

SELECT i.* FROM Izvodjac i
WHERE NOT EXISTS (SELECT d.DID, COUNT(*) FROM Dvorana d, Koncert k WHERE d.DID = k.DID AND k.JMBG = i.JMBG
GROUP BY d.DID HAVING COUNT(*) < 2) AND NOT EXISTS (SELECT d1.DID FROM Dvorana d1 WHERE NOT EXISTS(SELECT * FROM Koncert k1 WHERE k1.DID = d1.DID AND k1.JMBG = i.JMBG ))





[Ovu poruku je menjao MaelstroM dana 16.01.2012. u 21:53 GMT+1]
[ MaelstroM @ 17.01.2012. 00:59 ] @
np.
Anyway, upravo sam testirao upite u MySql-u. Ponedje fali zagrada ili postoji manja sintaksna greska pa to treba ispraviti, inace svi upiti ispravno rade.
[ vlada_vlada @ 17.01.2012. 18:06 ] @
a) Sta se desava ukoliko postoje dva izvodjaca sa istim imenom ? (Ime nije primarni kljuc tabele Izvodjaci, pa treba obratiti paznju)

b) Sta se desava ukoliko Tip ima vrednost NULL ?

c) Imas visak dekartovih proizvoda u FROM klauzuli.

g) Ovaj je zbrljan iako je ideja ok. Imas nepotrebne podupite, tabele (i njihova spajanja). Povrh svega toga i manjak zagrada :)

h) Tekst zadatka kaze "nijedan izvođač nije nastupao dva puta u tom gradu" - ne "dva ili vise puta". Mozda je i greska u prekucavanju zadatka.. Inace, ne treba tabela Izvodjaca u podupitu.

i) MAX(COUNT(*)) ? Van toga je ok.

j) Moze dosta jednostavnije uz upotrebu spoljnog join-a.

Takodje, (b) moze znacajno da se pojednostavi.

Cesto imas nepotrebne kolone u podupitima, ali da kazemo da to nije greska :)

[Ovu poruku je menjao vlada_vlada dana 17.01.2012. u 19:19 GMT+1]
[ lonelyrider_44 @ 17.01.2012. 19:03 ] @
Code:

SELECT i.Ime, COUNT(DISTINCT d.Naziv) as BrGradova FROM Izvodjac i, Koncert k, Dvorana d
 WHERE k.JMBG = i.JMBG AND k.DID = d.DID
 GROUP BY i.Ime
 HAVING BrGradova > 10


Tabele možeš da spajaš i ovako:
Code:

SELECT i.Ime, COUNT(DISTINCT d.Naziv) as BrGradova 
FROM Izvodjac i INNER JOIN  Koncert k INNER JOIN Dvorana d 
  ON k.DID = d.DID
  ON k.JMBG = i.JMBG
GROUP BY i.Ime
HAVING BrGradova > 10


Uslove (ON delovi) navodiš u suprotnom redosledu od redosleda navođenja tabela
Osim INNER JOIN-a, postoji i LEFT JOIN, potraži malo na netu da vidiš njegovu upotrebu.
[ MaelstroM @ 17.01.2012. 19:41 ] @
Citat:
vlada_vlada: a) Sta se desava ukoliko postoje dva izvodjaca sa istim imenom ? (Ime nije primarni kljuc tabele Izvodjaci, pa treba obratiti paznju)

b) Sta se desava ukoliko Tip ima vrednost NULL ?

c) Imas visak dekartovih proizvoda u FROM klauzuli.

g) Ovaj je zbrljan iako je ideja ok. Imas nepotrebne podupite, tabele (i njihova spajanja). Povrh svega toga i manjak zagrada :)

h) Tekst zadatka kaze "nijedan izvođač nije nastupao dva puta u tom gradu" - ne "dva ili vise puta". Mozda je i greska u prekucavanju zadatka.. Inace, ne treba tabela Izvodjaca u podupitu.

i) MAX(COUNT(*)) ? Van toga je ok.

j) Moze dosta jednostavnije uz upotrebu spoljnog join-a.

Takodje, (b) moze znacajno da se pojednostavi.

Cesto imas nepotrebne kolone u podupitima, ali da kazemo da to nije greska :)

[Ovu poruku je menjao vlada_vlada dana 17.01.2012. u 19:19 GMT+1]


a) U pravu si. Umjesto "GROUP BY i.Ime" treba "GROUP BY i.JMBG"
b) E sad to zavisi je li Tip postavljen na NOT NULL pri kreiranju tabele. Ako nije, onda treba da dodam jos jedan uslov ..or u2.TIP IS NULL u zagradi sa prethodnim. Dobro si primjetio. Kako moze da se pojednostavi?
c) Tacno. To sam vidio i ispravio pri testiranju.
g) U pravu si, nepotrebna je tabela Izvodjac, i moze se izbaciti jedan podupit.
h) Ja sam tumacio da je "svaki izvodjac nastupio najvise jednom u tom gradu", i tacno je da ne treba tabela Izvodjaci.
i) Pocetnicka greska :D
j) Kako?

Hvala na komentaru :)
[ MaelstroM @ 17.01.2012. 19:46 ] @
Citat:
lonelyrider_44: 
Code:

SELECT i.Ime, COUNT(DISTINCT d.Naziv) as BrGradova FROM Izvodjac i, Koncert k, Dvorana d
 WHERE k.JMBG = i.JMBG AND k.DID = d.DID
 GROUP BY i.Ime
 HAVING BrGradova > 10


Tabele možeš da spajaš i ovako:
Code:

SELECT i.Ime, COUNT(DISTINCT d.Naziv) as BrGradova 
FROM Izvodjac i INNER JOIN  Koncert k INNER JOIN Dvorana d 
  ON k.DID = d.DID
  ON k.JMBG = i.JMBG
GROUP BY i.Ime
HAVING BrGradova > 10


Uslove (ON delovi) navodiš u suprotnom redosledu od redosleda navođenja tabela

Znam ali mi je pomocu WHERE jednostavnije.

Citat:
Osim INNER JOIN-a, postoji i LEFT JOIN, potraži malo na netu da vidiš njegovu upotrebu.


I RIGHT JOIN i FULL OUTER JOIN.. znam kako se koriste, ali ne znam kako bi ih mogao primjeniti da pojednostavim ove upite.
[ vlada_vlada @ 17.01.2012. 20:10 ] @
Resenje za (b):

SELECT kid
FROM Ulaznica
GROUP BY kid
HAVING SUM(CASE WHEN tip = 'VIP' THEN 1 ELSE 0 END) > COUNT(*)/2

Uklonili smo 2 podupita.

Pod (j) neka ostane za razmisljanje jos malo.

Hint: Poslednji podupit NOT EXIST(...) moze da se eliminise.