Oracle PL/SQL Quiz Questions And Answers!

Reviewed by Godwin Iheuwa
Godwin Iheuwa, MS, Computer Science |
Computer Expert
Review Board Member
Godwin is a proficient Database Administrator currently employed at MTN Nigeria. He holds as MS in Computer Science from the University of Bedfordshire, where he specialized in Agile Methodologies and Database Administration. He also earned a Bachelor's degree in Computer Science from the University of Port Harcourt. With expertise in SQL Server Integration Services (SSIS) and SQL Server Management Studio, Godwin's knowledge and experience enhance the authority of our quizzes, ensuring accuracy and relevance in the realm of computer science.
, MS, Computer Science
Approved & Edited by ProProfs Editorial Team
The editorial team at ProProfs Quizzes consists of a select group of subject experts, trivia writers, and quiz masters who have authored over 10,000 quizzes taken by more than 100 million users. This team includes our in-house seasoned quiz moderators and subject matter experts. Our editorial experts, spread across the world, are rigorously trained using our comprehensive guidelines to ensure that you receive the highest quality quizzes.
Learn about Our Editorial Process
| By Ibecruzin
I
Ibecruzin
Community Contributor
Quizzes Created: 1 | Total Attempts: 23,945
Questions: 30 | Attempts: 23,958

