--1
--SELECT JRBR
--FROM SPJ
--WHERE NOT EXISTS ( SELECT *
--                   FROM SPJ AS SPJ2
--                   WHERE PRBR=SPJ.PRBR AND JRBR<>SPJ.JRBR);

--2
--SELECT SRBR, SIME
--FROM S
--WHERE NOT EXISTS ( SELECT *
--                   FROM SPJ
--                   WHERE SRBR<>S.SRBR AND JRBR='J4'
--                   AND NOT EXISTS ( SELECT *
--                                    FROM SPJ AS SPJ2
--                                    WHERE SRBR=S.SRBR AND JRBR='J4' AND PRBR=SPJ.PRBR));

--3
--I nacin
--SELECT P.PRBR, PIME, COUNT(*) AS BROJISPORUKA
--FROM P JOIN SPJ ON P.PRBR=SPJ.PRBR
--GROUP BY P.PRBR, PIME
--UNION
--SELECT PRBR, PIME, 0 AS BROJISPORUKA
--FROM P
--WHERE NOT EXISTS ( SELECT *
--                   FROM SPJ
--                   WHERE PRBR=P.PRBR)
--ORDER BY PRBR;

-- II nacin
--SELECT PRBR, PIME, ( SELECT COUNT(*)
--                             FROM SPJ
--                            WHERE PRBR=P.PRBR) AS BROJISPORUKA
--FROM P
--ORDER BY PRBR;

--III nacin
--SELECT P.PRBR, PIME, COUNT(SRBR) AS BROJISPORUKA
--FROM P LEFT OUTER JOIN SPJ ON P.PRBR=SPJ.PRBR
--GROUP BY P.PRBR, PIME
--ORDER BY P.PRBR;


--4

--SELECT SIME, S.GRAD, J.GRAD, JIME
--FROM S FULL OUTER JOIN J ON S.GRAD=J.GRAD;

-- II nacin

--SELECT SIME, S.GRAD SGRAD, J.GRAD JGRAD, JIME
--FROM S JOIN J ON S.GRAD=J.GRAD
--UNION 
--SELECT SIME, GRAD SGRAD, CAST(NULL AS CHAR(15)) JGRAD, CAST(NULL AS CHAR(5)) JIME
--FROM S
--WHERE NOT EXISTS ( SELECT *
--                   FROM J
--                   WHERE S.GRAD=J.GRAD) 
--UNION 
--SELECT CAST(NULL AS CHAR(15)) SIME, CAST(NULL AS CHAR(15)) SGRAD, GRAD JGRAD,  JIME
--FROM J
--WHERE NOT EXISTS ( SELECT *
--                   FROM S
--                   WHERE S.GRAD=J.GRAD); 

-- resenje sa jednom kolonom grad

--SELECT SIME,  COALESCE(S.GRAD, J.GRAD) GRAD, JIME
--FROM S FULL OUTER JOIN J ON S.GRAD=J.GRAD;

--5
--SELECT SPJ.*, CASE 
--                WHEN KOL>500 THEN 'VELIKA'
--                WHEN KOL>300 THEN 'SREDNJA'
--                ELSE 'MALA' 
--              END AS KATEGORIJA
--FROM SPJ;

--6
--SELECT KAT, COUNT(*)
--FROM ( SELECT SPJ.*, CASE 
--                WHEN KOL>500 THEN 'VELIKA'
--                WHEN KOL>300 THEN 'SREDNJA'
--                ELSE 'MALA' 
--              END AS KAT
--       FROM SPJ) AS KATEGORIJE
--GROUP BY KAT;


--WITH KATEGORIJE AS  ( 
--SELECT SPJ.*, CASE 
--                WHEN KOL>500 THEN 'VELIKA'
--                WHEN KOL>300 THEN 'SREDNJA'
--                ELSE 'MALA' 
--              END AS KAT
--       FROM SPJ
--)
--SELECT KAT, COUNT(*)
--FROM  KATEGORIJE
--GROUP BY KAT;


--7
--WITH SK(SRBR, BRI) AS ( SELECT SRBR, COUNT(*)  
--             FROM SPJ
--             GROUP BY SRBR
--             HAVING COUNT(*) > 3 )
--SELECT COUNT(*)
--FROM SK;

--8
--WITH A AS ( SELECT GRAD, SRBR, CAST(NULL AS CHAR(5)) AS JRBR
--            FROM S
--            UNION 
--            SELECT GRAD, CAST(NULL AS CHAR(5)) AS SRBR, JRBR
--            FROM J)
--SELECT GRAD, COUNT(SRBR) brS, COUNT(JRBR) brJ
--FROM A
--GROUP BY GRAD;

-- II resenje

--WITH SG AS ( SELECT GRAD, COUNT(*) BRS
--             FROM S
--             GROUP BY GRAD),
--JG AS ( SELECT GRAD, COUNT(*) BRJ
--       FROM J
--       GROUP BY GRAD)
--SELECT COALESCE(SG.GRAD, JG.GRAD), COALESCE(BRS, 0) BRS, COALESCE(BRJ, 0) BRJ
--FROM SG FULL OUTER JOIN JG ON SG.GRAD=JG.GRAD;

--9
--INSERT INTO S 
--VALUES ('S9', 'Petrovic', 15, 'Beograd');

--10
--INSERT INTO P 
--VALUES ('P7', 'Cekic', 'Crna', 2, NULL);

-- II resenje

--INSERT INTO P (PRBR, PIME, BOJA, TEZINA)
--VALUES ('P9', 'Cekic', 'Crna', 2);

--11
--INSERT INTO J
--SELECT 'J11', 'Most', GRAD
--FROM J
--WHERE J.JRBR='J5' ;

--12
--DELETE FROM S
--WHERE SRBR='S9';

--13
--DELETE FROM SPJ
--WHERE PRBR IN ( SELECT PRBR
--                                 FROM P
--                                 WHERE TEZINA>50);

--14
--UPDATE P
--SET TEZINA=TEZINA*1.5,
--    BOJA=NULL
--WHERE GRAD='Beograd';

--ILI

--UPDATE P
--SET (TEZINA, BOJA)=(TEZINA*1.5,NULL)
--WHERE GRAD='Beograd';

--15
--UPDATE S
--SET STATUS=STATUS+15
--WHERE SRBR IN ( SELECT DISTINCT SRBR
--                FROM SPJ
--               WHERE KOL>200);

--16
--UPDATE S
--SET STATUS = (SELECT MAX(STATUS)
--             FROM S)
--WHERE SRBR IN (SELECT SRBR
--               FROM SPJ JOIN J ON SPJ.JRBR=J.JRBR
--               WHERE GRAD='Beograd');






