--1
--SELECT GRAD, STATUS
--FROM S
--WHERE 2 >= ( SELECT COUNT(*)
--             FROM S S1
--             WHERE S1.STATUS > S.STATUS);

-- II nacin

--select s1.grad
--from s s1 left join s s2 on s1.status < s2.status
--group by s1.srbr, s1.grad
--having count(*) <=2;

--2

--SELECT SRBR, SUM( CASE WHEN GRAD='Pariz' THEN 1 ELSE 0 END) brP, SUM(CASE  WHEN GRAD='London' THEN 1 ELSE 0 END) brL
--FROM SPJ JOIN J ON SPJ.JRBR=J.JRBR
--WHERE GRAD IN ('Pariz', 'London')
--GROUP BY SRBR;
 
--WITH A AS (SELECT SRBR, COUNT(*) BRL
--FROM SPJ JOIN J ON SPJ.JRBR=J.JRBR
--WHERE GRAD='London'
--GROUP BY SRBR),
--B AS (
--SELECT SRBR, COUNT(*) BRP
--FROM SPJ JOIN J ON SPJ.JRBR=J.JRBR
--WHERE GRAD='Pariz'
--GROUP BY SRBR)
--SELECT COALESCE(A.SRBR, B.SRBR), COALESCE(BRP, 0), COALESCE(BRL, 0)
--FROM A FULL OUTER JOIN B ON A.SRBR=B.SRBR
--ORDER BY 1;

--3
--SELECT GRAD, COUNT(DISTINCT PRBR)
--FROM S JOIN SPJ ON S.SRBR=SPJ.SRBR
--GROUP BY GRAD;

--4
--SELECT DISTINCT S.GRAD, J.GRAD
--FROM S, J
--WHERE NOT EXISTS ( SELECT *
--                   FROM SPJ JOIN S S1 ON S1.SRBR=SPJ.SRBR JOIN J J1 ON J1.JRBR=SPJ.JRBR
--                   WHERE S1.GRAD=S.GRAD AND J1.GRAD=J.GRAD);

--SELECT S.GRAD, J.GRAD
--FROM S, J
--EXCEPT
--SELECT S.GRAD, J.GRAD
--FROM S JOIN SPJ ON S.SRBR=SPJ.SRBR JOIN J ON J.JRBR=SPJ.JRBR;
	

--5
--SELECT GRAD, AVG(STATUS + 0.0)
--FROM S
--GROUP BY GRAD;

--6
--UPDATE S
--SET STATUS = ( SELECT MAX(STATUS)
--               FROM S
--               WHERE GRAD='Beograd')
--WHERE GRAD='Beograd';

--7
select d.indeks, rtrim(ime) ||' '|| prezime, 
sum(krediti) polozeno
from db.dosije d join db.ispit i 
       on d.indeks=i.indeks
     join db.predmet p on p.id=i.id_predmeta
where ocena>5 and status_prijave='o'
group by d.indeks, rtrim(ime) ||' '|| prezime
order by polozeno;  


--9
--CREATE DISTINCT TYPE INDEKS AS INTEGER WITH COMPARISONS;

--10
--CREATE TABLE DOSIJE1 (
--		  INDEKS INDEKS NOT NULL , 
--		  ID_SMERA INTEGER NOT NULL , 
--		  STATUS VARCHAR(20) NOT NULL , 
--		  IME VARCHAR(50) NOT NULL , 
--		  PREZIME VARCHAR(50) NOT NULL ,  
--		  DAT_UPISA DATE NOT NULL,
--                  PRIMARY KEY (INDEKS),
--		  FOREIGN KEY (ID_SMERA) REFERENCES DB.SMER);  


--11
--INSERT INTO DOSIJE1 (INDEKS, ID_SMERA, STATUS, IME, PREZIME, DAT_UPISA)
--SELECT INDEKS, ID_SMERA, STATUS, IME, PREZIME, DAT_UPISA
--FROM DB.DOSIJE;

--12
--SELECT D1.INDEKS + D2.INDEKS
--FROM DOSIJE1 D1, DOSIJE1 D2
--WHERE D1.INDEKS=20060001 AND D2.INDEKS=20060005;

--SELECT INTEGER(D1.INDEKS) + INTEGER(D2.INDEKS)
--FROM DOSIJE1 D1, DOSIJE1 D2
--WHERE D1.INDEKS=INDEKS(20060001) AND D2.INDEKS=INDEKS(20060005);


--13
--CREATE FUNCTION GODINAUPISA(indeks INDEKS)
--RETURNS INTEGER
--RETURN INTEGER(INDEKS)/10000;

--14
--CREATE FUNCTION BROJINDEKSA(indeks INDEKS)
--RETURNS INTEGER
--RETURN MOD(INTEGER(INDEKS),10000);

--SELECT GODINAUPISA(INDEKS), BROJINDEKSA(INDEKS)
--FROM DOSIJE1;

--15
--CREATE FUNCTION MAX (INDEKS)
--RETURNS INDEKS
--SOURCE SYSIBM.MAX(INTEGER);

--SELECT MAX(INDEKS)
--FROM DOSIJE1;


