0

databases Online Quiz - 171

Description: databases Online Quiz - 171
Number of Questions: 20
Created by:
Tags: databases
Attempted 0/20 Correct 0 Score 0

Query to view installed Oracle version information ?

    1. select banner from v$version;
    1. select version from v$instance;
    1. select version from v$version;
  1. Both 1 and 2

  2. Both 2 and 3


Correct Option: D

Which is not a valid PL/SQL Exception ?

  1. Too_many_rows

  2. No_Data_Found

  3. Divide_Error

  4. Zero_Error


Correct Option: C

Which is not a valid PL/SQL Cursor Exceptions?

  1. Cursor_Already_Open

  2. Cursor_Not_Closed

  3. Invalid_Cursor

  4. None of the Above


Correct Option: B

Which one is not a valid pseudo-columns?

  1. USER

  2. UID

  3. CURVAL

  4. All are valid


Correct Option: D

What is correct regarding output of SIGN function?

  1. >0 for positive value

  2. 0 for positive value

  3. 0 for negative value

  4. 1 for positive value


Correct Option: D

The command used to open a CURSOR FOR loop is

  1. open

  2. fetch

  3. parse

  4. None


Correct Option: D

AI Explanation

To answer this question, you need to understand the concept of the CURSOR FOR loop in programming.

The correct answer is D) None.

In Oracle PL/SQL, the command used to open a CURSOR FOR loop is not explicitly mentioned. The CURSOR FOR loop implicitly opens the cursor and fetches the rows from the cursor one by one until there are no more rows to fetch. The syntax for the CURSOR FOR loop is as follows:

FOR  IN 
LOOP
    -- Statements to process each fetched row
END LOOP;

So, there is no specific command used to open the CURSOR FOR loop in Oracle PL/SQL. The loop itself takes care of opening and fetching the rows from the cursor.

Therefore, the correct answer is D) None.

Which procedure can be used to create a customized error message?

  1. RAISE_ERROR

  2. SQLERRM

  3. RAISE_APPLICATION_ERROR

  4. RAISE_SERVER_ERROR


Correct Option: C
Explanation:

To solve this question, the user needs to have knowledge of error handling in SQL.

Now, let's go through each option and explain why it is right or wrong:

A. RAISE_ERROR: This option is not a valid procedure to create a custom error message in SQL. RAISE_ERROR is not a standard Oracle SQL function.

B. SQLERRM: This option is not a procedure to create a custom error message, but it is a function that returns the error message associated with the most recently raised error exception. It is not used to raise custom exceptions.

C. RAISE_APPLICATION_ERROR: This option is correct. RAISE_APPLICATION_ERROR is a procedure used to create a customized error message in Oracle SQL. It allows an application to raise an error with a user-defined error message and error number.

D. RAISE_SERVER_ERROR: This option is not a valid procedure to create a custom error message in SQL. RAISE_SERVER_ERROR is not a standard Oracle SQL function.

Therefore, the correct answer is: C. RAISE_APPLICATION_ERROR

What happens when rows are found using a FETCH statement ?

  1. It causes the cursor to close

  2. It loads the current row values into variables

  3. It causes the cursor to open

  4. It creates the variables to load the values


Correct Option: B
Explanation:

When rows are found using a FETCH statement in SQL, it loads the current row values into variables.

Option B is the correct answer. The FETCH statement is used to retrieve rows from a cursor, and each time it is executed, it fetches the next row from the cursor result set and assigns the values to the specified variables. This allows us to work with the data retrieved from the cursor.

Here is a breakdown of the other options:

A. It causes the cursor to close: This option is incorrect. The FETCH statement does not close the cursor; it only retrieves the rows. The cursor can be closed explicitly using the CLOSE statement.

C. It causes the cursor to open: This option is incorrect. The FETCH statement is used after the cursor has been declared and opened. It does not open the cursor itself.

D. It creates the variables to load the values: This option is incorrect. The variables to hold the row values need to be declared before the FETCH statement is executed. The FETCH statement does not create the variables, but rather assigns the retrieved values to the existing variables.

Therefore, the correct answer is B. It loads the current row values into variables.

Which two statements are true? (Choose two)

  1. a) A function must return a value.

  2. b) A procedure must return a value.

  3. c) A function executes a PL/SQL statement.

  4. d) A function is invoked as part of an expression.

  5. e) A procedure must have a return data type specify in its declaration.


Correct Option: A,D

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) A function must return a value - This option is correct. A function is a subprogram that performs a specific task and returns a value. When a function is executed, it calculates a value and returns it to the calling program.

Option B) A procedure must return a value - This option is incorrect. Unlike a function, a procedure does not have to return a value. A procedure is a subprogram that performs a specific task, but it does not return a value.

Option C) A function executes a PL/SQL statement - This option is incorrect. A function can execute multiple PL/SQL statements, but it is not limited to executing only PL/SQL statements. A function can also perform calculations, access database tables, and perform other operations.

Option D) A function is invoked as part of an expression - This option is correct. A function can be called within an expression to perform a specific calculation or operation. The result of the function call is then used as part of the overall expression.

Option E) A procedure must have a return data type specified in its declaration - This option is incorrect. As mentioned earlier, a procedure does not have to return a value. Therefore, it does not require a return data type to be specified in its declaration.

The two statements that are true are Option A) A function must return a value, and Option D) A function is invoked as part of an expression.

