CSC 352/452: DATABASE PROGRAMMING MID-TERM EXAM | Complete Solution

CSC 352/452: Database Programming

Mid-term Exam
(300 Points)

Late exams will not be accepted or graded.

 

    The mid-term exam is a take-home, open-book, and open-notes exam.  You can use any of your class notes and readings to complete the exam.
    You may not consult in any form with any other person while doing this take-home exam.
    Please submit a text file containing all your answers to D2L before or on due date. All other file types (e.g., DOC, DOCX, RTF, PDF, JPG, or ZIP) will be rejected.
    Please review your assignment file before submitting it to make sure you have the correct one. It is your responsibility to ensure that you upload the correct assignment file. If you submit a blank/wrong file, you will simply receive a grade of zero.

Part I (CSC 352 and CSC 452 - 100 points)

There are a total of 10 questions. Each question is worth 10 points. Please read each question carefully and choose the correct answer.

Assume that the tab1 table exists in the underlying database.

Q1. Consider the following SELECT-INTO statement in a PL/SQL block. What happens if there are no rows satisfying the WHERE condition?

    

A.        A NO_DATA_FOUND exception is raised.

B.         A TOO_MANY_ROWS exception is raised.

C.         A ZERO_DIVIDE exception is raised.

D.        The SELECT-INTO statement executes successfully.

Q2. Evaluate the following PL/SQL block:

Which value is assigned to v_approval?

A. FALSE

B. TRUE

C. NULL

D. None of the above

Q3. PL/SQL records of the same declared type can be compared for equality by using the equality operator (=).

    

A.        The user must explicitly declare the cursor_idx in the DECLARATION section.

B.         It requires an OPEN cursor_name statement.

C.         It requires a CLOSE cursor_name statement.

D.        The cursor must return at least one row.

E.         It does not require a FETCH statement.

F.         All of the above

Q6.  In which section of a PL/SQL block is a WHEN NO_DATA_FOUND THEN clause allowed?

A. DECLARATION

B. EXECUTION

C. EXCEPTION

D. All of the above

Q7. Evaluate the following PL/SQL block:

     DECLARE

           CURSOR c_1 IS SELECT * FROM tab1 ORDER BY c2;

           v_1        c_1%ROWTYPE;

     BEGIN

           OPEN c_1;

           LOOP

                SELECT c_1 INTO v_1;

                EXIT WHEN

                     (c_1%NOTFOUND = TRUE OR c_1%ROWCOUNT = 2);

                DBMS_OUTPUT.PUT_LINE(v_1.c2);

           END LOOP;

           CLOSE c_1;

     END;

Why will the above block cause a syntax error?

A.        The EXIT WHEN statement is illegal.

B.         The ROWTYPE% attribute can only be used in reference to actual tables.

C.         The SELECT-INTO statement is illegal.

D.        The variable v_1 must be declared of the cursor’s %TYPE rather than %ROWTYPE.

Q8. What is the value of v_flag when the following PL/SQL block is executed successfully?

 

A.        The value is always NULL.

B.         The value is always FALSE.

C.         The value is always TRUE.

D.        The value is FALSE if and only if the tab1 table is empty.

E.         The value is TRUE if and only if the tab1 table is empty.

Q9. To which of the following will an exception raised in the DECLARATION section of the block B_3 propagate?

  
A1.      The B_1 block’s DECLARATION section

A2.      The B_1 block’s EXECUTION section

A3.      The B_1 block’s EXCEPTION section

B1.       The B_2 block’s DECLARATION section

B2.       The B_2 block’s EXECUTION section

B3.       The B_2 block’s EXCEPTION section

C1.       The B_3 block’s DECLARATION section

C2.       The B_3 block’s EXECUTION section

C3.       The B_3 block’s EXCEPTION section

D1.      The B_4 block’s DECLARATION section

D2.      The B_4 block’s EXECUTION section

D3.      The B_4 block’s EXCEPTION section

E.         None of the above

Q10. How many rows will be inserted into the tab1 table after the following PL/SQL block has been executed successfully (no runtime error)

A.        72

B.         73

C.         74

D.        75

E.         76

F.         None of the above

Part II (CSC 352 and CSC 452 - 200 points)

    You are not allowed to create/use temporary tables/views/functions/procedures/triggers.
    If you modified the DEPARTMENT and EMPLOYEE tables created in Assignment #1, you need to delete and re-populate them.
    PL/SQL collections are not required.
    The Exception section in your program is optional.

1) (CSC 352 and CSC 452 - 60 points)

The BIRTHDAY_DISTRIBUTION table consists of every day of the year, from January 1 to December 31, along with a ranking based on how many babies were born in the United States on that date between 1973 and 1999. Rank 1 is the most popular, rank 2 is the next most popular, and so forth.

Create and populate the BIRTHDAY_DISTRIBUTION table by using the following SQL statements. (You have to connect to CDM’s Oracle server to populate the birthday_ distribution table.)

