CREATE OR REPLACE VIEW PRAC AS
	SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO, D.DNAME, D.LOC
	FROM EMP E
	INNER JOIN DEPT D ON D.DEPTNO = E.DEPTNO
	WHERE D.LOC = 'NEW YORK';

-- zadanie 1a -- dziala
SELECT SUM(SAL) FROM PRAC;
-- zadanie 1b -- dziala
UPDATE PRAC SET SAL = SAL * 1.2;
-- zadanie 1c -- nie dziala
UPDATE PRAC SET DNAME = 'NOWY DZIAL';
-- zadanie 1d -- nie dziala
INSERT INTO PRAC (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO) VALUES(13, 'ENAME', 'JOB', 123, '81/12/03', 234, 5, 10);


-- zadanie 2
CREATE OR REPLACE VIEW PRAC3000 AS
	SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO
	FROM EMP E
	WHERE E.SAL < 3000
  WITH CHECK OPTION;
  
UPDATE PRAC3000 SET SAL = SAL + 1000; -- dziala
UPDATE PRAC3000 SET SAL = SAL + 10; -- dziala
UPDATE PRAC3000 SET SAL = SAL + 100 WHERE SAL < 2900; -- dziala


-- zadanie 3
CREATE OR REPLACE VIEW PRACMGR AS
	SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO
	FROM EMP E
	WHERE E.JOB = 'MANAGER'
  WITH READ ONLY;

UPDATE PRACMGR SET SAL = SAL + 10; -- nie dziala

-- zadanie 13 - WYZWALACZ
CREATE OR REPLACE VIEW PRACOWNICY AS
	SELECT E.EMPNO, E.ENAME, E.JOB, E.MGR, E.HIREDATE, E.SAL, E.COMM, E.DEPTNO, D.DNAME, D.LOC
	FROM EMP E
	INNER JOIN DEPT D ON D.DEPTNO = E.DEPTNO;

CREATE OR REPLACE TRIGGER PRACOWNICY_INSERT
INSTEAD OF INSERT ON PRACOWNICY
	FOR EACH ROW
		DECLARE
			P NUMBER;
			LP NUMBER;
		BEGIN
			SELECT COUNT(*) INTO P FROM DEPT
			WHERE DEPT.DEPTNO = :NEW.DEPTNO;
			IF p=0 THEN
				INSERT INTO DEPT VALUES(:NEW.DEPTNO, :NEW.DNAME, :NEW.LOC);
			END IF;
			SELECT COUNT(*) INTO P FROM EMP
			WHERE EMP.ENAME = :NEW.ENAME;
			IF P=0 THEN
				SELECT NVL(MAX(EMPNO), 1) INTO LP FROM EMP;
				LP := LP + 1;
				INSERT INTO EMP VALUES(LP, :NEW.ENAME, :NEW.JOB, NULL, :NEW.HIREDATE, :NEW.SAL, :NEW.COMM, :NEW.DEPTNO);
			ELSE
				UPDATE EMP SET JOB=:NEW.JOB, MGR=NULL, HIREDATE=:NEW.HIREDATE, SAL=:NEW.SAL, COMM=:NEW.COMM, DEPTNO=:NEW.DEPTNO
				WHERE ENAME = :NEW.ENAME;
			END IF;
		END;

-- zadanie 14
INSERT INTO PRACOWNICY (ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO, DNAME, LOC)
VALUES ('KOWALSKI', 'PROF', NULL, '81/04/02', 400, 5, 40, 'PJWSTK', 'WARSZAWA');