Halaman

Tuesday, August 20, 2019

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


Section 9


Which of the following is found in a function and not a procedure?  
                                                                               
                                               
                Return statement in the header (*)

                                               
                An exception section


                                               
                IN parameters

                                               
                Local variables in the IS/AS section

                                                                               
                                                               
To create a function successfully, the following steps should be performed.
A   Re-execute the code until it compiles correctly
B   Write the code containing the CREATE or REPLACE FUNCTION followed by the function code
C   Test the function from a SQL statement or an anonymous block
D   If the function fails to compile, correct the errors
E   Load the code into Application Express
F   Execute the code in Application Express

What is the correct order to perform these steps?

                                                                               
                                               
                D,B,E,F,A,C

                                               
                A,B,E,F,D,C

                                               
                B,E,F,D,A,C (*)

                                               
                B,C,E,F,D,A

                                                                               
                                                               
To create a function successfully,the first step is to test the code in an anonymous block.               
                                                                               
                                               
                True

                                               
                False (*)

                                                                               
Which statement is true regarding the following subprogram?
PROCEDURE at_proc IS
 PRAGMA AUTONOMOUS_TRANSACTION;
 dept_id NUMBER := 90;
 BEGIN
   UPDATE ...
   INSERT ...
 END at_proc;
                                                                               
                                               
                The subprogram's success depends on the calling program.

                                               
                The subprogram's success is independent of the calling program. (*)

                                               
                The subprogram cannot do a COMMIT.

                                               
                The subprogram will fail because the RETURN is not specified.

                                                                               
                                                               
How do you specify that you want a procedure MYPROCA to use Invoker's Rights?         
                                                                               
                                               
                CREATE OR REPLACE PROCEDURE myproca
AUTHID OWNER IS...

                                               
                CREATE OR REPLACE PROCEDURE myproca
AUTHID CURRENT_USER IS...
(*)


                                               
                Invoker's Rights are the default, therefore no extra code is needed.

                                               
                ALTER PROCEDURE myproca TO INVOKER;

                                               
                GRANT INVOKER TO myprocA;


You want to allow user JOE to query the CD_DETAILS table in your schema. Which command should you use?
                                                 
                                               
                GRANT SELECT TO joe ON cd_details;

                                               
                GRANT QUERY ON cd_details TO joe;

                                               
                GRANT cd_details TO joe;

                                               
                GRANT SELECT ON joe TO cd_details;

                                               
                GRANT SELECT ON cd_details TO joe; (*)

                                                                               
                                                               
When a database object is first created, only its owner (creator) and the Database Administrator are privileged to use it. True or False?     
                                                                               
                                               
                True (*)

                                               
                False

                                                                               
                                                               
What is one of the main purposes of the Data Dictionary?   
                                                                               
                                               
                To prevent users from accidentally dropping tables

                                               
                To ensure correct spelling of the values in VARCHAR2 table columns

                                               
                To provide a list of all objects in your schema, but not in other users' schemas

                                               
                To translate data from one language to another

                                               
                To provide a structured list of all objects in the database (*)

                                                                               
                                                               
User BOB is not a database administrator. BOB wants to see the names of all the tables in his schema, as well as all the tables in other users' schemas which he has privileges to use. Which Data Dictionary view would BOB query to do this?     
                                                                               
                                               
                USER_TABLES

                                               
                DBA_TABLES

                                               
                None of these.

                                               
                ALL_TABLES (*)

                                               
                USER_TAB_COLUMNS

                                                                               
                                                               
You have forgotten the name of the Dictionary view USER_TABLES. Which of the following statements is the best and quickest way to remind yourself?   
                                                                               
                                               
                SELECT * FROM dict
WHERE table_name LIKE 'USER%TAB%';
(*)


                                               
                Phone the database administrator.

                                               
                Read the online Oracle documentation at http://technet.oracle.com.

                                               
                SELECT * FROM dictionary
WHERE table_name = 'USER_TABLES';

                                               
                SELECT * FROM dictionary
WHERE table_name = 'USER%';



The database administrator has granted the DROP ANY PROCEDURE privilege to user KIM. This allows Kim to remove other users' procedures and functions from the database. How would Kim now drop function GET_EMP, which is owned by user MEHMET?  
                                                                               
                                               
                None of these

                                               
                DROP FUNCTION mehmet.get_emp (*)

                                               
                DROP FUNCTION get_emp FROM mehmet

                                               
                DROP PROGRAM mehmet.get_emp

                                               
                DROP PROCEDURE mehmet.get_emp

                                                                               

Which view would you query to see the detailed code of a procedure?  
                                                                               
                                               
                user_source (*)

                                               
                user_errors

                                               
                user_objects

                                               
                user_procedures

                                               
                user_dependencies

                                                                               
                                                               
The following function has been created:
CREATE OR REPLACE FUNCTION upd_dept
    (p_dept_id IN departments.department_id%TYPE)
    RETURN NUMBER IS
BEGIN
    UPDATE departments
    SET department_name = 'Accounting'
       WHERE department_id = p_dept_id;
    RETURN p_dept_id;
END;

Which of the following will execute successfully?

                                                                               
                                               
                DELETE FROM employees
WHERE department_id = upd_dept(80);
(*)


                                               
                DELETE FROM departments
WHERE department_id = upd_dept(department_id);

                                               
                SELECT upd_dept(department_id)
FROM employees;

                                               
                SELECT upd_dept(80)
FROM dual;

                                                                               

Why will the following statement fail?
SELECT employee_id, tax(p_value => salary)
  FROM employees;

                                                                               
                                               
                User-defined functions are not allowed in the SELECT clause.

                                               
                The statement will execute and not fail.

                                               
                The data type for the tax variable does not match the data type for salary.

                                               
                Name notation is not allowed. (*)

                                                                               
                                                               
A benefit of user-defined functions is that the function can accept any SQL or PL/SQL data type. True or False?      
                                                                               
                                               
                True

                                               
                False (*)

No comments:

Post a Comment

Final Exam Java Programming 2019 Learner - English

Final Exam