03 - Sisqlhlp - Ispitni Zadaci 1

SQL Rešeni zadaci - Ispitni zadaci 1 Zadatak 01: Data je šema relacione baze podataka: Firma(SifF, Naziv, SifM) Mesto(SifM, Naziv) Posiljka(SifP, Tezina, SifF, SifMod, SifMdo) Kamion(SifK, RegBr, Nosivost) Sastaviti SQL skript kojim se za svaku kombinaciju odlaznog i dolaznog mesta pošiljki dobijaju kamioni (RegBr) čija je nosivost dovoljna za sve takve pošiljke. Rešenje: Prvo za svaki uređeni par (SifMod, SifMdo), (tj. par šifara gde prva šifra predstavlja šifru odlaznog, a druga šifru dolaznog mesta pošiljke) određujemo najveću težinu koja je neka pošiljka između ta dva mesta imala. Ove informacije dobijamo iz tabele Posiljka. CREATE VIEW AS SELECT FROM GROUP BY

MaxTezine P.SifMod, P.SifMdo, MAX(P.Tezina) AS MaxT Posiljka P P.SifMod, P.SifMdo;

Zatim vršimo spajanje dobijenog pogleda i tabele Kamion kako bi dobili traženi rezultat. Dakle torku iz MaxTezine će mo spojiti sa torkom iz tabele Kamion ukoliko je polje Kamion.Nosivost veće of MaxTezine.MaxT. Na taj način dobijamo torke (SifMod, SifMdo, RegBr), koje označavaju da kamion sa registarskim brojem RegBr može poslužiti za prenos svih pošiljki između ta dva mesta. CREATE VIEW Rezultat AS SELECT M.SifMod, M.SifMdo, K.RegBr FROM MaxTezine M, Kamion K WHERE M.MaxT <= K.Nosivost;

Zadatak 02: Data je šema relacione baze podataka iz Zadatka 01. Sastaviti SQL skript kojim se daju parovi pošiljki (šifre) za koje postoje bar dva kamiona koja mogu da ih prevezu. Rešenje: U tekstu zadatka se traži da nađemo parove šifara pošiljki (SifP1, SifP2), takve da postoje bar dva kamiona koja mogu da prenesu zajedno ove dve pošiljke. Drugim rečima ovi kamioni trebaju da imaju nosivosti veće ili jednake zbiru težina ovih pozajmica. Uzećemo da je šifra prve pozajmice manja od šifre druge, kako bi izbegli duplirane rezultate (npr. (Sifra1, Sifra2) i (Sifra2, Sifra1)), i rezultate gde je prva pozajmica u stvari i druga pozajmica. Ovaj problem se rešava sledećim upitom. SELECT P1.SifP, P2.SifP FROM Posiljka P1, Posiljka P2 WHERE P1.SifP < P2.SifP AND (SELECT COUNT(K.SifK) FROM Kamion K WHERE K.Nosivost >= P1.Tezina + P2.Tezina) >= 2;

SQL Rešeni zadaci - Ispitni zadaci 1 Zadatak 03: Data je šema relacione baze podataka iz Zadatka 01. Sastaviti SQL skript koji daje šifre i nazive firmi koje nemaju ni jednu pošiljku iz mesta u kome se nalaze. Rešenje: Traže se firme za koje ne postoji ni jedna pozajmica te firme čije je odlazno mesto upravo mesto u kome se ova firma nalazi. Prethodna rečenica se veoma lako prevodi u sledeći upit. SELECT F.SifF, F.Naziv FROM Firma F WHERE NOT EXISTS (SELECT P.SifMod FROM Posiljka P WHERE P.SifF = F.SifF AND P.SifMod = F.SifM);

