Tag: databases

Questions Related to databases

OLEDB Command can be used to call Store Procedure from Database in data Flow Task.
technology databases

  1. True

  2. False

Show answer
Correct Option: A

For each loop is used to repeat a task by enumerating over a group of objects.
technology databases

  1. True

  2. False

Show answer
Correct Option: A

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

  1. True

  2. False

Show answer
Correct Option: A

Object is Valid Data type of SSIS Variable.
technology databases

  1. True

  2. False

Show answer
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?
technology databases

  1. SYS privileges

  2. Your privileges

  3. Public privileges

  4. User A’s privileges

Show answer
Correct Option: D

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?
technology databases

  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;

Show answer
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;

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

  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.

Show answer
Correct Option: B

Which statement is valid when removing procedures?
technology databases

  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.

Show answer
Correct Option: A

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?
technology databases

  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

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

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

  1. Oracle Advanced Queuing

  2. DBMS_JOB

  3. DBMS_SQL

  4. Pipelined Functions

Show answer
Correct Option: C