CREATE TABLE birthday_distribution

(MONTH        NUMBER,

 DAY          NUMBER,

 RANK NUMBER);

INSERT INTO birthday_distribution SELECT * FROM hchen.birthday_distribution;

COMMIT;

SELECT COUNT(*) FROM birthday_distribution;

 

Please make sure that there are 366 rows in your BIRTHDAY_DISTRIBUTION table.

In the BIRTHDAY_DISTRIBUTION table, you can find that September 16 is the most popular birthday (rank = 1) and February 29 is the least popular birthday (rank = 366). Excluding leap years, December 25 is the least popular birthday (rank = 365).

========================= Begin (1a) CSC 352 only ========================

1a) (CSC 352 only)

Write a PL/SQL anonymous block that displays the top two (2) most popular birthdays along with the ranks for each month. Sort your output in ascending order by months.

    You will lose 10 points if the title lines are missing in your output.
    You will lose 10 points if your output has incorrect format.
    If you have hard coded most popular birthdays or ranks (e.g., DBMS_OUTPUT.PUT_LINE ('1   20 (240)  14  (260) ');)  in your PL/SQL block, you will receive a zero grade.

Hint:  It is not necessary to use a cursor.

           

Test your program. You must ensure that the output of your program matches the following output:

 

 

========================== End (1a) CSC 352 only ========================

 

========================== Begin (1b) CSC 452 only =======================

1b) (CSC 452 only)

Write a PL/SQL anonymous block that accepts an integer for a month (1 ≤ m ≤ 12) from the user input and displays all days along with the ranks of the month. The top three (3) most popular birthdays of the month are marked with “*** 1st Most Popular Day”, “*** 2nd Most Popular Day”, and “*** 3rd Most Popular Day”, respectively. Sort your output in ascending order by days.

    You will lose 10 points if the title lines are missing in your output.
    You will lose 10 points if your output has incorrect format.
    If you have hard coded the most popular birthdays or ranks (e.g., DBMS_OUTPUT. PUT_LINE ('1  235');) in your PL/SQL block, you will receive a zero grade.
    To avoid complicating issues, you can assume that the user always enters input from keyboard that consists only of the digits 0 through 9 and Enter. But, you need to check whether the user input is between 1 and 12.

Test your program. You must ensure that the output of your program matches the following output:

 

Case 1

 

 

Output:

 

 

Case 2)

 

 

Output:

 

========================== End (1b) CSC 452 only =======================

2) (CSC 352 and CSC 452 - 70 points)

ABC Airlines Inc. keeps track of its employees in its Human Resources database. The HR_PERSON table contains basic employee information. The JOB_TYPE field indicates whether a person is a full-time employee (F) or a part-time employee (P). The JOB_STATUS field indicates whether a person is an active (A) or inactive (I) employee. The structure of the table is shown below along with some sample records:

HR_PERSON

PERSON_ID
    

LAST_NAME
    

FIRST_NAME
    

HIRE_DATE
    

JOB_TYPE
    

JOB_STATUS

NUMBER PK
    

VARCHAR2(30)
    

VARCHAR2(30)
    

DATE
    

CHAR
    

CHAR

1000
    

Smith
    

Ryan
    

04-MAY-90
    

F
    

I

1170
    

Brown
    

Dean
    

01-DEC-92
    

P
    

A

2010
    

Fisher
    

Jane
    

12-FEB-95
    

F
    

I

2080
    

Brewster
    

Andre
    

28-JUL-98
    

F
    

A

3190
    

Clark
    

Dan
    

04-APR-01
    

P
    

A

3500
    

Jackson
    

Tyler
    

01-NOV-05
    

F
    

A

4000
    

Miller
    

Mary
    

11-JAN-08
    

F
    

A

4100
    

Jackson
    

Peter
    

08-AUG-11
    

P
    

I

4200
    

Smith
    

Ryan
    

08-DEC-12
    

F
    

A

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 HR_PILOT as shown below:

HR_PILOT

PERSON_ID
    

PILOT_TYPE

NUMBER PK, FK
    

VARCHAR2(100)

1170
    

Commercial pilot

2010
    

Airline transport pilot

3500
    

Airline transport pilot

For example, Brewster Andre 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 HR_PERSON and HR_PILOT tables by using the following SQL statements.

CREATE TABLE hr_person

(

       person_id     NUMBER        PRIMARY KEY,

       last_name     VARCHAR2(30)  NOT NULL,

       first_name    VARCHAR2(30)  NOT NULL,

       hire_date     VARCHAR2(30)  NOT NULL,

       job_type      CHAR          NOT NULL,

       job_status    CHAR          NOT NULL

);

/

INSERT INTO hr_person VALUES (1000, 'Smith', 'Ryan', '04-MAY-90','F', 'I');