CREATE OR REPLACE PROCEDURE find_cpt (v_movie_id {Argument Mode} NUMBER, v_cost_per_ticket {argument mode} NUMBER) IS BEGIN IF v_cost_per_ticket > 8.5 THEN SELECT cost_per_ticket INTO v_cost_per_ticket FROM gross_receipt WHERE movie_id = v_movie_id; END IF; END; Which mode should be used for V_COST_PER_TICKET?

  1. IN OUT

  2. IN

  3. OUT

  4. RETURN


Correct Option: A
  1. a) In the package body.

  2. b) In the data base triggers.

  3. c) In the package specification.

  4. d) In the procedures declare section using the exact name in each.


Correct Option: A

For which trigger timing can you reference the NEW and OLD qualifiers?

  1. Statement and Row

  2. Statement only

  3. Row only

  4. Oracle Forms trigger


Correct Option: C

AI Explanation

To answer this question, you need to understand the concept of trigger timing in database systems.

In Oracle, trigger timing refers to when a trigger is fired during the execution of a SQL statement. There are two types of trigger timing: statement-level and row-level.

Statement-level triggers are fired once for each triggering statement, regardless of the number of rows affected by the statement. These triggers do not have access to the individual rows that were affected by the statement. Therefore, you cannot reference the NEW and OLD qualifiers in statement-level triggers.

On the other hand, row-level triggers are fired once for each affected row within the triggering statement. These triggers have access to the individual rows that were affected by the statement. In row-level triggers, you can reference the NEW and OLD qualifiers to access the new and old values of the affected rows.

Based on this information, the correct answer is:

C. Row only - This option is correct because you can reference the NEW and OLD qualifiers in row-level triggers, but not in statement-level triggers.

Examine this code: CREATE OR REPLACE STORED FUNCTION get_sal (p_raise_amt NUMBER, p_employee_id employees.employee_id%TYPE) RETURN NUMBER IS v_salary NUMBER; v_raise NUMBER(8,2); BEGIN SELECT salary INTO v_salary FROM employees WHERE employee_id = p_employee_id; v_raise := p_raise_amt * v_salary; RETURN v_raise; END; Which statement is true?

  1. a) This statement creates a stored procedure named get_sal.

  2. b) This statement returns a raise amount based on an employee id.

  3. c) This statement creates a stored function named get_sal with a status of invalid.

  4. d) This statement creates a stored function named get_sal.

  5. e) This statement fails.


Correct Option: E

When using a packaged function in a query, what is true?

  1. a) The COMMIT and ROLLBACK commands are allowed in the packaged function.

  2. b) You can not use packaged functions in a query statement.

  3. c) The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried.

  4. d) The packaged function can execute and INSERT, UPDATE, or DELETE statement against the table that is being queried if it is used in a subquery.

  5. e) The packaged function can execute an INSERT, UPDATEM or DELETE statement against the table that is being queried if the pragma RESTRICT REFERENCE is used.


Correct Option: C

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) The COMMIT and ROLLBACK commands are allowed in the packaged function. - This option is incorrect. In a packaged function used in a query, you cannot use the COMMIT and ROLLBACK commands because they are transaction control commands and are not allowed in a query context.

Option B) You cannot use packaged functions in a query statement. - This option is incorrect. Packaged functions can be used in a query statement.

Option C) The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried. - This option is correct. Packaged functions used in a query cannot execute any DML (Data Manipulation Language) statements like INSERT, UPDATE, or DELETE against the table that is being queried. They are only allowed to perform read operations.

Option D) The packaged function can execute an INSERT, UPDATE, or DELETE statement against the table that is being queried if it is used in a subquery. - This option is incorrect. Packaged functions used in a query cannot execute any DML statements, even if they are used in a subquery.

Option E) The packaged function can execute an INSERT, UPDATE, or DELETE statement against the table that is being queried if the pragma RESTRICT REFERENCE is used. - This option is incorrect. The pragma RESTRICT REFERENCE is used to restrict the ability of a packaged function to modify data. However, even with this pragma, a packaged function used in a query cannot execute any DML statements against the table being queried.

The correct answer is C) The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried. This option is correct because packaged functions used in a query are restricted to read operations only.

Which three are true regarding error propagation? (Choose three)

  1. a) An exception cannot propagate across remote procedure calls.

  2. b) An exception raised inside a declaration immediately propagates to the current block.

  3. c) The use of the RAISE; statement in an exception handler reprises the current exception

  4. d) An exception raised inside an exception handler immediately propagates to the enclosing block.


Correct Option: A,C,D

Which procedure of the dbms_output supply package would you use to append text to the current line of the output buffer?

  1. a) GET.

  2. b) GET_LINE.

  3. c) PUT_TEXT_LINE.

  4. d) PUT_LINE.


Correct Option: D

The first prime minister of Bangladesh was

  1. Manmohan

  2. Sonia Gnadhi

  3. Mujibur Rahman

  4. Obama


Correct Option: B,C

An Oracle instance is executing in a nondistributed configuration. The instance fails because of an operating system failure. Which background process would perform the instance recovery when the atabase is reopened ?

  1. PMON

  2. SMON

  3. RECO

  4. ARCn

  5. CKPT


Correct Option: B

A tablespace has a table with 30 extents in it. Is this bad?

  1. True

  2. False


Correct Option: B

You need to enforce these two business rules: 1. No two rows of a table can have duplicate values in the specified column. 2. A column cannot contain null values. Which type of constraint ensure that both of the above rules are true ?

  1. Check

  2. Unique

  3. Not Null

  4. Primary Key

  5. Foreign Key


Correct Option: D
- Hide questions