Oracle Mock 2

40 Questions

Settings
Please wait...
Oracle Quizzes & Trivia

A view A trigger target A mutating table A constraining table


Questions and Answers
  • 1. 
    The ALTER FUNCTION statement can be used to do which of the following? (Choose all that apply.)
    • A. 

      Modify the list of parameters for a function

    • B. 

      Change the program unit's processing section.

    • C. 

      Recompile the program unit's code

    • D. 

      Create an overloaded function

  • 2. 
    You have created a trigger with the following header: TRIGGER TRIG1 AFTER DELETE ON PURCHASE_ORDER FOR EACH ROW For the purpose of this trigger's processing section, PURCHASE_ORDER is considered
    • A. 

      A view A view

    • B. 

      A trigger target

    • C. 

      A mutating table

    • D. 

      A constraining table

  • 3. 
    You have stored a function called GET_STATUS in the database with the following header: FUNCTION GET_STATUS (p_order_id NUMBER, p_option VARCHAR2 DEFAULT NULL) RETURN VARCHAR2 IS Now you want to create a new procedure and store it in the database. Assuming you've declared all referenced variables, which of the following statements are syntactically acceptable for your new procedure? (Choose all that apply
    • A. 

      V_status := v_priority ∥ get_status(15, NULL);

    • B. 

      V_status := get_status(15);

    • C. 

      V_status := get_status(NULL);

    • D. 

      V_status := get_status(ABS(v_order_id), ‘FLAG’);

  • 4. 
    Your schema owns a procedure called ADD_PORT. You know that the procedure references a table in your schema, but you don't know which one. Which of the following data dictionary views can identify the table? (Choose all that apply.)
    • A. 

      USER_DEPENDENCIES

    • B. 

      USER_PRIVILEGE

    • C. 

      USER_OBJECT

    • D. 

      USER_ERRORS

  • 5. 
    You have stored the following procedure in your schema: PROCEDURE INITIALIZE_RATIOS IS BEGIN   UPDATE RATIO_LIST     SET A = 100,         B = 200   WHERE RATIO_LIST_ID = 1; END INITIALIZE_RATIOS; With this procedure stored in the database with a status of VALID, you issue the following DDL statement: ALTER TABLE RATIO_LIST ADD C NUMBER(3); What is the status of the procedure after this DDL is executed?
    • A. 

      Valid

    • B. 

      Invalid

    • C. 

      Changed

    • D. 

      New

  • 6. 
    The schema SSMITH owns a procedure SEND_NEWSLETTER with no parameters. SSMITH has granted privileges to this procedure to MAPPLEGATE. No synonyms exist for this procedure. Which of the following is a valid call to this procedure from a PL/SQL block in  the schema MAPPLEGATE?
    • A. 

      SSMITH.SEND_NEWSLETTER;

    • B. 

      MAPPLEGATE.SSMITH.SEND_NEWSLETTER;

    • C. 

      SEND_NEWSLETTER;

    • D. 

      PROCEDURE.SSMITH.SEND_NEWSLETTER;

  • 7. 
    A local function is
    • A. 

      A function within the same schema of another function

    • B. 

      Client-side function

    • C. 

      A function defined in the declaration of a PL/SQL block

    • D. 

      None

  • 8. 
    Procedures do not contain RETURN clause in the header
    • A. 

      True

    • B. 

      False

  • 9. 
    The STATUS column in the USER_TRIGGERS can be queried to reveal
    • A. 

      If a trigger is VALID or INVALID

    • B. 

      If a trigger is a BEFORE or AFTER trigger

    • C. 

      If a trigger is ENABLED or DISABLED

    • D. 

      If a trigger is NEW or MODIFIED

  • 10. 
    Which of the following is a conditional predicate? (Choose all that apply.)
    • A. 

      INSERTING

    • B. 

      UPDATING

    • C. 

      DELETING

    • D. 

      LOGGING

  • 11. 
    The procedure TICKET has no parameters. Which of the following is a valid call from a PL/SQL block to this procedure? (Choose all that apply.)
    • A. 

      EXEC TICKET();

    • B. 

      TICKET();

    • C. 

      TICKET;

    • D. 

      TICKET(NULL);

  • 12. 
    .Which two statements about subqueries are true? (Choose two.)
    • A. 

      A single row subquery can retrieve data from only one table

    • B. 

      A SQL query statement cannot display data from table B that is referred to in its subquery, unless table B is included in the main query's FROM clause.

    • C. 

      A SQL query statement can display data from table B that is referred to in its subquery, without including table B in its own FROM clause.

    • D. 

      A single row subquery can retrieve data from more than one table

    • E. 

      A single row subquery cannot be used in a condition where the LIKE operator is used for comparison.

  • 13. 
    Which two statements accurately describe a role? (Choose two.)
    • A. 

      A role can be given to a maximum of 1000 users.

    • B. 

      A user can have access to a maximum of 10 roles.

    • C. 

      A role can have a maximum of 100 privileges contained in it.

    • D. 

      Privileges are given to a role by using the CREATE ROLE statement.

    • E. 

      A role is a named group of related privileges that can be granted to the user.

    • F. 

      A user can have access to several roles, and several users can be assigned the same role.

  • 14. 
    Examine the data in the EMPLOYEES table. EMPLOYEES On the EMPLOYEES table, EMPLOYEE_ID is the primary key. MGR_ID is the ID of managers and refers to the EMPLOYEE_ID. The JOB_ID column is a NOT NULL column. Evaluate this DELETE statement: DELETE employee_id, salary, job_id FROM employees WHERE dept_id = 90; Why does the DELETE statement fail when you execute it?
    • A. 

      There is no row with dept_id 90 in the EMPLOYEES table.

    • B. 

      You cannot delete the JOB_ID column because it is a NOT NULL column.

    • C. 

      You cannot specify column names in the DELETE clause of the DELETE statement.

    • D. 

      . You cannot delete the EMPLOYEE_ID column because it is the primary key of the table.

  • 15. 
    .Evaluate these two SQL statements: SELECT last_name, salary , hire_date FROM EMPLOYEES ORDER BY salary DESC; SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC; What is true about them?
    • A. 

      . The two statements produce identical results.

    • B. 

      The two statements produce same results

    • C. 

      The second statement returns a syntax error

    • D. 

      The two statements can be made to produce identical results by adding a column alias for the salary column in the second SQL statement.

    • E. 

      There is no need to specify DESC because the results are sorted in descending order by default.

  • 16. 
    .The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL CUSTOMER_NAME VARCHAR2(100) NOT NULL STREET_ADDRESS VARCHAR2(150) CITY_ADDRESS VARCHAR2(50) STATE_ADDRESS VARCHAR2(50) PROVINCE_ADDRESS VARCHAR2(50) COUNTRY_ADDRESS VARCHAR2(50) POSTAL_CODE VARCHAR2(12) CUSTOMER_PHONE VARCHAR2(20) Which statement finds the rows in the CUSTOMERS table that do not have a postal code?
    • A. 

      SELECT customer_id, customer_name FROM customers WHERE postal_code CONTAINS NULL;

    • B. 

      SELECT customer_id, customer_name FROM customers; WHERE postal_code = '________'

    • C. 

      SELECT customer_id, customer_name FROM customers WHERE postal_code IS NULL;

    • D. 

      SELECT customer_id, customer_name FROM customers WHERE postal code IS NVL;

  • 17. 
    .Which four statements correctly describe functions that are available in SQL? (Choose four)
    • A. 

      INSTR returns the numeric position of a named character.

    • B. 

      NVL2 returns the first non-null expression in the expression list.

    • C. 

      DECODE translates an expression after comparing it to each search value.

    • D. 

      TRIM trims the heading of trailing characters (or both) from a character string.

    • E. 

      NVL compares two expressions and returns null if they are equal, or the first expression of they are not equal.

    • F. 

      NULLIF compares twp expressions and returns null if they are equal, or the first expression if they are not equal.

  • 18. 
    Examine the data from the EMP table: EMP_ID,DEPT_ID,COMMISSION 1 10 500 2 20 1000. 3 10 4 10 600 5 30 800 6 30 200 7 10 The COMMISSION column shows the monthly commission earned by the employee. Which three tasks would require subqueries or joins in order to perform in a single step? (Choose three)
    • A. 

      Deleting the records of employees who do not earn commission.

    • B. 

      Increasing the commission of employee 3 by the average commission earned in department 20.

    • C. 

      Finding the number of employees who do NOT earn commission and are working for department 20.

    • D. 

      Inserting into the table a new employee 10 who works for department 20 and earns a commission that is equal to the commission earned by employee 3.

    • E. 

      Creating a table called COMMISSION that has the same structure and data as the columns EMP_ID and COMMISSIONS of the EMP table.

  • 19. 
    The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL CUSTOMER_NAME VARCHAR2(100) NOT NULL STREET_ADDRESS VARCHAR2(150) CITY_ADDRESS VARCHAR2(50) STATE_ADDRESS VARCHAR2(50) PROVINCE_ADDRESS VARCHAR2(50) COUNTRY_ADDRESS VARCHAR2(50) POSTAL_CODE VARCHAR2(12) CUSTOMER_PHONE VARCHAR2(20) The CUSTOMER_ID column is the primary key for the table. Which two statements find the number of customers? (Choose two.)
    • A. 

      SELECT TOTAL(*) FROM customers;

    • B. 

      SELECT COUNT(*) FROM customers;

    • C. 

      SELECT TOTAL(customer_id) FROM customers;

    • D. 

      SELECT COUNT(customer_id) FROM customers;

    • E. 

      SELECT COUNT(customers) FROM customers;

  • 20. 
    .Which are DML statements? (Choose all that apply)
    • A. 

      COMMIT

    • B. 

      MERGE

    • C. 

      DROP

    • D. 

      DELETE

    • E. 

      UPDATE

    • F. 

      CREATE

  • 21. 
    .Examine the structure of the EMPLOYEES table: EMPLOYEE_ID NUMBER Primary Key FIRST_NAME VARCHAR2(25) LAST_NAME VARCHAR2(25) HIRE_DATE DATE You issue these statements: CREATE table new_emp ( employee_id NUMBER, name VARCHAR2(30)); INSERT INTO new_emp SELECT employee_id , last_name from employees; Savepoint s1; UPDATE new_emp set name = UPPER(name); Savepoint s2; Delete from new_emp; Rollback to s2; Delete from new_emp where employee_id =180; UPDATE new_emp set name = 'James'; Rollback to s2; UPDATE new_emp set name = 'James' WHERE employee_id =180; Rollback; At the end of this transaction, what is true?
    • A. 

      You have no rows in the table.

    • B. 

      You have an employee with the name of James.

    • C. 

      You cannot roll back to the same savepoint more than once.

    • D. 

      . Your last update fails to update any rows because employee ID 180 was already deleted.

  • 22. 
    Function invoke as a part of expression
    • A. 

      True

    • B. 

      False

  • 23. 
    Procedure does not perform a action.
    • A. 

      True

    • B. 

      False

  • 24. 
    DBMS_OUTPUT package involves procedures like
    • A. 

      PUT

    • B. 

      UTL_FILE

    • C. 

      ENABLE\DISABLE

    • D. 

      NEW_LINE

  • 25. 
    IN parameter is a changed mode
    • A. 

      True

    • B. 

      False

  • 26. 
    Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater then 5000?
    • A. 

      ORDER BY SALARY > 5000

    • B. 

      GROUP BY SALARY > 5000

    • C. 

      HAVING SALARY > 5000

    • D. 

      WHERE SALARY > 5000

  • 27. 
    Which four are correct guidelines for naming database tables? (Choose four)
    • A. 

      Must begin with either a number or a letter.

    • B. 

      Must be 1-30 characters long.

    • C. 

      Should not be an Oracle Server reserved word.

    • D. 

      Must contain only A-Z, a-z, 0-+, _, *, and #.

    • E. 

      Must contain only A-Z, a-z, 0-9, _, $, and #.

    • F. 

      Must begin with a letter.

  • 28. 
    How %NOTFOUND attribute can be defined?
    • A. 

      Active set has been retrieved

    • B. 

      Active set has not been retrieved

    • C. 

      Entire set is returned

    • D. 

      Set pointer is increased by next row

  • 29. 
    Examine the structure of the EMPLOYEES table: Column name Data type Remarks EMPLOYEE_ID NUMBER NOT NULL, Primary Key LAST_NAME VARCNAR2(30) FIRST_NAME VARCNAR2(30) JOB_ID NUMBER SAL NUMBER MGR_ID NUMBER References EMPLOYEE_ID column DEPARTMENT_ID NUMBER You need to create an index called NAME_IDX on the first name and last name fields of the EMPLOYEES table. Which SQL statement would you use to perform this task?
    • A. 

      CREATE INDEX NAME_IDX ON (first_name, last_name); D. CREATE INDEX NAME_IDX ON employees (first_name AND last_name);

    • B. 

      CREATE INDEX NAME_IDX (first_name AND last_name);

    • C. 

      CREATE INDEX NAME_IDX (first_name, last_name);

    • D. 

      CREATE INDEX NAME_IDX ON employees(first_name, last_name);

  • 30. 
    Which three are true regarding the use of outer joins? (Choose three)
    • A. 

      You cannot use IN operator in a condition that involves an outerjoin.

    • B. 

      You use (+) on both sides of the WHERE condition to perform an outerjoin.

    • C. 

      You use an outerjoin to see only the rows that do not meet the join condition.

    • D. 

      In the WHERE condition, you use (+) following the name of the column in the table without matching rows, to perform an outerjoin.

    • E. 

      You cannot link a condition that is involved in an outerjoin to another condition by using the OR operator.

  • 31. 
    The EMPLOYEES table has these columns: LAST NAME VARCHAR2(35) SALARY NUMBER(8,2) HIRE_DATE DATE              Management wants to add a default value to the SALARY column. You plan to alter the table by using this SQL statement: ALTER TABLE EMPLOYEES MODIFY (SALARY DEFAULT 5000); What is true about your ALTER statement?
    • A. 

      Column definitions cannot be altered to add DEFAULT values

    • B. 

      A change to the DEFAULT value affects only subsequent insertions to the table.

    • C. 

      Column definitions cannot be altered at add DEFAULT values for columns with a NUMBER data type.

    • D. 

      All the rows that have a NULL value for the SALARY column will be updated with the value 5000.

  • 32. 
    .Which SQL statement accepts user input for the columns to be displayed, the table name, and WHERE condition
    • A. 

      SELECT &1, "&2" FROM &3 WHERE last_name = '&4';

    • B. 

      SELECT &1, '&2' FROM &3 WHERE '&last_name = '&4' '

    • C. 

      SELECT &1, &2 FROM &3 WHERE last_name = '&4';

    • D. 

      SELECT &1, '&2' FROM EMP WHERE last_name = '&4';

  • 33. 
    Host variable should proceed with
    • A. 

      Hyphen

    • B. 

      Colon

    • C. 

      Semicoln

    • D. 

      Comma

  • 34. 
    Scalar data types hold a complex value
    • A. 

      True

    • B. 

      False

  • 35. 
    What  is the use of GET_LINES?
    • A. 

      RETRIEVES AN ARRAY OF LINES FROM OUTPUT BUFFER TO PROCEDURE

    • B. 

      DECLARES A FORMAL PARAMETER

    • C. 

      PLACES THE END_OF _LINE IN THE OUTPUT MARKER

    • D. 

      PACKAGE IS FULLY REFERENCED

  • 36. 
    You create a stored database function with the following header and declaration section: FUNCTION LEADING_PRODUCT (mon_of_sale VARCHAR2) RETURN VARCHAR2 IS   tot_sales NUMBER(5);   prod_name VARCHAR2(30); BEGIN Which of the following is a valid RETURN statement to include in the processing section of the function? (Choose all that apply.)
    • A. 

      RETURN tot_sales;

    • B. 

      RETURN prod_name;

    • C. 

      RETURN TRUE;

    • D. 

      RETURN CRUISES%ROWTYPE;

  • 37. 
    You have created a trigger with the following header: TRIGGER TRIG_SS_AUDIT AFTER UPDATE ON SHIP_ENGINES FOR EACH ROW You want to include a statement that will capture the resulting SHIP_STATUS value from any UPDATE statement as well as the current server system date and put them in a separate table for future reference. How will you reference these values?
    • A. 

      :old.SHIP_STATUS and :old.SYSDATE

    • B. 

      :new.SHIP_STATUS and :new.SYSDATE

    • C. 

      Query the SHIP_STATUS table to get the latest information and use SYSDATE.

    • D. 

      NONE

  • 38. 
    The package PARM _INIT contains a set of public constants, and the package FIN_UTILITIES includes procedures and functions that reference those public constants in PARM_INIT. With both packages having a status of VALID, you issue the following statement: DROP PACKAGE PARM_INIT; What is the result?                    
    • A. 

      The FIN_UTILITIES package has a status of INVALID.

    • B. 

      The FIN_UTILITIES package has a status of DISABLED

    • C. 

      The FIN_UTILITIES package is dropped.

    • D. 

      The FIN_UTILITIES package has a status of VALID.

  • 39. 
    The utility is used to provide the logical  backup
    • A. 

      True

    • B. 

      False

  • 40. 
    The transfer of data back and forth from PL\SQL to SQL is called context switching
    • A. 

      True

    • B. 

      False