SettingsSettingsSettings
Oracle PL/SQL Quiz Questions And Answers! - Quiz


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 results with your friends and see who got the better score. 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;

    Explanation
    The missing keyword in the given code is "FETCH". The FETCH keyword is used to retrieve the next row from the cursor into the specified variable, in this case, r1. After opening the cursor, the FETCH statement is used to fetch the data from the cursor into the variable r1.

    Rate this question:

  • 3. 

    Select invalid variable types

    • A.

      CHAR

    • B.

      VARCHAR1

    • C.

      VARCHAR2

    • D.

      INTEGER

    • E.

      NUMBER

    Correct Answer
    B. VARCHAR1
    Explanation
    VARCHAR1 is not an acceptable variable type. Only VARCHAR and VARCHAR2 exist.

    Rate this question:

  • 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

    Correct Answer
    C. OPEN, FETCH, CLOSE
    Explanation
    The correct sequence of commands to process a set of records when using explicit cursors is to first open the cursor, then fetch the records, and finally close the cursor. This allows the cursor to be initialized and prepared to retrieve the desired data, then fetch the records one by one, and finally close the cursor to release any associated resources.

    Rate this question:

  • 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;

    Correct Answer
    CLOSE
    Explanation
    An explicit cursor must always be closed.

    Rate this question:

  • 6. 

    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.

    Correct Answer
    D. Package specification is valid. This is an example of overloading.
    Explanation
    The given package specification is valid because it demonstrates the concept of overloading. Overloading allows multiple procedures with the same name but different parameter lists to exist within a package. In this case, there are two procedures named "foo" with different parameter types: one takes a VARCHAR2 parameter and the other takes a NUMBER parameter. This allows for flexibility and versatility in how the procedures can be called and used within the package.

    Rate this question:

  • 7. 

    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.

    Correct Answer
    D. %TYPE
    Explanation
    The correct answer is %TYPE. In PL/SQL, the %TYPE attribute is used to declare a variable with the same data type as a column in a table. By using %TYPE, we can ensure that the variable has the same data type as the column, which helps in maintaining data integrity and avoiding data type mismatches.

    Rate this question:

  • 8. 

    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

    Correct Answer
    B. %ROWTYPE
    Explanation
    The correct answer is %ROWTYPE. In PL/SQL, %ROWTYPE is used to define a record variable that has the same structure as a table or cursor record. It allows the variable to hold an entire row of data from a table or cursor. In this case, l_foo_table is being declared as a record variable with the same structure as SOME_TABLE using %ROWTYPE.

    Rate this question:

  • 9. 

    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

    Correct Answer
    B. Implicit
    Explanation
    The given code snippet does not explicitly declare a cursor using the CURSOR keyword. Instead, it uses a SELECT statement to retrieve a value from the DUAL table and stores it in the variable l_date. This is an example of an implicit cursor, where the SQL statement is directly executed without explicitly defining and opening a cursor.

    Rate this question:

  • 10. 

    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

    Correct Answer
    A. Explicit
    Explanation
    The given code snippet explicitly declares a cursor named "c1" using the "CURSOR" keyword. This makes it an explicit cursor. Explicit cursors are declared by the programmer and provide more control over the cursor operations, such as opening, fetching, and closing. This is in contrast to implicit cursors, which are automatically created by the database system for certain SQL statements. In this case, the programmer explicitly declares and uses the cursor, so the correct answer is "Explicit".

    Rate this question:

  • 11. 

    Supply the missing keyword.IF foo IS NULL __________   NULL;END IF;

    Correct Answer
    THEN
    Explanation
    The missing keyword in the given code snippet is "THEN". In this code, the IF statement checks if the variable "foo" is NULL. If it is, then the code will execute the statements inside the IF block, which in this case is "NULL;". The "THEN" keyword is used to indicate the start of the code block that should be executed if the condition is true.

    Rate this question:

  • 12. 

    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

    Correct Answer
    D. ELSIF
    Explanation
    The correct answer is ELSIF. In this code snippet, the keyword ELSIF is used to check if the variable "foo" is equal to 2. If it is, the variable "l_text" is assigned the value 'B'. If the condition is not met, the code proceeds to the ELSE statement and assigns the value 'C' to the variable "l_text".

    Rate this question:

  • 13. 

    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.

    Correct Answer
    B. In the PL/SQL declaration section.
    Explanation
    In PL/SQL, an explicit cursor is declared in the PL/SQL declaration section. This section is used to define variables, cursors, and other program objects before the executable part of the code. By declaring the cursor in this section, it can be accessed and used throughout the program. The working storage section is not the correct place to declare a cursor, as it is used for defining variables used within a specific block or procedure. The body section is used for writing the executable part of the code, and the exception section is used for handling exceptions. Therefore, the correct place to declare an explicit cursor is in the PL/SQL declaration section.

    Rate this question:

  • 14. 

    Enter the missing keyword to make a new stored procedure.____________ PROCEDURE foo( p_foo_text  IN VARCHAR2 ) ASBEGIN    NULL;END;

    Correct Answer
    CREATE
    CREATE OR REPLACE
    Explanation
    The missing keyword to make a new stored procedure is either "CREATE" or "CREATE OR REPLACE". The "CREATE" keyword is used to create a new stored procedure, while the "CREATE OR REPLACE" keyword is used to create a new stored procedure or replace an existing one if it already exists.

    Rate this question:

  • 15. 

    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

    Correct Answer
    D. 09/09/2009 00:00:00
    Explanation
    The statement will return the value "09/09/2009 00:00:00" because the TRUNC function is used to remove the time portion of the SYSDATE function, resulting in only the date being displayed. The TO_CHAR function is then used to format the date in the desired format "MM/DD/YYYY HH24:MI:SS". Since the time portion is truncated, it will be displayed as "00:00:00".

    Rate this question:

  • 16. 

    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

    Correct Answer
    D. Concatenation
    Explanation
    The || operator in this context is used to concatenate or combine multiple strings together. In the given SQL statement, it is used to concatenate the last name, a comma, the first name, a space, the middle name, and display them as a single string. Therefore, the correct answer is "Concatenation."

    Rate this question:

  • 17. 

    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.

    Correct Answer
    B. TRUNCATE
    Explanation
    Do not confuse TRUNCATE with TRUNC. Truncate is used to remove all rows from an Oracle table.

    Rate this question:

  • 18. 

    Fill in the blank with the name of the function to convert a date to an alphanumeric string.SELECT ________(sysdate, 'MM/DD/YYYY') FROM dual;

    Correct Answer
    TO_CHAR
    Explanation
    The correct answer is TO_CHAR. The TO_CHAR function is used to convert a date to an alphanumeric string. In this case, the sysdate is being converted to a string in the format MM/DD/YYYY using the TO_CHAR function.

    Rate this question:

  • 19. 

    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.

    Correct Answer
    C. LOOP ... UNTIL ; END LOOP;
    Explanation
    Oracle doesn't have a Perform Until or Repeat Until loop, but you can emulate one. The syntax for emulating a REPEAT UNTIL Loop is:

    LOOP
    {.statements.}
    EXIT WHEN boolean_condition;
    END LOOP;

    You would use an emulated REPEAT UNTIL Loop when you do not know how many times you want the loop body to execute. The REPEAT UNTIL Loop would terminate when a certain condition was met.

    Rate this question:

  • 20. 

    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.

    Correct Answer
    E. None of the above. All of these are valid.
    Explanation
    The given answer states that all the listed attributes (%NOTFOUND, %FOUND, %ROWCOUNT, %ISOPEN) are valid attributes of an Explicit cursor. This means that each of these attributes can be used with an Explicit cursor to perform specific operations.

    Rate this question:

  • 21. 

    All the blanks can be filled with one word.  Fill in the blank Starting in Oracle 9i, you can use the _________ statement within a SQL statement. It has the functionality of an IF-THEN-ELSE statement. The syntax for the ________ statement is: __________  [ expression ]  WHEN condition_1 THEN result_1  WHEN condition_2 THEN result_2  ...  WHEN condition_n THEN result_n  ELSE resultEND

    Correct Answer
    CASE
    Explanation
    Starting in Oracle 9i, you can use the CASE statement within a SQL statement. It has the functionality of an IF-THEN-ELSE statement.
    The syntax for the CASE statement is:

    CASE [ expression ] WHEN condition_1 THEN result_1 WHEN condition_2 THEN result_2 ... WHEN condition_n THEN result_n ELSE result END

    The CASE statement allows you to perform conditional logic within a SQL statement, making it a powerful tool for manipulating and transforming data. It allows you to specify multiple conditions and corresponding results, and also includes an optional ELSE clause to handle cases where none of the conditions are met.

    Rate this question:

  • 22. 

    Select the best answer below.  What are the components of a package?

    • A.

      Box, wrapping, and binding

    • B.

      Header and body

    • C.

      Specification and content

    • D.

      Specification and body

    • E.

      None of the above

    Correct Answer
    D. Specification and body
    Explanation
    The components of a package typically include the specification, which outlines the details and requirements of the package, and the body, which is the physical container that holds the contents. This answer accurately identifies the two main components of a package.

    Rate this question:

  • 23. 

    Select incorrect variable declarations.

    • A.

      Foo_number varchar2(10);

    • B.

      Foo_text number(10);

    • C.

      Foo_char char(1) := 'Y';

    • D.

      Foo_time date;

    • E.

      Foo_text varchar2(10) := 'hello world';

    Correct Answer
    B. Foo_text number(10);
    Explanation
    : The correct answer is B. foo_text number(10). This is an incorrect variable declaration in SQL because the data type of the variable does not match the data type of the value. The foo_text variable is declared as a number, but the value is a text. This will cause a syntax error or a data conversion error when the variable is used.

    Rate this question:

  • 24. 

    Select the best answer. PROCEDURE foo ( p_foo_text  IN VARCHAR2,   p_foo_number IN OUT NUMBER ) IS p_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

    Correct Answer
    B. IN OUT Parameters
    Explanation
    In the given PL/SQL procedure:
    PROCEDURE foo
    ( p_foo_text  IN VARCHAR2,
      p_foo_number IN OUT NUMBER ) IS
      
      
     p_foo_text is an IN parameter, indicating that it is used to pass values into the procedure. It is of type VARCHAR2.
    p_foo_number is an IN OUT parameter, indicating that it is used both to pass a value into the procedure and to receive a modified value back from the procedure. It is of type NUMBER.
    So, p_foo_text and p_foo_number are referred to as the procedure's parameters, and specifically, p_foo_number is an IN-OUT parameter.

    Rate this question:

  • 25. 

    Fill in the missing keyword. DECLARE     l_date    DATE; BEGIN   SELECT sysdate      ______ l_date      FROM dual; END;

    Correct Answer
    INTO
    Explanation
     In the given code snippet, the missing keyword is "INTO". The keyword "INTO" is used in SQL to specify that the result of a SELECT statement should be stored into a variable. In this case, the code selects the current date from the "sysdate" function and stores it into the variable "l_date". The "INTO" keyword is necessary to indicate where the result should be stored.

    Rate this question:

  • 26. 

    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 d WHERE 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.

    Correct Answer
    B. This SQL will list all employees who are in the Finance or Sales department earning more than 20000.
    Explanation
    The given SQL query performs an inner join between the `employee` table (aliased as `s`) and the `department` table (aliased as `d`) using the common column `department`. The `WHERE` clause filters the results based on the specified conditions:
    - `s.salary > 20000`: Filters employees with a salary greater than 20000.
    - `s.title = 'ANALYST'`: Filters employees with the title 'ANALYST'.
    - `(d.department = 'FINANCE' OR d.department = 'SALES')`: Filters departments to include only those with the department name 'FINANCE' or 'SALES'.
    As a result, the query will list all employees who meet the salary and title criteria and are associated with the 'FINANCE' or 'SALES' departments.

    Rate this question:

  • 27. 

    Select the best answer describing the maximum number of times the COMMIT will be executed. Example FOR 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.

    Correct Answer
    B. The commit is fired 100 times.
    Explanation
    In the given example, the loop iterates 1000 times, and the COMMIT statement is executed when the condition MOD(i, 100) = 0 is true. This condition is satisfied every 100 iterations, so the COMMIT statement is executed 100 times during the entire loop.

    Rate this question:

  • 28. 

    Which of the following is not a grouping function?

    • A.

      COUNT

    • B.

      SUM

    • C.

      DISTINCT

    • D.

      MIN

    • E.

      All of the above.

    Correct Answer
    C. DISTINCT
    Explanation
    Most functions can accept qualifiers as their arguments. These qualifiers are DISTINCT and ALL. If the DISTINCT qualifier is passed, then only distinct values returned by the query are considered. The ALL qualifier causes the function to consider all of the values returned by the query. If none is specified, then ALL is the default.

    Rate this question:

  • 29. 

    Which of the following is not a valid Oracle PL/SQL exception.

    • A.

      NO_DATA_FOUND ORA-01403

    • B.

      TWO_MANY_ROWS ORA-01422

    • C.

      DUP_VAL_ON_INDEX ORA-00001

    • D.

      OTHERS

    • E.

      None of the above. These are all valid.

    Correct Answer
    B. TWO_MANY_ROWS ORA-01422
    Explanation
    TWO_MANY_ROWS is not the correct name for the TOO_MANY_ROWS exception. Focus in the words "two" and "too". 

    Rate this question:

  • 30. 

    What is the value of l_child_number? DECLARE    l_parent_number    NUMBER  := 1; BEGIN    DECLARE       l_child_number   NUMBER := 2;      BEGIN        l_child_number := l_parent_number + l_child_number;    END;     DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_child_number)); EXCEPTION    WHEN OTHERS THEN       l_child_number := 0;       DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_child_number); END;

    • A.

      1

    • B.

      2

    • C.

      3

    • D.

      0

    • E.

      None of the above.

    Correct Answer
    C. 3
    Explanation
    The inner block declares `l_child_number` as 2.
    - It then performs the addition `l_parent_number + l_child_number`, which is 1 + 2 = 3.
    - The `DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_child_number));` in the inner block will output "3".
    - If there is an exception in the inner block, it sets `l_child_number` to 0 and outputs "0".
    - In the given code, there is no exception in the inner block, so the output will be "3".

    Rate this question:

Godwin Iheuwa |MS, Computer Science |
Computer Expert
Godwin is a proficient Database Administrator currently employed at MTN Nigeria. He holds as MS in Computer Science from the University of Bedfordshire, where he specialized in Agile Methodologies and Database Administration. He also earned a Bachelor's degree in Computer Science from the University of Port Harcourt. With expertise in SQL Server Integration Services (SSIS) and SQL Server Management Studio, Godwin's knowledge and experience enhance the authority of our quizzes, ensuring accuracy and relevance in the realm of computer science.
Back to Top Back to top
Advertisement
×

Wait!
Here's an interesting quiz for you.

We have other quizzes matching your interest.