0

databases Online Quiz - 237

Description: databases Online Quiz - 237
Number of Questions: 20
Created by:
Tags: databases
Attempted 0/20 Correct 0 Score 0

You can declare this only by using table contraint syntax

  1. Not NULL

  2. Primary key

  3. Foreign key

  4. Composite primary keys


Correct Option: D

Which character is used to continue a statement in SQL*Plus?

  1. *

  2. /

  3. -

  4. =


Correct Option: B

Which character function can be used to return a specified portion of a character string

  1. INSTR

  2. SUBSTRING

  3. SUBSTR

  4. POS


Correct Option: C

AI Explanation

To answer this question, you need to understand the character functions used in string manipulation.

Option A) INSTR - This option is incorrect because the INSTR function is used to find the starting position of a specified substring within a string.

Option B) SUBSTRING - This option is incorrect because the SUBSTRING function is used to extract a substring from a string based on a specified starting position and length.

Option C) SUBSTR - This option is correct because the SUBSTR function is used to extract a substring from a string based on a specified starting position and, optionally, a length.

Option D) POS - This option is incorrect because there is no standard character function called POS in most programming languages or database systems.

The correct answer is C) SUBSTR. This option is correct because the SUBSTR function is specifically designed to return a specified portion of a character string.

Which Oracle access method is the fastest way for Oracle to retrieve a single row?

  1. Primary key access

  2. Access via unique index

  3. Table access by ROWID

  4. Full table scan


Correct Option: C

When a user creates an object without a TABLESPACE clause, where will Oracle store the segment?

  1. System tablespace

  2. Users tablespace

  3. Default tablespace for the user

  4. Undefined


Correct Option: C

To produce a meaningful result set without any cartesian products, what is the minimum number of conditions that should appear in the WHERE clause of a four-table join?

  1. 8

  2. 3

  3. 2

  4. 4


Correct Option: B

To use Global temporary tables in a compound statement you require which type of tablespace to be present

  1. User temporary

  2. Large

  3. System

  4. Regular


Correct Option: A

Which of the following is not true for Stored Procedures?

  1. Simplifies code reuse, code standardization and code maintenance

  2. Controlled access to other database objects

  3. Improving application performance

  4. DB2 Stored Procedures can be written only in SQL.


Correct Option: D

AI Explanation

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

Option A) Simplifies code reuse, code standardization, and code maintenance - This option is true for Stored Procedures. Stored Procedures provide a way to encapsulate a set of SQL statements into a single unit, making it easier to reuse the code, maintain a standard structure, and update the code in one place.

Option B) Controlled access to other database objects - This option is true for Stored Procedures. Stored Procedures can control access to other database objects by specifying the necessary permissions and privileges required to execute the procedure. This helps in enforcing security and preventing unauthorized access to the database.

Option C) Improving application performance - This option is true for Stored Procedures. Stored Procedures can improve application performance by reducing network traffic. Instead of sending multiple SQL statements from the application to the database, the application can call a single Stored Procedure that performs the necessary operations on the database server, reducing the overhead of multiple round trips.

Option D) DB2 Stored Procedures can be written only in SQL - This option is not true for Stored Procedures. DB2 Stored Procedures can be written in other programming languages such as COBOL, C, Java, etc., in addition to SQL. This allows developers to leverage the power of different programming languages to implement complex business logic within the Stored Procedures.

The correct answer is D. This option is not true because DB2 Stored Procedures can be written in languages other than SQL.

  1. Is used to obtain info about the previous executed sql statement

  2. Analyze the db2diag error messages

  3. Returns the user ID calling the stored proc

  4. Gives the access plan of the packages


Correct Option: A

What is the Output ?

  1. 1

  2. 0

  3. 10

  4. 11


Correct Option: A

CREATE PROCEDURE wise.Test123 (IN VAR1 INTEGER) P1: BEGIN DECLARE C1 CURSOR WITH RETURN TO CLIENT FOR SELECT DISTINCT NO FROM (VALUES (1),(2),(3),(3),(2)) AS TEST (NO); SET VAR1= VAR1 * VAR1 +VAR1; END P1@ What happens when you call wise.Test123 (10)

  1. Return Status = 0

  2. 3 rows returned by result-set

  3. 5 rows returned by result-set

  4. 110


Correct Option: A

A file rahul.db2 has following content : Drop procedure rahul ( ) @ Create procedure rahul ( ) P1: begin Declare c1 cursor with return to client for Select current date from sysibm.sysdummy1; Open c1; End P1 @ What will following command do when fired on db2 command linedb2 –td@ -f rahul.db2

  1. NO SQL

  2. CONTAINS SQL

  3. READS SQL

  4. MODIFIES SQL


Correct Option: D

Which of the following can be a user defined SQLSTATE

  1. 20000

  2. 35000

  3. 60000

  4. 71000


Correct Option: D

A Simple stored procedure is created with Dynamic Result sets 3 However only 2 cursors with return to client are defined within the source code. What will happen when you call the SP ?

  1. Will executes successfully

  2. Fails as limit is 3 but only 2 opened

  3. Executes with warning

  4. Would not allow compiling the SP at the first place


Correct Option: A
  1. CURRENT_CLIENT_ACCT

  2. CURRENT_SCHEMA

  3. CURRENT_DATETIME

  4. CURRENT_PARTITION


Correct Option: B

What will be the initial value of V_MAX in the declaration statement shown below? DECLARE v_max DECIMAL(9,2);

  1. 0.0

  2. 2

  3. 9

  4. Null


Correct Option: D

Which is not a valid data type in DB2 LUW?

  1. BIGINT

  2. DECFLOAT

  3. XML

  4. DATETIME


Correct Option: D

CREATE PROCEDURE p1() BEGIN CREATE TABLE T1 (C1 CHAR); INSERT INTO T1 VALUES ('A'); SAVEPOINT SAVEPOINT1 ON ROLLBACK RETAIN CURSORS; INSERT INTO T1 VALUES ('B'); SAVEPOINT SAVEPOINT2 ON ROLLBACK RETAIN CURSORS; INSERT INTO T1 VALUES ('C'); SAVEPOINT SAVEPOINT3 ON ROLLBACK RETAIN CURSORS; INSERT INTO T1 VALUES ('D'); ROLLBACK TO SAVEPOINT SAVEPOINT3; ROLLBACK TO SAVEPOINT SAVEPOINT1; COMMIT WORK; INSERT INTO T1 VALUES ('E'); END@ Call p1() @ How many rows will be present in Table T1

  1. 0

  2. 1 (A)

  3. 5 (A,B,C,D,E)

  4. 2 (A,E)


Correct Option: D

Which IBM tool can you use to debug stored procedure?

  1. Control centre

  2. IBM Data studio

  3. Visual Explain

  4. Command editor


Correct Option: B
- Hide questions