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

Script component Can have only one Input flow and zero or more Output flows.

  1. True

  2. False


Correct Option: A

Object is Valid Data type of SSIS Variable.

  1. True

  2. False


Correct Option: A

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.

  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
Explanation:

To answer this question, the user needs to know about SQL triggers and how they can be used to enforce constraints on database operations. Option B is the correct answer because it creates a trigger that runs before each update of the sal (salary) column in the emp table. The trigger checks if the new salary is less than the old salary or greater than the old salary multiplied by 1.1 (i.e., increased by more than 10%). If either of these conditions is true, the trigger raises an error to prevent the update from occurring.

Option A is incorrect because it uses a check constraint to limit the salary increase, but it does not prevent salary decreases.

Option C is almost correct, but it has a syntax error. The FOR EACH ROW clause is not needed in this case, and the semicolon at the end of the RAISE_APPLICATION_ERROR function call is missing.

Option D is also incorrect because it creates a trigger that runs after each update of the sal column, which means the constraint is not enforced until after the update has already occurred.

Therefore, the correct answer is:

The Answer is: B. CREATE OR REPLACE TRIGGER check_sal BEFORE UPDATE OF sal ON emp

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

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
Explanation:

To solve this question, the user needs to know the concept of triggers in database management systems. The user must also understand the difference between statement-level triggers and row-level triggers.

In this scenario, we need to create a trigger that monitors every row that is changed in the EMP table and inserts that information into the AUDIT_TABLE. Therefore, we need to create a row-level trigger.

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

A. FOR EACH ROW trigger on the EMP table. This option is correct. A FOR EACH ROW trigger on the EMP table will monitor every row that is changed in the EMP table and execute the trigger code for each affected row. This is the type of trigger that we need to create to accomplish the task.

B. Statement-level trigger on the EMP table. This option is incorrect. A statement-level trigger on the EMP table triggers once for each SQL statement that is executed, regardless of the number of rows that are affected. This type of trigger would not be suitable for the task at hand.

C. FOR EACH ROW trigger on the AUDIT_TABLE table. This option is incorrect. A FOR EACH ROW trigger on the AUDIT_TABLE table would monitor every row that is changed in the AUDIT_TABLE table, but this is not what we need. We need to monitor changes in the EMP table.

D. Statement-level trigger on the AUDIT_TABLE table. This option is incorrect. A statement-level trigger on the AUDIT_TABLE table would only trigger once for each SQL statement that is executed, regardless of the number of rows that are affected. This type of trigger would not be suitable for the task at hand.

The Answer is: A. FOR EACH ROW trigger on the EMP table.

  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
Explanation:

The answer to the question is A. NULL.

When a variable is declared in PL/SQL without an initial value, it is initialized to NULL. This means that the variable has no value, and any attempt to use it will result in a NULL value.

Option B is incorrect because the value of a NUMBER variable is not automatically initialized to 0. Option C is incorrect because a compilation error will not occur if a variable is declared without an initial value. Option D is incorrect because an exception will not be raised if a variable is declared without an initial value.

Here is an example of how a NULL value is handled in PL/SQL:

DECLARE
  v_number NUMBER;
BEGIN
  v_number := NULL;
  dbms_output.put_line(v_number); -- This will print 'NULL'
END;

As you can see, the value of v_number is printed as NULL when it is first used in the executable section of the PL/SQL block. This is because the variable was declared without an initial value, and therefore its value is NULL.

Which of the following queries return the names of programs (without duplication) defined in the currently-connected schema whose source contains a call to DBMS_OUTPUT.PUT_LINE (assume that this program name does not appear inside comments)?

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

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.

  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 length for comments

  1. there is no limit

  2. 16843

  3. 255

  4. 1024


Correct Option: A

maximum of triggers in sybase

  1. 3

  2. 12

  3. 6

  4. 9


Correct Option: A
- Hide questions