XYZ Airlines Inc. keeps track of its employees in its Human Resources database.

Question

XYZ Airlines Inc. keeps track of its employees in its Human Resources database. The TAB_EMPLOYEE table contains basic employee information. The EMPLOYEE_TYPE field indicates whether an employee is a full-time (FT) or part-time (PT) employee. The EMPLOYEE_STATUS field indicates whether an employee is in an active (AC) or inactive (IN) work status. The structure of the table is shown below along with some sample records:

TAB_EMPLOYEE

EMPLOYEE_ID

LAST_NAME

FIRST_NAME

HIRE_DATE

EMPLOYEE_TYPE

EMPLOYEE_STATUS

VARCHAR2(7) PK

VARCHAR2(30)

VARCHAR2(30)

DATE

CHAR(2)

CHAR(2)

0A11000

Smith

Ryan

04-MAY-90

FT

IN

0A11170

Brown

Dean

01-DEC-92

PT

AC

0A12010

Fisher

Jane

12-FEB-95

FT

IN

0A12080

Brewster

Andre

28-JUL-98

FT

AC

0A13190

Clark

Dan

04-APR-01

PT

AC

0A13500

Jackson

Tyler

01-NOV-05

FT

AC

0A14000

Miller

Mary

11-JAN-08

FT

AC

0A14100

Jackson

Peter

08-AUG-11

PT

IN

0A14200

Smith

Ryan

08-DEC-12

FT

AC

An employee (full-time employee as well as part-time employee) can be a pilot. In this case, the information related to pilots is kept inside a separate table TAB_PILOT as shown below:

TAB_PILOT

EMPLOYEE_ID

PILOT_TYPE

VARCHAR2(7) PK, FK

VARCHAR2(100)

0A11170

Commercial pilot

0A12010

Airline transport pilot

0A13500

Airline transport pilot

For example, Andre Brewster is a full-time employee and is not a pilot while Tyler Jackson is a full-time employee and is also a pilot. On the other hand, Dean Brown is a part-time employee and is also a pilot. However, Dan Clark is a part-time employee but is not a pilot.

Create and populate the TAB_EMPLOYEE and TAB_PILOT tables by using the following SQL statements.

CREATE TABLE TAB_EMPLOYEE

(

       employee_id        VARCHAR2(7)        PRIMARY KEY,

       last_name           VARCHAR2(30)        NOT NULL,

       first_name        VARCHAR2(30)        NOT NULL,

       hire_date           VARCHAR2(30)        NOT NULL,

       employee_type      CHAR(2)             NOT NULL,

       employee_status     CHAR(2)             NOT NULL

);

/

INSERT INTO TAB_EMPLOYEE VALUES ('0A11000', 'Smith', 'Ryan', '04-MAY-90','FT', 'IN');

INSERT INTO TAB_EMPLOYEE VALUES ('0A11170', 'Brown', 'Dean', '01-DEC-92','PT', 'AC');

INSERT INTO TAB_EMPLOYEE VALUES ('0A12010', 'Fisher', 'Jane', '12-FEB-95','FT', 'IN');

INSERT INTO TAB_EMPLOYEE VALUES ('0A12080', 'Brewster', 'Andre', '28-JUL-98','FT', 'AC');

INSERT INTO TAB_EMPLOYEE VALUES ('0A13190', 'Clark', 'Dan', '04-APR-01','PT', 'AC');

INSERT INTO TAB_EMPLOYEE VALUES ('0A13500', 'Jackson', 'Tyler', '01-NOV-05','FT', 'AC');

INSERT INTO TAB_EMPLOYEE VALUES ('0A14000', 'Miller', 'Mary', '11-JAN-08','FT', 'AC');

INSERT INTO TAB_EMPLOYEE VALUES ('0A14100', 'Jackson', 'Peter', '08-AUG-11','PT','IN');

INSERT INTO TAB_EMPLOYEE VALUES ('0A14200', 'Smith', 'Ryan', '08-DEC-12','FT','AC');

COMMIT;

/

CREATE TABLE TAB_PILOT

(

     employee_id         VARCHAR2(7) PRIMARY KEY,

     pilot_type          VARCHAR2(100) NOT NULL,

       CONSTRAINT fk_employee_pilot FOREIGN KEY (employee_id)

             REFERENCES TAB_EMPLOYEE(employee_id)

);

/

INSERT INTO TAB_PILOT VALUES ('0A11170', 'Commercial pilot');

INSERT INTO TAB_PILOT VALUES ('0A12010', 'Airline transport pilot');

INSERT INTO TAB_PILOT VALUES ('0A13500', 'Airline transport pilot');

COMMIT;

/

