Oracle PL/SQL Quiz Questions And Answers!

30 Questions | Total Attempts: 12680

SettingsSettingsSettings
Please wait...
Oracle PL/SQL Quiz Questions And Answers!

PL/SQL is a procedural language extension to Structured Query Language. You can take this Oracle PL/SQL quiz questions and answers and check your knowledge. The purpose of PL/SQL is to combine database language and procedural programming language. The quiz below has been designed to help you refresh your memory on Oracle PL/SQL. Give it a shot and see your results. Don't forget to share the result with your friends and see who got the better results. All the best!


Questions and Answers
  • 1. 
    Write a function named date_as_mmyyyy, which will accept a date as a parameter. This date is then converted to characters and formatted as MMYYYY, and then returned.  If this date is null, then return the sysdate in this format.
  • 2. 
    Supply the missing keywordDECLARE     CURSOR c1 IS          SELECT * FROM DUAL;     r1   c1%ROWTYPE;BEGIN      OPEN c1:            ______________ c1 INTO r1;                       IF c1%NOTFOUND THEN                 NULL;            END IF;      CLOSE c1;END;
  • 3. 
    Select invalid variable types
    • A. 

      CHAR

    • B. 

      VARCHAR1

    • C. 

      VARCHAR2

    • D. 

      INTEGER

    • E. 

      NUMBER

  • 4. 
    List the correct sequence of commands to process a set of records when using explicit cursors.
    • A. 

      INITIALIZE, GET, CLOSE

    • B. 

      CURSOR, GET, FETCH, CLOSE

    • C. 

      OPEN, FETCH, CLOSE

    • D. 

      CURSOR, FETCH, CLOSE

    • E. 

      GET, SEEK, HIDE

  • 5. 
    Supply the missing keywordDECLARE     CURSOR c1 IS          SELECT * FROM DUAL;     r1   c1%ROWTYPE;BEGIN      OPEN c1:            FETCH c1 INTO r1;                       IF c1%NOTFOUND THEN                 RAISE;            END IF;      ________________ c1;END;
  • 6. 
    Select the best answer.PROCEDURE foo ( p_foo_text  IN VARCHAR2,  p_foo_number IN OUT NUMBER ) ISp_foo_text and p_foo_number are referred to as this procedure's _________
    • A. 

      Variables

    • B. 

      IN OUT Parameters

    • C. 

      Name

    • D. 

      Signature

    • E. 

      None of the above

  • 7. 
    Select the best answer.PACKAGE foo_foo ISPROCEDURE foo( p_foo_text IN VARCHAR2 );PROCEDURE foo(p_foo_number IN NUMBER);END;
    • A. 

      Package specification is invalid. Too many procedures named foo.

    • B. 

      Package specification is invalid. First procedure should be called foo_1, second procedure should be called foo_2.

    • C. 

      Package specification is valid. We can have an unlimited number of procedures name foo.

    • D. 

      Package specification is valid. This is an example of overloading.

    • E. 

      Package specification is invalid. We can only have one procedure named foo in the package.

  • 8. 
    Select the best answer to complete this variable declaration for a column value.DECLARE   l_foo_column_id        SOME_TABLE.SOME_COLUMN_________;BEGIN...
    • A. 

      %ID

    • B. 

      %ROWTYPE

    • C. 

      %COLUMNTYPE

    • D. 

      %TYPE

    • E. 

      None of the above.

  • 9. 
    Select the best answer to complete this variable declaration for a record.DECLARE   l_foo_table        SOME_TABLE_________;BEGIN...
    • A. 

      %TABLE

    • B. 

      %ROWTYPE

    • C. 

      %COLUMNTYPE

    • D. 

      %TYPE

    • E. 

      None of the above

  • 10. 
    Select the best answer.  This is an example of what _____ type of cursor?DECLARE    l_date   DATE;BEGIN   SELECT TRUNC(SYSDATE)        INTO l_date     FROM DUAL;END;
    • A. 

      Explicit

    • B. 

      Implicit

    • C. 

      Select

    • D. 

      PL/SQL

    • E. 

      None of the above

  • 11. 
    Select the best answer.  This is an example of what _____ type of cursor?DECLARE    l_date   DATE;    CURSOR c1 IS    SELECT TRUNC(SYSDATE)      FROM DUAL;BEGIN    OPEN c1;         FETCH c1 INTO l_date;    CLOSE c1;END;
    • A. 

      Explicit

    • B. 

      Implicit

    • C. 

      Select

    • D. 

      PL/SQL

    • E. 

      None of the above

  • 12. 
    Supply the missing keyword.IF foo IS NULL [Blank]__   NULL;END IF;
  • 13. 
    Enter the missing keyword.IF foo = 1 THEN    l_text := 'A';______  foo = 2 THEN   l_text := 'B';ELSE    l_text := 'C';END IF;
    • A. 

      ELSE

    • B. 

      ELSE IF

    • C. 

      ELIF

    • D. 

      ELSIF

    • E. 

      None of the above

  • 14. 
    Fill in the missing keyword.DECLARE    l_date    DATE;BEGIN  SELECT sysdate     ______ l_date     FROM dual;END;
  • 15. 
    Describe the result set that will be obtained from this join.SELECT d.department_name, s.first_name, s.last_name, s.title, s.salary   FROM employee s,             department dWHERE s.salary > 20000     AND s.title = 'ANALYST'     AND ( d.department = 'FINANCE' OR             d.department = 'SALES' )              
    • A. 

      This SQL will list all employees in the Finance department earning more than 20000.

    • B. 

      This SQL will list all employees who are in the Finance or Sales department earning more than 20000.

    • C. 

      This SQL will list all employees who are Analysts in the Finance or Sales department earning more than 20000.

    • D. 

      This SQL will return a cartesian product.

    • E. 

      None of the above.

  • 16. 
    Where do you declare an explicit cursor in the PL/SQL language?
    • A. 

      In the PL/SQL working storage section.

    • B. 

      In the PL/SQL declaration section.

    • C. 

      In the PL/SQL body section.

    • D. 

      In the PL/SQL exception section.

    • E. 

      None of the above.

  • 17. 
    Enter the missing keyword to make a new stored procedure.____________ PROCEDURE foo( p_foo_text  IN VARCHAR2 ) ASBEGIN    NULL;END;
  • 18. 
    Select the best answer describing the maximum number of times the COMMIT will be executed.ExampleFOR i IN 1..1000 LOOP   ...   IF MOD(i, 100) = 0 THEN      COMMIT;   END IF;   ...END LOOP;
    • A. 

      The commit is fired 1000 times.

    • B. 

      The commit is fired 100 times.

    • C. 

      The commit is fired 10 times.

    • D. 

      The commit is fired 1 time.

    • E. 

      None of the above.

  • 19. 
    Assuming the date and time is 09/09/2009 09:09:09, what value will the following statement return.SELECT TO_CHAR(TRUNC(SYSDATE),'MM/DD/YYYY HH24:MI:SS') FROM dual;
    • A. 

      09/09/2009 09:09:09

    • B. 

      09/09/2009 09:09:09AM

    • C. 

      09/09/2009

    • D. 

      09/09/2009 00:00:00

    • E. 

      None of the above

  • 20. 
    The || is is an example of what function.SELECT last_name || ', ' || first_name || ' ' || middle_nameFROM employees;
    • A. 

      Incarnation

    • B. 

      Integration

    • C. 

      Continuation

    • D. 

      Concatenation

    • E. 

      None of the above

  • 21. 
    Which of the following is not an Oracle DML function?
    • A. 

      DECODE

    • B. 

      TRUNCATE

    • C. 

      TO_CHAR

    • D. 

      NVL

    • E. 

      Trick question, all of these are Oracle DML functions.

  • 22. 
    Fill in the blank with the name of the function to convert a date to an alphanumeric string.SELECT [Blank](sysdate, 'MM/DD/YYYY') FROM dual;
  • 23. 
    Select the invalid PL/SQL looping construct.
    • A. 

      WHILE LOOP ... END LOOP;

    • B. 

      FOR rec IN some_cursor LOOP ... END LOOP;

    • C. 

      LOOP ... UNTIL ; END LOOP;

    • D. 

      LOOP ... EXIT WHEN ; END LOOP;

    • E. 

      None of the above. All are valid.

  • 24. 
    Select the best answer.  Which listed attribute is an invalid attribute of an Explicit cursor.
    • A. 

      %NOTFOUND

    • B. 

      %FOUND

    • C. 

      %ROWCOUNT

    • D. 

      %ISOPEN

    • E. 

      None of the above. All of these are valid.

  • 25. 
    Which of the following is not a grouping function.
    • A. 

      COUNT

    • B. 

      SUM

    • C. 

      DISTINCT

    • D. 

      MIN

    • E. 

      All of the above.

Back to Top Back to top