Tag: databases

Questions Related to databases

Local procedure A calls remote procedure B. Procedure B was compiled at 8 A.M. Procedure A was modified and recompiled at 9 A.M. Remote procedure B was later modified and recompiled at 11 A.M. The dependency mode is set to TIMESTAMP. What happens when procedure A is invoked at 1 P.M?

technology databases

  1. There is no affect on procedure A and it runs successfully

  2. Procedure B is invalidated and recompiles when invoked.

  3. Procedure A is invalidated and recompiles for the first time it is invoked.

  4. Procedure A is invalidated and recompiles for the second time it is invoked

Show answer
Correct Option: D

AI Explanation

To answer this question, we need to understand how the TIMESTAMP dependency mode works.

When the dependency mode is set to TIMESTAMP, it means that procedures are invalidated and recompiled if the timestamp of the remote procedure they call is newer than their own timestamp.

In this scenario:

  • Procedure B was compiled at 8 A.M.
  • Procedure A was modified and recompiled at 9 A.M.
  • Procedure B was later modified and recompiled at 11 A.M.

Since Procedure A was compiled before Procedure B's second modification at 11 A.M., the timestamp of Procedure B is newer than the timestamp of Procedure A. Therefore, when Procedure A is invoked at 1 P.M., it will be invalidated and recompiled for the second time.

So, the correct answer is:

D. Procedure A is invalidated and recompiles for the second time it is invoked.

Examine this procedure: CREATE OR REPLACE PROCEDURE DELETE_PLAYER (V_ID IN NUMBER) IS BEGIN DELETE FROM PLAYER WHERE ID = V_ID; EXCEPTION WHEN STATS_EXITS_EXCEPTION THEN DBMS_OUTPUT.PUT_LINE (‘cannot delete this player, child records exist in 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. Only predefined exceptions are allowed in the EXCEPTION section.

Show answer
Correct Option: C
Explanation:

To solve this question, the user needs to have knowledge about PL/SQL syntax, specifically about creating stored procedures, and error handling using exceptions.

Option A is incorrect. The procedure does not have any syntax errors; there is no errant comma.

Option B is incorrect. The STATS_EXIST_EXCEPTION is not a number or a variable that needs to be declared. It is a user-defined exception that will be raised in case of an exception with the specified condition.

Option C is correct. The STATS_EXIST_EXCEPTION is not declared as an exception, which will cause an error during compilation of the stored procedure. The correct way of declaring a user-defined exception is as follows:

EXCEPTION
  WHEN  THEN
      -- handle the exception

Option D is incorrect. PL/SQL allows users to define their own exceptions using the EXCEPTION keyword. Predefined exceptions are not the only exceptions allowed in the EXCEPTION section.

Therefore, the correct answer is:

The Answer is: C. The STATS_EXIST_EXCEPTION has not been declared as an exception.

What was a problem with navigational data access languages?

technology databases

  1. The user had to have knowledge of the table and index structures

  2. Navigational data access was far slower than declarative access

  3. Navigational access languages required the coder to embed their queries inside a procedural language shell

  4. Navigational languages were far slower then SQL

Show answer
Correct Option: A

Which is a major problem with SQL

technology databases

  1. SQL cannot support object-orientation

  2. The same query can be written in many ways, each with vastly different execution plans

  3. SQL syntax is too difficult for non-computer professionals to use

  4. SQL creates excessive locks within the database

Show answer
Correct Option: B

AI Explanation

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

Option A) SQL cannot support object-orientation - This option is incorrect. SQL is a language specifically designed for managing and manipulating relational databases. While it may not have built-in support for object-oriented features, it can still interact with object-oriented programming languages through various mechanisms.

Option B) The same query can be written in many ways, each with vastly different execution plans - This option is correct. One of the major problems with SQL is that it allows for different ways to write the same query, and each variation can result in a different execution plan. This can lead to performance issues if the query is not optimized properly.

Option C) SQL syntax is too difficult for non-computer professionals to use - This option is incorrect. While SQL may have a learning curve, it is designed to be a declarative language that is relatively easy to understand and use. There are also graphical tools and user-friendly interfaces available that make it more accessible to non-computer professionals.

Option D) SQL creates excessive locks within the database - This option is incorrect. SQL does use locks to manage concurrent access to the database, but it does not inherently create excessive locks. The management of locks and concurrency control is the responsibility of the database management system (DBMS) implementing SQL.

Therefore, the correct answer is B) The same query can be written in many ways, each with vastly different execution plans. This option is correct because the flexibility of SQL can lead to different query variations, and if not optimized properly, it can result in vastly different execution plans with potential performance implications.

Which of the following is an important feature of relational databases and SQL
technology databases

  1. Independence of table relationships

  2. High speed of SQL

  3. Powerful GUI front-end

  4. Easy to install and use

Show answer
Correct Option: A

Which of the following is an important consideration when tuning an SQL statement
technology databases

  1. The number of CPUs on the server

  2. The degree of parallelism on the tables

  3. The use of bitmap indexes

  4. The quality of the SQL optimization

Show answer
Correct Option: D

Which of the following database design features is most important to SQL performance?
technology databases

  1. Removal of data redundancy

  2. The introduction of data redundancy

  3. The introduction of non-first normal form relations

  4. The introduction of SQL*Plus

Show answer
Correct Option: B

Which of the following is a characteristic of a sequence?

technology databases

  1. A sequence will never generate duplicate values. b.The MAXVALUE of a sequence can be equal to the MINVALUE. c.It is not possible to create a sequence that generates a constant since the INCREMENT value must be greater than zero.

  2. The MAXVALUE of a sequence can be equal to the MINVALUE

  3. It is not possible to create a sequence that generates a constant since the INCREMENT value must be greater than zero.

  4. When a sequence cycles back to either the MAXVALUE or MINVALUE, it will always be equal to the specified value of either of these two boundaries.

Show answer
Correct Option: B

Which of the following DB2 data types should NOT be used to store double-byte character data?
technology databases

  1. DBCLOB

  2. GRAPHIC

  3. VARCHAR

  4. VARGRAPHIC

Show answer
Correct Option: C

Which of the following deletion rules on CREATE TABLE will allow parent table rows to be deleted if a dependent row exists?
technology databases

  1. ON DELETE RESTRICT

  2. ON DELETE NO ACTION

  3. ON DELETE SET NO VALUE

  4. ON DELETE CASCADE

Show answer
Correct Option: D