Oracle PL/SQL Quiz: Hardest Practice Test!

Reviewed by Samy Boulos

Samy Boulos, MSc (Computer Science) |

Data Engineer

Review Board Member

Samy Boulos is an experienced Technology Consultant with a diverse 25-year career encompassing software development, data migration, integration, technical support, and cloud computing. He leverages his technical expertise and strategic mindset to solve complex IT challenges, delivering efficient and innovative solutions to clients.

, MSc (Computer Science)

By Grvion

Grvion

Community Contributor

Quizzes Created: 1 | Total Attempts: 10,555

| Attempts: 10,562

Questions

Feedback

During the Quiz End of Quiz

Difficulty

Sequential Easy First Hard First

1/45 Questions

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

About This Quiz

Are you able to pass this Oracle PL/SQL test? This free procedural language is designed to run alongside the SQL within the same server process, bringing optimal efficiency. The Oracle PL, although not portable and doesn't interface that well with the tons of libraries available for other environments, can process the statements of the system query language with ease. By See moretaking this quiz, you will get to review how to use the languages back to back.

Oracle PL/SQL Quiz: Hardest Practice Test! - Quiz

Quiz Preview

2.

Supply the missing keyword DECLARE 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 statement is used to retrieve the next row from a cursor into a record variable. In this case, the FETCH statement is missing after the OPEN statement, which means that the code is not retrieving any rows from the cursor. Adding the FETCH statement after the OPEN statement will fetch the next row from the cursor into the record variable "r1".

Rate this question:

1
3.

Select invalid variable types.
- CHAR
- VARCHAR1
- VARCHAR2
- INTEGER
- NUMBER
Correct Answer

A. VARCHAR1

Explanation

In SQL-based database systems like Oracle, the valid variable types include CHAR, VARCHAR2, INTEGER, and NUMBER. However, there is no valid data type named VARCHAR1. The VARCHAR data type with a specified length is used in some SQL-based systems, but VARCHAR1 is not a recognized type in most databases. VARCHAR2 is commonly used to represent variable-length character strings, while CHAR is for fixed-length character strings.Thus, the correct answer is VARCHAR1, as it does not correspond to any known or widely accepted data type in SQL-based database systems. The other variable types are valid and widely used in database management.

Rate this question:
4.

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, 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 for the cursor to be initialized and prepared to retrieve data, the records to be fetched and processed, and then the cursor to be closed to release any resources it was using.

Rate this question:
5.

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

Correct Answer
CLOSE
6.

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

Explanation

In programming, the signature of a procedure refers to the specific details that define how it's used, including the name, parameter types, directions, and order. In this example, the procedure's signature comprises the name "foo" and the parameters p_foo_text (an input parameter of type VARCHAR2) and p_foo_number (an IN OUT parameter of type NUMBER). The signature is essential for understanding how to call the procedure and what type of data it expects.

Rate this question:
7.

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 named 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 given package specification is valid because it demonstrates overloading. Overloading allows multiple procedures with the same name but different parameter types or numbers to exist within a package. In this case, the package "foo_foo" has two procedures named "foo" with different parameter types - one takes a VARCHAR2 input and the other takes a NUMBER input. This allows for more flexibility and versatility in the usage of the package.

Rate this question:
8.

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. %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 specific column in a table. In this case, l_foo_column_id is being declared with the same data type as the SOME_COLUMN in the SOME_TABLE. This allows for consistency and avoids potential data type mismatches.

Rate this question:
9.

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. %ROWTYPE

Explanation

The correct answer is "%ROWTYPE". In PL/SQL, %ROWTYPE is a record type that represents a row in a table or a cursor. It allows you to declare variables that have the same structure as a row in a table, making it easier to manipulate data. In this case, "l_foo_table" is being declared as a variable with the same structure as a row in a table.

Rate this question:
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;
- Explicit
- Implicit
- Select
- PL/SQL
- None of the above
Correct Answer

A. Implicit

Explanation

The given code snippet does not explicitly declare a cursor, but it performs a select query and assigns the result to a variable using the "INTO" clause. This is known as an implicit cursor, where the cursor is automatically created and managed by the PL/SQL engine without the need for explicit declaration or manipulation. Therefore, the correct answer is "Implicit".

Rate this question:
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;
- Explicit
- Implicit
- Select
- PL/SQL
- None of the above
Correct Answer

A. Explicit

Explanation

