• databases Online Quiz - 173
Back
0

databases Online Quiz - 173

Description: databases Online Quiz - 173
Number of Questions: 20
Created by:
Tags: databases
Start the Quiz
Attempted 0/20 Correct 0 Score 0
‹
›

Implicit cursors are declared for

technology databases

  1. Some DML,PL/SQL statements

  2. All DML,PL/SQL statements

  3. Only PL/SQL statements

  4. None of the Above

Show answer
Correct Option: B

Which is the procedure used to issue User-Defined error messages from stored Sub-Programs?

technology databases

  1. Raise Application procedure

  2. Raise Application Error procedure

  3. Application Error procedure

  4. None of the Above

Show answer
Correct Option: B

How do you handle an Exception?

technology databases

  1. Trap it with a Handler

  2. Propagate it to the Calling Environment

  3. a & then b

  4. b & then a

Show answer
Correct Option: C

Examine this procedure: CREATE OR REPLACE PROCEDURE DELETE_PLAYER(V_IDIN NUMBER) IS BEGIN DELETE FROM PLAYER WHERE ID = V_ID EXCEPTION WHEN STATS_EXIST_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE(Cannotdeletethisplayer, childrecordsexistin PLAYER_BAT_STAT table); END; What prevents this procedure from being created successfully?

technology databases

  1. A comma has been left after the STATS_EXIST_EXCEPTION exception.

  2. The STATS_EXIST_EXCEPTION has not been declared as a number.

  3. The STATS_EXIST_EXCEPTION has not been declared as an exception.

  4. None

Show answer
Correct Option: C

AI Explanation

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

Option A) A comma has been left after the STATS_EXIST_EXCEPTION exception.

This option is incorrect because there is no comma after the STATS_EXIST_EXCEPTION exception in the given procedure. Therefore, this is not the reason preventing the successful creation of the procedure.

Option B) The STATS_EXIST_EXCEPTION has not been declared as a number.

This option is incorrect because the STATS_EXIST_EXCEPTION is not required to be declared as a number. It is used as an exception in the procedure.

Option C) The STATS_EXIST_EXCEPTION has not been declared as an exception.

This option is correct because the STATS_EXIST_EXCEPTION has not been declared as an exception in the given procedure. In order to use an exception, it needs to be declared beforehand using the EXCEPTION keyword followed by the exception name.

Option D) None

This option is incorrect because, as explained above, the STATS_EXIST_EXCEPTION has not been declared as an exception in the given procedure, which prevents the successful creation of the procedure.

The correct answer is C. The STATS_EXIST_EXCEPTION has not been declared as an exception. This option is correct because the exception needs to be declared before it can be used in the procedure.

Under which two circumstances do you design database triggers? (Choose two)

technology databases

  1. To duplicate the functionality of other triggers.

  2. To replicate built-in constraints in the Oracle server such as primary key and foreign key.

  3. To guarantee that when a specific operation is performed, related actions are performed.

  4. For centralized, global operations that should be fired for the triggering statement, regardless of which user or application issues the statement.

Show answer
Correct Option: C,D

Which two statements about the overloading feature of packages are true? (Choose two)

technology databases

  1. Only local or packaged sub programs can be overloaded.

  2. Overloading allows different functions with the same name that differ only in their return types.

  3. Overloading allows different subprograms with the same number, type and order of the parameter.

  4. Overloading allows different subprograms with the same name and same number or type of the parameters.

  5. Overloading allows different subprograms with the same name but different in either number or type or order of parameter.

Show answer
Correct Option: A,E

AI Explanation

To answer this question, we need to understand the concept of overloading in packages.

Option A) Only local or packaged subprograms can be overloaded. This option is true. Overloading in packages allows us to define multiple subprograms with the same name, but they must be either local or packaged subprograms.

Option B) Overloading allows different functions with the same name that differ only in their return types. This option is false. Overloading in packages does not allow different functions with the same name that differ only in their return types. Overloading is based on the parameters, not the return types.

