Database Programming Midterm Exam

Question: #6497

CSC 352/452: Database Programming Midterm Exam complete solution correct answer key
(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?

……

SELECT COUNT(*)

INTO v_count

FROM tab1

WHERE c1 > 0;

……

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:

DECLARE

v_stock BOOLEAN;

v_quota BOOLEAN := TRUE;

v_approval BOOLEAN := TRUE;

v_option BOOLEAN := v_approval;

BEGIN

v_approval := v_option AND v_quota AND v_stock;

END;

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 (=).

DECLARE

TYPE t_rec_1 IS RECORD (col1 NUMBER, col2 NUMBER);

v_rec_1 t_rec_1;

v_rec_2 t_rec_1;

BEGIN

……

IF v_rec_1 = v_rec_2 THEN

DBMS_OUTPUT.PUT_LINE('v_rec_1 = v_rec_2');

ELSE

DBMS_OUTPUT.PUT_LINE('v_rec_1 != v_rec_2');

END IF;

……

END;

A. FALSE

B. TRUE

Q4. Evaluate the following CURSOR statement:

DECLARE

CURSOR c_1 (p_1 VARCHAR2(50)) IS

SELECT c1, c2

FROM tab1

WHERE c9 = p_1;

Why will this statement cause a syntax error?

A. The SELECT statement is missing the INTO clause.

B. The UPPER or LOWER function is missing in the WHERE clause.

C. The size of the p_1 parameter cannot be specified.

D. You cannot define a cursor with a parameter in PL/SQL.

Q5. Which guideline relates to a CURSOR FOR Loop?

FOR cursor_idx IN cursor_name LOOP

statement1;

statement2;

......

END LOOP;

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?

DECLARE

v_flag BOOLEAN;

v_n NUMBER;

BEGIN

SELECT COUNT(*)

INTO v_n

FROM tab1;

v_flag := SQL%NOTFOUND;

END;

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?

<<B_4>>

DECLARE

v_4 NUMBER;

v_5 NUMBER;

BEGIN

v_4 := 10;

v_5 := v_4 + 80;

<<B_3>>

DECLARE

v_3 NUMBER := 90;

v_4 NUMBER := v_3 / (v_3 - v_5);

-- Run-time error, propagate to?

BEGIN

v_3 := v_3 * v_4 + 1;

v_4 := v_3 + v_4;

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR (B_3)!');

END B_3;

v_4 := v_4 * v_4;

<<B_2>>

DECLARE

v_2 NUMBER := 0;

v_5 NUMBER := v_4;

BEGIN

……

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR (B_2)!');

END B_2;

<<B_1>>

DECLARE

v_1 NUMBER := 3;

BEGIN

……

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR (B_1)!');

END B_1;

……

EXCEPTION

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('ERROR (B_4)!');

END B_4;

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)?

DECLARE

v_count NUMBER := 1;

v_1 NUMBER := 1;

BEGIN

DELETE FROM tab1;

COMMIT;

FOR i IN REVERSE 5..8 LOOP

INSERT INTO tab1 VALUES (i, i*2, i+3);

END LOOP;

SELECT COUNT(*)

INTO v_count

FROM tab1;

FOR i IN 2..v_count + 10 LOOP

INSERT INTO tab1 VALUES (i, i+10, i+20);

END LOOP;

INSERT INTO tab1 VALUES (99, 205, 306);

SELECT COUNT(*)

INTO v_1

FROM tab1;

WHILE v_1 >= 7 LOOP

IF v_1 = 10 OR v_1 = 25 OR v_1 = 35 OR v_1 = 45 THEN

INSERT INTO tab1 VALUES (v_1*20, v_1*30, v_1*40);

ELSE

v_1 := v_1 - 1;

END IF;

v_1 := v_1 - 2;

END LOOP;

FOR i IN 1..52 LOOP

IF i = 4 OR i = 16 OR i = 47 THEN

INSERT INTO tab1 VALUES (i*20, i*30, i*40);

END IF;

INSERT INTO tab1 VALUES (i*21, i*31, i*41);

END LOOP;

INSERT INTO tab1 VALUES (616, 222, 243);

INSERT INTO tab1 VALUES (77, 88, 99);

COMMIT;

END;

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)

DATE

CHAR

1000

Smith

Ryan

04-MAY-90

1170

Brown

Dean

01-DEC-92

2010

Fisher

Jane

12-FEB-95

2080

Brewster

Andre

28-JUL-98

3190

Clark

Dan

04-APR-01

3500

Jackson

Tyler

01-NOV-05

4000

Miller

Mary

11-JAN-08

4100

Jackson

Peter

08-AUG-11

4200

Smith

Ryan

08-DEC-12

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 must display salary/commission/total pay with a dollar ($) sign, a comma, and two decimal places (e.g., $1,234.56). If the employee does not have a commission, the commission must be shown as $0.00 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.

· 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 ========================

Solution: #6513

Database Programming Midterm Exam

Q9. To which of the following will an exception raised in the DECLARATION section of the block B_3 propagate? <> DECLARE v_4 NUMBER; v_5 NUMBER; BEGIN v_4 := 10; v_5 := v_4 + 80; <> DECLARE v_3 NUMBER := 90; v_4 NUMBER := v_3 / (v_3 - v_5); -- Run-time error, propagate to? BEGIN v_3 := v_3 * v_4 + 1; v_4 := v_3 + v_4; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR (B_3)!'); END B_3; v_4 := v_4 * v_4; <> DECLARE v_2 NUMBER := 0; v_5 NUMBER := v_4; BEGIN …… EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR (B_2)!'); END B_2; <> DECLARE v_1 NUMBER := 3; BEGIN …… EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('ERROR (B_1)!'); END B_1; …… EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT...