-- Brief description of the organization: XYZ Corporation is a technology company that specializes in software development.

-- Tables
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT,
    salary INT
);

-- Update, Insert, and Delete
UPDATE employees
SET salary = 60000
WHERE employee_id = 1;

INSERT INTO employees (employee_id, employee_name, department_id, salary)
VALUES (2, 'John Doe', 2, 50000);

DELETE FROM employees
WHERE employee_id = 2;

-- Three different loops
DECLARE
    i INT := 1;
BEGIN
    WHILE i <= 10 LOOP
        DBMS_OUTPUT.PUT_LINE('Loop iteration: ' || i);
        i := i + 1;
    END LOOP;
    
    FOR j IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('For loop iteration: ' || j);
    END LOOP;
    
    FOR k IN (SELECT * FROM employees) LOOP
        DBMS_OUTPUT.PUT_LINE('Employee name: ' || k.employee_name);
    END LOOP;
END;

-- If statement
DECLARE
    salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO salary
    FROM employees
    WHERE employee_id = 1;
    
    IF salary > 50000 THEN
        DBMS_OUTPUT.PUT_LINE('High salary');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Low salary');
    END IF;
END;

-- Cursors
DECLARE
    CURSOR c_emp IS
    SELECT * FROM employees;
    
    emp_rec employees%ROWTYPE;
BEGIN
    OPEN c_emp;
    
    LOOP
        FETCH c_emp INTO emp_rec;
        EXIT WHEN c_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('Employee name: ' || emp_rec.employee_name);
    END LOOP;
    
    CLOSE c_emp;
END;

-- Trigger
CREATE OR REPLACE TRIGGER salary_trigger
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 50000 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be less than 50000');
    END IF;
END;

-- Exceptions
BEGIN
    RAISE_APPLICATION_ERROR(-20002, 'Explicitly raised exception');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Exception occurred: ' || SQLERRM);
END;

BEGIN
    SELECT salary INTO employees.salary
    FROM employees
    WHERE employee_id = 100;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No data found');
END;

-- Procedure
CREATE OR REPLACE PROCEDURE update_salary (emp_id IN INT, new_salary IN INT)
AS
BEGIN
    UPDATE employees
    SET salary = new_salary
    WHERE employee_id = emp_id;
END;

-- Function
CREATE OR REPLACE FUNCTION get_employee_name (emp_id IN INT)
RETURN VARCHAR2
AS
    emp_name VARCHAR2(50);
BEGIN
    SELECT employee_name INTO emp_name
    FROM employees
    WHERE employee_id = emp_id;
    
    RETURN emp_name;
END;

-- Package
CREATE OR REPLACE PACKAGE employee_package
AS
    PROCEDURE update_salary (emp_id IN INT, new_salary IN INT);
    FUNCTION get_employee_name (emp_id IN INT) RETURN VARCHAR2;
END employee_package;
/