Oracle Preparatory Exam-1

40 Questions

Settings
Please wait...
Oracle Quizzes & Trivia

Questions and Answers
  • 1. 
    Examine this package specification and body: CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; FUNCTION get_budget (v_studio_id IN NUMBER) RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; RETURN v_yearly_budget; END get_budget; END theater_pck; Which will successfully invoke the FIND_SEATS_SOLD procedure within SQL*Plus?
    • A. 

      EXECUTE find_seats_sold (500,11);

    • B. 

      Theater_pck.find_seats_sold (500,11);

    • C. 

      EXECUTE theater_pck.find_seats_sold (500,11);

    • D. 

      SELECT find_seats_sold(movie_id, theater_id) FROM gross_receipt;

  • 2. 
    Examine this package specification and body: CREATE OR REPLACE PACKAGE theater_pck IS PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER); END theater_pck; CREATE OR REPLACE PACKAGE BODY theater_pck IS current_avg_cost_per_ticket NUMBER; PROCEDURE find_seats_sold (v_movie_id IN NUMBER DEFAULT 34, v_theater_id IN NUMBER) IS v_seats_sold gross_receipt.seats_sold%TYPE; v_budget studio.yearly_budget%TYPE; BEGIN SELECT seats_sold INTO v_seats_sold FROM gross_receipt WHERE movie_id = v_movie_id AND theater_id = v_theater_id; END find_seats_sold; FUNCTION get_budget (v_studio_id IN NUMBER) RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; RETURN v_yearly_budget; END get_budget; END theater_pck; Which code will successfully assign a value to the CURRENT_AVG_COST_PER_TICKET variable within SQL*Plus?
    • A. 

      BEGIN current_avg_cost_per_ticket := 6.75; END;

    • B. 

      BEGIN theater_pck.current_avg_cost_per_ticket := 6.75; END;

    • C. 

      BEGIN SELECT AVG(cost_per_ticket) INTO current_avg_cost_per_ticket FROM gross_receipt; END;

    • D. 

      This variable is private to the package and cannot be directly assigned a value within SQL*Plus.

  • 3. 
    You decide to use packages to logically group related programming constructs. Which two types of constructs can be grouped within a package? (Choose two.)
    • A. 

      View

    • B. 

      Database trigger

    • C. 

      Cursor

    • D. 

      Application trigger

    • E. 

      Variable

  • 4. 
    When a database trigger routine does not have to take place before the triggering event, which timing should you assign to the trigger?
    • A. 

      ON

    • B. 

      OFF

    • C. 

      AFTER

    • D. 

      BEFORE

  • 5. 
    Which event occurs when a database trigger fails?
    • A. 

      The DML event that invoked the database trigger is committed

    • B. 

      The DML is successful. Each processed row is marked invalid.

    • C. 

      The DML is successful. The modified table is marked invalid

    • D. 

      The DML event that invoked the database trigger is rolled back.

  • 6. 
    Which two statements are true about  NULL values?
    • A. 

      A) You cannot search for a NULL value in a column using the WHERE clause.

    • B. 

      B) If a NULL value is returned in the subquery or if NULL is included in the list when using a NOT IN operator, no rows will be returned

    • C. 

      C) Only = and != operators can be used to search for NULL values in a column.

    • D. 

      D) In an ascending order sort, NULL values appear at the bottom of the result set.

    • E. 

      E) Concatenating a NULL value to a non- NULL string results in a NULL

  • 7. 
    DBMS_FLASHBACK.GET_SYSTEM.CHANGE_NUMBER helps to obtain current SCN at my time. 
    • A. 

      True

    • B. 

      False

  • 8. 
    What is true about stored procedures? 
    • A. 

      A stored procedure uses the DELCLARE keyword in the procedure specification to declare formal parameters.

    • B. 

      A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification.

    • C. 

      . A stored procedure must have at least one executable statement in the procedure body.

    • D. 

      A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters.

  • 9. 
    Consider the PL/SQL BLOCK   Begin i=0 While i<=3 loop ----- End loop How many times this loop will be executed?
    • A. 

      4

    • B. 

      3

    • C. 

      Error in code

    • D. 

      No output

  • 10. 
    Evaluate this PL/SQL block. BEGIN FOR i IN 1..10 LOOP IF I=4 OR I=6 THEN null; ELSE INSERT INTO test(result) VALUES (I) ; END IF; COMMIT; END LOOP; ROLL BACK; END. How many values will be inserted into the TEST table?
    • A. 

      8

    • B. 

      0

    • C. 

      4

    • D. 

      6

    • E. 

      10

  • 11. 
    Which aggregate function is valid on the START_DATE column? (START_DATE datatype DATE)
    • A. 

      SUM(start_date)

    • B. 

      AVG(start_date)

    • C. 

      COUNT(start_date)

    • D. 

      AVG(start_date, end_date)

    • E. 

      MAXIMUM(start_date)

  • 12. 
    Which of the following correctly shows the correct use of the TRUNC command on a date?
    • A. 

      SELECT TRUNC(TO_DATE(12-Feb-99,DD-MON-YY, 'YEAR')) "Date " FROM DUAL;

    • B. 

      TRUNC = TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR', "Date " FROM DUAL;

    • C. 

      SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;

    • D. 

      Date = TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;

  • 13. 
    The STUDENT_GRADES table has these columns: STUDENT_ID NUMBER(12) SEMESTER_END DATE GPA NUMBER(4,3) Which statement finds the highest grade point average (GPA) per semester?
    • A. 

      SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL;

    • B. 

      SELECT (gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

    • C. 

      SELECT MAX(gpa) FROM student_grades WHERE gpa IS NOT NULL GROUP BY semester_end;

    • D. 

      SELECT MAX(gpa) GROUP BY semester_end WHERE gpa IS NOT NULL FROM student_grades;

    • E. 

      SELECT MAX(gpa) FROM student_grades GROUP BY semester_end WHERE gpa IS NOT NULL;

  • 14. 
    Which three statements correctly describe the functions and use of constraints? (Choose three)
    • A. 

      Constraints enforce rules at the view level

    • B. 

      Constraints provide data independence

    • C. 

      Constraint make complex queries easy

    • D. 

      Constraints enforce rules at the table level

    • E. 

      Constraints prevent the deletion of an index if there are dependencies

    • F. 

      Constraints prevent the deletion of a table if there are dependencies

  • 15. 
    Which three are true statements about dependent objects?
    • A. 

      Invalid objects cannot be described.

    • B. 

      An object with status of invalid cannot be a referenced object.

    • C. 

      All schema objects have a status that is recorded in the data dictionary.

    • D. 

      You can view whether an object is valid or invalid in the USER_STATUS data dictionary view.

    • E. 

      The Oracle server automatically records dependencies among objects.

    • F. 

      You can view whether an object is valid or invalid in the USER_OBJECTS data dictionary view

  • 16. 
    Which two statements describe the state of a package variable after executing the package in which it is declared? 
    • A. 

      It persists across transactions within a session.

    • B. 

      It persists from session to session for the same user.

    • C. 

      It does not persist across transaction within a session.

    • D. 

      It persists from user to user when the package is invoked.

    • E. 

      It does not persist from session to session for the same user.

  • 17. 
    Which code can you use to ensure that the salary is not increased by more than 10% at a time nor is it ever decreased? 
    • A. 

      CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END;

    • B. 

      CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END;

    • C. 

      CREATE OR REPLACE TRIGGER check_sal AFTER UPDATE OR sal ON emp WHEN (new.sal < old.sal OR -new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, 'Do not decrease salary not increase by more than 10%'); END;

  • 18. 
    Which statements are true? (Choose all that apply) 
    • A. 

      If errors occur during the compilation of a trigger, the trigger is still created.

    • B. 

      If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_TRIGGERS data dictionary view to see the compilation errors.

    • C. 

      If errors occur during the compilation of a trigger you can use the SHOW ERRORS command within iSQL *Plus to see the compilation errors

    • D. 

      If errors occur during the compilation of a trigger you can go into SQL *Plus and query the USER_ERRORS data dictionary view to see compilation errors.

  • 19. 
    Examine this database trigger: CREATE OR REPLACE TRIGGER prevent_gross_modification {additional trigger code} BEGIN IF TO_CHAR(sysdate,'DY') = 'MON' THEN RAISE_APPLICATION_ERROR(-20000, 'Gross receipts cannot be entered on Monday'); END IF; END; This trigger must fire before each DELETE, INSERT, and UPDATE of the GROSS_RECEIPT table. It should fire only once for the entire data manipulation statement. Which additional trigger code must you add?
    • A. 

      BEFORE (gross_receipt) DELETE, INSERT, UPDATE

    • B. 

      BEFORE DELETE OR INSERT OR UPDATE ON gross_receipt FOR EACH ROW

    • C. 

      AFTER DELETE OR INSERT OR UPDATE ON gross_receipt

    • D. 

      BEFORE DELETE OR INSERT OR UPDATE ON gross_receipt

  • 20. 
    • A. 

      VARIABLE g_yearly_budget NUMBER :g_yearly_budget := GET_BUDGET(11);

    • B. 

      VARIABLE g_yearly_budget NUMBER EXECUTE g_yearly_budget := GET_BUDGET(11);

    • C. 

      VARIABLE g_yearly_budget NUMBER EXECUTE :g_yearly_budget := GET_BUDGET(11);

    • D. 

      VARIABLE :g_yearly_budget NUMBER EXECUTE :g_yearly_budget := GET_BUDGET(11);

  • 21. 
    How do functions simplify maintainability?
    • A. 

      By limiting changes to logic to one location

    • B. 

      By eliminating the need for an executable section

    • C. 

      By eliminating the need for the storage of object code

    • D. 

      By ignoring side effects when invoked from within a SQL statement

  • 22. 
    Examine this function: CREATE OR REPLACE FUNCTION get_budget RETURN number IS v_yearly_budget NUMBER; BEGIN SELECT yearly_budget INTO v_yearly_budget FROM studio WHERE id = v_studio_id; RETURN v_yearly_budget; END; What additional code is needed to compile this function successfully?
    • A. 

      Remove the first RETURN statement.

    • B. 

      Change "RETURN number" to "OUTPUT number".

    • C. 

      Add "(v_studio_id IN NUMBER)" right after the IS keyword.

    • D. 

      Add "(v_studio_id IN NUMBER)" right before the RETURN statement of the header.

  • 23. 
    Evaluate this SELECT statement: SELECT * FROM user_dependencies WHERE referenced_name = 'EMPLOYEE'; The EMPLOYEE table is in your schema. Which result will this statement accomplish?
    • A. 

      Displays all the direct dependencies on the EMPLOYEE table

    • B. 

      Displays only the direct dependencies you created on the EMPLOYEE table

    • C. 

      Displays the name of the indirect dependencies on the EMPLOYEE table

    • D. 

      Displays the OWNER column to indicate the schema of any referenced objects

  • 24. 
    Which three tables or views could you use to help track dependencies?
    • A. 

      DEPTREE

    • B. 

      IDEPTREE

    • C. 

      DEPENDENCIES

    • D. 

      USER_OBJECTS

    • E. 

      USER_DEPENDENCIES

  • 25. 
    You have just moved a stand-alone function into a package. What could you add to the package specification to check the purity level of this function?
    • A. 

      PRAGMA PURITY_CHECK

    • B. 

      PRAGMA EXCEPTION_INIT

    • C. 

      PRAGMA FUNCTION_PURITY

    • D. 

      PRAGMA RESTRICT_REFERENCES

  • 26. 
    When using a PL/SQL stored package, which statement about side effects is true?
    • A. 

      Side effects are changes to database tables or public packaged variables declared in the package specification.

    • B. 

      Side effects do not delay the execution of a query.

    • C. 

      All side effects are allowed when a function is called from a SQL query or DML statement

    • D. 

      Side effects are the yield order-dependent results of a function called from a SQL statement.

  • 27. 
    Which three statements about restrictions on package functions used in SQL?
    • A. 

      A function called from a DML statement cannot read or modify the particular table being modified by the DML statement.

    • B. 

      A function called from a query statement or from a parallelized DML statement cannot execute a DML statement or modify the database.

    • C. 

      A function called from a query or DML statement cannot end the current transaction, create or roll back a savepoint, or alter the system or session.

    • D. 

      A function called from a DML statement can read or modify the particular table being modified by the DML statement.

    • E. 

      A function called from a query statement or from a parallelized DML statement can execute a DML statement or modify the database.

    • F. 

      A function called from a query or DML statement can end the current transaction, create or roll back a savepoint, or alter the system or session.

  • 28. 
    Which two statements about package overloading are true?
    • A. 

      The subprograms must be local.

    • B. 

      The subprograms can be local or remote.

    • C. 

      It allows you to exceed the maximum number of subprograms

    • D. 

      Two subprograms with the same name must differ only in return type.

    • E. 

      Two subprograms with the same name and number of formal parameters must have at least one parameter defined with a different data type.

  • 29. 
    Examine this package specification and execution: CREATE OR REPLACE PACKAGE prod_pack IS PROCEDURE order_product (p1 IN NUMBER, p2 IN CHAR); PROCEDURE order_product (p3 IN NUMBER, p4 IN VARCHAR2); END prod_pack; When executing this packaged procedure, you receive this message: PLS-00307: too many declarations of 'ORDER_PRODUCT' match this call What caused this error?
    • A. 

      Each procedure must have a unique name.

    • B. 

      The names of the arguments must be identical.

    • C. 

      Each argument name must be unique because there are the same number of arguments.

    • D. 

      At least one argument must be in a different data type family because there are the same number of arguments.

  • 30. 
    Management wants to proposes hike of salary of employees by 1000. John is needed to create a report regarding this?
    • A. 

      Create a view

    • B. 

      Creates a new table

    • C. 

      Add new column

    • D. 

      Create a query by adding 1000 to sal column

  • 31. 
    Which of the following is true
    • A. 

      Lgwr moves data from logbuffer to logfiles

    • B. 

      Lgwr moves data from logfiles to logbuffer

    • C. 

      Dbwr moves data from logbuffer to logfiles

    • D. 

      Lgwr moves data from logbuffer to archivefiles

  • 32. 
    Which of the following mechnism is used for read consistency in oracle
    • A. 

      Archive files

    • B. 

      Redo

    • C. 

      Undo

    • D. 

      Temp

  • 33. 
    Which three are valid ways to minimize dependency failure? 
    • A. 

      Querying with the SELECT * notification.

    • B. 

      Declaring variables with the %TYPE attribute.

    • C. 

      Specifying schema names when referencing objects.

    • D. 

      Declaring records by using the %ROWTYPE attribute.

    • E. 

      Specifying package.procedure notation while executing procedures.

  • 34. 
    Which statement about triggers is true?
    • A. 

      You use an application trigger to fire when a DELETE statement occurs.

    • B. 

      You use a database trigger to fire when an INSERT statement occurs.

    • C. 

      You use a system event trigger to fire when an UPDATE statement occurs.

    • D. 

      You use INSTEAD OF trigger to fire when a SELECT statement occurs

  • 35. 
    When creating a function in SQL *Plus, you receive this message: Warning: Function created with compilation errors.. Which command can you issue to see the actual error message?
    • A. 

      SHOW FUNCTION_ERROR

    • B. 

      SHOW USER_ERRORS

    • C. 

      SHOW ALL_ERRORS

    • D. 

      SHOW ERRORS

  • 36. 
    Examine this code: CREATE OR REPLACE TRIGGER update_emp AFTER UPDATE ON emp BEGIN INSERT INTO audit_table (who, dated) VALUES (USER, SYSDATE); END; You issue an UPDATE command in the EMP table that results in changing 10 rows. How many rows are inserted into the AUDIT_TABLE ?
    • A. 

      1

    • B. 

      10

    • C. 

      None

    • D. 

      A value equal to the number of rows in the EMP table.

  • 37. 
    You have created a stored procedure DELETE_TEMP_TABLE that uses dynamic SQL to remove a table in your schema. You have granted the EXECUTE privilege to user A on this procedure. When user A executes the DELETE_TEMP_TABLE procedure, under whose privileges are the operations performed by default?
    • A. 

      SYS privileges

    • B. 

      Your privileges

    • C. 

      Public privileges

    • D. 

      User A's privileges

    • E. 

      User A cannot execute your procedure that has dynamic SQL.

  • 38. 
    You have a stored function called SHIP_DOCK with no parameters that returns BOOLEAN in a schema MSALZBERG, which owns no stored procedures. Next, you issue this command: CREATE OR REPLACE PROCEDURE SHIP_DOCK(p_ship_id NUMBER) IS BEGIN   UPDATE SHIPS SET STATUS = 'DOCK' WHERE SHIP_ID = p_ship_id; END SHIP_DOCK; Which of the following statements best describes the result?
    • A. 

      The procedure is created, stored in the data dictionary, and given a status of INVALID. The function remains in the data dictionary.

    • B. 

      The procedure is created, stored in the data dictionary, and given a status of VALID.

    • C. 

      The ORA-00955 message "name is already used by an existing object" appears.

    • D. 

      The procedure is created, stored in the data dictionary, and given a status of INVALID. The function no longer exists in the data dictionary

  • 39. 
    When Select…into or any DML statement is issued , oracle internally opens the cursor. This is called implicit cursor. It is named as ‘SQL’.
    • A. 

      True

    • B. 

      False

  • 40. 
    Which three features are provided by Oracle Net Services? 
    • A. 

      Data encryption

    • B. 

      User authentication

    • C. 

      Transparent data conversion

    • D. 

      Configuration and administration mechanisms

    • E. 

      Support for multiple network transport protocols