SQL pitanja iz rokova za FON

SQL pitanja iz rokova za FON 06.05.2008. u 23:13 · pre 225 meseci #1

Spremam ispit iz Baza Podataka na FONu i sve ide OK osim SQL pitanja a to me u stvari najvise i interesuje.

Dosta se mucim sa ovim zadatkom a ne mogu nikako da proverim da li je tacno. Probao sam da napravim bazu po modelu ali slaba vajda. Ako neko ima vremena da baci pogled samo da mi kaze jel ovo sto ja kucam ima smisla ili sam nacisto promasio...

Posebno sam se namucio pod c) ako moze neki komentar.

Hvala unapred sledi zadatak i ovo sto sam ja uradio.

Data je relaciona shema:

SEZONA ( NAZIV_SEZONE , DATOD , DATDO)
PREDSTAVA (PREDID, NAZIV, TIP)
REPERTOAR (PREDID, DATUMIVREME, STATUS)
GLUMAC (GLUMID, IME, PREZIME)
POSTAVA (PREDID, GLUMID, NAZIV_SEZONE, ULOGA)

Vrednosti atributa STATUS mogu biti “PLANIRANA”, “ODIGRANA” ili ”OTKAZANA”.

Napisati SQL:1999 naredbe kojima se:

a) Prikazati sve glumce koji u sezoni “2006/2007” nisu igrali u predstavi “Mnogo buke ni oko čega”.
(8 poena)

b) Prikazati nazive svih predstava koje su u sezoni “2005/2006” odigrane tačno jedanput.
(8 poena)

c) Kreirati pogled kojim se prikazuje NAZIV PREDSTAVE, UKUPAN BROJ ODIGRANIH PREDSTAVA,
UKUPAN BROJ OTKAZANIH PREDSTAVA koje su najviše puta odigrane do tekućeg datuma, a procenat
otkazanih u odnosu na ukupan broj predstava je manji od 10 procenata.
(8 poena)

7.
a) SELECT IME, PREZIME FROM GLUMAC WHERE GLUMID IN(SELECT DISTINCT GLUMID FROM POSTAVA WHERE NAZIV_SEZONE=’2006/2007’ AND PRED ID <> (SELECT PREDID FROM PREDSTAVA WHERE NAZIV = ’Mnogo buke ni oko cega’))

b) SELECT NAZIV FROM PREDSTAVA WHERE PREDID IN (SELECT PREDID FROM (SELECT COUNT(*) AS BROJ_ODIGRANIH_2005, PREDID FROM (SELECT PREDID FROM REPERTOAR WHERE DATUMIVREME BETWEEN (SELECT DATOD FROM SEZONA WHERE NAZIV_SEZONE=’2005/2006’) AND (SELECT DATDO FROM SEZONA WHERE NAZIV_SEZONE=’2005/2006’) AND STATUS = ’ODIGRANA’) GROUP BY PREDID) WHERE BROJ_ODIGRANIH_2005 = 1)

c) Jel ovaj pogled treba da prikaze samo jednu predstavu koja je najviše puta odigrana a da joj je procenat otkazanih u odnosu na ukupan broj predstava manji od 10%?

CREATE VIEW UKUPNO (PREDID, UKUPNO_DO_DANAS) AS SELECT PREDID, COUNT(*) FROM REPERTOAR WHERE DATUMIVREME < LOCALTIMESTAMP GROUP BY PREDID

DOBIJEMO
PREDID UKUPNO_DO_DANAS
1322 98
2162 65
… …

CREATE VIEW ODIGRANE (PREDID, ODIGRANE_DO_DANAS) AS SELECT PREDID, COUNT(*) FROM REPERTOAR WHERE DATUMIVREME < LOCALTIMESTAMP AND STATUS = ‘ODIGRANA’ GROUP BY PREDID

DOBIJEMO
PREDID ODIGRANE_DO_DANAS
1322 90
2162 45
… …

CREATE VIEW OTKAZANE (PREDID, OTKAZANE_DO_DANAS) AS SELECT PREDID, COUNT(*) FROM REPERTOAR WHERE DATUMIVREME < LOCALTIMESTAMP AND STATUS = ‘OTKAZANA’ GROUP BY PREDID

DOBIJEMO
PREDID OTKAZANE_DO_DANAS
1322 8
2162 20
… …

CREATE VIEW SUMARNO AS SELECT * FROM UKUPNO, ODIGRANE, OTKAZANE (TREBA LI DA DODAM USING(PREDID))

