Oracle PL/SQL Quiz: Hardest Practice Test!

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

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.

Submit

3. Enter the missing keyword to make a new stored procedure.

____________ PROCEDURE foo
( p_foo_text IN VARCHAR2 ) AS
BEGIN
NULL;
END;

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.

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.

Submit

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

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

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

Submit

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

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

Incantination

Integration

Continuation

Concatenation

None of the above

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.

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.

6. Select invalid variable types.

CHAR

VARCHAR1

VARCHAR2

INTEGER

NUMBER

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.

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.

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

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.

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

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

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.

Submit

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

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.

Submit

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

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.

Submit

12. 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 declaration is problematic because it uses a variable name ("foo_text") that suggests a text-based value but assigns it a numeric data type (number(10)). This inconsistency creates confusion about the variable's intended use. Variables named with "text" generally indicate a character-based data type like varchar2 or char. A correct variable declaration would align the name with the appropriate data type.

Explanation

This declaration is problematic because it uses a variable name ("foo_text") that suggests a text-based value but assigns it a numeric data type (number(10)). This inconsistency creates confusion about the variable's intended use. Variables named with "text" generally indicate a character-based data type like varchar2 or char. A correct variable declaration would align the name with the appropriate data type.

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

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.

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.

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

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.

15. 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 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'.

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

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

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.

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.

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

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.

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.

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

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.

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.

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

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

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

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.

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.

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

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

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.

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.

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

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.

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.

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

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.

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

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.

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.

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

%NOTFOUND

%FOUND

%ROWCOUNT

%ISOPEN

None of the above.

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.

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.

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

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.

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.

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

DECODE

TRUNCATE

TO_CHAR

NVL

Trick question, all of these are Oracle DML functions.

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.

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.

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

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

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

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

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.

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.

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

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;

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;

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

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.

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

Only a DBA can execute the DROP TABLE command.

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.

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.

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

Explanation

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

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.

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.

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

Exception handling

Executable

Declarative

Heading

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.

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.

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

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

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

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

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.

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.

43. Which is NOT an SQL*Plus command?

DESCRIBE

CHANGE

LIST

UPDATE

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.

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.

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

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

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

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

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.

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.

Submit