This example illustrates an explicit cursor in PL/SQL. Explicit cursors are user-defined and require manual operations such as OPEN, FETCH, and CLOSE. In the given code snippet, the cursor c1 is declared explicitly, then explicitly opened, fetched into a variable (l_date), and closed. This cursor operates over a specific SQL query, allowing control over the fetch and loop operations.Implicit cursors, on the other hand, are automatically managed by Oracle for single SQL statements like SELECT INTO and don't require explicit OPEN or CLOSE operations."Explicit" is the best answer, as it highlights the explicit declaration and management of the cursor within the PL/SQL block, demonstrating a specific example of user-controlled cursor handling.

Rate this question:
12.

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 condition "IF foo IS NULL" is checked, and if it evaluates to true (i.e., foo is indeed NULL), then the code block following the "THEN" keyword will be executed. The "THEN" keyword is necessary to indicate the start of the code block that should be executed when the condition is true.

Rate this question:

1
13.

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

Explanation

The correct answer is ELSIF. In this code snippet, the keyword ELSIF is used to check if the value of the variable "foo" is equal to 2. If it is, then the variable "l_text" is assigned the value 'B'. If the value of "foo" is not equal to 2, then the variable "l_text" is assigned the value 'C'.

Rate this question:

1
14.

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 INTO keyword is used in a SELECT statement to specify the variables or columns where the retrieved data should be stored. In this case, the sysdate value is being selected from the dual table and stored into the variable l_date using the INTO keyword.

Rate this question:
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 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
Correct Answer

A. This SQL will return a cartesian product
16.

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 section

Explanation

In the PL/SQL declaration section, explicit cursors are declared in order to retrieve and manipulate data from the database. This section is where variables, constants, types, and cursors are declared before they are used in the PL/SQL block. By declaring the cursor in the declaration section, it allows for better organization and readability of the code, as well as making it easier to manage and maintain the cursor throughout the program.

Rate this question:
17.

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 correct answer is to use either "CREATE" or "CREATE OR REPLACE" to make a new stored procedure. 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:

1
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;
- 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 times

Explanation

The COMMIT statement will be executed 10 times because the loop iterates from 1 to 1000 and the IF condition checks if the current iteration number is divisible by 100. Since 1000 is divisible by 100, the condition will be true for 10 iterations (100, 200, 300, ..., 1000), resulting in the COMMIT statement being executed 10 times.

Rate this question:
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;
- 09/09/2009 09:09:09
- 09/09/2009 09:09:09 AM
- 09/09/2009
- 09/09/2009 00:00:00
- None of the above
Correct Answer

A. 09/09/2009 00:00:00

Explanation

The SELECT statement is using the TO_CHAR function to convert the TRUNC(SYSDATE) value into a specific format. TRUNC(SYSDATE) returns the current date with the time portion truncated to 00:00:00. The TO_CHAR function then formats this date as 'MM/DD/YYYY HH24:MI:SS'. Therefore, the statement will return '09/09/2009 00:00:00', which is the correct answer.

Rate this question:
20.

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

Explanation

The || symbol in the given query is used to concatenate or combine different strings together. In this case, it is used to concatenate the last name, a comma, first name, a space, middle name, and display the result as a single string. Therefore, the correct answer is Concatenation.

Rate this question:
21.

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

Explanation

In Oracle, TRUNCATE is not a DML (Data Manipulation Language) function. Instead, it's a Data Definition Language (DDL) command used to quickly remove all rows from a table without affecting its structure. This command does not generate individual DELETE operations, making it different from other DML functions.

Rate this question:

1
22.

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

Correct Answer
TO_CHAR

Explanation
The correct function to convert a date to an alphanumeric string in this query is TO_CHAR. This function is commonly used in SQL to convert a date or timestamp to a specific format. In this case, the sysdate function is used to get the current date, and 'MM/DD/YYYY' is the format specified for the conversion. The TO_CHAR function then converts the date to a string in the specified format, allowing it to be displayed as an alphanumeric value.

Rate this question:
23.

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.
Correct Answer

A. 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:
24.

Select the best answer. Which listed attribute is an invalid attribute of an Explicit cursor.
- %NOTFOUND
- %FOUND
- %ROWCOUNT
- %ISOPEN
- None of the above.
Correct Answer

A. None of the above.

Explanation

