1. You need to change the job title “Secretary” to “Administrative Assistant” for all secretaries. Which of the following statements accomplishes this?
a. UPDATE worker
b. UPDATE worker
Job := ‘Administrative Assistant’
WHERE UPPER(job) = ‘Secretary’
c. UPDATE worker
SET job = ‘Administrative Assistant’
WHERE UPPER(job) = ‘Secretary’;
d. UPDATE worker
SET values job = ‘Administrative Assistant’
WHERE UPPER(job) = ‘Secretary’;
2. You must remove all data from the color table while leaving the table definition intact. What command should you issue, if you must be able to undo the operation?
a. DROP TABLE color.
b. DELETE FROM color.
c. TRUNCATE TABLE color.
d. This cannot be done.
3. In which section of a PL/SQL block is a user-defined exception raised?
a. Heading.
b. Execution.
c. Declarative.
d. Exception handling.
4. In nesting blocks, ______.
a. A variable name must be unique between blocks.
b. A variable defined in the outer block is visible in the inner blocks.
c. A variable defined in the inner block is visible in the outer blocks.
d. A variable in an inner block may have the same name as a variable in an outer block if the data types are different.
5. Examine the following:
1. DECLARE
2. i NUMBER := 0;
3. x_date DATE ;
4. BEGIN
5. i := i + 1;
6. LOOP
7. i := v_date + 5;
8. i := i + 1;
9. EXIT WHEN i = 5;
10. END LOOP;
11. END
You encounter unexpected results while executing the above code. How can you trace the values of counter variable 1 and date variable x_date in an SQL* PLUS environment?
a. Set the SQL* PLUS session variable DEBUGGER=TRUE.
b. Insert the statement:
DBMS_OUTPUT .PUT_LINE (I, x_date);
Between lines 8-9
c. Insert the statement:
DBMS_OUTPUT .DEBUG_VAR (I, x_date);
Between lines 8-9
d. Insert the statement:
DBMS_OUTPUT .PUT_LINE (I ||’’|| TO_CHAR(v_date));
Between lines 8-9
6. Examine the following:
SET SERVEROUTPUT ON
DECLARE
v_name worker.wname%TYPE;
v_num NUMBER;
v_sal NUMBER(8,2);
BEGIN
--- This code displays salaries if larger than 10,000.
SELECT wname, sal
INTO v_name, v_sal
FROM worker
WHERE workerno=101;
IF(v_sal.GT.10000) THEN
DBMS_OUTPUT.PUT_LINE(‘Salary is ‘||’ v_sal
|| ‘for worker’ || v_name);
END IF;
END
SET SERVER OUTPUT OF
This statement produces a compilation error when which PL/SQL block is executed?
a. v_num NUMBER;
b. v_name NUMBER;
c. IF (v_sal.GT.10000) THEN
d. --- This code displays salaries if larger than 10,000.
7. You query the worker database with this command:
SELECT last_name, first_name
FROM worker
WHERE SALARY_IN
(SELECT salary)
FROM worker
WHERE division_no=3 OR division_no=5);
Which values are displayed?
a. Last name and first name of workers in division numbers 3 and 5.
b. Last name and first name of all workers except those working in divisions 3 and 5.
c. Last name and first name of all workers with the same salary as workers in divisions 3 and 5.
d. Last name and first name of workers whose salaries fall in the range of salaries in divisions 3 and 5.
8. Which operator is not appropriate in the joined condition of a non-equijoin select statement?
a. In operators.
b. Like operators
c. Equal operators.
d. Greater than and equal to operators.
9. The worker table has 11 columns. You often query the table with conditions based on four or more columns. You create an index on all columns in the table. What will result?
a. Updates on the table will be slower.
b. Updates on the table will be faster.
c. Queries on the table will be faster.
d. The size of the worker table will be increased.
10. You issue the following command:
CREATE PUBLIC SYNONYM work
FOR ed, worker;
Which task has been accomplished?
a. The object is now accessible to all users.
b. All users are given object privileges to the table.
c. The need to qualify object names with the schema is eliminated only for you.
d. The need to qualify object names with the schema is eliminated for all users.
