Tag: databases

Questions Related to databases

Which two describe a stored procedure? (Choose two)

technology databases

  1. A stored procedure is typically written in SQL.

  2. A stored procedure is a named PL/SQL block that can accept parameters.

  3. A stored procedure is a type of PL/SQL subprogram that performs an action.

  4. A stored procedure has three parts: the specification, the body, and the exception handler part.

  5. The executable section of a stored procedure contains statements that assigns values, control execution, and return values to the calling environment.

Show answer
Correct Option: B,C

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) A stored procedure is typically written in SQL. - This option is incorrect because a stored procedure can be written in various programming languages, not just SQL. However, it is true that SQL is commonly used for writing stored procedures.

Option B) A stored procedure is a named PL/SQL block that can accept parameters. - This option is correct because a stored procedure is a named block of code that can be executed with specified input parameters. It is not limited to PL/SQL; it can be written in other programming languages as well.

Option C) A stored procedure is a type of PL/SQL subprogram that performs an action. - This option is correct because a stored procedure is a type of subprogram that performs a specific action or set of actions. It is not limited to PL/SQL and can be written in other programming languages.

Option D) A stored procedure has three parts: the specification, the body, and the exception handler part. - This option is incorrect because the three-part structure described here is specific to PL/SQL procedures, not stored procedures in general. Stored procedures can have different structures depending on the programming language.

Option E) The executable section of a stored procedure contains statements that assign values, control execution, and return values to the calling environment. - This option is incorrect because it describes the functionality of the executable section of a stored procedure, but it does not fully describe a stored procedure as a whole.

The correct answer is B) A stored procedure is a named PL/SQL block that can accept parameters. and C) A stored procedure is a type of PL/SQL subprogram that performs an action. These options accurately describe the characteristics of a stored procedure.

A CALL statement inside the trigger body enables you to call ______.

technology databases

  1. A package.

  2. A stored function.

  3. A stored procedure.

  4. Another database trigger.

Show answer
Correct Option: C

AI Explanation

To answer this question, let's go through each option to understand why it is correct or incorrect:

Option A) A package - This option is incorrect because a CALL statement inside a trigger body is not used to call a package. Packages are typically used to group related functions, procedures, and variables together.

Option B) A stored function - This option is incorrect because a CALL statement inside a trigger body is not used to call a stored function. Stored functions are typically used to perform a specific task and return a value.

Option C) A stored procedure - This option is correct. A CALL statement inside a trigger body enables you to call a stored procedure. Stored procedures are blocks of code that can be executed with specific input parameters and can perform various tasks in a database.

Option D) Another database trigger - This option is incorrect because a CALL statement inside a trigger body is not used to call another database trigger. Triggers are automatically executed in response to specific events and are not typically called directly by other triggers.

The correct answer is C) A stored procedure. This option is correct because a CALL statement inside a trigger body can be used to call a stored procedure and execute the code within it.

Which type of argument passes a value from a procedure to the calling environment?

technology databases

  1. VARCHAR2

  2. BOOLEAN

  3. OUT

  4. IN

Show answer
Correct Option: C

AI Explanation

To answer this question, we need to understand the different types of arguments in programming.

Option A) VARCHAR2 - VARCHAR2 is a data type used to store character strings of variable length. However, it is not directly related to passing values from a procedure to the calling environment.

Option B) BOOLEAN - BOOLEAN is a data type that represents logical values (true or false). Similar to VARCHAR2, it is not specifically used for passing values from a procedure to the calling environment.

Option C) OUT - This option is the correct answer. The OUT parameter is used to pass values from a procedure to the calling environment. It allows the procedure to modify the value of the parameter and return it to the caller.

Option D) IN - The IN parameter is used to pass values from the calling environment to a procedure. It allows the procedure to use the value but does not modify it or return it to the caller.

The correct answer is C) OUT because it is the type of argument that passes a value from a procedure to the calling environment.

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHAR2) IS BEGIN INSERT INTO PLAYER (ID,LAST_NAME) VALUES (V_ID, V_LAST_NAME); COMMIT; END; This procedure must invoke the APD_BAT_STAT procedure and pass a parameter. Which statement, when added to the above procedure will successfully invoke the UPD_BAT_STAT procedure?
technology databases

  1. EXECUTE UPD_BAT_STAT(V_ID);

  2. UPD_BAT_STAT(V_ID);

  3. RUN UPD_BAT_STAT(V_ID);

  4. START UPD_BAT_STAT(V_ID);

Show answer
Correct Option: B

When creating a function in SQL *Plus, you receive this message: .Warning: Function created with compilation errors.. Which command can you issue to see the actual error message?
technology databases

  1. SHOW FUNCTION_ERROR

  2. SHOW USER_ERRORS

  3. SHOW ERRORS

  4. SHOW ALL_ERRORS

Show answer
Correct Option: C