Write a PL/SQL anonymous block that accepts a last name (LAST_NAME) from the user input and displays employee’s information (employee_id, last_name, first_name, hire_date, employee_status, employee_type, and pilot_type). Sort your output in ascending order by the name (last_name, first_name) and hire_date.

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

If the last name is in the TAB_EMPLOYEE table (LAST_NAME), your program displays the corresponding employee’s information. We have duplicate names in the TAB_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 employee_type (“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 employee_status (“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 pilot type is shown as “N/A” in your output.

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 TAB_EMPLOYEE and TAB_PILOT 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.

EMPLOYEE_ID

LAST_NAME

FIRST_NAME

HIRE_DATE

EMPLOYEE_TYPE

EMPLOYEE_STATUS

VARCHAR2(7) PK

VARCHAR2(30)

VARCHAR2(30)

DATE

CHAR(2)

CHAR(2)

0A11000

Smith

Ryan

04-MAY-90

FT

IN

0A11170

Brown

Dean

01-DEC-92

PT

AC

0A12010

Fisher

Jane

12-FEB-95

FT

IN

0A12080

Brewster

Andre

28-JUL-98

FT

AC

0A13190

Clark

Dan

04-APR-01

PT

AC

0A13500

Jackson

Tyler

01-NOV-05

FT

AC

0A14000

Miller

Mary

11-JAN-08

FT

AC

0A14100

Jackson

Peter

08-AUG-11

PT

IN

0A14200

Smith

Ryan

08-DEC-12

FT

AC

Explanation / Answer

PL/SQL Query for the question is as follows:

create or replace PROCEDURE get_employee_info_by_LAST_NAME

( p_LAST_NAME VARCHAR DEFAULT -1 )

AS

DECLARE

i NUMBER( .... ); # '.....' ARE THE NO. OF EMLOYEES IN DB

N/A = 0;

BEGIN

FOR at IN (SELECT EMPLOYEE_ID,UPPER( LAST_NAME) , FIRST_ NAME, HIRE_DATE, EMPLOYEE_TYPE, EMPLOYEE_STATUS,PILOT_TYPE FROM TAB_EMPLOYEE FULL OUTER JOIN TAB_PILOT WHERE EMPLOYEE_ID.TAB_EMPLOYEE = EMPLOYEE_ID.TAB_PILOT AND LAST_NAME = p_LAST_NAME ORDER BY LAST_NAME ASC, FIRST_NAME ASC, HIRE_DATE ASC )

FOR i IN 1..(...) LOOP

IF ( at.EMPLOYEE_TYPE = FT ) THEN

FT := FULL-TIME;

END IF;

IF ( at.EMPLOYEE_TYPE = PT ) THEN

FT := PART-TIME;

END IF;

IF ( at.EMPLOYEE_STATUS = AC ) THEN

AC := ACTIVE;

END IF;

IF ( at.EMPLOYEE_STATUS = IN ) THEN

IN := INACTIVE;

END IF;

IF ( EMPLOYEE_ID.TAB_EMPLOYEE != EMPLOYEE_ID.TAB_PILOT ) THEN

PILOT_TYPE := N/A;

END IF;

END LOOP;

DBMS_OUTPUT.PUT_LINE( ' EMPLOYEE_ID ' || at.EMPLOYEE_ID ) ;

DBMS_OUTPUT.PUT_LINE( ' LAST_NAME' || at.p_LAST_NAME);

DBMS_OUTPUT.PUT_LINE(' FIRST_NAME ' || at.FIRST_NAME);

DBMS_OUTPUT.PUT_LINE( ' HIRE_DATE' || at.HIRE_DATE);

DBMS_OUTPUT.PUT_LINE( ' EMPLOYEE_TYPE' || at.EMPLOYEE_TYPE);

DBMS_OUTPUT.PUT_LINE( ' EMPLOYEE_STATUS' || at.EMPLOYEE_STATUS);

DBMS_OUTPUT.PUT_LINE( ' PILOT_TYPE ' || at.PILOT_TYPE);

EXCEPTION

WHEN NO_DATA_FOUND THEN

SELECT EMPLOYEE_ID,UPPER( LAST_NAME) , FIRST_ NAME, HIRE_DATE, EMPLOYEE_TYPE, EMPLOYEE_STATUS,PILOT_TYPE FROM TAB_EMPLOYEE FULL OUTER JOIN TAB_PILOT WHERE EMPLOYEE_ID.TAB_EMPLOYEE = EMPLOYEE_ID.TAB_PILOT;

END;

END get_employee_info_by_LAST_NAME;

# tag line are comments....

if there is any other query please ask in comments....

Related Questions

Navigate

• Browse (All)
• Browse X
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.