Halaman

Monday, August 26, 2019

Answer Section 12 Quiz Database Programming with PL/SQL 2019 Learner - English


Section 12


The following procedure adds a column of datatype DATE to the EMPLOYEES table. The name of the new column is passed to the procedure as a parameter.
CREATE OR REPLACE PROCEDURE addcol
    (p_col_name IN VARCHAR2) IS
    v_first_string VARCHAR2(100) := 'ALTER TABLE EMPLOYEES ADD (';
    v_second_string VARCHAR2(6) := ' DATE)';
BEGIN
    ... Line A
END;


Which of the following will work correctly when coded at line A? (Choose two.)


                                                                               
                                                (Choose all correct answers)      
                                                                               
                                               
                EXECUTE IMMEDIATE v_first_string || p_col_name || v_second_string;
(*)


                                               
                EXECUTE IMMEDIATE 'v_first_string' || p_col_name || 'v_second_string';

                                               
                EXECUTE v_first_string || p_col_name || v_second_string;

                                               
                v_first_string := v_first_string || p_col_name;
EXECUTE IMMEDIATE v_first_string || v_second_string;
(*)


                                               
                v_first_string || p_col_name || v_second_string;

                                                                               
                                                               
You want to create a function which drops a table. You write the following code:
CREATE OR REPLACE FUNCTION droptab
    (p_tab_name IN VARCHAR2)
RETURN BOOLEAN IS
BEGIN
    DROP TABLE p_tab_name;
    RETURN TRUE;
EXCEPTION
    WHEN OTHERS THEN RETURN FALSE;
END;

Why will this procedure not compile successfully?

                                                                               
                                               
                Because you cannot use RETURN in the exception section

                                               
                Because the PL/SQL compiler cannot check if the argument of p_tab_name is a valid table-name (*)

                                               
                Because you can never drop a table from inside a function

                                               
                Because you do not have the privilege needed to drop a table

                                                                               
                                                               
Name two reasons for using Dynamic SQL.          
                                                                               
                                                (Choose all correct answers)      
                                                                               
                                               
                Creates a SQL statement with varying column data, or different conditions (*)

                                               
                Enables data-definition statements to be written and executed from PL/SQL (*)

                                               
                Enables system control statements to be written and executed from PL/SQL

                                               
                Avoids errrors at compile time of DML statements

                                                                               
                                                               
Which of the following SQL statements can be included in a PL/SQL block only by using Dynamic SQL? (Choose two.)   
                                                                               
                                                (Choose all correct answers)      
                                                                               
                                               
                DELETE

                                               
                SAVEPOINT

                                               
                ALTER (*)

                                               
                SELECT ..... FOR UPDATE NOWAIT

                                               
                GRANT (*)

                                                                               
                                                               
A public packaged procedure contains the following SQL statement:
UPDATE employees
SET salary = salary * 1.1;
When is this SQL statement parsed?      
                                                                               
                                               
                When the package header is loaded into memory

                                               
                When the package is loaded into memory

                                               
                When the package body is created (*)

                                               
                Only the first time the procedure is executed

                                               
                When the package specification is created


Examine the following procedure, which drops a table whose name is passed as an IN parameter:
CREATE OR REPLACE PROCEDURE drop_tab
    (p_table_name IN VARCHAR2) IS
    v_sql_statement VARCHAR2(100);
BEGIN
    ...
END;

Which of the following will work correctly when coded in the procedure's executable section? (Choose two.)

                                                         
                                                (Choose all correct answers)      
                                                                               
                                               
                v_sql_statement := 'DROP TABLE ';
EXECUTE IMMEDIATE v_sql_statement p_table_name;

                                               
                EXECUTE IMMEDIATE 'DROP TABLE ' || p_table_name;
(*)


                                               
                EXECUTE IMMEDIATE 'DROP TABLE p_table_name';

                                               
                v_sql_statement := 'DROP TABLE ' || p_table_name;
EXECUTE IMMEDIATE v_sql_statement;
(*)


                                               
                v_sql_statement := 'DROP TABLE ' || p_table_name;
EXECUTE IMMEDIATE 'v_sql_statement';

                                                                               
                                                               
The easiest way to include DDL statements in a PL/SQL block is to use the DBMS_SQL package. True or False? 
                                                                               
                                               
                True

                                               
                False (*)

                                                                               
                                                               
What does the RETURNING clause do in the example below?
CREATE OR REPLACE PROCEDURE new_dept
  (p_dept_name IN departments.name%TYPE) IS
  v_new_dept_id departments.dept_id%TYPE;