Option C) Overloading allows different subprograms with the same number, type, and order of the parameter. This option is false. Overloading does not consider the order of the parameters. It only considers the number and types of the parameters.

Option D) Overloading allows different subprograms with the same name and the same number or type of the parameters. This option is false. Overloading allows different subprograms with the same name, but they must have different number or type of parameters.

Option E) Overloading allows different subprograms with the same name but different in either number or type or order of the parameter. This option is true. Overloading in packages allows us to define multiple subprograms with the same name, but they must have different number or type or order of parameters.

Therefore, the correct statements about the overloading feature of packages are A) Only local or packaged subprograms can be overloaded, and E) Overloading allows different subprograms with the same name but different in either number or type or order of the parameter.

What does “REPLACE” indicate in creating procedures?

technology databases

  1. An existing will be dropped and it will be replaced by the new version.

  2. An existing will be dropped and it will not be replaced by the new version.

  3. No change will occur

  4. There is no such option available in creating procedures.

Show answer
Correct Option: A

Which two statements about packages are true? (Choose two)

technology databases

  1. Packages can be nested.

  2. You can pass parameters to packages.

  3. A package is loaded into memory each time it is invoked.

  4. The contents of packages can be shared by many applications.

  5. You can achieve information hiding by making package constructs private.

Show answer
Correct Option: D,E

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

AI Explanation

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

Option A) CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER - This option is correct because it declares a stored program unit as a function named "tax_amt" that takes in a parameter "p_id" of type NUMBER and returns a value of type NUMBER.

Option B) CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER) RETURN NUMBER - This option is incorrect because it declares a stored program unit as a procedure named "tax_amt". Procedures do not have a RETURN clause to specify a return type.

Option C) CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER) - This option is correct because it declares a stored program unit as a procedure named "tax_amt" that takes in two parameters: "p_id" of type NUMBER and "p_amount" of type OUT NUMBER. The OUT parameter allows the procedure to return a value.

Option D) CREATE OR REPLACE FUNCTION tax_amt (p_id NUMBER) RETURN NUMBER(10,2) - This option is incorrect because the syntax "RETURN NUMBER(10,2)" is not valid. The return type of a function should be specified without precision or scale.

Option E) CREATE OR REPLACE PROCEDURE tax_amt (p_id NUMBER, p_amount OUT NUMBER(10, 2)) - This option is incorrect because the syntax "p_amount OUT NUMBER(10, 2)" is not valid. The OUT parameter should be specified without precision or scale.

The correct answers are A and C. These options correctly declare a stored program unit as a function and a procedure, respectively.

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

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 uses the DELCLARE keyword in the procedure specification to declare formal parameters. This option is incorrect. A stored procedure in PL/SQL uses the DECLARE keyword to declare local variables, not formal parameters.

Option B) A stored procedure is named PL/SQL block with at least one parameter declaration in the procedure specification. This option is incorrect. While a stored procedure can have parameter declarations in the procedure specification, it is not a requirement for a stored procedure to have at least one parameter.

Option C) A stored procedure must have at least one executable statement in the procedure body. This option is correct. In PL/SQL, a stored procedure must have at least one executable statement in its procedure body. This means that the procedure must contain code that performs an action or manipulates data.

Option D) A stored procedure uses the DECLARE keyword in the procedure body to declare formal parameters. This option is incorrect. As mentioned earlier, a stored procedure uses the DECLARE keyword to declare local variables, not formal parameters.

The correct answer is C. This option is correct because a stored procedure must have at least one executable statement in its procedure body.

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

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.

- Hide questions
ADVERTISEMENT

Find more quizzes from top tags

general knowledge
3615 Quizzes
119858 Questions
 technology
307 Quizzes
36705 Questions
 sports
963 Quizzes
19148 Questions
 history
1187 Quizzes
13475 Questions
 physics
598 Quizzes
13441 Questions
 biology
1160 Quizzes
12174 Questions
 science & technology
551 Quizzes
11015 Questions
 chemistry
490 Quizzes
9892 Questions
 maths
518 Quizzes
9324 Questions
 softskills
0 Quizzes
7131 Questions