/* Aufgabe 1 -------------------------------------------------------- */
/* a.1 */
SELECT name, familyname FROM employee;
/* a.2 */
SELECT name, familyname, age FROM employee where age > 45;
/* a.3 */
SELECT name, description FROM department;
/* a.4 */
SELECT description FROM jobtype;

/* b.1 */
UPDATE jobtype SET description = 'supervisor' WHERE jobid = 2;
/* b.2 */
UPDATE employee SET salary = salary + 10 WHERE jobid <> 1

/* Aufgabe 2 -------------------------------------------------------- */
/* a */
SELECT AVG(salary) FROM employee;
/* b */
SELECT AVG(salary) FROM employee WHERE salary BETWEEN 3000 AND 4000;

/* c */
(SELECT name, familyname, salary
FROM employee
WHERE jobid <> 1
ORDER BY salary DESC
FETCH FIRST ROW ONLY)

UNION

(SELECT name, familyname, salary
FROM employee
WHERE jobid <> 1
ORDER BY salary ASC
FETCH FIRST ROW ONLY);


/* Aufgabe 3 -------------------------------------------------------- */
/* a.1 */
SELECT e.name, e.familyname, d.name
FROM employee e, department d
WHERE e.deptid = d.deptid;
/* a.2 */
SELECT e.name, e.familyname, d.name
FROM employee e
LEFT OUTER JOIN department d ON d.deptid = e.deptid;

/* b) */
SELECT DISTINCT d.name
FROM department d
LEFT OUTER JOIN employee e ON e.deptid = d.deptid
LEFT OUTER JOIN jobtype j ON j.jobid = e.jobid
WHERE j.description IN ('supervisor', 'boss');

/* c) */
SELECT DISTINCT name
FROM department
WHERE deptid NOT IN (
	SELECT DISTINCT d.deptid
	FROM department d
	LEFT OUTER JOIN employee e ON e.deptid = d.deptid
	LEFT OUTER JOIN jobtype j ON j.jobid = e.jobid
	WHERE j.description IN ('supervisor', 'boss')
);


/* Aufgabe 4 -------------------------------------------------------- */
WITH hierarchy (deptid) AS
(
	SELECT root.deptid
	FROM department root
	WHERE name = 'DevTeam'

	UNION ALL

	SELECT sub.deptid
	FROM department sub, hierarchy super
	WHERE sub.parent = super.deptid
)
SELECT e.empid, e.name, e.familyname
FROM employee e
INNER JOIN hierarchy h ON h.deptid = e.deptid;


/* Aufgabe 5 -------------------------------------------------------- */
/* Tabelle Erzeugen */
CREATE TABLE salary_variation (
	old_salary INTEGER,
	new_salary INTEGER,
	EmpID INTEGER NOT NULL,
	changed_on TIMESTAMP,
	changed_by VARCHAR(128),
	FOREIGN KEY (EmpID) REFERENCES employee
);

/* Trigger erzeugen */
CREATE TRIGGER SalaryTrigger 
AFTER UPDATE OF salary ON employee
REFERENCING OLD AS old_employee
NEW AS new_employee
FOR EACH ROW MODE DB2SQL
INSERT INTO salary_variation VALUES 
	( old_employee.salary, 
	new_employee.salary, 
	old_employee.empid, 
	CURRENT TIMESTAMP, 
	CURRENT USER );

/* testen */
UPDATE employee SET salary = 5000 where empid = 1;
UPDATE employee SET salary = 1 where empid = 1;
SELECT * FROM salary_variation;