DOBIJEMO
PREDID UKUPNO_DO_DANAS ODIGRANE_DO_DANAS OTKAZANE_DO_DANAS
1322 98 90 8
2162 65 45 20
… … … …

CREATE VIEW TOP_PREDSTAVE (NAZIV_PREDSTAVE, UKUPAN_BROJ_ODIGRANIH_PREDSTAVA, UKUPAN_BROJ_OTKAZANIH_PREDSTAVA) AS SELECT NAZIV, ODIGRANE_DO_DANAS, OTKAZANE_DO_DANAS FROM PREDSTAVA JOIN SUMARNO WHERE (OTKAZANE_DO_DANAS/ UKUPNO_DO_DANAS)<0.10

CREATE VIEW TOP_PREDSTAVA AS SELECT * FROM TOP_PREDSTAVE WHERE UKUPAN_BROJ_ODIGRANIH_PREDSTAVA = MAX(UKUPAN_BROJ_ODIGRANIH_PREDSTAVA)

Re: SQL pitanja iz rokova za FON 08.05.2008. u 15:07 · pre 225 meseci #2

Posto spremas ispit, imas raxcuna da kreiras sebi tabelu i unses neke test podatke. Onda zakaci skripte za kreiranje tabela i test podataka uz poruku pa cemo pokusati da nesto uradimo.

Re: SQL pitanja iz rokova za FON 08.05.2008. u 15:29 · pre 225 meseci #3

Preporucujem da malo formatiras izraze kad psies SQL, lakse se cita i lakse se nalaze greske. Evo lepi sajt gde ima bnesplaatyn 'Code Formatter':
http://www.wangz.net/cgi-bin/pp/gsqlparser/sqlpp/sqlformat.tpl

Evo kako bi mogao da izgleda odgovor na prvi zadatak. Resicemo ga metodom negacije. Ako pronadjemo sve glumce koji JESU IGRALI u trazenoj predstavi, lako je onda prikazati iz tabele GLUMAC samo one koji ne pripadaju skupu onih koji jesu igrali. Napominjem da predsave koje su 'Palnirane' a nisu odigrane, necemo uzeti u obzir. Isto tako, predstave koje su otkazane se ne broje.

Code:

-- Ovo pokazuje sve odigrane predstave 'Mnogo buke ni oko čega'
-- Query1:
SELECT
    R.PredID
    , R.DatumVreme
    , PR.Naziv
FROM REPERTOAR AS R
JOIN PREDSTAVA AS PR ON PR.PredID = R.PredID
JOIN SEZONA AS S ON R.DatumVreme BETWEEN S.DatOd AND S.DatDo
WHERE PR.Naziv = 'Mnogo buke ni oko čega'
AND R.Status = 'ODIGRANA'    -- ne zelimo predstave koje su 'Otkazana' ili 'Planirana'
AND S.Sezone = '2005/2006'

Sad dodamo Postavu u igru, da vidimo koji glummci su zaista IGARLI predstavu 'Mnogo buke ni oko čega' u sezoni '2005/2006'

Code:

--Query2:
SELECT
-- Posto na s interesuju samo Glumci, mozemo da komentujemo ostale kolone
--    R.PredID
--    , R.DatumVreme
--    , PR.Naziv
     GlumID
FROM REPERTOAR AS R
JOIN PREDSTAVA AS PR ON PR.PredID = R.PredID
JOIN SEZONA AS S ON R.DatumVreme BETWEEN S.DatOd AND S.DatDo
JOIN POSTAVA AS PO ON PO.predID = PR.PredID    -- NOVI RED, sve ostalo isto kao prethodni Query1
WHERE PR.Naziv = 'Mnogo buke ni oko čega'
AND R.Status = 'ODIGRANA'
AND S.Sezone = '2005/2006'

Koji glumci NISU igrali u predstavu 'Mnogo buke ni oko čega' u sezoni '2005/2006'?
Pa svi oni koje ne vrace Query2:

Code:

--Query3:
SELECT G.GlumID, G.Ime, G.Prezime
FROM GLUMAC AS G
WHERE GlumID NOT IN
-- ovo sto sledi u zagradi je u stvari Query2:
(
SELECT
GlumID
FROM REPERTOAR AS R
JOIN PREDSTAVA AS PR ON PR.PredID = R.PredID
JOIN SEZONA AS S ON R.DatumVreme BETWEEN S.DatOd AND S.DatDo
JOIN POSTAVA AS PO ON PO.predID = PR.PredID
WHERE PR.Naziv = 'Mnogo buke ni oko čega'
AND R.Status = 'ODIGRANA'
AND S.Sezone = '2005/2006'
)