In PL/SQL, explicit cursors are user-defined cursors that require manual operations such as opening, fetching, and closing. These cursors have associated attributes that provide information about their state and result sets.Here are the attributes of explicit cursors:%NOTFOUND: Returns TRUE if the last fetch operation did not find a row.%FOUND: Returns TRUE if the last fetch operation found a row.%ROWCOUNT: Returns the number of rows fetched by the cursor.%ISOPEN: Returns TRUE if the cursor is open.Since all these attributes are valid for explicit cursors in PL/SQL, the correct answer is None of the above. All these attributes are commonly used to manage and track the state of explicit cursors.

Rate this question:

1
25.

Which of the following is not a grouping function.
- COUNT
- SUM
- DISTINCT
- MIN
- All of the above.
Correct Answer

A. 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:

1
26.

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 and return different results based on different conditions.

Rate this question:
27.

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-01422

Explanation

Oracle PL/SQL has various built-in exceptions to handle errors in code. Common exceptions include:NO_DATA_FOUND (ORA-01403): This exception is raised when a SELECT INTO statement does not return any data.TOO_MANY_ROWS (ORA-01422): This exception occurs when a SELECT INTO statement returns more than one row.DUP_VAL_ON_INDEX (ORA-00001): This exception is triggered when a duplicate value is inserted into a unique index.OTHERS: This generic exception handler catches any unhandled exceptions.Given that all these exceptions are valid in Oracle PL/SQL, the correct answer is None of the above. These are all valid. Each exception serves a distinct purpose within the context of Oracle PL/SQL error handling.

Rate this question:

1
28.

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.
Correct Answer

A. 0

Explanation

DECLARE
l_parent_number NUMBER := 1;
BEGIN
DECLARE
l_child_number NUMBER := 2;
BEGIN
-- l_parent_number and l_child_number are in scope
l_child_number := l_parent_number + l_child_number;
END;
-- l_child_number is no longer in scope and will
-- trigger an exception
DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_child_number));
EXCEPTION
WHEN OTHERS THEN
-- exception will fire and display 0
l_child_number := 0;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(l_child_number);
END;

Rate this question:

1
29.

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 correct answer is "SHOW ERRORS;". This command is used to display the errors from a recently created view or stored procedure. It allows the user to identify and troubleshoot any errors or issues that may have occurred during the creation process. The other options, "SHOW MISTAKES;", "DISPLAY MISTAKES;", and "DISPLAY ERRORS;", are not valid commands in this context.

Rate this question:
30.

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 body

Explanation

The components of a package typically include the specification, which provides details and instructions about the package, and the body, which contains the actual content or items being packaged. These two components are essential in ensuring that the package is properly labeled and its contents are protected and clearly identified. The other options, such as box, wrapping, binding, header, and content, may also be present in a package, but they are not the primary components that define a package.

Rate this question:
31.

The PLAYER table contains these columns: ID NUMBER(9) NAME VARCHAR(2) MANAGER_ID NUMBER(9) In this instance, managers are players, and you need to display a list of players. Evaluate these two SQL statements: SELECT p.name, m.name FROM player p, player m WHEREm.id = p.manager_id; SELECT p.name, m.name FROM player p, player m WHEREm.manager_id = p.id; How will the results differ?
- Statement 1 will not execute; statement 2 will.
- The results will be the same, but the display will be different.
- Statement 1 will execute; statement 2 will not
- Statement 1 is self-join; statement 2 is not.
Correct Answer

A. The results will be the same, but the display will be different.

Explanation

Both statements are joining the PLAYER table with itself using different aliases (p and m). In statement 1, the condition for the join is m.id = p.manager_id, which means that it is matching the manager_id of one player with the id of another player. In statement 2, the condition for the join is m.manager_id = p.id, which means that it is matching the manager_id of one player with the id of another player. Both statements will return the same results, but the display will be different because the columns are selected in a different order (p.name, m.name in statement 1 and m.name, p.name in statement 2).

Rate this question:
32.

Which statement is true when a DROP TABLE command is executed on a table?
- Any pending transactions on the table are rolled back.
- The table structure and its deleted data cannot be rolled back and restored once the DROP TABLE command is executed.
- The table structure and its deleted data cannot be rolled back and restored once the DROP TABLE command is executed.
- Only a DBA can execute the DROP TABLE command.
Correct Answer

A. The table structure and its deleted data cannot be rolled back and restored once the DROP TABLE command is executed.

Explanation

When a DROP TABLE command is executed on a table, the table structure and its deleted data cannot be rolled back and restored. This means that once the command is executed, the table and all its data will be permanently deleted from the database. This action cannot be undone, and it is not possible to retrieve the table or its data after the DROP TABLE command has been executed.

Rate this question:

0
33.

