0

databases Online Quiz - 144

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

You created stored procedure DELETE_TEMP_TABLE that uses dynamic SQL to remove a table in your schema. You have granted the EXECUTE privilege to user A on this procedure. When user A executes the DELETE_TEMP_TABLE procedure, under whose privileges are the operations performed by default?

  1. SYS privileges

  2. Your privileges

  3. Public privileges

  4. User A’s privileges


Correct Option: D

AI Explanation

To answer this question, let's go through each option:

Option A) SYS privileges - This option is incorrect because the operations performed by the DELETE_TEMP_TABLE procedure are not performed under the SYS privileges. The SYS privileges are typically reserved for the system administrator.

Option B) Your privileges - This option is incorrect because the operations performed by the DELETE_TEMP_TABLE procedure are not performed under your privileges. Your privileges are separate from the privileges granted to user A.

Option C) Public privileges - This option is incorrect because the operations performed by the DELETE_TEMP_TABLE procedure are not performed under the public privileges. Public privileges typically refer to privileges granted to all users.

Option D) User A's privileges - This option is correct. When user A executes the DELETE_TEMP_TABLE procedure, the operations are performed under user A's privileges. The EXECUTE privilege granted to user A on the procedure allows user A to execute the procedure and perform the operations specified within it.

Therefore, the correct answer is option D. The operations performed by the DELETE_TEMP_TABLE procedure are performed under user A's privileges.

Which code can you use to ensure that the salary is not increased by more than 10% at a time nor is it ever decreased?

  1. ALTER TABLE emp ADD CONSTRAINT ck_sal CHECK (sal BETWEEN sal AND sal*1.1);

  2. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp - 6 - FOR EACH ROW WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, ‘Do not decrease salary not increase by more than 10%’); END;

  3. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp WHEN (new.sal < old.sal OR new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, ‘Do not decrease salary not increase by more than 10%’); END;

  4. CREATE OR REPLACE TRIGGER check_sal AFTER UPDATE OR sal ON emp WHEN (new.sal < old.sal OR -new.sal > old.sal * 1.1) BEGIN RAISE_APPLICATION_ERROR ( - 20508, ‘Do not decrease salary not increase by more than 10%’); END;


Correct Option: B

Examine the code and state which of the following options is correct? CREATE OR REPLACE PACKAGE bonus IS g_max_bonus NUMBER := .99; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER; FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER; END; / CREATE OR REPLACE PACKAGE BODY bonus IS v_salary employees.salary%TYPE; v_bonus employees.commission_pct%TYPE; FUNCTION calc_bonus (p_emp_id NUMBER) RETURN NUMBER - 7 - IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employee_id = p_emp_id; RETURN v_bonus * v_salary; END calc_bonus FUNCTION calc_salary (p_emp_id NUMBER) RETURN NUMBER IS BEGIN SELECT salary, commission_pct INTO v_salary, v_bonus FROM employees WHERE employees RETURN v_bonus * v_salary + v_salary; END cacl_salary; END

  1. You can call the BONUS.CALC_SALARY packaged function from an INSERT command against the EMPLOYEES table.

  2. You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table.

  3. You can call the BONUS.CALC_SALARY packaged function form a DELETE command against the EMPLOYEES table.

  4. You can call the BONUS.CALC_SALARY packaged function from an UPDATE command against the EMPLOYEES table.


Correct Option: B

AI Explanation

To answer this question, let's examine the code provided.

The given code defines a package called bonus with two functions: calc_bonus and calc_salary. The package body then implements these functions.

Option A) You can call the BONUS.CALC_SALARY packaged function from an INSERT command against the EMPLOYEES table. This option is incorrect because the calc_salary function is not designed to be used in an INSERT command. It is meant to calculate and return the salary based on the employee ID.

Option B) You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table. This option is correct. The calc_salary function can be called in a SELECT command to retrieve the calculated salary for a specific employee ID.

Option C) You can call the BONUS.CALC_SALARY packaged function from a DELETE command against the EMPLOYEES table. This option is incorrect. The calc_salary function is not designed to be used in a DELETE command. It is meant to calculate and return the salary based on the employee ID.

Option D) You can call the BONUS.CALC_SALARY packaged function from an UPDATE command against the EMPLOYEES table. This option is incorrect. The calc_salary function is not designed to be used in an UPDATE command. It is meant to calculate and return the salary based on the employee ID.

Therefore, the correct answer is B) You can call the BONUS.CALC_SALARY packaged function from a SELECT command against the EMPLOYEES table.

Which statement is valid when removing procedures?

  1. Use a drop procedure statement to drop a standalone procedure.

  2. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package specification.

  3. Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package body.

  4. For faster removal and re-creation, do not use a drop procedure statement. Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause.


Correct Option: A

AI Explanation

To answer this question, you need to understand the process of removing procedures in a database. Let's go through each option to understand why it is correct or incorrect:

Option A) Use a drop procedure statement to drop a standalone procedure. This option is correct. When you want to remove a standalone procedure, you can use the "DROP PROCEDURE" statement to delete it from the database.

Option B) Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package specification. This option is incorrect. If a procedure is part of a package, you need to drop the entire package using the "DROP PACKAGE" statement, not just the procedure. Recompiling the package specification alone will not remove the procedure from the database.

Option C) Use a drop procedure statement to drop a procedure that is part of a package. Then recompile the package body. This option is incorrect. Similar to option B, if a procedure is part of a package, you need to drop the entire package using the "DROP PACKAGE" statement, not just the procedure. Recompiling the package body alone will not remove the procedure from the database.

