37p

/*1. feladat*/ 10p
CREATE OR REPLACE FUNCTION elso(osszeg NUMBER) RETURN HR.EMPLOYEES.employee_id%TYPE IS
ret HR.EMPLOYEES.employee_id%TYPE;
BEGIN
SELECT EMPLOYEE_ID
INTO ret
FROM HR.EMPLOYEES WHERE SALARY = (
SELECT MIN(SALARY) FROM HR.EMPLOYEES 
WHERE SALARY>osszeg);
return ret;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
      return null;
    WHEN TOO_MANY_ROWS THEN
      RAISE_APPLICATION_ERROR(-20001, 'Több ilyen dolgozó van');
END;


/*2. feladat*/ 6p
CREATE OR REPLACE PROCEDURE masodik(helyseg IN HR.LOCATIONS.CITY%TYPE, reszlegszam OUT NUMBER, raktarszam OUT NUMBER) IS
BEGIN
FOR i IN (SELECT FIRST_NAME, LAST_NAME FROM HR.EMPLOYEES INNER JOIN HR.DEPARTMENTS ON HR.EMPLOYEES.DEPARTMENT_ID = HR.DEPARTMENTS.DEPARTMENT_ID
INNER JOIN HR.LOCATIONS ON HR.DEPARTMENTS.LOCATION_ID=HR.LOCATIONS.LOCATION_ID WHERE HR.LOCATIONS.city=helyseg)
LOOP
      DBMS_OUTPUT.PUT_LINE(i.first_name || ' ' || i.last_name);
END LOOP;
SELECT COUNT(DEPARTMENT_ID) INTO reszlegszam FROM HR.DEPARTMENTS INNER JOIN HR.LOCATIONS ON HR.DEPARTMENTS.location_id = HR.LOCATIONS.location_id WHERE city = helyseg;
SELECT COUNT(WAREHOUSE_ID) INTO raktarszam FROM OE.WAREHOUSES INNER JOIN HR.LOCATIONS ON OE.WAREHOUSES.location_id = HR.LOCATIONS.location_id WHERE city = helyseg;
END;


/*3. feladat*/ 7p
DECLARE
alma HR.EMPLOYEES.employee_id%TYPE;
korte HR.EMPLOYEES.employee_id%TYPE;
barack HR.EMPLOYEES.employee_id%TYPE;
reszlegszam NUMBER;
raktarszam NUMBER;
exc EXCEPTION;
PRAGMA EXCEPTION_INIT (exc, -20001);
BEGIN
  BEGIN
  alma:=ELSO(25000);
  EXCEPTION
        WHEN exc THEN
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
  END;
  BEGIN
  korte:=ELSO(9400);
  EXCEPTION
        WHEN exc THEN
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
  END;
  barack:=ELSO(11300);
  MASODIK('Seattle',reszlegszam,raktarszam);
END;

CREATE TABLE jobs AS SELECT * FROM HR.JOBS;

/*4. feladat*/ 2p
ALTER TABLE jobs ADD sum_salary NUMBER(10);
update jobs set sum_salary = (SELECT SUM(salary) FROM EMPLOYEES WHERE job_id = jobs.job_id);

/*5. feladat*/ 6p
CREATE OR REPLACE TRIGGER otodik
  AFTER INSERT
  OR UPDATE OF job_id
  OR DELETE
  ON employees
BEGIN
  update jobs set sum_salary = (SELECT SUM(salary) FROM EMPLOYEES WHERE job_id = jobs.job_id);
END;

/*6. feladat*/ 5p
CREATE OR REPLACE TRIGGER hatodik
  AFTER INSERT
  OR UPDATE OF salary
  OR DELETE
  ON employees
BEGIN
  update jobs set min_salary = (SELECT MIN(salary) FROM EMPLOYEES WHERE job_id = jobs.job_id);
  update jobs set max_salary = (SELECT MAX(salary) FROM EMPLOYEES WHERE job_id = jobs.job_id);
END;

CREATE TABLE EMPLOYEES AS SELECt * FROM HR.EMPLOYEES;

DROP TABLE EMPLOYEES;

/*7. feladat*/ 1p
BEGIN
INSERT INTO EMPLOYEES(EMPLOYEE_ID, LAST_NAME, EMAIL, HIRE_DATE, JOB_ID) VALUES(9780, 'Szabo', 'valami@valami.com', SYSDATE, 'IT_PROG');
DELETE FROM EMPLOYEES WHERE rownum<=3;
UPDATE EMPLOYEES
  SET SALARY = 25000
  WHERE rownum<=2;
END;


/* csak magamnak */
DECLARE
alma HR.EMPLOYEES.employee_id%TYPE;
korte HR.EMPLOYEES.employee_id%TYPE;
barack HR.EMPLOYEES.employee_id%TYPE;
reszlegszam NUMBER;
raktarszam NUMBER;
exc EXCEPTION;
PRAGMA EXCEPTION_INIT (exc, -20001);
BEGIN
  BEGIN
  alma:=ELSO(25000);
  DBMS_OUTPUT.PUT_LINE(alma);
  EXCEPTION
        WHEN exc THEN
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
  END;
  BEGIN
  korte:=ELSO(9400);
  DBMS_OUTPUT.PUT_LINE(korte);
  EXCEPTION
        WHEN exc THEN
          DBMS_OUTPUT.PUT_LINE(SQLERRM);
  END;
  barack:=ELSO(11300);
  DBMS_OUTPUT.PUT_LINE(barack);
  MASODIK('Seattle',reszlegszam,raktarszam);
  DBMS_OUTPUT.PUT_LINE(reszlegszam || ' ' || raktarszam);
END;