-- Moze i LEFT JOIN umesto NOT IN

Query3 je odgovor na zadatak 1.

P.S. Jos jedan dobar alat za formatiranje SQL izkza - zakacio sam fajl

SQLinForm_2008.jar82.29k

Re: SQL pitanja iz rokova za FON 08.05.2008. u 15:45 · pre 225 meseci #4

b) Prikazati NAzive svih predstava koje su u sezoni “2005/2006” odigrane tačno jedanput.
(8 poena)

Evo ih sve predstave koje su zaista odigrane u sezoni 2005/2006:

Code:

-- Query 1: sve predstave koje su odigrane u sezoni “2005/2006”
-- Ovo je prakticno isti kveri kao u zdatku 1, samo je izbaceno WHERE Predstava = '...'
SELECT
    R.PredID
    , R.DatumVreme
    , PR.Naziv
FROM REPERTOAR AS R
JOIN PREDSTAVA AS PR ON PR.PredID = R.PredID
JOIN SEZONA AS S ON R.DatumVreme BETWEEN S.DatOd AND S.DatDo
WHERE R.Status = 'ODIGRANA'    -- ne zelimo predstave koje su 'Otkazana' ili 'Planirana'
AND S.Sezone = '2005/2006'

Kako da ih prebrojimo? PA, pomocu GROUP BY i HAVING, ocigledno

Code:

SELECT
    R.PredID
    , R.DatumVreme
    , MIN(PR.Naziv) AS Predstava    -- izmena
    , COUNT(*)                        -- novi red
FROM REPERTOAR AS R
JOIN PREDSTAVA AS PR ON PR.PredID = R.PredID
JOIN SEZONA AS S ON R.DatumVreme BETWEEN S.DatOd AND S.DatDo
WHERE R.Status = 'ODIGRANA'    -- ne zelimo predstave koje su 'Otkazana' ili 'Planirana'
AND S.Sezone = '2005/2006'
-- ovo dole je dodato, GROUP BY i HAVING:
GROUP BY
    R.PredID
    , R.DatumVreme
HAVING COUNT(*) = 1

Za treci zadatak, videcemo verovatno suta, dsad moram malo i da radim, da me ne otpuste

Re: SQL pitanja iz rokova za FON 13.05.2008. u 01:37 · pre 224 meseca #5

Hvala ti veliko na ulozenom trudu.

Par dana nije bilo odgovora mislio sam da nece ni biti zbog konfuzije. Da li ti se cini da su zahtevi strasno lose formulisani? Sad sam vec zasao malo u problrmatiku a i dalje se nevidjeno mucim da shvatim sta se od mene trazi...

Sto se tice kreiranja tabela (i pnjenja naravno) probao sam to da uradim ali nema vajde. Strasno vremena gubim. Najbrze bi mi bilo u Accessu ali izgleda da u njemu ne mogu da izvrsavam SQL naredbe. Probao sam da napravim u MySQLu ali se tu sintaksa razlikuje a na ispitu se trazi cist SQL, a i ove tabele treba dobrano napuniti da bi sve to vredelo...

Sto se tice formatiranja slazem se da bi morao nesto da uradim po tom pitanju ako zelim da mi bilo ko pomogne, jer ovako treba covek samo sat vremena da izgubi dok shvati sta sam ja i kako hteo. Meni su podjednako, ako ne i vazniji, komentari na moja resenja i njihovu tacnost nego resenja. Program za formatiranje ne mogu da pokrenem jer mi se jar otvara u instaleru za Nokiju a kad pokrenem klasu sa main metodom dobijam exception kome ne mogu nista. Probacu da pratim formatiranja iz ovog sto si gore napisao...