Option D) For faster removal and re-creation, do not use a drop procedure statement. Instead, recompile the procedure using the alter procedure statement with the REUSE SETTINGS clause. This option is incorrect. While recompiling a procedure using the "ALTER PROCEDURE" statement with the "REUSE SETTINGS" clause can be faster, it does not remove the existing procedure from the database. It only updates the definition of the procedure without dropping and recreating it.

The correct answer is A) Use a drop procedure statement to drop a standalone procedure. This option is correct because when removing a standalone procedure, you can use the "DROP PROCEDURE" statement to delete it from the database.

You need to create a trigger on the EMP table that monitors every row that is changed and places this information into the AUDIT_TABLE. What type of trigger do you create?

  1. FOR EACH ROW trigger on the EMP table.

  2. Statement-level trigger on the EMP table.

  3. FOR EACH ROW trigger on the AUDIT_TABLE table

  4. Statement-level trigger on the AUDIT_TABLE table


Correct Option: A

Which of the following do not help you execute multiple PL/SQL programs simultaneously?

  1. Oracle Advanced Queuing

  2. DBMS_JOB

  3. DBMS_SQL

  4. Pipelined Functions


Correct Option: C

In a PL/SQL block, a variable is declared as NUMBER without an initial value. What will its value be when it is first used in the executable section of the PL/SQL block?

  1. NULL

  2. 0

  3. Results in a compilation error

  4. An exception will be raised


Correct Option: A
  1. SELECT name FROM USER_DEPENDENCIES WHERE referenced_name = 'DBMS_OUTPUT.PUT_LINE'

  2. SELECT name from ALL_SOURCE WHERE owner = USER AND name = 'DBMS_OUTPUT.PUT_LINE'

  3. SELECT DISTINCT name from ALL_SOURCE WHERE INSTR (text, 'DBMS_OUTPUT.PUT_LINE') > 0

  4. SELECT DISTINCT name from USER_SOURCE WHERE INSTR (UPPER (text), 'DBMS_OUTPUT.PUT_LINE') > 0


Correct Option: D

PL/SQL raises an exception, in which TWO of the following cases:

  1. When a SELECT statement returns no rows

  2. When a SELECT statement returns more than one row

  3. When the datatypes of SELECT clause and INTO clause do not match

  4. When INTO statement is missing in the SELECT statement


Correct Option: A,B

AI Explanation

To answer this question, we need to understand when PL/SQL raises an exception.

Option A) When a SELECT statement returns no rows - This option is correct. PL/SQL raises an exception called "NO_DATA_FOUND" when a SELECT statement returns no rows. This exception can be handled using an exception block.

Option B) When a SELECT statement returns more than one row - This option is correct. PL/SQL raises an exception called "TOO_MANY_ROWS" when a SELECT statement returns more than one row. This exception can also be handled using an exception block.

Option C) When the datatypes of SELECT clause and INTO clause do not match - This option is incorrect. PL/SQL does not raise an exception for datatype mismatch between the SELECT and INTO clauses. Instead, it will raise a compilation error during the compilation phase if the datatypes do not match.

Option D) When INTO statement is missing in the SELECT statement - This option is incorrect. PL/SQL does not raise an exception when the INTO statement is missing in a SELECT statement. Instead, it will raise a compilation error during the compilation phase.

The correct answer is A, B. These options are correct because PL/SQL raises exceptions when a SELECT statement returns no rows (NO_DATA_FOUND) or returns more than one row (TOO_MANY_ROWS).

What is the result if two NULL values are compared to each other?

  1. TRUE

  2. FALSE

  3. UNDEFINED

  4. NULL


Correct Option: D

AI Explanation

To answer this question, let's understand how NULL values are handled in comparisons.

When comparing two NULL values in most programming languages and databases, the result is usually NULL. This means that the comparison is inconclusive or unknown.

Therefore, the correct answer is:

D. NULL

This option is correct because when two NULL values are compared to each other, the result is NULL.

Functions for error trapping are contained in which section of a PL/SQL block?

  1. Header

  2. Declarative

  3. Executable

  4. Exception


Correct Option: C,D

AI Explanation

To answer this question, we need to understand the structure of a PL/SQL block.

A PL/SQL block is divided into three sections:

  1. Header: This section is optional and is used to declare the name and parameters of the PL/SQL block.

  2. Declarative: This section is also optional and is used to declare variables, cursors, types, and other program objects that are used within the block.

  3. Executable: This section contains the actual executable code of the PL/SQL block.

The correct answer is:

C. Executable: The executable section is where the main code logic of the PL/SQL block is written. It contains statements and control structures that are executed sequentially.

D. Exception: The exception section is used to handle and trap errors that may occur during the execution of the PL/SQL block. It contains error-handling code that is executed when an exception is raised.

Therefore, the functions for error trapping are contained in the executable and exception sections of a PL/SQL block.

What is the smallest possible PL/SQL block?

  1. Begin null; end;

  2. Declare x varchar2(100); begin dbms_output.put_line(x); end;

  3. Begin .. statement1; exception ... end;

  4. Begin dbms_output.put_line(sysdate); end;;


Correct Option: A

maximum number of segments for any database

  1. 8

  2. 1

  3. 31

  4. 32


Correct Option: D

maximum length for comments

  1. there is no limit

  2. 16843

  3. 255

  4. 1024


Correct Option: A
- Hide questions