PL/SQL Question: XYZ Airlines Inc. keeps track of its employees in its Human Resources database. The T_PILOT table contains basic pilot information. The structure of the table is shown below with some sample records:

T_PILOT

PID	PTYPE
NUMBER PK	VARCHAR2(100)
123	Commercial pilot
456	Airline transport pilot

The T_EMPLOYEE table contains basic employee information. The ETYPE field indicates whether an employee is a full-time (FT) or part-time (PT) employee. The ESTATUS field indicates whether an employee is in an active (AC) or inactive (IN) work status. The PID field has the information related to pilots. The structure of the table is shown below along with some sample records:

T_EMPLOYEE

EID	LNAME	FNAME	ETYPE	ESTATUS	PID
VARCHAR2(7) PK	VARCHAR2(30)	VARCHAR2(30)	CHAR(2)	CHAR(2)	NUMBER FK
0A11000	Smith	Ryan	FT	IN	NULL
0A11170	Brown	Dean	PT	AC	123
0A12010	Fisher	Jane	FT	IN	456
0A12080	Brewster	Andre	FT	AC	NULL
0A13190	Clark	Dan	PT	AC	NULL
0A13500	Jackson	Tyler	FT	AC	456
0A14000	Miller	Mary	FT	AC	NULL
0A14100	Jackson	Peter	PT	IN	NULL
0A14200	Smith	Ryan	FT	AC	NULL

From the above tables, you can find that Andre Brewster is not a pilot (PID: NULL), Dean Brown is a Commercial pilot (PID: 123), and Jane Fisher is an Airline transport pilot (PID: 456).

Create and populate the T_PILOT and T_EMPLOYEE tables by using the following SQL statements.

CREATE TABLE T_PILOT

(

pid NUMBER PRIMARY KEY,

ptype VARCHAR2(100) NOT NULL

);

/

INSERT INTO T_PILOT VALUES (123, 'Commercial pilot');

INSERT INTO T_PILOT VALUES (456, 'Airline transport pilot');

COMMIT;

/

CREATE TABLE T_EMPLOYEE

(

eid VARCHAR2(7) PRIMARY KEY,

lname VARCHAR2(30) NOT NULL,

fname VARCHAR2(30) NOT NULL,

etype CHAR(2) NOT NULL,

estatus CHAR(2) NOT NULL,

pid NUMBER,

CONSTRAINT fk_emp_pilot FOREIGN KEY (pid) REFERENCES T_PILOT(pid)

);

/

INSERT INTO T_EMPLOYEE VALUES ('0A11000', 'Smith', 'Ryan', 'FT', 'IN', NULL);

INSERT INTO T_EMPLOYEE VALUES ('0A11170', 'Brown', 'Dean', 'PT', 'AC', 123);

INSERT INTO T_EMPLOYEE VALUES ('0A12010', 'Fisher', 'Jane','FT', 'IN', 456);

INSERT INTO T_EMPLOYEE VALUES ('0A12080', 'Brewster', 'Andre', 'FT', 'AC', NULL);

INSERT INTO T_EMPLOYEE VALUES ('0A13190', 'Clark', 'Dan', 'PT', 'AC', NULL);

INSERT INTO T_EMPLOYEE VALUES ('0A13500', 'Jackson', 'Tyler', 'FT', 'AC', 456);

INSERT INTO T_EMPLOYEE VALUES ('0A14000', 'Miller', 'Mary', 'FT', 'AC', NULL);

INSERT INTO T_EMPLOYEE VALUES ('0A14100', 'Jackson', 'Peter', 'PT','IN', NULL);

INSERT INTO T_EMPLOYEE VALUES ('0A14200', 'Smith', 'Ryan', 'FT', 'AC', NULL);

COMMIT;

/

Write a PL/SQL anonymous block that accepts a last name (LNAME) from the user input and displays employees information (LNAME, FNAME, EID, ESTATUS, ETYPE, and PTYPE). Sort your output in ascending order by the name (LNAME, FNAME) and then EID.

If the last name is NOT in the T_EMPLOYEE table (LNAME), your program displays information about ALL employees.

If the last name is in the T_EMPLOYEE table (LNAME), your program displays the corresponding employees information. We have duplicate names in the T_EMPLOYEE table.

The name is not case sensitive (e.g., Jackson = JACKSON). You will lose 10 points if you do not use the UPPER (or LOWER) function in your program.

The ETYPE (FT or PT) must be displayed as Full-Time or Part-Time in your output. You will lose 10 points if you fail to do so.

The ESTATUS (AC or IN) must be displayed as Active or Inactive in your output. You will lose 10 points if you fail to do so.

If an employee is not a pilot, the PTYPE is shown as ****** in your output.

Hard coding the EID, LNAME, FNAME, PID (e.g., 123, 456), or PTYPE will receive 0 points.

You will lose 10 points if the title lines are missing in your output.

You will lose 10 points if your output is not in the correct format (e.g., wrong order of columns).

You can only use the T_PILOT and T_EMPLOYEE tables. You will receive 0 points if you use other tables.

You will receive 0 points if you submit more than one PL/SQL program