Primjer druge zadaće

Primjer se odnosi na demo bazu „fakultet“ koju poznajete s vježbi. Slijede četiri upita, za svaki od njih: naredba u SQL-u, opis što naredba vraća te opis kako ona radi.

 

 

Upit 1)

---------

 

SQL naredba:

SELECT OIB, CONCAT(SUBSTRING(IMEN, 1, 1), '. ', SUBSTRING(PREZIMEN, 1, 1), '.') 'Inicijali'

FROM NASTAVNIK ORDER BY OIB;

 

Što upit vraća: Vrijednosti OIB-a te inicijali svih nastavnika, uzlazno sortirano po vrijednosti OIB-a.

 

Kako upit radi: Izlazna tablica s dva stupca, OIB i Inicijali, formira se na sljedeći način. Za svaki redak tablice NASTAVNIK dodajemo po jedan novi redak u izlaznu tablicu. Vrijednost OIB u novom retku je vrijednost OIB u retku tablice NASTAVNIK. Vrijednost Inicijali računa se konkatenacijom sljedećih vrijednosti: prvo slovo stringa IMEN (podniz stringa IMEN koji počinje na indeksu 1 i duljina mu je 1), točka i razmak, prvo slovo stringa PREZIMEN, točka. Konačno, izlaznu tablicu sortiramo po vrijednosti OIB-a.

 

 

Upit 2)

---------

 

SQL naredba:

SELECT COUNT(*) Broj FROM UPISAO, PREDMET, NASTAVNIK

WHERE UPISAO.OCJENA = 5 AND UPISAO.SIFRA = PREDMET.SIFRA

AND PREDMET.OIB = NASTAVNIK.OIB AND NASTAVNIK.PREZIMEN = 'Goedel';

 

Što upit vraća: broj potpuno uspješno položenih ispita (tj. ocjenom 5) na kolegijima profesora Goedela.

 

Kako upit radi: Prvo formiramo pomoćnu tablicu T koja je Kartezijev produkt tablica UPISAO, PREDMET i NASTAVNIK. Potom kreiramo novu pomoćnu tablicu U u koju dodajemo točno one retke R tablice T koji ispunjavaju uvjete da je atribut OCJENA retka R jednaka 5, atribut SIFRA koji je potekao iz faktora UPISAO jednak atributu SIFRA koji je potekao iz faktora PREDMET, atribut OIB koji je potekao iz faktora PREDMET jednak atributu OIB koji je potekao iz faktora NASTAVNIK te konačno atribut PREZIMEN jednak 'Goedel'. Izlaznu tablicu čini jedan stupac, Broj, s jedinim retkom: broj redaka tablice U.

 

 

Upit 3)

---------

 

SQL naredba:

SELECT SEMESTAR, AVG(ECTS) 'Prosjecni bodovi' FROM PREDMET

WHERE EXISTS (SELECT * FROM UPISAO WHERE PREDMET.SIFRA = UPISAO.SIFRA)

GROUP BY SEMESTAR;

 

Što upit vraća: Za svaki semestar, oznaka semestra i prosječan broj ECTS bodova kolegija u tom semestru, ali bez kolegija koje nitko nije upisao.

 

Kako upit radi:  Za svaki redak R tablice PREDMET odabiremo hoćemo li ga dodati u pomoćnu tablicu T, ovisno o tome je li ispunjen uvjet EXISTS (SELECT * FROM UPISAO WHERE PREDMET.SIFRA = UPISAO.SIFRA). Provjeru uvjeta radimo gradnjom pomoćne tablice U. Tablica U sastoji se od redaka tablice UPISAO čija je vrijednost atributa SIFRA jednaka vrijednosti atributa SIFRA retka R. Ako i samo ako postoji barem jedan redak u tako formiranoj tablici U, redak R dodajemo u tablicu T.

Potom u tablici T grupiramo retke u skupine s obzirom na međusobno jednaku vrijednost atributa SEMESTAR.

Izlazna tablica s dva stupca, SEMESTAR i Inicijali, formira se na sljedeći način. Za svaku spomenutu skupinu u tablici T dodajemo po jedan redak u izlaznu tablicu. Vrijednost SEMESTAR tog retka jednaka je vrijednosti SEMESTAR redaka u skupini. Vrijednost 'Prosjecni bodovi' računa se kao aritmetička sredina svih redaka u skupini, ignorirajući eventualne retke s NULL vrijednostima.

 

Upit 4)

---------

 

SQL naredba:

SELECT YEAR(S1.DATR) Godina, COUNT(*) 'Broj odabira' FROM STUDENT S1, STUDENT S2

WHERE S1.JMBAG < S2.JMBAG AND YEAR(S1.DATR) = YEAR(S2.DATR)

GROUP BY YEAR(S1.DATR)

HAVING COUNT(*) > 2;

 

Što upit vraća: Za svaku godinu ispisuje broj mogućih odabira dvočlanih skupova studenata koji su oboje rođeni te godine, i to samo za godine za koje postoji barem dva moguća takva skupa.

 

Kako upit radi: Prvo formiramo pomoćnu tablicu T koja je Kartezijev produkt tablica STUDENT (alias S1) i STUDENT (alias S2). Potom kreiramo novu pomoćnu tablicu U u koju dodajemo točno one retke R tablice T koji ispunjavaju uvjete da je atribut JMBAG koji je potekao iz faktora S1 manji od atributa JMBAG koji je potekao iz faktora S2, a atribut DATR koji je potekao iz faktora S1 jednak atributu DATR koji je potekao iz faktora S2. Tako dobivena tablica U grupira se po skupinama ovisno o vrijednosti atributa DATR.  Izlaznu tablicu čine stupci Godina i 'Broj odabira', te ju formiramo tako da za svaku skupinu s barem tri retka dodajemo po jedan redak u izlaznu tablicu. Atribut Godina treba biti godina iz datuma DATR, a atribut 'Broj odabira' broj redaka u skupini.

 

 

Gornja zadaća ispunjava tražene uvjete na sljedeći način:

      barem tri upita moraju kombinirati podatke iz dviju ili više tablica: upiti 2, 3 i 4;

      barem jedan mora kombinirati podatke iz tri ili više tablica: upit 2;

      barem dva moraju sadržavati klauzulu GROUP BY: upiti 3 i 4;

      barem jedan mora sadržavati ugniježđene SELECT naredbe: upit 3.