Zadatak 04: Date su sledeće šeme relacija u okviru baze podataka jednog video kluba: Film(SifF, Naziv, Duzina, Aktivan) Kaseta(SifK, Duzina) Sadrzi(SifK, SifF) Clan(SifC, Ime) Drzi(SifK, SifC, Datum) Pozajmica(SifP, SifC, SifK, SifF, Datum, Dana) Sastaviti sekvencu SQL naredbi kojima se postiže da se kasete bez prometa (nisu pozajmljivane niti su kod članova) oslobađaju za presnimavanje, a filmovi bez prometa markiraju kao neaktivni (sa Aktivan = NULL). Rešenje: Prvo je potrebno naći kasete koje su bez prometa. To su kasete koji nisu trenutno držane a nisu ni pozajmljivane. Šifre takvih kaseta će biti sadržane u sledećem pogledu: CREATE VIEW K_BEZ_PROMETA AS SELECT K.SifK FROM Kaseta K WHERE NOT EXISTS ((SELECT FROM WHERE UNION (SELECT FROM WHERE

D.SifK Drzi D D.SifK = K.SifK) P.SifK Pozajmica P P.SifK = K.SifK));

Korišćenje operacije union u ovom slučaju je moglo biti zamenjeno korišćenjem dve klauzule not exists. Ipak, ovako je upit za nijansu kraći. U tekstu zadatka se kaže da treba osloboditi ovakve kasete za presnimavanje. Drugim rečima treba izbrisati odgovarajuće torke iz tabele Sadrzi. DELETE

SQL Rešeni zadaci - Ispitni zadaci 1 FROM WHERE

Sadrzi SifK IN (SELECT * FROM K_BEZ_PROMETA);

Sličnu stvar treba uraditi i sa neaktivnim filmovima. Samo je sada situacija nešto složenija. Film je bez prometa ukoliko nije nikada pozajmljivan, i ako ne postoji kaseta koja je trenutno kod nekog člana a sadrži dati film. Tabela Pozajmica sadrži informacije o pozajmljenom filmu, dok kod tabele Drzi, ovu informaciju možemo dobiti prirodnim spajanjem sa tabelom Sadrzi. CREATE VIEW F_BEZ_PROMETA AS SELECT F.SifF FROM Film F WHERE NOT EXISTS ((SELECT FROM WHERE UNION (SELECT FROM WHERE

S.SifF Drzi D, Sadrzi S D.SifK = S.SifK AND S.SifF = F.SifF) P.SifF Pozajmica P P.SifF = F.SifF));

Naravno, sada treba izmeniti tabelu Film tako da sadrži odgovarajuće informacije o filmovima bez prometa. UPDATE Film SET Aktivan = NULL WHERE SifF IN (SELECT * FROM F_BEZ_PROMETA);

Zadatak 05: Data je šema relacione baze podataka iz Zadatka 04. Sastaviti jednu ili više SQL naredbi kojima se dobijaju kombinacije po dve šifre različitih filmova koji mogu da se snime zajedno na kasetu najveće dužine. Rešenje: Zahtev ovog zadatka bi se mogao formulisati i na sledeći način: Naći sve parove šifara filmova takvih da je šifra prvog filma manja od šifre drugog filma (ovaj uslov obezbeđuje različitost šifara, i sprečava dupliranje rezultata npr. (SifF1, SifF2) i (SifF2, SifF1) ne bi smeli zajedno da se pojave) i da je njihova zbirna dužina manja ili jednaka od dužine najduže kasete. Sada je prilično lako napisati odgovarajući upit: SELECT F1.SifF, F2.SifF FROM Film F1, Film F2 WHERE F1.SifF < F2.SifF AND F1.Duzina + F2.Duzina <= (SELECT MAX(K.Duzina) FROM Kaseta K);

Napomena: U rešenjima navedenih zadataka je korišćena praksa da se za svaku korišćenu tabelu (relaciju) u upitu koristi određeni alias (nadimak, skraćenica); na primer Film F1, ovo nije uvek neophodno i može se u mnogim slučajevima izostaviti, ali je to jedan od stilova pisanja upita, koji je po nekima lakši za čitanje. Treba obratiti pažnju da većina sql vendora ne dozvoljava korišćenje aliasa u svojim implementacijama unutar update, delete i insert naredbi, iz razloga koji ovde neće biti objašnjavani. Zadatak 06:

