Nothing Special   »   [go: up one dir, main page]
Scribd Logo
Upload

Welcome to Scribd!

  • 10 views

    практис9

    Uploaded by

    Morales Meow
    The document contains SQL code to: 1. Create tables to store employee, department, and job data with triggers to validate data entry and synchronize records between tables 2. Define a procedure and trigger to validate employee salaries are within the allowed range for their job 3. Demonstrate using the package and trigger to add a new employee record

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    практис9

    Uploaded by

    Morales Meow
    10 views5 pages
    The document contains SQL code to: 1. Create tables to store employee, department, and job data with triggers to validate data entry and synchronize records between tables 2. Define a procedure and trigger to validate employee salaries are within the allowed range for their job 3. Demonstrate using the package and trigger to add a new employee record

    Original Description:

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document contains SQL code to: 1. Create tables to store employee, department, and job data with triggers to validate data entry and synchronize records between tables 2. Define a procedure and trigger to validate employee salaries are within the allowed range for their job 3. Demonstrate using the package and trigger to add a new employee record

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    10 views5 pages

    практис9

    Uploaded by

    Morales Meow
    The document contains SQL code to: 1. Create tables to store employee, department, and job data with triggers to validate data entry and synchronize records between tables 2. Define a procedure and trigger to validate employee salaries are within the allowed range for their job 3. Demonstrate using the package and trigger to add a new employee record

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    of 5

    -- creating table --

    CREATE TABLE employees AS SELECT * FROM hr.employees WHERE 1=2;


    INSERT INTO employees SELECT * FROM hr.employees;

    CREATE TABLE departments AS SELECT * FROM hr.departments WHERE 1=2;


    INSERT INTO departments SELECT * FROM hr.departments;

    CREATE TABLE jobs AS SELECT * FROM hr.jobs WHERE 1=2;


    INSERT INTO jobs SELECT * FROM hr.jobs;

    CREATE SEQUENCE employee_seq


    START WITH 150
    INCREMENT BY 1
    NOCACHE
    NOCYCLE;

    --1
    CREATE OR REPLACE TRIGGER delete_department_trigger
    AFTER DELETE ON departments
    FOR EACH ROW
    BEGIN
    DELETE FROM employees
    WHERE department_id = :old.department_id;
    END;
    --
    select * from employees
    DELETE FROM departments WHERE department_id = 90;
    --

    --2
    CREATE OR REPLACE TRIGGER salary_check_trigger
    BEFORE INSERT ON employees
    FOR EACH ROW
    BEGIN
    IF :new.job_id = 'SA_REP' AND (:new.salary < 2000 OR :new.salary > 7000) THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary must be between 2000 and 7000 for
    SA_REP');
    END IF;
    END;
    --
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date,
    job_id, salary)
    VALUES (1001, 'John', 'Doe', 'johndoe@example.com', SYSDATE, 'SA_REP', 1000);

    --3
    CREATE OR REPLACE TRIGGER insert_employee_trigger
    AFTER INSERT ON employees
    FOR EACH ROW
    BEGIN
    DBMS_OUTPUT.PUT_LINE('Trigger: Employee ' || :new.employee_id || ' inserted');
    END;
    --
    INSERT INTO employees (employee_id, first_name, last_name, email, hire_date,
    job_id, salary)
    VALUES (1001, 'John', 'Doe', 'johndoe@example.com', SYSDATE, 'SA_REP', 5000);

    --4
    CREATE OR REPLACE TRIGGER delete_department_trigger
    BEFORE DELETE ON departments
    FOR EACH ROW
    DECLARE
    emp_count NUMBER;
    BEGIN
    SELECT COUNT(*) INTO emp_count
    FROM employees
    WHERE department_id = :old.department_id;

    IF emp_count > 0 THEN


    RAISE_APPLICATION_ERROR(-20001, 'Trigger: Cannot delete department with active
    employees.');
    END IF;
    END;

    --
    DELETE FROM departments WHERE department_id = 30;

    --5
    CREATE OR REPLACE TRIGGER salary_update_trigger
    BEFORE UPDATE OF salary ON employees
    FOR EACH ROW
    BEGIN
    IF :new.salary = :old.salary THEN
    RAISE_APPLICATION_ERROR(-20001, 'Trigger: New salary cannot be the same as the
    old salary');
    END IF;
    END;
    --
    UPDATE employees SET salary = 9000 WHERE employee_id = 103;
    --select * from employees

    --6
    CREATE OR REPLACE TRIGGER employee_dml_trigger
    BEFORE INSERT OR UPDATE OR DELETE ON employees
    FOR EACH ROW
    BEGIN
    IF INSERTING THEN
    DBMS_OUTPUT.PUT_LINE('Trigger: The employee with id ' || :new.employee_id || '
    is inserted');
    ELSIF UPDATING THEN
    DBMS_OUTPUT.PUT_LINE('Trigger: The employee with id ' || :old.employee_id || '
    is updated');
    ELSIF DELETING THEN
    DBMS_OUTPUT.PUT_LINE('Trigger: The employee with id ' || :old.employee_id || '
    is deleted');
    END IF;
    END;
    --
    DELETE FROM employees WHERE employee_id = 106;

    --====================PART 2 =================================----
    --1----------

    CREATE OR REPLACE PROCEDURE CHECK_SALARY(


    p_job_id IN JOBS.JOB_ID%TYPE,
    p_salary IN EMPLOYEES.SALARY%TYPE
    ) IS
    v_min_salary JOBS.MIN_SALARY%TYPE;
    v_max_salary JOBS.MAX_SALARY%TYPE;
    BEGIN
    SELECT MIN_SALARY, MAX_SALARY INTO v_min_salary, v_max_salary
    FROM JOBS
    WHERE JOB_ID = p_job_id;

    IF p_salary < v_min_salary OR p_salary > v_max_salary THEN


    RAISE_APPLICATION_ERROR(-20001, 'Invalid salary ' || p_salary || '.
    Salaries for job ' || p_job_id || ' must be between ' || v_min_salary || ' and ' ||
    v_max_salary);
    END IF;
    END;

    CREATE OR REPLACE TRIGGER CHECK_SALARY_TRG


    BEFORE INSERT OR UPDATE OF job_id, salary ON employees
    FOR EACH ROW
    WHEN (NEW.job_id <> OLD.job_id OR NEW.salary <> OLD.salary)
    BEGIN
    CHECK_SALARY(:NEW.job_id, :NEW.salary);
    END;

    --Using
    INSERT INTO employees (employee_id, first_name, last_name, email, phone_number,
    hire_date, job_id, salary, commission_pct, manager_id, department_id)
    VALUES (employee_seq.NEXTVAL, 'John', 'Doe', 'johndoe@example.com', '555-1234',
    SYSDATE, 'SA_REP', 8000, NULL, 100, 80);
    select * from employees
    ---2-----------------
    -- answers for questions in doc file

    -- 3 --
    -- a --
    CREATE OR REPLACE TRIGGER CHECK_SALARY_TRG
    BEFORE INSERT OR UPDATE OF job_id, salary ON employees
    FOR EACH ROW
    WHEN (NEW.job_id <> OLD.job_id OR NEW.salary <> OLD.salary)
    BEGIN
    CHECK_SALARY(:NEW.job_id, :NEW.salary);
    END;

    -- b --
    --- Перед этим нужно запустить в конце пэккейдж
    BEGIN
    EMP_PKG.ADD_EMPLOYEE(
    p_first_name => 'Eleanor',
    p_last_name => 'Beh',
    p_email => 'EBEH',
    p_phone => '1234567890',
    p_hire_date => '27-SEP-20',
    p_job => 'IT_PROG',
    p_salary => 5000,
    p_manager => NULL,
    p_department => 30
    );
    END;

    --
    --c
    UPDATE employees
    SET salary = salary + 2000
    WHERE job_id = 'IT_PROG';

    --d
    UPDATE employees
    SET salary = 10000
    WHERE employee_id = (
    SELECT MIN(employee_id)
    FROM employees
    WHERE first_name = 'Eleanor' AND last_name = 'Beh'
    );

    --e
    --Since only the job_id and not the salary has changed,
    -- the CHECK_SALARY_TRG trigger will not fire.
    UPDATE employees
    SET job_id = 'ST_MAN'
    WHERE employee_id = (SELECT employee_id
    FROM employees
    WHERE first_name = 'Eleanor' AND last_name = 'Beh');

    ---Пэккейдж
    CREATE OR REPLACE PACKAGE emp_pkg IS
    PROCEDURE add_employee (
    p_first_name IN employees.first_name%TYPE,
    p_last_name IN employees.last_name%TYPE,
    p_email IN employees.email%TYPE,
    p_phone IN employees.phone_number%TYPE,
    p_hire_date IN employees.hire_date%TYPE,
    p_job IN employees.job_id%TYPE,
    p_salary IN employees.salary%TYPE,
    p_manager IN employees.manager_id%TYPE,
    p_department IN employees.department_id%TYPE
    );
    END emp_pkg;

    CREATE OR REPLACE PACKAGE BODY emp_pkg IS


    PROCEDURE add_employee (
    p_first_name IN employees.first_name%TYPE,
    p_last_name IN employees.last_name%TYPE,
    p_email IN employees.email%TYPE,
    p_phone IN employees.phone_number%TYPE,
    p_hire_date IN employees.hire_date%TYPE,
    p_job IN employees.job_id%TYPE,
    p_salary IN employees.salary%TYPE,
    p_manager IN employees.manager_id%TYPE,
    p_department IN employees.department_id%TYPE
    ) AS
    l_employee_id employees.employee_id%TYPE;
    BEGIN
    INSERT INTO employees (
    first_name,
    last_name,
    email,
    phone_number,
    hire_date,
    job_id,
    salary,
    manager_id,
    department_id
    ) VALUES (
    p_first_name,
    p_last_name,
    p_email,
    p_phone,
    p_hire_date,
    p_job,
    p_salary,
    p_manager,
    p_department
    ) RETURNING employee_id INTO l_employee_id;
    DBMS_OUTPUT.PUT_LINE('Employee added with ID ' || l_employee_id);
    END add_employee;
    END emp_pkg;

    You might also like