CREATE TABLE Department(
  	idDep NUMERIC(2) PRIMARY KEY,
  	name VARCHAR2(20) NOT NULL,
  	address VARCHAR2(50)
);

CREATE TABLE Employee(
  	idEmp NUMERIC(5) PRIMARY KEY,
  	firstName VARCHAR2(20) NOT NULL,
  	lastName VARCHAR2(20) NOT NULL,
  	dep NUMERIC(2) NOT NULL
);

ALTER TABLE Employee 
  	ADD CONSTRAINT depFor 
  	FOREIGN KEY (dep) REFERENCES Department(idDep);

--inserimenti tabella Department

INSERT INTO Department VALUES (1,'Amministrazione','Via Roma, 3 Udine');
INSERT INTO Department(idDep,name,address) VALUES (2,'Ricerca','Via Delle Scienze, 123 Udine');
INSERT INTO Department(idDep,address,name) VALUES (3,'Via Delle Scienze, 125 Udine','Laboratorio');

--script per inserimenti tabella Employee

INSERT INTO Employee VALUES (11111,'Paolo','Bianchi',1);
INSERT INTO Employee VALUES (22222,'Leonardo','Rossi',2);
INSERT INTO Employee VALUES (33333,'Eleonora','Neri',3);
INSERT INTO Employee VALUES (44444,'Cristina','Verdi',1);
INSERT INTO Employee VALUES (55555,'Antonio','Rossi',2);
INSERT INTO Employee VALUES (66666,'Marco','Gialli',3);
INSERT INTO Employee VALUES (77777,'Maria','Neri',2);

-esempio per prova commit

UPDATE Employee
 SET dep=3
 WHERE idEmp=77777;

COMMIT;

--query

SELECT idEmp, firstName, lastName
	FROM Employee
	WHERE dep=1;

SELECT IDEMP,FIRSTNAME,LASTNAME
 FROM	DEPARTMENT,EMPLOYEE
 WHERE DEPARTMENT.IDDEP=EMPLOYEE.DEP and DEPARTMENT.NAME='Ricerca';

SELECT IDEMP, FIRSTNAME, LASTNAME
 FROM DEPARTMENT JOIN EMPLOYEE ON DEPARTMENT.IDDEP=EMPLOYEE.DEP
 WHERE DEPARTMENT.NAME='Ricerca';


--variazione per esempio di trigger
ALTER TABLE Department
  	ADD numEmp NUMERIC(3);
--aggiornamento colonna aggiunta
UPDATE Department
SET numEmp = (	SELECT count(*)
			FROM Employee
			WHERE dep=Department.iddep);
--trigger 1
CREATE OR REPLACE TRIGGER trigger_numEmp_ins
AFTER INSERT OR UPDATE OF dep ON Employee
FOR EACH ROW
BEGIN
	UPDATE Department
	SET numEmp=numEmp+1
	WHERE idDep=:new.dep;
END;

--trigger 2

CREATE OR REPLACE TRIGGER trigger_numEmp_del
AFTER DELETE OR UPDATE OF dep ON Employee
FOR EACH ROW
BEGIN
	UPDATE Department
	SET numEmp=numEmp-1
	WHERE idDep=:old.dep;
END;

--istruzioni SQL per controllare i trigger
--INSERT INTO Employee VALUES (77770,'Maria','Neri',2);
INSERT INTO Employee(idEmp,firstName,lastName,dep) VALUES (77770,'Maria','Neri',2);
DELETE FROM Employee WHERE idemp=33333;
UPDATE Employee 
 SET dep=3
 WHERE idemp=11111;


--creazione procedura per l'aggiornamento
CREATE OR REPLACE PROCEDURE NUMEMP
IS
BEGIN
	UPDATE Department
	SET numEmp = (SELECT COUNT(*)
			  FROM Employee
			  WHERE dep=Department.iddep);
END;

CREATE OR REPLACE PROCEDURE NUMEMP0
IS
BEGIN
	UPDATE Department
	SET numEmp = 0;
END;

BEGIN
	NUMEMP0();
END;


BEGIN
	NUMEMP();
END;

--creazione procedura con parametro per mettere nel trigger precedente
CREATE OR REPLACE PROCEDURE numEmpPiu(
	dep IN NUMBER)
IS
BEGIN
	UPDATE Department
 	SET numEmp=numEmp+1
	WHERE idDep=dep;
END;

CREATE OR REPLACE TRIGGER trigger_numEmp_ins
AFTER INSERT OR UPDATE ON Employee
FOR EACH ROW
BEGIN
	numEmpPiu(:new.dep);
END;

INSERT INTO Employee VALUES (88888,'Mario','Grigio',1);

--creazione funzione
CREATE OR REPLACE FUNCTION dipartimentoDaMatricola(
	matricola IN numeric)
return VARCHAR2
is
	nome varchar2(20);
BEGIN
	SELECT NAME INTO nome
	FROM EMPLOYEE E, DEPARTMENT D
	WHERE E.idEmp=matricola AND E.dep=D.idDep;
	RETURN nome;
END;

BEGIN
	dbms_output.put_line(dipartimentoDaMatricola(22222));
END;


--esempio PL/SQL
ALTER TABLE Employee
  	ADD salary NUMERIC(5);
UPDATE Employee
SET salary=1800
WHERE idEmp<40001;
UPDATE Employee
SET salary=1000
WHERE idEmp>40000;



DECLARE
	CURSOR emp_dep(dnum NUMBER) IS
		SELECT salary FROM Employee WHERE dep = dnum;
	totale_stipendi NUMBER(5) := 0;
	maggiore_stipendio NUMBER(5) := 0;
	numero_1500 NUMBER(5) := 0;
BEGIN
	/* The number of iterations will equal the number of rows
	   returned by emp_dep. */
	FOR emp_record IN emp_dep(2) LOOP
		totale_stipendi := totale_stipendi + emp_record.salary;
		IF emp_record.salary > 1500.00 THEN
			numero_1500 := numero_1500 + 1;
		END IF;
		IF emp_record.salary > maggiore_stipendio THEN
			maggiore_stipendio := emp_record.salary;
		END IF;
	END LOOP;
	dbms_output.put_line('Totale stipendi: ' || totale_stipendi);
	dbms_output.put_line('Stipendio maggiore: ' || maggiore_stipendio);
	dbms_output.put_line('Numero stipendi maggiori di 1.500 euro: ' || numero_1500);
END;