04 - Sisqlhlp - Ispitni Zadaci 2

SQL Rešeni zadaci - Ispitni zadaci 2 Zadatak 13: Data je šema relacione baze podataka za proveru znanja učenika. Testiranje se vrši na osnovu pitanja za koja je ponuđeno po više odgovora od kojih je samo jedan tačan. Učenici polažu test tako što za pitanja koja znaju zaokružuju odgovor koji smatraju tačnim. Pitanje(SifP, Tekst, Poena) Odgovor(SifP, SifO, Tekst, Tacnost) Ucenik(SifU, Ime, Polozio) Zaokruzeno(SifU, SifP, SifO) Sastaviti jednu ili više SQL naredbi kojima se u tabeli Ucenik kolona Polozio postavlja na 'D' ako je učenik po osnovu zaokruženih odgovora dobio na testu više od 0.5 od maksimalnog broja poena, a 'N' ako to nije ostvario. Rešenje: Sledeći pogled sadrži šifre učenika koji su položili test. Unutar pogleda prvo spajamo sve četri tabele, tako da dobijamo samo one torke koje predstavljaju tačan odgovor studenta na neko pitanje. Ovi uslovi spajanja su navedeni u where klauzuli. Svaka tako dobijena torka nam daje informaciju da je neki učenik tačno odgovorio na neko pitanje i pritom osvojio određeni broj poena. Sad u unutar klauzule group by grupišemo dobijene torke po šifri učenika. A zatim izdvajamo klauzulom having samo one šifre učenika koji su dobili više od 50% od maksimalnog broja poena na testu. CREATE VIEW AS SELECT FROM WHERE

POLOZILI U.SifU Ucenik U, Zaokruzeno Z, Pitanje P, Odgovor O U.SifU = Z.SifU AND Z.SifP = P.SifP AND Z.SifO = O.SifO AND P.SifP = O.SifP AND O.Tacnost = 'Da' GROUP BY U.SifU HAVING SUM(P.Poena) > (SELECT 0.5*SUM(Poena) FROM Pitanje);

Sada je veoma jednostavno uneti tražene promene u tabelu Ucenik. UPDATE Ucenik SET Polozio = 'Da' WHERE SifU IN (SELECT * FROM POLOZILI); UPDATE Ucenik SET Polozio = 'Ne' WHERE SifU NOT IN (SELECT * FROM POLOZILI);

Zadatak 14: Data je šema relacione baze podataka iz Zadatka 13. Sastaviti jednu ili više SQL naredbi koje daju šifre i imena učenika koji su tačno odgovorili na sva pitanja. Rešenje:

SQL Rešeni zadaci - Ispitni zadaci 2 Pošto smo uradili prethodni zadatak, sada je odgovor prilično jednostavan. Šifre i imena ovih učenika dobijamo malom modifikacijom pogleda POLOZILI iz prethodnog zadatka. Razlike su: što će mo sada prikazivati i ime učenika i to što će mo sada proveravati da li je osvojen broj poena maksimalan, a ne veći od polovine maksimalnog kao što je to bio slučaj u prethodnom zadatku. CREATE VIEW AS SELECT FROM WHERE

URADILI_SVE U.SifU, U.Ime Ucenik U, Zaokruzeno Z, Pitanje P, Odgovor O U.SifU = Z.SifU AND Z.SifP = P.SifP AND Z.SifO = O.SifO AND P.SifP = O.SifP AND O.Tacnost = 'Da' GROUP BY U.SifU, U.Ime HAVING SUM(P.Poena) = (SELECT SUM(Poena) FROM Pitanje);

Treba primetiti, da smo morali da dodamo još jedan atribut u group by klauzulu, koji inače nije neophodan, ali jeste u ovom slučaju. To je zato što se svaki atribut iz select klauzule mora nalaziti i u group by klauzuli. Naravno problem je mogao biti rešen i na nekoliko drugačijih načina jer se radi o deljenju. Zadatak 15: Data je šema relacione baze podataka iz Zadatka 13. Sastaviti jednu ili više SQL naredbi kojima se daju šifre pitanja na koje niko nije tačno odgovorio. Rešenje: Prvo će mo naći sva pitanja (to jest njihove šifre) na koja je bar jednom tačno odgovoreno. CREATE VIEW ODGOVORENA_PITANJA AS SELECT O.SifP FROM Zaokruzeno Z, Odgovor O WHERE Z.SifP = O.SifP AND Z.SifO = O.SifO AND O.Tacnost = 'Da';