BEGIN
  INSERT INTO departments (dept_id, name)
    VALUES dept_seq.NEXTVAL, p_dept_name
    RETURNING dept_seq.CURRVAL INTO v_new_dept_id;
  DBMS_OUTPUT.PUT_LINE(p_dept_name ||' is department number ' || v_new_dept_id);
END new_dept;
                                                                               
                                               
                Inserts the new department id in the department table

                                               
                Performs the SELECT statement to determine the department id of the new department (*)

                                               
                Uses the new department number in a cursor

                                                                               
                                                               
What is wrong with this code example?
CREATE OR REPLACE PROCEDURE insert_emps IS
  TYPE t_emp IS TABLE OF employees%ROWTYPE INDEX BY BINARY_INTEGER;
   v_emptab t_emp;
BEGIN
   FORALL i IN v_emptab.FIRST..v_emptab.LAST
     INSERT INTO employees VALUES v_emptab(i);
   END LOOP;
END insert_emps;
                                                             
                                               
                The phrase should be FOR ALL.

                                               
                Nothing is wrong; it will compile successfully.

                                               
                FORALL does not require END LOOP. (*)

                                               
                v_emptab is incorrectly typed.

                                                                               
                                                               
Which of the following are NOT benefits of using the NOCOPY hint? (Choose two)     
                                                                               
                                                (Choose all correct answers)      
                                                                               
                                               
                Faster because a single copy of the data is used

                                               
                Eliminates extra processing

                                               
                Uses a larger block of server memory for faster access (*)

                                               
                Safer because it uses passing by value (*)

                                               
                Efficient since it uses less memory


In the following example, where do you place the phrase DETERMINISTIC?
CREATE OR REPLACE FUNCTION total_sal
  (p_dept_id IN -- Position A
employees.department_id%TYPE)
  RETURN NUMBER -- Position B
    IS v_total_sal NUMBER;
BEGIN
  SELECT SUM(salary) INTO v_total_sal
    FROM employees WHERE department_id = p_dept_in;
  RETURN v_total_sal -- Position C;
END total_sal;

                                                                               
                                               
                Position A

                                               
                Position B (*)

                                               
                Position C

                                                                               
                                                               
In the following example, where do you place the phrase BULK COLLECT?
...
BEGIN
  SELECT -- Position A
     salary -- Position B
     INTO v_saltab -- Position C
     FROM employees WHERE department_id = 20 ORDER BY salary
     -- Position D
;
...

                                                                               
                                               
                Position A

                                               
                Position B (*)

                                               
                Position C

                                               
                Position D

                                                                               
                                                               
What is the main purpose for using the RETURNING clause?   
                                                                               
                                               
                Return more readily any exceptions that are raised by the statement

                                               
                Improve performance by minimizing the number of statements

                                               
                Improve performance by returning a single value

                                               
                Improve performance by making one call to the SQL engine (*)

                                                                               
                                                               
What is the correct syntax to use the RETURNING phrase at Position A?
DECLARE
TYPE EmpRec IS RECORD (last_name employees.last_name%TYPE, salary employees.salary%TYPE);
emp_info EmpRec;
emp_id NUMBER := 100;
BEGIN
  UPDATE employees
  SET salary = salary * 1.1 WHERE employee_id = emp_id
-- Position A
  dbms_output.put_line('Just gave a raise to ' || emp_info.last_name || ', who now makes ' || emp_info.salary);
END;
                                                         
                                               
                RETURNING FROM emp_info;

                                               
                last_name, salary RETURNING INTO emp_info;

                                               
                RETURNING last_name, salary TO emp_info;

                                               
                RETURNING last_name, salary INTO emp_info; (*)

                                                                               
                                                               
Where would you place the BULK COLLECT statement in the following example?
DECLARE
 TYPE DeptRecTab IS TABLE OF departments%ROWTYPE;
 dept_recs DeptRecTab;
CURSOR c1 IS
SELECT department_id, department_name, manager_id, location_id
  -- Position A
  FROM departments
  WHERE department_id > 70;
BEGIN
 OPEN c1
  -- Position B;
 FETCH c1
  -- Position C
 INTO dept_recs;
END;
 Mark for Review
(1) Points
                                                                               
                                               
                Position A

                                               
                Position B

                                               
                Position C (*)

No comments:

Post a Comment

Final Exam Java Programming 2019 Learner - English

Final Exam