Oracle Mock 2

40 Questions | Total Attempts: 88

SettingsSettingsSettings
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