SQL Rešeni zadaci - Ispitni zadaci 1 Data je šema relacione baze podataka iz Zadatka 04. Sastaviti SQL skript koji daje šifre i nazive filmova za koje nema ni jedne slobodne kasete (sve kasete su kod članova). Rešenje: Drugim rečima, treba naći šifru i naziv svakog filma za koji je broj kaseta na kojima se nalazi taj film jednak broju kaseta na kojima se nalazi taj film i koje su trenutno pozajmljene. Ovo je samo jedno od rešenja, pošto je očigledno da se radi o varijanti deljenja, mogli smo primeniti neku od preostalih SQL implementacija deljenja. SELECT F.SifF, F.Naziv FROM Film F WHERE (SELECT COUNT(S.SifK) FROM Sadrzi S WHERE S.SifF = F.SifF) = (SELECT COUNT(D.SifK) FROM Drzi D, Sadrzi S WHERE D.SifK = S.SifK AND S.SifF = F.SifF);

Vrednost prvog podupita je broj kaseta na kojima se dati film nalazi. Vrednost drugog podupita je broj kaseta na kojima se dati film nalazi a koje su trenutno kod članova. Treba napomenuti da je ovde pretpostavljeno da su prihvatljive i šifre i nazivi filmova koji se ne nalaze ni na jednoj kaseti (jer i za njih ne postoji slobodna kaseta). Da ovo nije slučaj (to jest da ovi filmovi nisu poželjni u rezultatu upita), jedna dodatna provera bi ih eliminisala i rešila problem. Zadatak 07: Data je šema relacione baze podataka: Oblast(SifO, Naziv) Naslov(SifN, Naziv, SifO) Autor(SifA, Ime, BrOb) JeAutor(SifA, SifN, Koji) Sastaviti jednu ili više SQL naredbi kojima se ažurira podatak BrOb u relaciji Autor, gde je BrOb broj različitih oblasti iz kojih je autor pisao naslove. Rešenje: Prvo će mo napraviti upit koji svakog autora (njegovu šifru) povezuje sa svim oblastima (njihovim šiframa) koje je on napisao. CREATE VIEW AS SELECT FROM WHERE

AUTOR_OBLAST DISTINCT J.SifA, N.SifO JeAutor J, Naslov N J.SifN = N.SifN;

Ove informacije dobijamo spajanjem tabela JeAutor i Naslov. Zatim dobijamo parove (SifA, SifO) što označava da je Autor sa šifrom SifA pisao iz oblasti SifO. Ključna reč distinct

SQL Rešeni zadaci - Ispitni zadaci 1 obezbeđuje da se ovakvi parovi ne ponavljaju bespotrebno. Zatim za svakog autora brojimo različite oblasti iz kojih je pisao. CREATE VIEW AS SELECT FROM GROUP BY

BROJ_OBLASTI AO.SifA, COUNT(AO.SifO) AS BrOb AUTOR_OBLAST AO AO.SifA;

Na kraju na odgovarajući način ažuriramo tabelu Autor. UPDATE Autor SET BrOb = (SELECT B.BrOb FROM BROJ_OBLASTI B WHERE B.SifA = Autor.SifA);

Zadatak 8: Data je šema relacione baze podataka iz Zadatka 07. Sastaviti SQL skript koji daje nazive oblasti iz kojih svi naslovi imaju samo jednog autora. Rešenje: Koristićemo pomoćni pogled koji sadrži one naslove koji imaju samo jednog autora. CREATE VIEW NASLOVI1 AS SELECT N.SifN FROM Naslov N WHERE (SELECT COUNT(J.SifA) FROM JeAutor J WHERE J.SifN = N.SifN) = 1;