Pitanja na koje nije odgovoreno (to jest njihove šifre) dobijamo tako što od skupa pitanja oduzmemo skup rešenih pitanja. CREATE VIEW NEODGOVORENA_PITANJA AS (SELECT P.SifP FROM Pitanje P) EXCEPT (SELECT * FROM RESENA_PITANJA);

Na ovaj način dobijamo tražene šifre na veoma efikasan način. Napomena: Operacija oduzimanja skupova (Except, tj. Minus) nije moguća u mnogim implementacijama SQL-a. Zadatak 16:

SQL Rešeni zadaci - Ispitni zadaci 2 Data je šema relacione baze podataka: Student(SifS, Ime, Godina) Predmet(SifP, Naziv, Godina) Nastavnik(SifN, Ime) Ispit(SifS, SifP, Rok, Ocena, SifN) Ako sa Godina = 0 označavamo da je student završio studije, sastaviti SQL skript kojim se kao završeni označavaju svi studenti koji su položili sve ispite. Rešenje: Prvi korak u rešavanju ovog problema je naravno nalaženje šifara studenata koji su položili i nad kojima je neophodno izvršiti zadatu promenu. Studenti koji su završili fakultet su oni studenti za koje je broj položenih ispita iz različitih predmeta jednak broju predmeta. Ovde posebno naglašavamo reč različitih, jer je moguće da neki student više puta položi isti predmet (na primer ako nije zadovoljan ocenom), pa to ne treba računati kao da je položio više predmeta (obratiti pažnju na reč distinct u sledećem pogledu). CREATE VIEW ZAVRSILI AS SELECT S.SifS FROM Student S WHERE (SELECT COUNT(P.SifP) FROM Predmet P) = (SELECT COUNT(DISTINCT I.SifP) FROM Ispit I WHERE I.SifS = S.SifS AND I.Ocena > 5);

Pošto sada imamo šifre studenata koji su završili fakultet, nije teško izvršiti traženo ažuriranje tabele Student. UPDATE Student SET Godina = 0 WHERE SifS IN (SELECT * FROM ZAVRSILI);

Zadatak 17: Data je šema relacione baze podataka iz Zadatka 16. Sastaviti SQL skript koji daje imena studenata koji su sve položene ispite iz prethodne godine položili iz prvog puta. Rešenje: Sledi prilično složen upit koji se najlakše može objasniti ako se student koji zadovoljava uslov zadatka okarakteriše običnim jezikom kao: Student za koga ne postoji izlazak na ispit iz predmeta iz prethodne godine koji je položio a za koji postoji drugi izlazak na ispit iz istog predmeta na ranijem roku ali da je tada student pao. Prilično konfuzna rečenica, ali često se do rešenja na SQLu dolazi lakše tako što se rečenica prvo dobro definiše na govornom jeziku. SELECT S.SifS, S.Ime FROM Student S WHERE NOT EXISTS (SELECT *

SQL Rešeni zadaci - Ispitni zadaci 2 FROM WHERE

Predmet P, Ispit I1 I1.SifS = S.SifS AND P.SifP = I1.SifP AND P.Godina = S.Godina-1 AND OCENA > 5 AND EXISTS(SELECT * FROM Ispit I2 WHERE I2.SifS = S.SifS AND I2.SifP = P.SifP AND I2.Rok < I1.Rok AND I2.Ocena = 5));

Pored imena studenta koje se u tekstu zadatka traži, ovde prikazujemo i šifru, kako bi u slučaju da postoji više studenata sa istim imenom rešenje bilo jasnije. Zadatak 18: Data je šema relacione baze podataka iz Zadatka 16. Sastaviti SQL skript koji daje imena studenata koji su izlazili na samo jedan ispit. Rešenje: Rešenje je prilično jednostavno, imena studenata čija se šifra pojavljuje u samo jednoj torki tabele Ispit. SELECT S.SifS, S.Ime FROM Student S WHERE S.SifS IN (SELECT FROM GROUP BY HAVING

I.SifS Ispit I I.SifS COUNT(*) = 1);

