Tag: databases

Questions Related to databases

Private constructs in the package body are hidden and inaccessible. Is this an advantage of a package?
technology databases

  1. a)Yes

  2. b)No

  3. c)

  4. d)

Show answer
Correct Option: A

When you use the DEFINE variable command, what datatype is the variable?
technology databases

  1. VARCHAR2

  2. CHAR

  3. LONG

  4. NUMBER

Show answer
Correct Option: B

The following statement will raise an exception on which line? select dept_name, avg(all salary) ,count() “number of employees” from emp , dept where deptno = dept_no and count() > 5 group by dept_name order by 2 desc;
technology databases

  1. select dept_name, avg(all salary), count(*) “number of employees"

  2. where deptno = dept_no

  3. and count(*) > 5

  4. group by dept_name

  5. order by 2 desc;

Show answer
Correct Option: C

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

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

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

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

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

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

Show answer
Correct Option: A,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. a) EXECUTE UPD_BAT_STAT;

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

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

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

  5. e) RUN UPD_BAT_STAT;

Show answer
Correct Option: A,B
  1. 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. a) g_comm has a value of 15 at 9:06am for Smith.

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

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

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

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

Show answer
Correct Option: B

Which two describe a stored procedure? (Choose two)
technology databases

  1. a) A stored procedure is typically written in SQL.

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

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

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

  5. e) 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

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

  1. a) VARCHAR2

  2. b) BOOLEAN

  3. c) OUT

  4. d) IN

Show answer
Correct Option: C
Explanation:

To solve this question, the user needs to be familiar with the different types of arguments that can be passed between a procedure and the calling environment.

A. VARCHAR2 is a data type used to store character strings in Oracle.

B. BOOLEAN is a data type used to store logical values (TRUE or FALSE) in Oracle.

C. OUT is a parameter mode that is used to pass values from a procedure to the calling environment.

D. IN is a parameter mode that is used to pass values from the calling environment to a procedure.

Therefore, the correct answer is:

The Answer is: C

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. a) SHOW FUNCTION_ERROR

  2. b) SHOW USER_ERRORS

  3. c) SHOW ERRORS

  4. d) SHOW ALL_ERRORS

Show answer
Correct Option: C
Explanation:

To solve this question, the user needs to know how to check for the errors in an SQL function.

The correct answer is:

C. SHOW ERRORS

Explanation:

When a function is created with compilation errors, the SHOW ERRORS command can be issued to see the actual error message. This command displays the compilation errors of the last CREATE or REPLACE FUNCTION or PACKAGE statement that was executed in the current session. Using this command can help identify and fix any syntax errors or other issues with the function code.

Option A is incorrect because there is no SHOW FUNCTION_ERROR command in SQL *Plus.

Option B is incorrect because SHOW USER_ERRORS is not a valid command in SQL *Plus.

Option D is incorrect because SHOW ALL_ERRORS is not a valid command in SQL *Plus.

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. a) You need to execute the command CALCTAX(1000); .

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

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

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

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

Show answer
Correct Option: E