Question 1

You can declare this only by using table contraint syntax

technology databases

Not NULL
Primary key
Foreign key
Composite primary keys

Answer

Correct Option: D

Question 2

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

technology databases

*
/
-
=

Answer

Correct Option: B

Question 3

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

technology databases

INSTR
SUBSTRING
SUBSTR
POS

Answer

Correct Option: C

Question 4

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

technology databases

Primary key access
Access via unique index
Table access by ROWID
Full table scan

Answer

Correct Option: C

Question 5

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

technology databases

System tablespace
Users tablespace
Default tablespace for the user
Undefined

Answer

Correct Option: C

Question 6

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?

technology databases

8
3
2
4

Answer

Correct Option: B

Question 7

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

technology databases

User temporary
Large
System
Regular

Answer

Correct Option: A

Question 8

Which of the following is not true for Stored Procedures?

technology databases

Simplifies code reuse, code standardization and code maintenance
Controlled access to other database objects
Improving application performance
DB2 Stored Procedures can be written only in SQL.

AI Explanation

AI 1

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.

Answer

Correct Option: D

Question 9

GET DIAGNOSTICS

technology databases

Is used to obtain info about the previous executed sql statement
Analyze the db2diag error messages
Returns the user ID calling the stored proc
Gives the access plan of the packages

Answer

Correct Option: A

Question 10

What is the Output ?

technology databases

1
0
10
11

Answer

Correct Option: A

Question 11

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)

technology databases

Return Status = 0
3 rows returned by result-set
5 rows returned by result-set
110

Answer

Correct Option: A

Question 12

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

technology databases

NO SQL
CONTAINS SQL
READS SQL
MODIFIES SQL

Answer

Correct Option: D

Question 13

Which of the following can be a user defined SQLSTATE

technology databases

20000
35000
60000
71000

Answer

Correct Option: D

Question 14

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 ?

technology databases

Will executes successfully
Fails as limit is 3 but only 2 opened
Executes with warning
Would not allow compiling the SP at the first place

Answer

Correct Option: A

Question 15

What one is valid special register?

technology databases

CURRENT_CLIENT_ACCT
CURRENT_SCHEMA
CURRENT_DATETIME
CURRENT_PARTITION

Answer

Correct Option: B

Question 16

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

technology databases

0.0
2
9
Null

Answer

Correct Option: D

Question 17

Which is not a valid data type in DB2 LUW?

technology databases

BIGINT
DECFLOAT
XML
DATETIME

Answer

Correct Option: D

Question 18

CREATE PROCEDURE testproc( IN i1 INT, INOUT i3 INT) SPECIFIC testproc BEGIN SET i3 = i1; END @ CREATE PROCEDURE testproc( IN i1 INT, INOUT i2 INT, INOUT i3 INT) SPECIFIC testp BEGIN SET i3 = i1 * i2; END @ Given that the statements in the exhibits have executed successfully, which solution contains the complete set of commands that could be used to drop both procedures in the order presented?

technology databases

DROP PROCEDURE testp; DROP PROCEDURE testp;
DROP PROCEDURE testp; DROP PROCEDURE testproc;
DROP SPECIFIC PROCEDURE testproc; DROP PROCEDURE testproc;
DROP PROCEDURE testproc(INT); DROP PROCEDURE testproc(INT);

Answer

Correct Option: C

Question 19

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

technology databases

0
1 (A)
5 (A,B,C,D,E)
2 (A,E)

Answer

Correct Option: D

Question 20

Which IBM tool can you use to debug stored procedure?

technology databases

Control centre
IBM Data studio
Visual Explain
Command editor

Answer

Correct Option: B

Description: databases Online Quiz - 237
Number of Questions: 20
Created by: Aliensbrain Bot
Tags: databases

databases Online Quiz - 237

AI Explanation

Find more quizzes from top tags