The employee table includes these columns: e_id INT, e_name VARCHAR (45), dept_id INT salart INT You must create a stored function, getMaxSalary(), which returns the maximum salary paid for a given…

Question

Exhibits

Options

• ACREATE FUNCTION getMaxSalary(v_dept_id INT) RETURNS CHAR(50) BEGIN SELECT MAX(salary) INTO @m FROM employee WHERE dept_id = v_dept_id; RETURN @m; END
• BCREATE FUNCTION getMaxSalary(v_dept_id INT) RETURNS INT DETERMINISTIC BEGIN USE schemaname; SELECT MAX(salary) INTO msalary FROM employee WHERE dept_id = v_dept_id; RETURN msalary; END
• CCREATE FUNCTION getMaxSalary(v_dept_id INT) RETURNS INT DETERMINISTIC BEGIN DECLARE msalary INT; SELECT MAX(salary) INTO msalary FROM employee WHERE dept_id = v_dept_id; RETURN msalary; END
• DCREATE FUNCTION getMaxSalary(v_dept_id INT) RETURNS INT DECLARE msalary INT; BEGIN SELECT MAX(salary) INTO msalary FROM employee WHERE dept_id = v_dept_id; SET msalary = msalary; RETURN msalary; END

• A
  90% (61)
• B
  6% (4)
• C
  1% (1)
• D
  3% (2)

Explanation

The marked answer of A is incorrect - this appears to be an error in the answer key. C is the correct answer.

Option C is the only syntactically valid MySQL stored function: it returns INT (matching the salary column type), includes the required DETERMINISTIC characteristic, properly declares the local variable with DECLARE msalary INT inside the BEGIN...END block, uses SELECT INTO to capture the result, and returns it cleanly.

Why the distractors fail:

• A returns CHAR(50) instead of INT (wrong return type for a salary), and uses a session-scoped user variable @m instead of a properly declared local variable - both are poor practice and the return type mismatch is a logic error.
• B includes USE schemaname inside the function body, which is illegal in MySQL stored routines; it also references msalary without declaring it.
• D places DECLARE outside the BEGIN...END block, which is a syntax error in MySQL - declarations must appear at the top of the block. The SET msalary = msalary line is also a no-op.

Memory tip: Think "D-S-R inside BEGIN" - Declare, Select INTO, Return. Everything lives inside BEGIN...END, the return type must match the column type, and DECLARE always comes first inside the block.

No community discussion yet for this question.