08 begin return vraise psalary end calcsal which

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: E OR REPLACE FUNCTION calc_sal(p_salary NUMBER) RETURN NUMBER IS v_raise NUMBER(4,2) DEFAULT 1.08; BEGIN RETURN v_raise * p_salary; END calc_sal; / Which statement accurately call the stored function CALC_SAL? (Choose two) A. UPDATE employees (calc_sal(salary)) SET salary = salary * calc_sal(salary); B. INSERT calc_sal(salary) INTO employees WHERE department_id = 60; C. DELETE FROM employees(calc_sal(salary)) WHERE calc_sal(salary) > 1000; D. SELECT salary, calc_sal(salary) FROM employees WHERE department_id = 60; E. SELECT last_name, salary, calc_sal(salary) FROM employees ORDER BY calc_sal(salary); Answer: D,E Explanation: D. This function is using the function in the column list of the select statement. This Actualtests.com - The Power of Knowing 1Z0-147 function does not perform any DML or violate any of the restriction on functions that are invoked as part of a SQL expression. E. This function is correctly using the function in the column list and the ORDER BY clause of the select statement. Functions can be invoked in the column list, WHERE, GROUP BY, HAVING and ORDER BY clauses of a SELECT statement. Restrictions on functions that are invoked as part of SQL expressions are: Functions should be stored in the database. Functions should retrun data types accepted by SQL. Functions should not include any transaction,session , or system control statments. Functions should have formal parameters of the IN mode. Functions should not modify any database tables,if called as part of a SELECT statment. Functions should not modify or read the same table,if called as part of an UPDATE or DELETE. Functions should use only positional to pass values to the formal parameters. Incorrect Answers A. Functions can only be invoked in the in the SET and WHERE clause of an UPDATE statement. B. Functions can only be invoked in the VALUES clause of an INSERT statement. C. Functions can only be invoked in the WHERE clause of a DELETE statement. QUESTION 64: This statement fails when executed: CREATE OR REPLACE TRIGGER CALC_TEAM_AVG AFTER INSER...
View Full Document

This document was uploaded on 03/08/2014.

Ask a homework question - tutors are online