You issue this command: CREATE SYNONYM emp FOR ed.employee; Which task has been accomplished?
- The need to qualify an object name with its schema was eliminated for users with access.
- The need to qualify an object name with its schema was eliminated for only you.
- The need to qualify an object name with its schema was eliminated for user Ed.
- The need to qualify an object name with its schema was eliminated for all users.
Correct Answer

A. The need to qualify an object name with its schema was eliminated for only you.

Explanation

When you create a synonym in Oracle using the CREATE SYNONYM command, it creates an alias for an object, such as a table or view. This alias allows you to reference the object without having to specify its full schema-qualified name.
In this case, the command CREATE SYNONYM emp FOR ed.employee creates a synonym called emp for the employee table in the ed schema. However, unless it's a public synonym, only the user who creates the synonym can use it without schema qualification.

Since this command doesn't include the PUBLIC keyword, it creates a private synonym, meaning it is specific to the user who created it. Other users will still need to use the schema-qualified name unless they create their own synonym or a public synonym exists.

Rate this question:
34.

Which is NOT an SQL*Plus command?
- DESCRIBE
- CHANGE
- LIST
- UPDATE
Correct Answer

A. UPDATE

Explanation

The command UPDATE is not an SQL*Plus command. SQL*Plus is a command-line interface for Oracle Database that allows users to interact with the database using SQL commands. The DESCRIBE command is used to display the structure of a table or view. The CHANGE command is not a valid SQL*Plus command. The LIST command is used to display the contents of a table or view. However, the UPDATE command is used to modify the data in a table.

Rate this question:
35.

In which situation should you use an outer join query?
- The employee table column corresponding to the region table column contains null values for rows that need to be displayed.
- The employee table has two columns that correspond.
- The employee and region tables have corresponding columns.
- The employee and region tables have no corresponding columns.
Correct Answer

A. The employee table column corresponding to the region table column contains null values for rows that need to be displayed.

Explanation

An outer join query should be used when the employee table column corresponding to the region table column contains null values for rows that need to be displayed. This means that there are rows in the employee table that do not have a corresponding value in the region table, and we still want to include these rows in the result of the query. The outer join allows us to include these rows with null values in the result set.

Rate this question:
36.

Which two conditions in a PL/SQL block cause an exception to occur?
- The datatypes in the SELECT list are inconsistent with the datatypes in the INTO clause.
- The SELECT statement returns more than one row.
- The SELECT statement contains a GROUP BY clause.
- The SELECT statement does not have a WHERE clause.
Correct Answer(s)

A. The SELECT statement returns more than one row.
A. The SELECT statement does not have a WHERE clause.

Explanation

In a PL/SQL block, an exception occurs when the SELECT statement returns more than one row or when the SELECT statement does not have a WHERE clause. If the SELECT statement returns more than one row, it means that the query is retrieving multiple rows of data, which can cause issues when trying to assign the result to a single variable. On the other hand, if the SELECT statement does not have a WHERE clause, it means that all rows from the table will be selected, which can also lead to unexpected results or errors.

Rate this question:
37.

Which statement shows the view definition of the view EMP_VIEW that is created based on the table EMP?
- SELECT view_text FROM TABLE emp WHERE view_name = 'EMP_VIEW';
- SELECT view_text FROM my_views WHERE view_name = 'EMP_VIEW';
- DESCRIBE VIEW emp_view
- SELECT text FROM user_views WHERE view_name = 'EMP_VIEW';
Correct Answer

A. SELECT text FROM user_views WHERE view_name = 'EMP_VIEW';

Explanation

The correct answer is "SELECT text FROM user_views WHERE view_name = 'EMP_VIEW';". This statement retrieves the view definition of the view EMP_VIEW from the user_views table, where the view_name is 'EMP_VIEW'.

Rate this question:
38.

You have decided to permanently remove all the data from the STUDENT table, and you need the table structure in the future. Which single command performs this?
- DELETE * FROM student;
- DROP TABLE student;
- TRUNCATE TABLE student;
- DROP TABLE student;
Correct Answer

A. TRUNCATE TABLE student;

Explanation

The correct answer is TRUNCATE TABLE student; This command is used to permanently remove all the data from a table while keeping the table structure intact. It is a faster and more efficient way to delete all the records compared to using the DELETE command. The DROP TABLE command would delete the table structure as well, which is not what is required in this scenario.

Rate this question:
39.