Examine this code: CREATE OR REPLACE FUNCTION gen_email_name (p_first_name VARCHAR2, p_last_name VARCHAR2, p_id NUMBER) RETURN VARCHAR2 is v_email_name VARCHAR2(19); BEGIN v_email_home := SUBSTR(p_first_name, 1, 1) || SUBSTR(p_last_name, 1, 7) || [email protected] .; UPDATE employees SET email = v_email_name WHERE employee_id = p_id; RETURN v_email_name; END; You run this SELECT statement: SELECT first_name, last_name gen_email_name(first_name, last_name, 108) EMAIL FROM employees; What occurs?

technology databases

  1. Employee 108 has his email name updated based on the return result of the function.

  2. The statement fails because functions called from SQL expressions cannot perform DML.

  3. The statement fails because the functions does not contain code to end the transaction.

  4. The SQL statement executes successfully, because UPDATE and DELETE statements are ignoring in stored functions called from SQL expressions.

  5. The SQL statement executes successfully and control is passed to the calling environment.

Show answer
Correct Option: B

Given a function CALCTAX : CREATE OR REPLACE FUNCTION calc tax (sal NUMBER) RETURN NUMBER IS BEGIN RETURN (sal * 0.05); END; If you want to run the above function from the SQL *Plus prompt, which statement is true?
technology databases

  1. You need to execute the command CALCTAX(1000); .

  2. You need to execute the command EXECUTE FUNCTION calc tax; .

  3. You need to create a SQL *Plus environment variable X and issue the command :X := CALCTAX(1000); .

  4. You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX;

  5. You need to create a SQL *Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);

Show answer
Correct Option: E
Explanation:

To run the given function from the SQL*Plus prompt, the user needs to know the correct syntax for invoking a function in Oracle SQL. The function CALCTAX takes in a single parameter, sal, and returns the result of sal * 0.05.

Option A is incorrect because it is missing the keyword SELECT to return the result of the function.

Option B is incorrect because the keyword FUNCTION is not used in the EXECUTE statement, and the argument sal is not provided.

Option C is incorrect because SQL*Plus environment variables are not required to execute a function, and it is missing the keyword SELECT to return the result of the function.

Option D is incorrect because it is missing the argument sal.

Option E is the correct answer because it first declares an SQL*Plus environment variable X, executes the function CALCTAX with the argument 1000, and assigns the result to the variable X. The keyword SELECT is not required because the result is assigned to a variable.

Therefore, the answer is: E. You need to create a SQL*Plus environment variable X and issue the command EXECUTE :X := CALCTAX(1000);

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

Examine this code: CREATE OR REPLACE PRODECURE add_dept (p_dept_name VARCHAR2 DEFAULT 'placeholder', p_location VARCHAR2 DEFAULT 'Boston') IS BEGIN INSERT INTO departments VALUES (dept_id_seq.NEXTVAL, p_dept_name, p_location); END add_dept; / Which three are valid calls to the add_dep procedure ? (Choose three)
technology databases

  1. add_dept;

  2. add_dept( .Accounting .);

  3. add_dept(, .New York .);

  4. add_dept(p_location=> .New York .);

Show answer
Correct Option: A,B,D

Examine this function: CREATE OR REPLACE FUNCTION CALC_PLAYER_AVG (V_ID in PLAYER_BAT_STAT.PLAYER_ID%TYPE) RETURN NUMBER IS V_AVG NUMBER; BEGIN SELECT HITS / AT_BATS INTO V_AVG FROM PLAYER_BAT_STAT WHERE PLAYER_ID = V_ID; RETURN (V_AVG); END; Which statement will successfully invoke this function in SQL *Plus?

technology databases

  1. SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT;

  2. EXECUTE CALC_PLAYER_AVG (31);

  3. CALC_PLAYER (.RUTH.);

  4. CALC_PLAYER_AVG(31);

  5. START CALC_PLAYER_AVG(31)

Show answer
Correct Option: A

AI Explanation

To determine which statement will successfully invoke the given function in SQL *Plus, let's go through each option:

Option A) SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT; This option is correct. It successfully invokes the function CALC_PLAYER_AVG by passing the PLAYER_ID column from the PLAYER_BAT_STAT table as the argument. The function will calculate the average for each player.

Option B) EXECUTE CALC_PLAYER_AVG (31); This option is incorrect. The EXECUTE statement is used to execute a stored procedure or anonymous block, not a function. In this case, you should use the SELECT statement to invoke the function.

Option C) CALC_PLAYER (.RUTH.); This option is incorrect. The function name is CALC_PLAYER_AVG, not CALC_PLAYER. Additionally, the argument should be provided as a value, not a string literal. The correct format is CALC_PLAYER_AVG(31).

Option D) CALC_PLAYER_AVG(31); This option is incorrect. Although the function name and argument are correct, you should use the SELECT statement to invoke the function.

Option E) START CALC_PLAYER_AVG(31) This option is incorrect. The START command is used to execute a script file in SQL *Plus, not to invoke a function.

The correct answer is Option A) SELECT CALC_PLAYER_AVG(PLAYER_ID) FROM PLAYER_BAT_STAT. This option successfully invokes the CALC_PLAYER_AVG function by passing the PLAYER_ID column from the PLAYER_BAT_STAT table as the argument.