Tag: databases

Quizzes (243) Questions (4862)

Questions Related to databases

Multiple choice technology databases

Which two describe a stored procedure? (Choose two)

  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.
Reveal answer Fill a bubble to check yourself
B,C Correct answer
Full explanation
Multiple choice technology databases

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?

  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);
Reveal answer Fill a bubble to check yourself
B Correct answer
Full explanation
Multiple choice technology databases

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?

  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.
Reveal answer Fill a bubble to check yourself
B Correct answer
Full explanation
Multiple choice technology databases

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?

  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);
Reveal answer Fill a bubble to check yourself
E Correct answer
Full explanation
Multiple choice technology databases

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.
Reveal answer Fill a bubble to check yourself
A Correct answer
Full explanation
Multiple choice technology databases

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)

  1. add_dept;

  2. add_dept( .Accounting .);

  3. add_dept(, .New York .);

  4. add_dept(p_location=> .New York .);
Reveal answer Fill a bubble to check yourself
A,B,D Correct answer
Full explanation
Multiple choice technology databases

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?

  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)
Reveal answer Fill a bubble to check yourself
A Correct answer
Full explanation