Oracle Quiz

30 Questions

Settings
Please wait...
Oracle Quizzes & Trivia

Questions and Answers
  • 1. 
    To write a query that performs an outer join of tables A and B and returns all rows from B, You need to write
    • A. 

      A.any outer join

    • B. 

      B.a left outer join

    • C. 

      C.a cross join

    • D. 

      D.a right outer join

    • E. 

      E.an inner join

  • 2. 
    Top N analysis requires _____ and _____. (Choose two.)
    • A. 

      A.the use of rowed

    • B. 

      B.a GROUP BY clause

    • C. 

      C. an ORDER BY clause

    • D. 

      D. only an inline view

    • E. 

      E. an inline view and an outer query

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

      A. COMMIT

    • B. 

      B. MERGE

    • C. 

      C. UPDATE

    • D. 

      D. DELETE

    • E. 

      E. CREATE

    • F. 

      F. DROP

  • 4. 
    Which of the following statements creates a valid procedure? (Choose all that apply.)
    • A. 

      A.CREATE OR REPLACE PROCEDURE PROC_TEST IS NULL; END;

    • B. 

      B.CREATE OR REPLACE PROCEDURE PROC_TEST AS BEGIN NULL; END;

    • C. 

      C.CREATE OR REPLACE PROCEDURE TEST IS NULL; END;

    • D. 

      D.CREATE OR REPLACE PROCEDURE TEST IS DECLARE BEGIN NULL; END;

  • 5. 
    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. 

      A.v_status := v_priority ∥ get_status(15, NULL);

    • B. 

      B.v_status := get_status(15);

    • C. 

      C.v_status := get_status(NULL);

    • D. 

      D.v_status := get_status(ABS(v_order_id), ‘FLAG’);

  • 6. 
    • A. 

      A.ASSIGN_PURCHASE_ORDER;

    • B. 

      B.ASSIGN_PURCHASE_ORDER(supplier_id => 700);

    • C. 

      C.ASSIGN_PURCHASE_ORDER(‘01-JAN-2004’);

    • D. 

      D.ASSIGN_PURCHASE_ORDER(SEQ_PURCHASE_ORDER_ID.NEXTVAL);

  • 7. 
    Consider the following function: FUNCTION ACTIVE_SESSION_COUNT RETURN NUMBER This function does not write to the database or package constructs. You can invoke this function from which of the following? (Choose all that apply.)
    • A. 

      A.The WHERE clause of an UPDATE statement

    • B. 

      B.The left side of an assignment statement

    • C. 

      C.The COLUMN list of an INSERT statement

    • D. 

      D.The default value assignment of the parameter of a procedure

  • 8. 
    I)Procedural Language is an extension to SQL     ii)Structured Query Language is an extension to SQL
    • A. 

      A)Only i is true

    • B. 

      B)Only ii is true

    • C. 

      C)Both i & ii

    • D. 

      D)None

  • 9. 
    Which data type is used to hold single value?  
    • A. 

      A) LOB

    • B. 

      B)Reference data types

    • C. 

      C)Composite data types

    • D. 

      D)Scalar data types

  • 10. 
    Which part contains cursors and user defined exceptions that are referenced in the executable and declarative sections.  
    • A. 

      A) Declarative

    • B. 

      B) Executable

    • C. 

      C) Exception handling

    • D. 

      D)Both a & c

  • 11. 
    PUT_LINE combines the action of                             
    • A. 

      A) PUT and LINE

    • B. 

      B) PUT and NEW_LINE

    • C. 

      C) PUT_LINE

    • D. 

      D) none

  • 12. 
    Consider the following code sample: CREATE OR REPLACE FUNCTION BALANCE_LEDGER(account_id NUMBER) RETURN NUMBER IS   v_result BOOLEAN; BEGIN   RECONCILE(account_id, v_result);   IF (v_result) THEN   RETURN '1';   ELSE                 RETURN '2';   END IF; END BALANCE_LEDGER; Which of the following statements is true about this code sample? (Choose all that apply.) . .
    • A. 

      A.It will not compile due to RETURN datatype conflicts.

    • B. 

      B.It will compile, but it will not execute due to RETURN datatype conflicts

    • C. 

      C.It will compile, but it will not execute within SQL statements.

    • D. 

      D.It will compile and execute

  • 13. 
    Benefits of Subprograms
    • A. 

      A)Easy maintenance

    • B. 

      B)Improved data security and integrity

    • C. 

      C) Improved code clarity

    • D. 

      D) All of the above

  • 14. 
    You submit a statement to the database that starts with the following: CREATE PROCEDURE PROC_AUDIT (AUDIT_ID AUDITS.AUDIT%TYPE) IS Which of the following statements is guaranteed to be true?
    • A. 

      A. The code for this program unit will definitely be stored in the data dictionary.

    • B. 

      B.This procedure will definitely have a status of VALID.

    • C. 

      C.This procedure will be callable from a SQL statement

    • D. 

      D.None of the above

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

      A.Modify the list of parameters for a function.

    • B. 

      B.Change the program unit's processing section.

    • C. 

      C.Recompile the program unit's code.

    • D. 

      D.Create an overloaded function.

  • 16. 
    You submit the following statement to the database: CREATE OR REPLACE FUNCTION CREDIT_CHECK (p_card_id NUMBER) RETURN VARCHAR2 IS   CURSOR cur_card_services IS     SELECT APPROVAL_CODE     FROM   CARD_SERVICE     WHERE  CARD_ID = p_card_id;   v_approval_code CARD_SERVICE.APPROVAL_CODE%TYPE; BEGIN   OPEN  cur_card_services;   FETCH cur_card_services INTO v_approval_code;   CLOSE cur_card_services;   RETURN v_approval_code; END CREDIT_CHECK; Which of the following statements will successfully execute this function from SQL*Plus? (Choose all that apply.)
    • A. 

      A.EXEC CREDIT_CHECK(4020);

    • B. 

      B.SELECT CREDIT_CHECK(p_card_id => 101) FROM DUAL;

    • C. 

      C.BEGIN DBMS_OUTPUT.PUT_LINE(CREDIT_CHECK(101)); END;

    • D. 

      D.CREDIT_CHECK(4020);

  • 17. 
    Which of the following statements describes a difference between a stored database procedure and a stored database function?
    • A. 

      A.Procedures are easier to code and invoke than functions.

    • B. 

      B.Functions execute more quickly than procedures.

    • C. 

      C.Functions can be invoked from within a SQL statement; procedures cannot.

    • D. 

      D.Procedures can handle more parameters.

  • 18. 
    Which of the following is an advantage of a stored database procedure?
    • A. 

      A.It runs faster than a stored database function.

    • B. 

      B.It can be invoked by any privileged schema from anywhere on the network.

    • C. 

      C.It can be automatically invoked by any privileged client from anywhere on the operating system network configuration protocol adapter.

    • D. 

      D. When a stored database procedure is executed, all other stored database procedures are automatically loaded so future calls to those procedures will be faster.

  • 19. 
    Consider the following procedure: PROCEDURE ADD_PORT(new_port_name VARCHAR2) IS BEGIN   INSERT INTO PORTS (PORT_ID, PORT_NAME)   VALUES (SEQ_PORT_ID.NEXTVAL, new_port_name);   COMMIT; END ; Which of the following is a valid call to this procedure from SQL*Plus?
    • A. 

      A.ADD_PORT(‘Miami’);

    • B. 

      B.EXECUTE ADD_PORT(‘Miami’);

    • C. 

      C.SELECT ADD_PORT(‘Miami’) FROM DUAL;

    • D. 

      D.EXECUTE PROCEDURE ADD_PORT(‘Miami’);

  • 20. 
    You have already successfully compiled and stored a procedure called ADD_LINE_ITEM in the database. Now you want to make a change to the procedure's exception handling section. Which of the following statements will you use to make this change?
    • A. 

      A.ALTER PROCEDURE

    • B. 

      B.DROP PROCEDURE

    • C. 

      C.CREATE PROCEDURE

    • D. 

      D.CREATE or REPLACE PROCEDURE

  • 21. 
    Which of the following data dictionary views contains the source code for a stored packaged function? USER_TEXT USER_FUNCTIONS USER_SOURCE USER_PACKAGES
    • A. 

      A.USER_TEXT

    • B. 

      B.USER_FUNCTIONS

    • C. 

      C.USER_SOURCE

    • D. 

      D.USER_PACKAGES

  • 22. 
    Where in the data dictionary can you find the status of a stored database procedure? (Choose all that apply.)
    • A. 

      A.USER_OBJECTS

    • B. 

      B.USER_PROCEDURES

    • C. 

      C.USER_STATUS

    • D. 

      D.USER_SOURCE

  • 23. 
    Which of the following queries will display the source code of a function called CANCEL_ORDER?
    • A. 

      A.SELECT TEXT FROM USER_SOURCE WHERE NAME = ‘CANCEL_ORDER’ ORDER BY TEXT;

    • B. 

      B.SELECT TEXT FROM USER_FUNCTIONS WHERE NAME = ‘CANCEL_ORDER’ ORDER BY LINE_NUMBER;

    • C. 

      C.SELECT TEXT FROM USER_SOURCE WHERE TYPE = ‘FUNCTION’ AND NAME = ‘CANCEL_ORDER’ ORDER BY LINE;

    • D. 

      D.SELECT TEXT FROM USER_SOURCE WHERE OBJECT_TYPE = ‘FUNCTION’

  • 24. 
    • A. 

      A.VALID

    • B. 

      B.INVALID

    • C. 

      C.CHANGED

    • D. 

      D.NEW

  • 25. 
    What is the result of the following SELECT statement? SELECT ROUND (631.1250, 2), ROUND (631.1250, 0), ROUND (631.1250, -2), ROUND (631.1250, 3) FROM DUAL;
    • A. 

      A.600 0 631.00 631

    • B. 

      B.631.13 631 600 631.125

    • C. 

      C.630 0 631.12 631

    • D. 

      D.2 0 -2 3

  • 26. 
    Declare j number; begin for i in 1..6 loop j:=i; goto outer; end loop; <<outer>> j:=j+1; end; What is the value j
    • A. 

      A.2

    • B. 

      B.7

    • C. 

      C.6

    • D. 

      D.0

  • 27. 
    Declare j number; begin for i in 1..6 loop j:=i; goto outer; <<outer>> j:=j+1; end loop; end; What is the value j
    • A. 

      A.2

    • B. 

      B.6

    • C. 

      C.7

    • D. 

      D.0

  • 28. 
    You need to unite the data from the STUDENTS and FACULTY tables for a new report. Which SQL SELECT capability are you using to accomplish this?
    • A. 

      A.SELECTION

    • B. 

      B.JOINING

    • C. 

      C.PROJECTION

    • D. 

      D.MANAGEMENT

  • 29. 
    What values does the SYSDATE return when used in a SELECT statement? (Select all that apply.)
    • A. 

      A.The date the database was created

    • B. 

      B.The current database server time

    • C. 

      C.The time the database was created

    • D. 

      D.The current database server date

  • 30. 
    Which of the following is true if you use the alter tablespace statement and specify the TEMPORARY clause (Choose all that apply)?
    • A. 

      A.Oracle no longer perform any checkpoint for the online datafiles in the tablespace

    • B. 

      B.Oracle does not ensure that all files are written

    • C. 

      C.Oracle performs a checkpoint for all online datafiles in the tablespace

    • D. 

      D.The offline files may require media recovery before you bring the tablespace online

    • E. 

      E.The offline files may require media recovery after you bring the tablespace online