Evo zadatka iz sledeceg roka, mislim da mi resenja ovde imaju nekog smisla pa bih zamolio za komentar, posebno pod b. Pod c nemam snage veceras da radim :(

Citat:

Data je relaciona shema:
SPORTSKO_DRUSTVO (SifraDrustva, Naziv, Adresa, DatumOsnivanja )
KLUB (SifraKluba, Naziv, Adresa, DatumOsnivanja, SifraDrustva)
TRENER (SifraTrenera, Ime, Prezime, DatumZaposlenja, MaticniKlub)
ANGAZOVANJE (SifraKluba, SifraTrenera , Datum, BrojSati )
ISPLATA ( SifraIsplate, Datum, Iznos, SifraKluba, SifraTrenera)
Napisati SQL:1999 naredbe kojima se:
a) Prikazuje Prezime, Ime, GodineStaza, Naziv kluba za one trenere koji su zaposleni u sportskom društvu sa
nazivom «Atleta» u poslednjih 5 godina. Rezultat sortirati u opadajućem redosledu godina staža i rastućem
prezimena.
(8 poena)
b) Prikazuje za sva sportska društva klubove koji imaju broj zaposlenih trenera izmedu 5 i 10, i koji su bili
angažovani samo u matičnom klubu.
(8 poena)
c) Kreira pogled HONORARI(Prezime, Ime, BrojKlubova, Mesec, UkupnaMesecnaIsplata) kojim se prikazuju
mesečne isplate trenerima u prethodnoj godini koji su angažovani van matičnog kluba. Kolona BrojKlubova
prikazuje broj različitih klubova koji su isplatili honorar treneru u datom mesecu.
(8 poena)

Moja resenja:

a)

Code:

SELECT
    T.Prezime,
    T.Ime,
    (CURRENT_DATE – T.DatumZaposlenja) YEAR AS GodineStaza,
    K.Naziv AS NazivKluba
FROM TRENER AS T
JOIN KLUB AS K ON T.MaticniKlub = K.SifraKluba
JOIN SPORTSKO_DRUSTVO AS SD ON K.SifraDrustva = SD.SifraDrustva
WHERE SD.Naziv = “Atleta”
AND ((CURRENT_DATE – T.DatumZaposlenja) YEAR)<=5
ORDER BY GodineStaza DESC, T.Prezime ASC;

b)

Code:

SELECT
K.Naziv
FROM KLUB AS K
JOIN ANGAZOVANJE AS A ON K.SifraKluba = A.SifraKluba
WHERE K.SifraKluba = ALL
(
SELECT
A.SifraKluba
FROM ANGAZOVANJE AS A
JOIN TRENER AS T ON A.SifraTrenera = T.SifraTrenera
WHERE T.MaticniKlub = K.SifraKluba
)
GROUP BY K.SifraKluba
HAVING COUNT(*) BETWEEN 5 AND 10

Re: SQL pitanja iz rokova za FON 13.05.2008. u 03:18 · pre 224 meseca #6

Citat:

ali se tu sintaksa razlikuje a na ispitu se trazi cist SQL, a i ove tabele treba dobrano napuniti da bi sve to vredelo...

kolega, probajte sa PostgreSQL, tu radi cist SQL :) /to saznah kasno.../

Potpis

Žena u krevetu i vino na stolu nikako ne smeju da čekaju. Jer, vino se greje a žena hladi.

-vinolog

Re: SQL pitanja iz rokova za FON 13.05.2008. u 12:31 · pre 224 meseca #7

Hvala kolega, sutra je ispit ali posto cu verovatno da okinem za dalje cu razmotriti!

Dosao sam u sledecu situaciju:

Nekom selekcijom dobijem sifre trenera koje mi odgovaraju, i sad treba da vidim u koji klubovima svi treneri su u tom skupu. Znaci treba mi nesto kao IN ali ne vrednost u skupu nego skup u skupu. Sta da radim ovde?Da pravim razliku pa sa exist da vidim da li postoji?

Re: SQL pitanja iz rokova za FON 13.05.2008. u 13:17 · pre 224 meseca #8

Bogami dosta sam brzo uradio ovaj pod c)

Code:

CREATE VIEW HONORARI (Prezime, Ime, BrojKlubova, Mesec, UkupnaMesecnaIsplata) AS
SELECT
    T.Prezime,
    T.Ime,
    COUNT(*),
    EXTRACT(MONTH FROM DATE I.Datum) AS MESEC,
    SUM(I.Iznos)
FROM TRENER AS T
JOIN ISPLATA AS I ON T.SifraTrenera = I.SifraTrenera
WHERE T.MaticniKlub <> I.SifraKluba
AND EXTRACT(YEAR FROM DATE I.Datum) = EXTRACT(YEAR FROM DATE (CURRENT_DATE)) – 1
GROUP BY T.SifraTrenera, MESEC

Forum kao aplikacija na telefonu

Srodne teme