INSERT INTO hr_person VALUES (1170, 'Brown', 'Dean', '01-DEC-92','P', 'A');

INSERT INTO hr_person VALUES (2010, 'Fisher', 'Jane', '12-FEB-95','F', 'I');

INSERT INTO hr_person VALUES (2080, 'Brewster', 'Andre', '28-JUL-98', 'F', 'A');

INSERT INTO hr_person VALUES (3190, 'Clark', 'Dan', '04-APR-01','P', 'A');

INSERT INTO hr_person VALUES (3500, 'Jackson', 'Tyler', '01-NOV-05', 'F', 'A');

INSERT INTO hr_person VALUES (4000, 'Miller', 'Mary', '11-JAN-08', 'F', 'A');

INSERT INTO hr_person VALUES (4100, 'Jackson', 'Peter', '08-AUG-11', 'P','I');

INSERT INTO hr_person VALUES (4200, 'Smith', 'Ryan', '08-DEC-12', 'F','A');

COMMIT;

/

CREATE TABLE hr_pilot

(

       person_id            NUMBER        PRIMARY KEY,

       pilot_type           VARCHAR2(100) NOT NULL,

       CONSTRAINT fk_hr_person_pilot FOREIGN KEY (person_id)

              REFERENCES hr_person(person_id)

);

/

INSERT INTO hr_pilot VALUES (1170, 'Commercial pilot');

INSERT INTO hr_pilot VALUES (2010, 'Airline transport pilot');

INSERT INTO hr_pilot VALUES (3500, '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 name, job type, job status, hire date, and pilot type). Sort your output in ascending order by the employee name (Last Name, First Name), job type (Full-Time, Part-Time),  job status (Active, Inactive), and hire date.

    If the last name is NOT in the HR_PERSON table (LAST_NAME), your program displays information about ALL employees.
    If the last name is in the HR_PERSON table (LAST_NAME), your program displays the corresponding employee’s information. We have duplicate names in the HR_PERSON table.
    The last 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 job type (“F” or “P”) must be displayed as “Full-Time Employee” or “Part-Time Employee” in your output. You will lose 10 points if you fail to do so. (Hint: you can use IF…THEN…ELSE/CASE/DECODE statement/function to convert one string to another.)
    The status (“A” or “I”) must be displayed as “Active” or “Inactive” in your output. You will lose 10 points if you fail to do so. (Hint: you can use IF…THEN…ELSE/CASE/DECODE statement/function to convert one string to another.)
    If an employee is not a pilot, the pilot type is shown as “------” 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 has incorrect format.

Test your program. You must ensure that the output of your program matches the following sample output:

Case 1)

 

Output:

 

Case 2)

 

Output:

 

Case 3)

 

Output:

 

3) (CSC 352 and CSC 452 - 70 points)

========================= Begin (3a) CSC 352 only ========================

3a) (CSC 352 only)

Based on the tables created in Assignment #1, write a PL/SQL program that accepts an employee ID from the user input and displays 1) employee name, hire date, salary, commission, total pay (salary + commission), and his/her department name (If the given employee does not belong to any department, the department name is shown as “------” in your output.), and 2) all employees (alone with their hire dates) who work in the same department as the given employee and were hired before the given employee (or “NO OUTPUT”).  Sort your output by the employee name.

 

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

    You will lose 10 points if your output has incorrect format.

    Hard coding (e.g., IF v_emp_id  = 7596 THEN v_1 := ...) will receive a zero grade.

To avoid complicating issues, you can assume that the user always enters input from keyboard that consists only of the digits 0 through 9 and Enter.

Test your program. You must ensure that the output of your program matches the following sample output:

Case 1)

 

Output:

 

Case 2)

 

Output:

 

Case 3)

Output:

 

Case 4)

 

Output:

 

========================= End (3a) CSC 352 only =========================

========================= Begin (3b) CSC 452 only ========================

3b) (CSC 452 only)

Based on the tables created in Assignment #1, write a PL/SQL anonymous block that displays all employees who were hired on the days of the week on which the highest number of employees were hired. The output of the program must contain all the hire dates, employee names, job, their corresponding department names (If an employee does not belong to any department, the department name is shown as “------” in your output.), and their corresponding manager names (If an employee does not have a manager, the manager name is shown as “------” in your output.). Sort your output by days of the week (Monday, Tuesday, …, Friday) and the hire date.

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

    Hard coding (e.g., IF v_day = 'Thursday' OR v_day = 'Friday' OR v_max_num = 4 THEN …) will receive a zero grade.

Hints:  

(1)        TO_CHAR(hire_date, 'Day')

(2)   TRIM(TO_CHAR(hire_date, 'Day'))

(3)        TRIM(TO_CHAR(hire_date, 'D')

(4)   GROUP BY TO_CHAR(hire_date, 'Day')

(5)        HW1 (Part III)

The output of your program must match the following:

 

========================= End (3b) CSC 452 only ========================