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) Define a user-defined record type called Emp_Sal that has three fields as: empno, lastname, and sal. These fields correspond to columns of employee_id, last_name, salary in employees table. You program will define their data types correspondently (such as varchar2, or employees.last_name%type, etc.) .

In a PL/SQL anonymous block, your program will retrieve the info of the employee whose employee ID is 103, then insert related information, (his employee ID, last name, and salary) into that user-defined record. After have populated that record, your program will print out the contents of this record.

2) We will reuse the code that defines the record type of Emp_Sal in Q1.

In Q1, we have defined a user-defined record type called Emp_Sal that has three fields as: empno, lastname, and sal. You will define the nested table type named as Emp_Sal_NT, that uses Emp_Sal as its element; then declare a NT variable LIST.

Retrieve data from Employees table for those people who work in department 60. Populate this LIST with the retrieved data, print out the contents of the LIST.

Note, we use NT_variable_name (index) to reference the NT element; in this question, we need to use NT_variable_name (index).record_field to reference the elements field. Such as

LIST(i).empno for empno column.