Primjer izvještaja o drugoj zadaći

Osnovne informacije

Ime baze: fakultet

Kratak opis baze: pohranjuju se podaci o studentima i nastavnicima nekog fakulteta te o predmetima koje oni upisuju odnosno predaju.

Popis tablica: STUDENT, NASTAVNIK, PREDMET, UPISAO.

Sadržaj tablica

STUDENT

+------------+----------+---------+------------+--------+

| JMBAG | PREZIMES | IMES | DATR | GODINA |

+------------+----------+---------+------------+--------+

| 36398757 | Markovic | Marko | 2001-09-05 | 1 |

| 1191203289 | Petrovic | Petar | 2000-11-05 | 2 |

| 1192130031 | Horvat | Dragica | 2000-05-12 | 2 |

| 1191205897 | Jankovic | Marija | 2002-01-01 | 1 |

| 165043021 | Kolar | Ivan | 2000-02-27 | 3 |

| 36448430 | Grubisic | Katica | 1999-09-11 | 3 |

| 246022858 | Vukovic | Janko | 2002-09-01 | 1 |

| 246022859 | Tokic | Slavko | 1999-02-04 | 4 |

| 246022875 | Vukovic | Mirko | 1998-07-21 | 4 |

| 246022888 | Puskarec | Janja | 1996-07-29 | 5 |

+------------+----------+---------+------------+--------+

NASTAVNIK

+-------------+----------+--------+--------+-------+

| OIB | PREZIMEN | IMEN | BRSOBE | PLACA |

+-------------+----------+--------+--------+-------+

| 13257600947 | Cantor | Georg | 102 | 12000 |

| 25810043761 | Goedel | Kurt | 305 | 6000 |

| 33571209458 | Codd | Edgar | 127 | 8000 |

| 44102179316 | Klein | Felix | 252 | 12000 |

| 50076128203 | Pascal | Blaise | 101 | 11000 |

| 67741205512 | Turing | Alan | 315 | 10000 |

| 67554120551 | Levinov | Boris | 315 | 9000 |

| 34571209458 | Lovelace | Ada | 127 | 13000 |

+-------------+----------+--------+--------+-------+

PREDMET

+-------+-----------------------+-------------+----------+------+

| SIFRA | NASLOV | OIB | SEMESTAR | ECTS |

+-------+-----------------------+-------------+----------+------+

| 56001 | Baze podataka | 33571209458 | L | 5 |

| 72001 | Linearna algebra | 44102179316 | Z | 6 |

| 72005 | Matematicka analiza | 25810043761 | L | 6 |

| 56002 | Programiranje u C-u | 67741205512 | Z | 5 |

| 72009 | Analiticka geometrija | 44102179316 | L | 5 |

| 56000 | Programiranje | 44102179316 | L | 5 |

+-------+-----------------------+-------------+----------+------+

UPISAO

+------------+-------+------------+--------+

| JMBAG | SIFRA | DATU | OCJENA |

+------------+-------+------------+--------+

| 36398757 | 72001 | 2020-09-05 | NULL |

| 1191203289 | 56001 | 2019-10-03 | 3 |

| 1191203289 | 56002 | 2019-10-03 | 2 |

| 1192130031 | 72001 | 2019-09-15 | 5 |

| 1192130031 | 56002 | 2019-09-15 | 4 |

| 1192130031 | 72009 | 2019-09-15 | 2 |

| 1191205897 | 72009 | 2020-09-05 | NULL |

| 165043021 | 56001 | 2018-10-03 | 3 |

| 165043021 | 72001 | 2018-10-03 | 4 |

| 165043021 | 56002 | 2019-06-10 | NULL |

| 36448430 | 56001 | 2019-09-04 | 5 |

| 246022858 | 56001 | 2020-09-03 | NULL |

| 246022858 | 72001 | 2020-09-03 | NULL |

| 246022858 | 56002 | 2020-09-25 | NULL |

| 246022858 | 72009 | 2020-09-25 | NULL |

| 246022888 | 72009 | 2020-09-25 | 5 |

| 1191203289 | 72001 | 2017-10-01 | 3 |

| 165043021 | 56000 | 2019-06-10 | NULL |

+------------+-------+------------+--------+

Upit 1

SQL naredba:

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

FROM NASTAVNIK ORDER BY OIB;

Rezultat izvođenja naredbe:

+-------------+-----------+

| OIB | Inicijali |

+-------------+-----------+

| 13257600947 | G. C. |

| 25810043761 | K. G. |

| 33571209458 | E. C. |

| 34571209458 | A. L. |

| 44102179316 | F. K. |

| 50076128203 | B. P. |

| 67554120551 | B. L. |

| 67741205512 | A. T. |

+-------------+-----------+

Š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';

Rezultat izvođenja naredbe:

+------+

| Broj |

+------+

| 0 |

+------+

Š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;

Rezultat izvođenja naredbe:

+----------+------------------+

| SEMESTAR | Prosjecni bodovi |

+----------+------------------+

| Z | 5.5000 |

| L | 5.0000 |

+----------+------------------+

Š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;

Rezultat izvođenja naredbe:

+--------+--------------+

| Godina | Broj odabira |

+--------+--------------+

| 2000 | 3 |

+--------+--------------+

Š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.

Primjedbe

Gornji upiti ispunjavaju 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.