Using SQL*Plus, you created a user with this command: CREATE USER Jennifer IDENTIFIED BY jbw122; What should you do to allow the user database access?
- Use the ALTER USER to assign the user a default profile.
- Use the ALTER USER command to assign the user a default tablespace.
- No action is required to give the user database access.
- Grant the user the CREATE SESSION privilege.
Correct Answer

A. Grant the user the CREATE SESSION privilege.

Explanation

To allow the user database access, you should grant the user the CREATE SESSION privilege. This privilege allows the user to establish a session and connect to the database. Without this privilege, the user will not be able to access the database.

Rate this question:
40.

You are a user of the PROD database which contains over 1000 tables, and you need to determine the number of tables you can access. Which data dictionary view could you query to display this information?
- ALL_OBJECTS
- DBA_SEGMENTS
- USER_OBJECTS
- DBA_TABLES
Correct Answer

A. ALL_OBJECTS

Explanation

The correct answer is ALL_OBJECTS. The ALL_OBJECTS data dictionary view contains information about all objects (tables, views, indexes, etc.) accessible to the current user. By querying this view, you can retrieve the number of tables you can access in the PROD database. The other options (DBA_SEGMENTS, USER_OBJECTS, DBA_TABLES) are not suitable for this purpose as they either provide information about segments, objects owned by the user, or all tables in the database (including those not accessible to the user).

Rate this question:
41.

In which section of a PL/SQL block is a user-defined exception raised?
- Exception handling
- Executable
- Declarative
- Heading
Correct Answer

A. Executable

Explanation

In a PL/SQL block, a user-defined exception is raised within the executable section. This section contains the actual code that is executed when the block is run. It is where the program logic is written and any exceptions that occur during the execution of this code are handled. The executable section is responsible for performing calculations, making database queries, and executing other statements. Therefore, it is the appropriate section for raising a user-defined exception.

Rate this question:
42.

Evaluate this IF statement: IF v_value > 100 THEN v_new_value := 2 * v_value; ELSIF v_value > 200 THEN v_new_value := 3 * v_value; ELSIF v_value < 300 THEN v_new_value := 4 * v_value; ELSE v_new_value := 5 * v_value; END IF; What would be assigned to V_NEW_VALUE if V_VALUE is 250?
- 250
- 1250
- 500
- 750
Correct Answer

A. 500

Explanation

If V_VALUE is 250, the first condition (V_VALUE > 100) is true, but the second condition (V_VALUE > 200) is false. Therefore, the second branch is not executed. The third condition (V_VALUE < 300) is also true. So, the third branch is executed, and V_NEW_VALUE is assigned the value of 4 * V_VALUE, which is 4 * 250 = 1000. Therefore, the correct answer is 500.

Rate this question:
43.

Examine the structure of the STUDENT table: Name Null?Type------------------ -------------- ------------STUD_ID NOT NULL NUMBER(3) NAME NOT NULL VARCHAR2(25) ADDRESS VARCHAR2(50) GRADUATION DATE Graduation column is a foreign key column to the GRADDATE table. " Examine the data in the GRADDATE table: GRADUATION--------------20-JAN-1999 12-MAY-1999 19-JAN-2000 25-MAY-2000 13-JAN-2001 29-MAY-2001 Which update statement produces the following error? ORA-02291: integrity constraint (SYS_C23) violated - parent key not found
- UPDATE student SET name = 'Smith', graduation = '15-AUG-2000' WHERE stud_id = 101;
- UPDATE studentD.UPDATE student SET stud_id = NULL, address = '100 Main Street' WHERE graduation = '20-JAN-1999';
- UPDATE student SET name = 'Smith', graduation = '29-MAY-2001' WHERE stud_id = 101;
- UPDATE student SET stud_id = 999, graduation = '29-MAY-2001' WHERE stud_id = 101;
Correct Answer

A. UPDATE student SET name = 'Smith', graduation = '15-AUG-2000' WHERE stud_id = 101;

Explanation

The update statement that produces the error is "UPDATE student SET name = 'Smith', graduation = '15-AUG-2000' WHERE stud_id = 101;". This is because the value '15-AUG-2000' in the graduation column does not exist in the GRADDATE table, which is referenced as a foreign key in the STUDENT table. The error is caused by the violation of the integrity constraint, as the parent key (value in the GRADDATE table) is not found.

Rate this question:
44.

