Advertisement
Domy131097

[LV4] Baze podataka

Apr 18th, 2018
137
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. //1. zadatak
  2. SELECT SUBSTRING(ime, 1, 1) AS i, SUBSTRING(prezime, 1, 1) AS p, YEAR(datRod) AS dr
  3. FROM student;
  4. SELECT *FROM student ORDER BY datRod DESC;
  5. SELECT ime, prezime FROM student WHERE datRod = (SELECT MIN(datRod) FROM student WHERE spol = 'F');
  6.  
  7. //2. zadatak
  8. SELECT COUNT(mbr) FROM student;
  9. SELECT COUNT(DISTINCT pbrPreb) FROM student;
  10.  
  11. //3. zadatak
  12. SELECT AVG(ocjena) FROM ispit WHERE ocjena > 1;
  13.  
  14. //4. zadatak
  15. SELECT s.ime, s.prezime, AVG(ocjena) AS "prosjek" FROM student AS s, ispit AS i
  16. WHERE s.mbr = i.mbrStud AND i.ocjena > 1 GROUP BY s.ime, s.prezime, i.ocjena ORDER BY ocjena DESC;
  17.  
  18. //5. zadatak
  19. CREATE VIEW student_pogled AS
  20. 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
  21. WHERE i.mbrStud=s.mbr AND i.sifPredmeta=p.sifra;
Advertisement
RAW Paste Data Copied
Advertisement