Zatim tražimo oblasti iz kojih svi naslovi imaju samo jednog autora. Drugim rečima tražimo oblasti za koje ne postoji naslov iz te oblasti koji nema samo jednog autora. Sledeći pogled sadrži upit sa jednom od implementacija deljenja u SQL-u. Jednu smo već pomenuli (korišćenje dve count funkcije) koja je inače efikasnija. CREATE VIEW REZULTAT AS SELECT DISTINCT O.Naziv FROM Oblast O WHERE NOT EXISTS (SELECT N.SifN FROM Naslov N WHERE O.SifO = N.SifO AND NOT EXISTS (SELECT N1.SifN FROM NASLOVI1 N1 WHERE N1.SifN = N.SifN));

Zadatak 09: Data je šema relacione baze podataka iz Zadatka 07. Napisati SQL skript koji daje šifre autora koji su napisali više od jednog naslova. Rešenje: Upit je prilično jednostavan, tako da ga ne treba posebno objašnjavati. Grupišemo tabelu JeAutor

SQL Rešeni zadaci - Ispitni zadaci 1 po atributu SifA, ali prikazujemo samo one šifre autora koje su povezane sa više naslova. SELECT FROM GROUP BY HAVING

J.SifA JeAutor J J.SifA COUNT(J.SifN) > 1;

Zadatak 10: Data je šema relacione baze podataka: (Šema je slična šemi iz Zadatka 04, ali je zadatak sa drugog ispitnog roka.) Film(SifF, Naziv, Duzina) Kaseta(SifK, Duzina) Clan(SifC, Ime) Sadrzi(SifK, SifF) Pozajmica(SifP, SifK, SifF, SifC, Dana) Sastaviti jednu ili više SQL naredbi kojima se izvršava sledeće: sa jedne ili više kaseta koje imaju minimalni broj pozajmica ili broj pozajmica 0 briše se sadržaj. Rešenje: Pomoću sledećeg pogleda dobijamo broj pozajmica za svaku kasetu, ali treba obratiti pažnju na to da će se u ovom pogledu nalaziti samo šifre knjiga (i brojevi njihovih pozajmica) koje su pozajmljivane bar jedanput. CREATE VIEW AS SELECT FROM GROUP BY

BR_POZ(SifK, BrPoz) P.SifK, COUNT(P.SifP) Pozajmica P P.SifK;

Sada lako iz prethodnog pogleda možemo izdvojiti šifre onih kaseta koje su najmanje puta pozajmljivane. CREATE VIEW MIN_POZ(SifK) AS SELECT B.SifK FROM BR_POZ B WHERE B.BrPoz = (SELECT MIN(BrPoz) FROM BR_POZ);

Takođe nas zanimaju i šifre kaseta koje nisu ni jedanput pozajmljivane. Njih možemo dobiti tako što će mo pronaći sve šifre kaseta koje se ne nalaze u projekciji po atributu SifK tabele Pozajmica ili pogleda BR_POZ. CREATE VIEW NULA_POZ(SifK) AS SELECT K.SifK FROM Kaseta K WHERE K.SifK NOT IN (SELECT SifK FROM Pozajmica);

Jasno je da treba izbrisati sadržaj sa kaseta koje su minimalan broj puta bile pozajmljivane ili imaju nula pozajmica, tako da sada pravimo pogled koji sadrži šifre svih ovih kaseta. CREATE VIEW ZA_BRISANJE(SifK) AS (SELECT SifK FROM MIN_POZ) UNION

SQL Rešeni zadaci - Ispitni zadaci 1 (SELECT SifK FROM NULA_POZ);

Brisanje sadržaja sa neke kasete, predstavlja brisanje odgovarajuće torke iz tabele Sadrzi. Sledi brisanje sadržaja sa kaseta čije šifre smo dobili u pogledu ZA_BRISANJE. DELETE FROM Sadrzi WHERE SifK IN (SELECT * FROM ZA_BRISANJE);