Zadatak 19: Data je šema relacione baze podataka fudbalskog saveza za potrebe evidencije utakmica jedne sezone (pretpostavka je da fudbaleri ne mogu da menjaju tim u kome igraju, u toku sezone): Fudbaler(SifF, Ime, SifT) Tim(SifT, Naziv, Mesto) Utakmica(SifU, SifTDomaci, SifTGost, Kolo, Ishod, Godina) Igrao(SifF, SifU, PozicijaIgraca) Gol(SifG, SifU, SifF, RedniBrGola, Minut) Karton(SifK, SifU, SifF, Tip, Minut) Sastaviti SQL skript kojim se formita tabela Utakmica, ukoliko je poznato da utakmicu igraju dva različita tima čije se šifre nalaze u tabeli Tim, da ishod utakmice može biti iz skupa vrednosti {Xnerešeno, 1-pobeda domaćih, 2-pobeda gostiju} i da postoji svega 30 kola u kojima se utakmice igraju. U toku sezone svaki par timova odigra dve utakmice, pri čemu je jedna na domaćem, a druga na gostujućem terenu. Rešenje: Pri kreiranju ove tabele zabranili smo NULL vrednost u smim kolonama(atributima) što je u većini slučajeva dobra praksa, jer one često izazivaju neočekivane rezultate. U tabeli imamo dva strana ključa, to su SifTDomaci i SifTGost. Dalje važnija ograničenja su: dozvoljeni brojevi kola

SQL Rešeni zadaci - Ispitni zadaci 2 (od 1 do 30), dozvoljene vrednosti atributa Ishod, razičitost timova koji igraju i ograničenje da dva tima mogu odigrati samo dve utakmice u toku jedne sezone. CREATE TABLE Utakmica( SifU INT PRIMARY KEY, SifTDomaci INT NOT NULL REFERENCES Tim(SifT) ON UPDATE NO ACTION ON DELETE NO ACTION, SifTGost INT NOT NULL REFERENCES Tim(SifT) ON UPDATE NO ACTION ON DELETE NO ACTION, Kolo INT NOT NULL CHECK(Kolo BETWEEN 1 AND 30), Ishod CHAR(1) NOT NULL CHECK(Ishod IN ('X', '1', '2')), Godina INT NOT NULL, CONSTRAINT RazlicitiTimovi CHECK(SifTDomaci <> SifTGost), UNIQUE(SifTDomaci, SifTGost), );

Kod stranih ključeva zabranili smo brisanje i modifikaciju primarnog ključa u tabeli Tim, dok postoje odgovarajuće utakmice tog tima u tabeli Utakmica. Naravno ovaj deo naredbe bi mogao da bude drugačiji u zavisnosti od konkretne situacije. U ovom slučaju to nije preterano važno. Zadatak 20: Data je šema relacione baze podataka iz Zadatka 19. Sastavit SQL skript koji na osnovu postojećih tabela najpre kreira novu tabelu Statistika, a zatim je popunjava tako što za svaki tim iz tabele Tim upisuje ukupan broj odigranih utakmica, ukupan broj datih golova i dobijenih kartona. Rešenje: Najlakši način da rešimo ovaj problem je umetanje tri korelisana upita unutar glavnog upita, koji će nam tražiti ukupan broj utakmica, golova i kartona redom za svaki tim. Nije baš uobičajeno ugnježdavanje upita unutar same select klauzule, međutim u ovom slučaju je to sasvim opravdano. Ipak prvo treba kreirati tabelu Statistika. CREATE TABLE Statistika( SifT INT PRIMARY KEY REFERENCES Tim(SifT) ON UPDATE CASCADE ON DELETE CASCADE, Naziv CHAR(30) NOT NULL, BrUtakmica INT NOT NULL, BrGolova INT NOT NULL, BrKartona INT NOT NULL );

A zatim je popunjavamo odgovarajućim podacima. INSERT INTO Statistika(SifT, Naziv, BrUtakmica, BrGolova, BrKartona) SELECT T.SifT, T.Naziv, (SELECT COUNT(*) FROM Utakmica U WHERE U.SifTDomaci = T.SifT OR U.SifTGost = T.SifT), (SELECT COUNT(*)

SQL Rešeni zadaci - Ispitni zadaci 2

FROM

FROM WHERE (SELECT FROM WHERE Tim T;

Fudbaler F.SifT = COUNT(*) Fudbaler F.SifT =

F, Gol G T.SifT AND F.SifF = G.SifF), F, Karton K T.SifT AND F.SifF = K.SifF)

Drugi, efikasniji, ali i dosta duži način rešavanja ovog problema je da napravimo tri pogleda koji će sadržati statistiku za sve timove. Prvi će sadržati statistiku utakmica, drugi golova, treći kartona. Upiti tih pogleda će biti slični ugnježdenim upitima unutar prikazanog upita, međutim oni će sadržati još i group by klauzulu. Dalje bi za svaki od datih pogleda morali da napravimo još po jedan pogled koji sadrži još i timove koji imaju 0 utakmica (prvi pogled), 0 golova (drugi pogled) i 0 kartona (treći pogled). Zatim bi ova tri pogleda prirodno spojili i dobili tabelu statistika

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.