Oracle PL/SQL Quiz: Trivia Exam!
-
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';
-
This Oracle PL\/SQL Trivia Exam tests your knowledge on variable declarations, cursor operations, procedure signatures, and package specifications. It's ideal for learners looking to validate their PL\/SQL skills and understanding of database programming concepts.
(168).jpg "Oracle PL/SQL Quiz: Trivia Exam! - Quiz")
Quiz Preview
- 2.
Select invalid variable types.
-
CHAR
-
VARCHAR1
-
VARCHAR2
-
INTEGER
-
NUMBER
Correct Answer
A. VARCHAR1Explanation
VARCHAR1 is not an acceptable variable type. Only VARCHAR and VARCHAR2 exist.Rate this question:
-
- 3.
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
Correct Answer
A. 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 one by one, and finally close the cursor. Opening the cursor initializes it and prepares it for fetching records. Fetching retrieves the records from the cursor one by one. Closing the cursor releases any resources associated with it. This sequence ensures that the cursor is properly initialized, records are retrieved, and resources are freed after processing.Rate this question:
-
- 4.
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
Correct Answer
A. SignatureExplanation
The terms "p_foo_text" and "p_foo_number" in the procedure "foo" are referred to as its signature. The signature of a procedure includes the names and data types of its parameters, which in this case are "IN VARCHAR2" and "IN OUT NUMBER".Rate this question:
-
- 5.
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 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.
Correct Answer
A. Package specification is valid. This is an example of overloading.Explanation
The package specification is valid because it demonstrates the concept of overloading. Overloading allows multiple procedures with the same name to exist in the same package, as long as they have different parameter lists. In this case, the package has two procedures named foo, but they have different parameter types (VARCHAR2 and NUMBER). This allows for flexibility and convenience when calling the procedures, as the appropriate procedure can be automatically selected based on the parameter types provided.Rate this question:
-
- 6.
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.
Correct Answer
A. %TYPEExplanation
The correct answer is "%TYPE". In PL/SQL, "%TYPE" is used to declare a variable that has the same data type as a specified column in a table. In this case, the variable "l_foo_column_id" will have the same data type as the "SOME_COLUMN" in the "SOME_TABLE". This allows for flexibility and ensures that the variable matches the data type of the column it is referencing.Rate this question:
-
- 7.
Select the best answer to complete this variable declaration for a record.DECLARE l_foo_table SOME_TABLE_________;BEGIN...
-
%TABLE
-
%ROWTYPE
-
%COLUMNTYPE
-
%TYPE
-
None of the above
Correct Answer
A. %ROWTYPEExplanation
The correct answer is "%ROWTYPE". In PL/SQL, "%ROWTYPE" is used to declare a record variable that has the same structure as a table or cursor row. It allows the record variable to have the same columns and data types as the table or cursor row, making it easier to manipulate and assign values to the record variable.Rate this question:
-
- 8.
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
Correct Answer
A. ImplicitExplanation
The given code snippet does not explicitly declare a cursor using the CURSOR keyword. Instead, it uses a SELECT statement to fetch data into the variable l_date directly. This is an example of an implicit cursor, where the cursor is automatically created and managed by the Oracle database system without the need for explicit declaration and control.Rate this question:
-
- 9.
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
Correct Answer
A. ExplicitExplanation
The given code snippet explicitly declares a cursor named c1 using the CURSOR keyword. This indicates that the cursor is explicitly defined by the programmer. Therefore, the correct answer is Explicit.Rate this question:
-
- 10.
Enter the missing keywordIF 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
Correct Answer
A. ELSIFExplanation
The correct keyword to fill in the blank is "ELSIF". In this code snippet, the IF statement checks the value of the variable "foo". If foo is equal to 1, then the variable "l_text" is assigned the value 'A'. If foo is equal to 2, then "l_text" is assigned the value 'B'. If none of these conditions are met, then "l_text" is assigned the value 'C'. The keyword "ELSIF" is used to check for additional conditions after the initial IF statement.Rate this question:
-
- 11.
Describe the result set that will be obtained from this joinSELECT 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' )
-
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
Correct Answer
A. This SQL will return a cartesian product -
- 12.
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
Correct Answer
A. In the PL/SQL declaration sectionExplanation
In the PL/SQL language, an explicit cursor is declared in the PL/SQL declaration section. This section is where variables, constants, and cursors are declared before they are used in the program. By declaring the cursor in this section, it allows the program to define the cursor and its associated query before executing it. This helps in organizing the code and making it more readable and maintainable.Rate this question:
-
- 13.
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;
-
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.
Correct Answer
A. The commit is fired 10 timesExplanation
The commit is fired 10 times because the loop iterates from 1 to 1000, and the IF statement checks if the current iteration is a multiple of 100. Since there are 10 multiples of 100 between 1 and 1000 (100, 200, 300, ..., 1000), the COMMIT statement will be executed 10 times.Rate this question:
-
- 14.
Assuming the date and time is 09/09/2009 09:09:09, what value will the following statement returnSELECT 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
Correct Answer
A. 09/09/2009 00:00:00Explanation
The given statement uses the TRUNC function to truncate the current date (SYSDATE) to the nearest day. The TO_CHAR function then converts this truncated date to a string format with the format 'MM/DD/YYYY HH24:MI:SS'. Since the time portion of the date is truncated, it will be set to 00:00:00. Therefore, the statement will return the value '09/09/2009 00:00:00'.Rate this question:
-
- 15.
The || is is an example of what functionSELECT last_name || ', ' || first_name || ' ' || middle_nameFROM employees;
-
Incantination
-
Integration
-
Continuation
-
Concatenation
-
None of the above
Correct Answer
A. ConcatenationExplanation
The || symbol in the given query is used to concatenate or join different strings together. In this case, it is used to concatenate the last name, a comma, first name, a space, middle name, and so on. Therefore, the correct answer is "Concatenation".Rate this question:
-
- 16.
Which of the following is not an Oracle DML function?
-
DECODE
-
TRUNCATE
-
TO_CHAR
-
NVL
-
Trick question, all of these are Oracle DML functions.
Correct Answer
A. TRUNCATEExplanation
Do not confuse TRUNCATE with TRUNC. Truncate is used to remove all rows from an Oracle table.Rate this question:
-
- 17.
Select the best answer. Which listed attribute is an invalid attribute of an Explicit cursor.
-
%NOTFOUND
-
%FOUND
-
%ROWCOUNT
-
%ISOPEN
-
None of the above. All of these are valid.
Correct Answer
A. None of the above. All of these are valid.Explanation
All of the listed attributes (%NOTFOUND, %FOUND, %ROWCOUNT, %ISOPEN) are valid attributes of an Explicit cursor.Rate this question:
-
- 18.
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.
Correct Answer
A. TWO_MANY_ROWS ORA-01422Explanation
TWO_MANY_ROWS is not the correct name for the TOO_MANY_ROWS exception.Rate this question:
-
- 19.
What command can you use to see the errors from a recently created view or stored procedure?
-
SHOW MISTAKES;
-
DISPLAY MISTAKES;
-
DISPLAY ERRORS;
-
SHOW ERRORS;
-
None of the above.
Correct Answer
A. SHOW ERRORS;Explanation
The command "SHOW ERRORS;" can be used to see the errors from a recently created view or stored procedure. This command allows users to view any errors or mistakes that may have occurred during the creation or execution of the view or stored procedure. It provides a way to identify and troubleshoot any issues or errors in the code.Rate this question:
-
- 20.
Select the best answer below. What are the components of a package?
-
Box, wrapping and binding
-
Header and body
-
Specification and content
-
Specification and body
-
None of the above
Correct Answer
A. 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 main part or content of the package. These components are essential in ensuring that the package is properly designed and contains the necessary information or items. The other options listed, such as box, wrapping, binding, header, and content, may be related to packaging but do not accurately represent the main components of a package.Rate this question:
-
Quiz Review Timeline (Updated): Mar 22, 2023 +
Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.
-
Current Version
-
Mar 22, 2023Quiz Edited by
ProProfs Editorial Team -
May 10, 2017Quiz Created by
Asgari.sematec
Back to top