Tag: databases

Questions Related to databases

A function must return zero or more values to its calling environment, whereas a procedure returns a value to the calling environment.

technology databases

  1. True

  2. False

Show answer
Correct Option: B

Which of the following statements is false with respect to packages?

technology databases

  1. The package itself cannot be called, parameterized, or nested.

  2. The format of a package is similar to that of a subprogram.

  3. The contents can be shared by many applications once written.

  4. None of the above.

Show answer
Correct Option: D

Identifiers defined only in the package body are public constructs. These are visible outside the package body
technology databases

  1. True

  2. False

Show answer
Correct Option: B

Private constructs in the package body are hidden and inaccessible.This is an advantage of a package

technology databases

  1. True

  2. False

Show answer
Correct Option: A

This statement fails when executed: CREATE OR REPLACE TRI GGER CALC_TEAM_AVG AFTER I NSERT ON PLAYER BEGIN INSERT INTO PLAYER_BATSTAT ( PLAYER_I D, SEASON_YEAR, AT_BATS, HI TS) VALUES ( : NEW. I D, 1 997, 0, 0) ; END; To which type must you convert the trigger to correct the error?

technology databases

  1. Row

  2. Statement

  3. ORACLE FORM trigger

  4. Before

Show answer
Correct Option: A

Which two program declarations are correct for a stored program unit? (Choose two)
technology databases

  1. CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER

  2. CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER) RETURN NUMBER

  3. CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER)

  4. CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER(10,2)

  5. CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER(10, 2))

Show answer
Correct Option: A,C

What is true about stored procedures?
technology databases

  1. A stored procedure uses the DELCLARE keyword in the procedure specification to declare formal parameters.

  2. A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification.

  3. A stored procedure must have at least one executable statement in the procedure body.

  4. A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters.

Show answer
Correct Option: C

Examine this procedure: CREATE OR REPLACE PROCEDURE UPD_BAT_STAT (V_ID IN NUMBER DEFAULT 10, V_AB IN NUMBER DEFAULT 4) IS BEGIN UPDATE PLAYER_BAT_STAT SET AT_BATS = AT_BATS + V_AB WHERE PLAYER_ID = V_ID; COMMIT; END; Which two statements will successfully invoke this procedure in SQL *Plus? (Choose two)

technology databases

  1. EXECUTE UPD_BAT_STAT;

  2. EXECUTE UPD_BAT_STAT(V_AB=>10, V_ID=>31);

  3. EXECUTE UPD_BAT_STAT(31, 'FOUR', 'TWO');

  4. UPD_BAT_STAT(V_AB=>10, V_ID=>31);

  5. RUN UPD_BAT_STAT;

Show answer
Correct Option: A,B

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_name := 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; Examine this code: CREATE OR REPLACE PACKAGE comm_package IS g_comm NUMBER := 10; PROCEDURE reset_comm(p_comm IN NUMBER); END comm_package; / User Jones executes the following code at 9:01am: EXECUTE comm_package.g_comm := 15 User Smith executes the following code at 9:05am: EXECUTE comm_paclage.g_comm := 20 Which statement is true?

technology databases

  1. g_comm has a value of 15 at 9:06am for Smith.

  2. g_comm has a value of 15 at 9:06am for Jones.

  3. g_comm has a value of 20 at 9:06am for both Jones and Smith.

  4. g_comm has a value of 15 at 9:03 am for both Jones and Smith.

  5. g_comm has a value of 10 at 9:06am for both Jones and Smith.

  6. g_comm has a value of 10 at 9:03am for both Jones and Smith.

Show answer
Correct Option: B

Which three are valid ways to minimize dependency failure? (Choose three)

technology databases

  1. Querying with the SELECT * notification.

  2. Declaring variables with the %TYPE attribute.

  3. Specifying schema names when referencing objects.

  4. Declaring records by using the %ROWTYPE attribute.

  5. Specifying package.procedure notation while executing procedures.

Show answer
Correct Option: A,B,D