Oracle PL/SQL Quiz

1. Add the missing keyword DECLARE 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;

An explicit cursor must always be closed.

Explanation

An explicit cursor must always be closed.

Submit

2. Supply the missing keyword.

IF foo IS NULL _______
NULL;
END IF;

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.

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.

Submit

3. Supply the missing keyword DECLARE CURSOR c1 IS SELECT * FROM DUAL; r1 c1%ROWTYPE; BEGIN OPEN c1: Blank] c1 INTO r1; IF c1%NOTFOUND THEN NULL; END IF; CLOSE c1; END;

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.

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.

Submit

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

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.

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.

Submit

5. 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 result
END

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.

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.

Submit

6. 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;

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.

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.

Submit

7. The || is is an example of what function.

SELECT last_name || ', ' || first_name || ' ' || middle_name
FROM employees;

Incarnation

Integration

Continuation

Concatenation

None of the above

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."

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."

8. 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;

Explicit

Implicit

Select

PL/SQL

None of the above

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".

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".

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

INITIALIZE, GET, CLOSE

CURSOR, GET, FETCH, CLOSE

OPEN, FETCH, CLOSE

CURSOR, FETCH, CLOSE

GET, SEEK, HIDE

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.

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.

10. Select invalid variable type:

CHAR

VARCHAR1

VARCHAR2

INTEGER

NUMBER

VARCHAR1 is not an acceptable variable type. Only VARCHAR and VARCHAR2 exist.

Explanation

VARCHAR1 is not an acceptable variable type. Only VARCHAR and VARCHAR2 exist.

11. Where do you declare an explicit cursor in the PL/SQL language?

In the PL/SQL working storage section.

In the PL/SQL declaration section.

In the PL/SQL body section.

In the PL/SQL exception section.

None of the above.

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.

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.

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

%COLUMNTYPE

%TYPE

None of the above

%TABLE

%ROWTYPE

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.

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.

13. Which of the following is not a grouping function?

COUNT

SUM

DISTINCT

MIN

All of the above.

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.

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.

14. 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;

Explicit

Implicit

Select

PL/SQL

None of the above

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.

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.

15. What are the components of a package?

Box, wrapping, and binding

Header and body

Specification and content

Specification and body

None of the above

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.

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.

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

%ID

%ROWTYPE

%COLUMNTYPE

%TYPE

None of the above.

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.

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.

17. Enter the missing keyword.

IF foo = 1 THEN
l_text := 'A';
______ foo = 2 THEN
l_text := 'B';
ELSE
l_text := 'C';
END IF;

ELSE

ELSE IF

ELIF

ELSIF

None of the above

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".

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".

18. Select incorrect variable declarations.

Foo_number varchar2(10);

Foo_text number(10);

Foo_char char(1) := 'Y';

Foo_time date;

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

foo_text number(10 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.

Explanation

foo_text number(10 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.

19. Select the best answer: PACKAGE foo_foo IS PROCEDURE foo ( p_foo_text IN VARCHAR2 ); PROCEDURE foo (p_foo_number IN NUMBER); END;

Package specification is invalid. Too many procedures named foo.

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

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

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

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

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.

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.

20. Select the invalid PL/SQL looping construct.

WHILE LOOP ...END LOOP;

FOR rec IN some_cursor LOOP ...END LOOP;

LOOP ... UNTIL ;END LOOP;

LOOP ... EXIT WHEN ;END LOOP;

None of the above. All are valid.

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.

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.

21. 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;

The commit is fired 1000 times.

The commit is fired 100 times.

The commit is fired 10 times.

The commit is fired 1 time.

None of the above.

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.

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.

22. Which listed attribute is an invalid attribute of an Explicit cursor.

%NOTFOUND

%FOUND

%ROWCOUNT

%ISOPEN

None of the above. All of these are valid.

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.

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.

23. 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;

09/09/2009 09:09:09

09/09/2009 09:09:09AM

09/09/2009

09/09/2009 00:00:00

None of the above

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".

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".

24. Which of the following is not an Oracle DML function?

DECODE

TRUNCATE

TO_CHAR

NVL

Trick question, all of these are Oracle DML functions.

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

Explanation

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

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

NO_DATA_FOUND ORA-01403

TWO_MANY_ROWS ORA-01422

DUP_VAL_ON_INDEX ORA-00001

OTHERS

None of the above. These are all valid.

TWO_MANY_ROWS is not the correct name for the TOO_MANY_ROWS exception. Focus in the words "two" and "too".

Explanation

TWO_MANY_ROWS is not the correct name for the TOO_MANY_ROWS exception. Focus in the words "two" and "too".

26. 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 _________.

Variables

IN OUT Parameters

Name

Signature

None of the above

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.

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.

27. 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;

1

2

3

0

None of the above.

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".

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".

28. 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' )

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

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

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

This SQL will return a cartesian product.

None of the above.

The SQL query is designed to retrieve information (department name, first name, last name, title, and salary) about employees from joined tables employee and department. The WHERE clause specifies that:

Employees must have a salary greater than 20,000.

Their job title must be 'ANALYST'.

They must belong to either the 'FINANCE' or 'SALES' department.

This filtering ensures that only employees who meet all these conditions—being analysts with a specified salary threshold in either the Finance or Sales departments—are selected. The query does not produce a cartesian product because the join conditions and filters are not specified in the question but assumed to be based on a common column not explicitly shown in the SQL snippet.

Explanation

The SQL query is designed to retrieve information (department name, first name, last name, title, and salary) about employees from joined tables employee and department. The WHERE clause specifies that:

Employees must have a salary greater than 20,000.

Their job title must be 'ANALYST'.

They must belong to either the 'FINANCE' or 'SALES' department.

This filtering ensures that only employees who meet all these conditions—being analysts with a specified salary threshold in either the Finance or Sales departments—are selected. The query does not produce a cartesian product because the join conditions and filters are not specified in the question but assumed to be based on a common column not explicitly shown in the SQL snippet.