DATABASE PROGRAMMING

CREATE TABLE employees

( employee_id NUMBER(6)

, first_name VARCHAR2(20)

, last_name VARCHAR2(25) CONSTRAINT emp_last_name_nn NOT NULL

, email VARCHAR2(25) CONSTRAINT emp_email_nn NOT NULL

, phone_number VARCHAR2(20)

, hire_date DATE CONSTRAINT emp_hire_date_nn NOT NULL

, job_id VARCHAR2(10) CONSTRAINT emp_job_nn NOT NULL

, salary NUMBER(8,2)

, commission_pct NUMBER(2,2)

, manager_id NUMBER(6)

, department_id NUMBER(4)

1) Write a PL/SQL block, in that program, define a procedure named dept_info (so this procedure just lives in that block only), that procedure will accept the department ID as IN parameter, via two OUT parameters, pass back to the invoker (program) two values: the department name, and the full name (first name, space, last name) of the department manager.

Note, you may use two separate simple SQL statements to get each data we needed.

In the executable section, the program will invoke this procedure dept_info, print out the related info we get from the procedure, the department name and the full name of its manager for that department (ID) 100.

2) In this question 2, you may reuse the coding as in question 1. This time, you will create a procedure called dept_info in your schema, that procedure will accept the department ID as IN parameter, via two OUT parameters, pass back to the invoker (program) two values: the department name, and the full name (first name, space, last name) of the department manager.

In the executable section of a PL/SQL, the program will define a cursor to find out for those departments (distinct department ID) that some of its employees working in that department their salary are less than $3,000. You program will run a loop, to invoke this procedure dept_info for each of these departments, print out the related info we get from the procedure, the department name and the full name of its manager.