[LV4] Baze podataka

//1. zadatak
SELECT SUBSTRING(ime, 1, 1) AS i, SUBSTRING(prezime, 1, 1) AS p, YEAR(datRod) AS dr
FROM student;
SELECT *FROM student ORDER BY datRod DESC;
SELECT ime, prezime FROM student WHERE datRod = (SELECT MIN(datRod) FROM student WHERE spol = 'F');

//2. zadatak
SELECT COUNT(mbr) FROM student;
SELECT COUNT(DISTINCT pbrPreb) FROM student;

//3. zadatak
SELECT AVG(ocjena) FROM ispit WHERE ocjena > 1;

//4. zadatak
SELECT s.ime, s.prezime, AVG(ocjena) AS "prosjek" FROM student AS s, ispit AS i
WHERE s.mbr = i.mbrStud AND i.ocjena > 1 GROUP BY s.ime, s.prezime, i.ocjena ORDER BY ocjena DESC;

//5. zadatak
CREATE VIEW student_pogled AS
SELECT i.sifPredmeta AS "sifra", i.ocjena, i.datIspita AS "datum ispita", s.ime, s.prezime, p.naziv FROM ispit AS i, student AS s, predmet AS p
WHERE i.mbrStud=s.mbr AND i.sifPredmeta=p.sifra;