Oracle PL/SQL Quiz Questions And Answers!
(31).jpg "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. VARCHAR1Explanation
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, CLOSEExplanation
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
CLOSEExplanation
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. %TYPEExplanation
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. %ROWTYPEExplanation
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. ImplicitExplanation
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. ExplicitExplanation
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
THENExplanation
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. ELSIFExplanation
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 REPLACEExplanation
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:00Explanation
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. ConcatenationExplanation
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. TRUNCATEExplanation
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_CHARExplanation
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
CASEExplanation
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 bodyExplanation
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 ParametersExplanation
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
INTOExplanation
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. DISTINCTExplanation
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-01422Explanation
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. 3Explanation
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:
-
Quiz Review Timeline +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Jan 16, 2024Quiz Edited by
ProProfs Editorial Team
Expert Reviewed by
Godwin Iheuwa -
Sep 04, 2009Quiz Created by
Ibecruzin
Back to top