### databases Online Quiz - 68

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

In which sceneria would you use the ROLLUP operator for expresiion or column with in a GROUPBY clause?

1. To find the groups forming the subtotal in a row

2. To create group-wise totals for the groups specified within a GROUPBY clause

3. To create grouping for expressions or columns specified within a GROUPBY clause in one direction,from right to left for calculating the subtotals.

4. To create grouping for expressions or columns specified within a GROUPBY clause in all possible direction,which is cross-tabular report for calculating the subtotals

Correct Option: C

Which statement best describes the GROUPING function?

1. used to set the order for the groups to be used for calculating the grand totals and subtotals

2. used to form various groups to caculate totals and subtotals created using ROLLUP and CUBE operators

3. used to specify if the NULLL value in an expresion is a stored NULL value or created by ROLLUP and CUBE

4. used to specify the concatenated groups expressions to be used for calculating the grand totals and subtotals

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) used to set the order for the groups to be used for calculating the grand totals and subtotals - This option is incorrect. The GROUPING function is not used to set the order of groups. It is used to specify if a NULL value in an expression is a stored NULL value or created by the ROLLUP and CUBE operators.

Option B) used to form various groups to calculate totals and subtotals created using ROLLUP and CUBE operators - This option is incorrect. The GROUPING function is not used to form groups. It is used to determine if a NULL value in an expression is a stored NULL value or created by the ROLLUP and CUBE operators.

Option C) used to specify if the NULL value in an expression is a stored NULL value or created by ROLLUP and CUBE - This option is correct. The GROUPING function is used to determine whether a NULL value in an expression is a stored NULL value or created by the ROLLUP and CUBE operators.

Option D) used to specify the concatenated group expressions to be used for calculating the grand totals and subtotals - This option is incorrect. The GROUPING function is not used to specify concatenated group expressions. It is used to determine if a NULL value in an expression is a stored NULL value or created by the ROLLUP and CUBE operators.

The correct answer is Option C. This option is correct because the GROUPING function is used to specify if a NULL value in an expression is a stored NULL value or created by the ROLLUP and CUBE operators.

Given below is the SQL statements executed in a user session: CREATE TABLE product (pcode NUMBER(2) ,pname VARCHAR2(10)); INSERT INTO product VALUES (1,'PEN'); INSERT INTO product VALUES (2,'PENCIL'); SAVEPOINT A; UPDATE product SET pcode=10 where pcode=1; SAVEPOINT B; DELETE FROM product where pcode=2; COMMIT; DELETE FROM product where pcode=10; ROLLBACK TO SAVEPOINT a; which statement describes the consequences?

1. both the DELETE statements would be rolledback

2. No SQL statements would be rolledback

3. only the second DELETE statements would be rolledback

4. both the DELETE statements and the update statements would be rolledback

Correct Option: B

#### AI Explanation

To answer this question, let's go through each statement and understand its consequences:

1. CREATE TABLE product (pcode NUMBER(2), pname VARCHAR2(10));

• This statement creates a table named "product" with two columns: "pcode" of type NUMBER(2) and "pname" of type VARCHAR2(10).
• This statement does not have any consequences on the data in the table.
2. INSERT INTO product VALUES (1,'PEN');

• This statement inserts a row into the "product" table with values 1 and 'PEN' for the "pcode" and "pname" columns, respectively.
• This statement adds a row to the "product" table.
3. INSERT INTO product VALUES (2,'PENCIL');

• This statement inserts another row into the "product" table with values 2 and 'PENCIL' for the "pcode" and "pname" columns, respectively.
• This statement adds another row to the "product" table.
4. SAVEPOINT A;

• This statement creates a savepoint named "A" in the current transaction.
• This savepoint allows us to rollback to this point later if needed.
5. UPDATE product SET pcode=10 WHERE pcode=1;

• This statement updates the "pcode" column of the "product" table, setting the value to 10 where the current value is 1.
• This statement updates the value of one row in the "product" table.
6. SAVEPOINT B;

• This statement creates a savepoint named "B" in the current transaction.
• This savepoint allows us to rollback to this point later if needed.
7. DELETE FROM product WHERE pcode=2;

• This statement deletes the row from the "product" table where the "pcode" column has a value of 2.
• This statement removes one row from the "product" table.
8. COMMIT;

• This statement commits the current transaction, making all the changes permanent.
• Once committed, the changes cannot be rolled back.
9. DELETE FROM product WHERE pcode=10;

• This statement attempts to delete the row from the "product" table where the "pcode" column has a value of 10.
• However, since the previous update statement changed the value from 1 to 10, there is no row with a "pcode" value of 10.
• As a result, this statement does not delete any rows from the "product" table.
10. ROLLBACK TO SAVEPOINT A;

