CSC 352/452: DATABASE PROGRAMMING M I D -T E R M E X A M (3 0 0 P O I N T S ) 2016 NEW

CSC 352/452: DATABASE PROGRAMMING
M I D -T E R M E X A M
(3 0 0 P O I N T S ) Late exams will not be accepted or graded.
Due on Sunday, 2/21/2016 at 11:59PM The mid-term exam is a take-home 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. In D2L, only the most recent submission is kept. 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. The mid-term exam is due by 11:59 pm Sunday, February 21, 2016. Do not
wait until the last day to submit your assignment file! Submit ahead in case
of unforeseen circumstances such as a computer crash or illness. There are
no exceptions to the deadline. Part I (CSC 352 and CSC 452 - 120 points)
There are a total of 12 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. In which section of a PL/SQL block is a WHEN TOO_MANY_ROWS THEN clause
allowed?
A. DECLARATION
B. EXECUTION
C. EXCEPTION
D. All of the above
Q2. Evaluate the following CURSOR statement:
DECLARE
CURSOR c_1 (p_ename VARCHAR2(50)) IS
SELECT * FROM tab1 WHERE c90 = p_ename;
Page 1 of 17 Why will this statement cause a syntax error?
A.
B.
C.
D. The size of the p_ename parameter cannot be specified.
The SELECT statement is missing the INTO clause.
The UPPER or LOWER function is missing in the WHERE clause.
All of the above. Q3. What is the purpose of the FETCH command in using an explicit cursor?
A.
B.
C.
D. To define a cursor to be used later.
To execute the query and identify the active set.
To retrieve rows (records) from the active set into local variables.
To close a cursor. Q4. PL/SQL records of the same declared type can be compared for equality by using the
equality operator (=).
DECLARE
TYPE t_1 IS RECORD (col_1 NUMBER, col_2 NUMBER);
v_1 t_1;
v_2 t_1;
BEGIN ……
IF v_1 = v_2 THEN
DBMS_OUTPUT.PUT_LINE('v_1 = v_2');
ELSE
DBMS_OUTPUT.PUT_LINE('v_1 != v_2');
END IF; ……
END; A.
B. FALSE
TRUE Q5. Evaluate the following PL/SQL block:
DECLARE
v_stock
BOOLEAN;
v_quota
BOOLEAN := TRUE;
v_approval
BOOLEAN := TRUE;
v_option
BOOLEAN := NULL;
BEGIN
v_option := v_approval;
v_approval := v_option AND v_stock AND v_quota;
END;
Which value is assigned to v_approval?
Page 2 of 17 A. NULL
B. FALSE
C. TRUE
D. None of the above
Q6. 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%FOUND;
END;
A.
B.
C.
D.
E.
F. The value is TRUE if and only if the tab1 table is empty.
The value is FALSE if and only if the tab1 table is empty.
The value is NULL if and only if the tab1 table is empty.
The value is always TRUE.
The value is always FALSE.
The value is always NULL. Q7. Evaluate the following PL/SQL block:
DECLARE
CURSOR c_1 IS SELECT * FROM tab1 ORDER BY c20;
v_1
c_1%ROWTYPE;
BEGIN
OPEN c_1;
LOOP
SELECT c_1 INTO v_1;
EXIT WHEN c_1%NOTFOUND;
IF c_1%ROWCOUNT > 5 THEN
DBMS_OUTPUT.PUT_LINE(v_1.c2);
END IF;
END LOOP;
CLOSE c_1;
END;
Why will the above block cause a syntax error?
A.
B.
C. The %ROWTYPE attribute can only be used in reference to actual tables.
The %ROWCOUNT attribute can only be used in implicit cursors.
The EXIT WHEN statement is illegal.
Page 3 of 17 D. The SELECT-INTO statement is illegal. Q8. Which guideline relates to a CURSOR FOR Loop?
FOR c_idx IN cursor_name LOOP
statement1;
statement2;
......
END LOOP; A.
B.
C.
D. The user must explicitly declare the c_idx in the DECLARATION section.
It does not require a FETCH statement.
The cursor must return at least one row.
All of the above Q9. Consider the following SELECT-INTO statement in a PL/SQL block. What happens
if there is no row satisfying the WHERE condition?
……
SELECT
COUNT(*)
INTO
v_count
FROM
tab1
WHERE
c10 = 0 AND c20 > 0;
……
A.
B.
C.
D.
E. The SELECT-INTO statement executes successfully.
A TOO_MANY_ROWS exception is raised.
A NO_DATA_FOUND exception is raised.
A ZERO_DIVIDE exception is raised.
All of the above Q10. In a PL/SQL block, when a variable is declared as NOT NULL, you must initialize
the variable when it is declared.
A.
B. FALSE
TRUE Q11. To which of the following will an exception raised in the DECLARATION section of
the block B_2 propagate?
<<B_1>>
DECLARE
v_4
v_5
BEGIN
v_4
v_5 NUMBER;
NUMBER;
:= 10;
:= v_4 + 80;
Page 4 of 17 <<B_2>>
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_2)!');
END B_2;
v_4 := v_4 * v_4;
<<B_3>>
DECLARE
v_2 NUMBER := 0;
v_5 NUMBER := v_4;
BEGIN
……
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR (B_3)!');
END B_3;
<<B_4>>
DECLARE
v_1 NUMBER := 3;
BEGIN
……
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR (B_4)!');
END B_4;
……
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('ERROR (B_1)!');
END B_1;
A1.
A2.
A3.
A4.
B1.
B2. The B_1 block’s DECLARATION section
The B_2 block’s DECLARATION section
The B_3 block’s DECLARATION section
The B_4 block’s DECLARATION section
The B_1 block’s EXECUTION section
The B_2 block’s EXECUTION section
Page 5 of 17 B3.
B4.
C1.
C2.
C3.
C4.
D. The B_3 block’s EXECUTION section
The B_4 block’s EXECUTION section
The B_1 block’s EXCEPTION section
The B_2 block’s EXCEPTION section
The B_3 block’s EXCEPTION section
The B_4 block’s EXCEPTION section
None of the above Q12. 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 6..11 LOOP
INSERT INTO tab1 VALUES (i, i*2, i+3);
END LOOP;
SELECT
INTO
FROM COUNT(*)
v_count
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
INTO
FROM COUNT(*)
v_1
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 2..48 LOOP
IF i = 4 OR i = 16 OR i = 47 OR i = 48 THEN
INSERT INTO tab1 VALUES (i*20, i*30, i*40);
END IF; Page 6 of 17 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.
B.
C.
D.
E.
F. 72
73
74
75
76
None of the above Page 7 of 17 Part II (CSC 352 and CSC 452 - 180 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.
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).
Page 8 of 17 ========================= Begin (1a) CSC 352 only ========================
1a) (CSC 352 only) Write a PL/SQL anonymous block that displays top three (3) most popular birthdays
along with the ranks for each month. Sort your output in ascending order by months and
then ranks. You will lose 10 points if the title lines are missing in your output.
You will lose 10 points if your output has an incorrect format. For example, you
must display the birthdays and ranks for the same month in one line.
If you have hard coded the birthdays or ranks (e.g., DBMS_OUPT.PUT_LINE('1
20 240 14 260 27 262') in your PL/SQL block, you will receive 0
points.
Submitting more than one PL/SQL program will receive 0 points. Test your program. You must ensure that the output of your program matches the following
output (one month per line): Output: ========================== End (1a) CSC 352 only ========================
========================== Begin (1b) CSC 452 only =======================
1b) (CSC 452 only) Write a PL/SQL anonymous block that displays three (3) most and three (3) least popular
birthdays along with the ranks for each month. Sort your output in ascending order by
months, most popular birthdays, and then least popular birthdays. Page 9 of 17 You will lose 10 points if the title lines are missing in your output.
You will lose 10 points if your output has an incorrect format. For example, you
must display the birthdays and ranks for the same month in one line.
If you have hard coded the birthdays or ranks (e.g., DBMS_OUPT.PUT_LINE('1 20
240 14 260 27 262')) in your PL/SQL block, you will receive 0 points.
Submitting more than one PL/SQL program will receive 0 points. Test your program. You must ensure that the output of your program matches the following
output (one month per line): ========================== End (1b) CSC 452 only =======================
2) (CSC 352 and CSC 452 - 60 points) ABC Airlines Inc. keeps track of its employees in its Human Resources database. The
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:
PERSON
PERSON_ID LAST_NAME FIRST_NAME NUMBER PK
1000 VARCHAR2(30)
Smith VARCHAR2(30)
Ryan 1170 Brown Dean 2010 Fisher Jane 2080 Brewster Andre 3190 Clark Dan 3500 Jackson Tyler 4000 Miller Mary 4100 Jackson Peter HIRE_DAT
E
DATE
04-MAY90
01-DEC92
12-FEB95
28-JUL98
04-APR01
01-NOV05
11-JAN08
08-AUG11 Page 10 of 17 JOB_TYPE
F JOB_STATU
S
CHAR
I P A F I F A P A F A F A P I CHAR 4200 Smith Ryan 08-DEC12 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 PILOT as shown
below:
PILOT
PERSON_ID
NUMBER PK, FK
1170
2010
3500 PILOT_TYPE
VARCHAR2(100)
Commercial pilot
Airline transport pilot
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 PERSON and PILOT tables by using the following SQL
statements.
CREATE TABLE person
(
person_id
last_name
first_name
hire_date
job_type
job_status
);
/
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
INSERT INTO
COMMIT;
/ person
person
person
person
person
person
person
person
person NUMBER
VARCHAR2(30)
VARCHAR2(30)
VARCHAR2(30)
CHAR
CHAR VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES
VALUES (1000,
(1170,
(2010,
(2080,
(3190,
(3500,
(4000,
(4100,
(4200, PRIMARY KEY,
NOT NULL,
NOT NULL,
NOT NULL,
NOT NULL,
NOT NULL 'Smith', 'Ryan', '04-MAY-90','F', 'I');
'Brown', 'Dean', '01-DEC-92','P', 'A');
'Fisher', 'Jane', '12-FEB-95','F', 'I');
'Brewster', 'Andre', '28-JUL-98', 'F', 'A');
'Clark', 'Dan', '04-APR-01','P', 'A');
'Jackson', 'Tyler', '01-NOV-05', 'F', 'A');
'Miller', 'Mary', '11-JAN-08', 'F', 'A');
'Jackson', 'Peter', '08-AUG-11', 'P','I');
'Smith', 'Ryan', '08-DEC-12', 'F','A'); CREATE TABLE pilot
(
person_id
NUMBER
PRIMARY KEY,
pilot_type
VARCHAR2(100) NOT NULL,
CONSTRAINT fk_person_pilot FOREIGN KEY (person_id)
REFERENCES person(person_id)
);
/
INSERT INTO pilot VALUES (1170, 'Commercial pilot');
INSERT INTO pilot VALUES (2010, 'Airline transport pilot');
INSERT INTO pilot VALUES (3500, 'Airline transport pilot');
COMMIT;
/ Page 11 of 17 Write a PL/SQL anonymous block that accepts a last name (LAST_NAME) from the
user input and displays employee’s information (name, hire date, job type, job status, 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 PERSON table (LAST_NAME), your program
displays information about ALL employees.
If the last name is in the PERSON table (LAST_NAME), your program displays
the corresponding employee’s information. We have duplicate names in the
PERSON 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 job type (“F” or “P”) must be displayed as “Full-Time” or “Part-Time”
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 an incorrect format.
Using different table/column names will receive 0 points.
Submitting more than one PL/SQL program will receive 0 points. Test your program. You must ensure that the output of your program matches the following
sample output: Case 1) Output: Page 12 of 17 Case 2) Output: Case 3) Output: Case 4)
……
3) (CSC 352 and CSC 452 - 60 points)
========================= Begin (3a) CSC 352 only ======================== Page 13 of 17 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, job, hire date, 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 jobs and 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. Hard coding (e.g., IF v_emp_id
= 7596 THEN v_1 := ...) 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 has an incorrect format.
Submitting more than one PL/SQL program will receive 0 points. 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: Page 14 of 17 Case 3) Output: Case 4) Page 15 of 17 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 the names and hire dates of their corresponding managers (If an employee
does not have a manager, the manager name and hire date are shown as “------” in
your output.). Sort your output by days of the week (Monday, Tuesday, …, Friday) and
the hire date. Hints:
(1)
(2)
(3)
(4) You will lose 10 points if the title lines are missing in your output.
You will lose 10 points if your output has an incorrect format.
Hard coding (e.g., IF v_day = 'Thursday' OR v_day = 'Friday'
OR v_max_num = 4 THEN …) will receive 0 points.
Submitting more than one PL/SQL program will receive 0 points. TO_CHAR(hire_date, 'Day')
TRIM(TO_CHAR(hire_date, 'Day'))
TRIM(TO_CHAR(hire_date, 'D')
GROUP BY TO_CHAR(hire_date, 'Day') The output of your program must match the following: Page 16 of 17 ========================= End (3b) CSC 452 only ======================== Page 17 of 17