The EMPLOYEE table contains these columns: FIRST_NAMEVARCHAR2(25) COMMISSION NUMBER(3,2) Evaluate this SQL statement: SELECT first_name, commission " FROM employee WHERE commission = (SELECTcommission FROMemployee WHEREUPPER(first_name) = 'SCOTT') What would cause this statement to fail?
- A. There is more than one employee with the first name Scott.
- The FIRST_NAME values in the database are in lowercase
- Scott has a NULL commission value.
- Scott has a zero commission value.
Correct Answer

A. A. There is more than one employee with the first name Scott.

Explanation

This statement would fail if there is more than one employee with the first name Scott. This is because the subquery in the WHERE clause is expecting to return a single value for the commission of the employee with the first name Scott. If there are multiple employees with the same first name, the subquery will return multiple values and the statement will fail.

Rate this question:
45.

The EMPLOYEE table contains these columns: FIRST_NAMEVARCHAR2(25) COMMISSION NUMBER(3,2) Evaluate this SQL statement: SELECT first_name, commission " FROM employee WHERE commission = (SELECTcommission FROM employee WHERE UPPER(first_name) = 'SCOTT') What would cause this statement to fail?
- A. There is more than one employee with the first name Scott.
- The FIRST_NAME values in the database are in lowercase
- Scott has a NULL commission value.
- Scott has a zero commission value.
Correct Answer

A. A. There is more than one employee with the first name Scott.

Explanation

This statement would fail if there is more than one employee with the first name Scott. The subquery in the WHERE clause is used to retrieve the commission value for the employee with the first name Scott. However, if there are multiple employees with the same first name, the subquery will return multiple commission values, which will cause the statement to fail.

Rate this question:

Samy Boulos |MSc (Computer Science) |

Data Engineer

Quiz Review Timeline (Updated): Apr 24, 2024 +

Our quizzes are rigorously reviewed, monitored and continuously updated by our expert board to maintain accuracy, relevance, and timeliness.

Current Version
Apr 24, 2024

Quiz Edited by
ProProfs Editorial Team

Expert Reviewed by
Samy Boulos
May 25, 2012

Quiz Created by
Grvion

Oracle PL/SQL Quiz: Hardest Practice Test!

Select incorrect variable declarations

Quiz Preview

Supply the missing keyword DECLARE 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;

Select invalid variable types.

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

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

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 _________

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

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

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

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;

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;

Supply the missing keyword IF foo IS NULL _________ NULL; END IF;

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

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

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

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

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

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;

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;

The || is is an example of what functionSELECT last_name || ', ' || first_name || ' ' || middle_nameFROM employees;

Which of the following is not an Oracle DML function?

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

Select the invalid PL/SQL looping construct.

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

Which of the following is not a grouping function.

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

What command can you use to see the errors from a recently created view or stored procedure?

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

Which statement is true when a DROP TABLE command is executed on a table?

You issue this command: CREATE SYNONYM emp FOR ed.employee; Which task has been accomplished?

Which is NOT an SQL*Plus command?

In which situation should you use an outer join query?

Which two conditions in a PL/SQL block cause an exception to occur?

Which statement shows the view definition of the view EMP_VIEW that is created based on the table EMP?

You have decided to permanently remove all the data from the STUDENT table, and you need the table structure in the future. Which single command performs this?

Using SQL*Plus, you created a user with this command: CREATE USER Jennifer IDENTIFIED BY jbw122; What should you do to allow the user database access?

You are a user of the PROD database which contains over 1000 tables, and you need to determine the number of tables you can access. Which data dictionary view could you query to display this information?

In which section of a PL/SQL block is a user-defined exception raised?

Evaluate this IF statement: IF v_value > 100 THEN v_new_value := 2 * v_value; ELSIF v_value > 200 THEN v_new_value := 3 * v_value; ELSIF v_value < 300 THEN v_new_value := 4 * v_value; ELSE v_new_value := 5 * v_value; END IF; What would be assigned to V_NEW_VALUE if V_VALUE is 250?

The EMPLOYEE table contains these columns: FIRST_NAMEVARCHAR2(25) COMMISSION NUMBER(3,2) Evaluate this SQL statement: SELECT first_name, commission " FROM employee WHERE commission = (SELECTcommission FROMemployee WHEREUPPER(first_name) = 'SCOTT') What would cause this statement to fail?

The EMPLOYEE table contains these columns: FIRST_NAMEVARCHAR2(25) COMMISSION NUMBER(3,2) Evaluate this SQL statement: SELECT first_name, commission " FROM employee WHERE commission = (SELECTcommission FROM employee WHERE UPPER(first_name) = 'SCOTT') What would cause this statement to fail?