• This statement rolls back the transaction to the savepoint named "A".
• This means that all changes made after the savepoint (i.e., the update statement and the second delete statement) will be undone.

Based on the above analysis, the correct answer is: B) No SQL statements would be rolled back.

• Since the COMMIT statement was executed before the ROLLBACK TO SAVEPOINT A statement, all the changes made before the COMMIT (i.e., the create table statement, insert statements, update statement, and the first delete statement) are permanent and cannot be rolled back.
• The ROLLBACK TO SAVEPOINT A statement only rolls back the changes made after the savepoint, but since there were no changes made after the savepoint in this case, no SQL statements are rolled back.

which madatory clause has to be added to the following statements successfully create an external table called EMPDET? CREATE TABLE EMPDET( empno CHAR(2) ,ename CHAR(5) ,deptno N?UMBER(4)) ORGANIZATION EXTERNAL ( LOCATION('emp.dat'));

1. TYPE

2. REJECT LIMIT

3. ACESS PARAMETERS

4. DEFAULT DIRECTORY

Correct Option: D

which two statements are true about the GROUPING function ?

1. used to find the groups forming the subtotal in a row

2. used to identify the NULL value in the aggregate function.

3. use to form the group sets involved in generating the totals and subtotals

4. only be used with ROLLUP and CUBE operators specified in the GROUP BY clause

Correct Option: A,D

the following are the steps for a correlated subquery ,listed in a random order: 1)the where clause of the outer query is evaluted 2)The candiadate row is fetched from the table specified in the outer query 3) The procedure is repeated for the subsequent rows of the table,till all the rows are processed. 4)Rows are returned by the inner query ,after being evaluted with the value from the candiadate row in the outer query. Identify the option that contains the steps in the correct sequences in which the oracle server evalutes a correlated subquery ?

1. 4,2,1,3

2. 4,1,2,3

3. 2,4,1,3

4. 2,1,4,3

Correct Option: C

How to prevent ROW MIGRATION?

1. specify large pct free

2. specify large pct used

3. specify large initial & next sizes

4. specify small initrans

Correct Option: A

Which NLS parameter can be specified only as an environment variable?

1. NLS_Language

2. NLS_Lang

3. NLS_Sort

4. NLS_Territory

Correct Option: B

How many maximum number of multiplex control files can present in a database?

1. 4

2. 8

3. 12

4. 16

Correct Option: B

Multiple WHAT can be share an SGA?

1. SERVER PROCESSES

2. INSTANCES

3. TABLESPACES

4. DATABASES

Correct Option: B

wHICH SCRIPT CREATE THE DATA DICTIONARY TABLES?

1. catalog.sql

2. sql.bsq

3. catproc.sql

4. dictionary.sql

Correct Option: B

which can not be possible?

1. move index to a new tablespace

2. change the initial extent size of the index

3. collect statistics on the index

4. specify a new name for the index

Correct Option: D

Which file can be multiplexed?

1. parameter file

2. data files

3. redo log files

Correct Option: C

WHY DO THE FLAT FILES ARE USED AS SOURCES OR TARGETS ?

1. ITS A DUMMY RULE

3. EASY TO MAINTAIN

4. TO RESOLVE THE PERFORMANCE ISSUES

Correct Option: B,D

A UNIX VERSIONED OS SHOULD BE AVAILABLE INORDER TO INSTALL INFORMATICA

1. True

2. False

Correct Option: B

Which of the following will delete all the rows from the table T1

1. Delete * from table T1

2. Delete all from T1

3. Delete * from T1

4. Delete From T1

Correct Option: D

An Alias can be alter name forhich DB2 object

1. Sequence

2. Trigger

3. Schema

4. View

Correct Option: D

Which of the following action may cause a trigger to be fired?

1. Drop

2. Alter

3. Delete

4. Rollback

Correct Option: C

Which of the following DB2 data types does NOT have a fixed length?

1. XML

2. INT

3. CHAR

4. Double

Correct Option: A

#### AI Explanation

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

Option A) XML - This option is correct. XML data type in DB2 does not have a fixed length. It is a variable-length data type that can store XML data of varying lengths.

Option B) INT - This option is incorrect. INT data type in DB2 does have a fixed length. It represents a signed integer and has a fixed length of 4 bytes.

Option C) CHAR - This option is incorrect. CHAR data type in DB2 does have a fixed length. It is used to store fixed-length character strings and requires you to specify the length of the string.

Option D) Double - This option is incorrect. Double data type in DB2 does have a fixed length. It represents a double-precision floating-point number and has a fixed length of 8 bytes.

The correct answer is A) XML. This option is correct because the XML data type in DB2 does not have a fixed length.

To which of the following resources can a lock NOT be applied?

1. Table Space

2. Bufferpools

3. Table

4. Rows

Correct Option: B
- Hide questions