Tag: databases

Questions Related to 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)
technology databases

  1. a) add_dept;

  2. b) add_dept( .Accounting .);

  3. c) add_dept(, .New York .);

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

Show answer
Correct Option: A,B,D

When creating store procedures and functions which construct allows you to transfer values to and from the calling environment?
technology databases

  1. a) Local variables.

  2. b) Arguments.

  3. c) Boolean variables.

  4. d) Substitution variables.

Show answer
Correct Option: B
Explanation:

When creating stored procedures and functions, the option that allows you to transfer values to and from the calling environment is:

B. Arguments.

Arguments, also known as parameters, are variables that are defined within a stored procedure or function and can be used to receive values from the calling environment. These values can be passed to the procedure or function when it is executed, and the procedure or function can also return values back to the calling environment using these arguments.

Option A, local variables, are variables defined within the scope of the procedure or function and are not used for transferring values to and from the calling environment.

Option C, boolean variables, are variables that can hold a true or false value and are not specifically designed for transferring values to and from the calling environment.

Option D, substitution variables, are variables used in SQL*Plus and Oracle SQL Developer tools and are not used for passing values to and from the calling environment in stored procedures or functions.

So, the correct answer is B. Arguments.

Under which situation do you create a server side procedure?
technology databases

  1. a) When the procedure contains no SQL statements.

  2. b) When the procedure contains no PL/SQL commands.

  3. c) When the procedure needs to be used by many client applications accessing several remote databases.

  4. d) When the procedure needs to be used by many users accessing the same schema objects on a local database.

Show answer
Correct Option: D

The PROCEDURE_ADD_PRODUCT is defined within a package specifications as follows: PROCEDURE_ADD_PRODUCT (P_PRODNO NUMBER,P_PRODNAME VARCHER2); Which procedure declaration can’t be added to package specifications?
technology databases

  1. a) PROCEDURE add_product (p_order_date DATE);

  2. b) PROCEDURE add_product (p_name VARCHER2, P_ORDERED DATE);

  3. c) PROCEDURE add_product (p_prodname VARCHER2, P_PRISE NUMBER);

  4. d) PROCEDURE add_product (p_prize NUMBER, P_DESCRIPTION VARCHER2);

Show answer
Correct Option: D

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHER2(30)) IS BEGIN INSERT INTO PLAYER(ID, LAST_NAME) VALUES(V_ID, V_LAST_NAME); COMMIT; END; Why does this command fail when executed?
technology databases

  1. a) When declaring arguments length is not allowed.

  2. b) When declaring arguments each argument must have a mode specified.

  3. c) When declaring arguments each argument must have a length specified.

  4. d) When declaring a VARCHAR2 argument it must be specified.

Show answer
Correct Option: A

The ADD_PLAYER, UPD_PLAYER_STAT and UPD_PITCHER_STAT procedures are grouped together in a package. A variable must be shared among only these procedures. Where should you declare this variable?
technology databases

  1. a) In the package body.

  2. b) In the data base triggers.

  3. c) In the package specification.

  4. d) In the procedures declare section using the exact name in each.

Show answer
Correct Option: A

When using a packaged function in a query, what is true?
technology databases

  1. a) The COMMIT and ROLLBACK commands are allowed in the packaged function.

  2. b) You can not use packaged functions in a query statement.

  3. c) The packaged function cannot execute an INSERT, UPDATE, or DELETE statement against the table that is being queried.

  4. d) The packaged function can execute and INSERT, UPDATE, or DELETE statement against the table that is being queried if it is used in a subquery.

  5. e) The packaged function can execute an INSERT, UPDATEM or DELETE statement against the table that is being queried if the pragma RESTRICT REFERENCE is used.

Show answer
Correct Option: C

All users currently have the INSERT privileges on the PLAYER table. You want only your users to insert into this table using the ADD_PLAYER procedure. Which two actions must you take? (Choose two)
technology databases

  1. a) GRANT SELECT ON ADD_PLAYER TO PUBLIC;

  2. b) GRANT EXECUTE ON ADD_PLAYER TO PUBLIC;

  3. c) GRANT INSERT ON PLAYER TO PUBLIC;

  4. d) GRANT EXECUTE, INSERT ON ADD_PLAYER TO PUBLIC;

  5. e) REVOKE INSERT ON PLAYER FROM PUBLIC;

Show answer
Correct Option: B,E
  1. Examine this package body: CREATE OR REPLACE PACKAGE BODY forward_pack IS V_sum NUMBER; - 44 - PROCEDURE calc_ord(. . . ); PROCEDURE generate_summary(. . . ) IS BEGIN Calc_ord(. . . ); . . . END calc_ord; END forward_pack; / Which construct has a forward declaration?
technology databases

  1. a) V_SUM

  2. b) CALC_ORD.

  3. c) FORWARD_PACK

  4. d) GENERATE_SUMMARY.

Show answer
Correct Option: B

Examine this procedure: CREATE OR REPLACE PROCEDURE ADD_PLAYER (V_ID IN NUMBER, V_LAST_NAME VARCHER2(30)) IS BEGIN INSERT INTO PLAYER(ID, LAST_NAME) VALUES(V_ID, V_LAST_NAME); COMMIT; END; Why does this command fail when executed?
technology databases

  1. a) When declaring arguments length is not allowed.

  2. b) When declaring arguments each argument must have a mode specified.

  3. c) When declaring arguments each argument must have a length specified.

  4. d) When declaring a VARCHAR2 argument it must be specified.

Show answer
Correct Option: A