Naravno isto rešenje je moglo biti dobijeno sa manje pogleda i u manje koraka, ali ovde priloženo rešenje je postepeno i jasnije. Zadatak 11: Data je šema relacione baze podataka iz Zadatka 10. Sastaviti jednu ili više SQL naredbi koje daju šifre i imena članova koji su pozajmljivali sve filmove koje je pozajmljivao član šifre 'MB01'. Rešenje: Sledećim upitom dobijamo sve šifre kaseta koje je pozajmljivao član sa šifrom 'MB01'. Ključna reč distinct nam obezbeđuje da se šifre ne ponavljaju u slučaju da je ovaj član pozajmljivao više puta neku kasetu. CREATE VIEW MB01_KASETE AS SELECT DISTINCT P.SifK FROM Pozajmica P WHERE P.SifC = 'MB01';

Zatim nalazimo samo pozajmice (u formatu (SifC, SifK)) onih knjiga koje je pozajmio i član sa datom šifrom. Ključna reč distinct se koristi ponovo u istu svrhu kako bi sprečili ponavljanje torki ukoliko je neki član više puta pozajmljivao istu kasetu. CREATE VIEW MB01_POZAJMICE AS SELECT DISTINCT P.SifC, P.SifK FROM Pozajmica P, MB01_KASETE MK WHERE P.SifK = MK.SifK;

Sledeći korak je dobijanje šifara članova koji su pozajmili sve knjige koje je pozajmio i član sa šifrom 'MB01'. CREATE VIEW AS SELECT FROM GROUP BY HAVING

TRAZENE_SIFRE MP.SifC MB01_POZAJMICE MP MP.SifC COUNT(MP.SifK) = (SELECT COUNT(SifK) FROM MB01_KASETE);

Na kraju, nalazimo i imena ovih članova. Ali vodimo računa o tome da se u toj listi ne nađe i član sa šifrom 'MB01'. CREATE VIEW TRAZENI_CLANOVI AS SELECT C.SifC, C.Ime

SQL Rešeni zadaci - Ispitni zadaci 1 FROM WHERE

Clan C, TRAZENE_SIFRE T C.SifC = T.SifC AND C.SifC <> 'MB01';

Treba nampomenuti, da nam u drugom pogledu nije bila neophodna informacija o šifri knjige. Da smo je izostavili, dobili bi smo pogled u kome se šifra člana X pojavljuje onoliko puta koliko je puta pozajmljivao knjige koje je takođe pozajmio i član sa šifrom 'MB01'. U sledećem upitu bi jednostavno prebrojili pojavljivanje svake od šifara članova. Ukoliko član sa šifrom 'MB01' nije pozajmljivao ni jednu knjigu upit neće dati ni jedan rezultat, što je svakako poželjna osobina. Zadatak 12: Data je šema relacione baze podataka iz Zadatka 10. Sastaviti SQL skript koji daje šifre i nazive filmova koji su sadržani na jednoj ili više kaseta a nisu pozajmljivani. Rešenje: Potrebno je naći šifre filmova koji su sadržani na bar jednoj kaseti, ali koji nisu pozajmljivani. CREATE VIEW TRAZENI_FILMOVI AS (SELECT S.SifF FROM Sadrzi S) EXCEPT (SELECT P.SifF FROM Pozajmica P);

Zatim je vrlo jednostavno naći i imena ovih filmova. SELECT F.SifF, F.Naziv FROM Film F WHERE F.SifF IN (SELECT * FROM TRAZENI_FILMOVI);

Napomena 1: U prethodnim zadacima je pretpostavljeno da se pod pozajmicama ne podrazumevaju pozajmice koje su trenutno u toku (iz tabele Drzi), već samo one pozajmice koje su završene (iz tabele Pozajmica). Napomena 2: Operacija Except nije implementirana u Microsoft SQL Server-u.

Molio bih vas da sve greške koje primetite u ovom dokumentu prijavite na email: [email protected] (bilo da su slovne, logičke, stilske, sintaksne,..., nejasnoće) kako bi napravili bolju verziju i olakšali posao kolegama. Ovo je probna verzija dokumenta! Zadatke rešio: U.V. - S.I. - E.T.F.