### databases Online Quiz - 190

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

Which SELECT statement will the result 'ello world' from the string 'Hello World'?

1. SELECT SUBSTR ('Hello',) FROM dual;

2. SELECT INITCAP (TRIM ('Hello World',1,1) FROM dual;

3. SELECT LOWER (SUBSTR ('Hello World',1,1) FROM dual

4. SELECT LOWER (SUBSTR ('Hello World',2,1) FROM dual;

5. SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual;

Correct Option: E

#### AI Explanation

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

Option A) SELECT SUBSTR ('Hello',) FROM dual; This option is incorrect because the SUBSTR function requires three arguments: the string, the starting position, and the length. In this case, the starting position and length are not provided.

Option B) SELECT INITCAP (TRIM ('Hello World',1,1) FROM dual; This option is incorrect because the TRIM function is used to remove specified characters from the beginning and/or end of a string. In this case, the starting position and length are not provided correctly.

Option C) SELECT LOWER (SUBSTR ('Hello World',1,1) FROM dual This option is incorrect because the SUBSTR function is used to extract a substring from a string. In this case, it is extracting the first character 'H' and converting it to lowercase using the LOWER function. It does not provide the desired result 'ello world'.

Option D) SELECT LOWER (SUBSTR ('Hello World',2,1) FROM dual; This option is incorrect because the SUBSTR function is used to extract a substring from a string. In this case, it is extracting the second character 'e' and converting it to lowercase using the LOWER function. It does not provide the desired result 'ello world'.

Option E) SELECT LOWER (TRIM ('H' FROM 'Hello World')) FROM dual; This option is correct because it uses the TRIM function to remove the letter 'H' from the string 'Hello World'. It then converts the remaining string 'ello world' to lowercase using the LOWER function. This provides the desired result 'ello world'.

The correct answer is Option E.

SELECT* FROM order; You use this statement to retrieve data from a data table for __________.

1. Updating

2. Viewing

3. Deleting

4. Inserting

5. Truncating

Correct Option: B,D

Which four statements correctly describe functions that are available in SQL? (Choose four)

1. INSTR returns the numeric position of a named character.

2. NVL2 returns the first non-null expression in the expression list.

3. DECODE translates an expression after comparing it to each search value

4. TRIM trims the heading of trailing characters (or both) from a character string

5. NVL compares two expressions and returns null if they are equal, or the first expression of they are not equal

6. NULLIF compares twp expressions and returns null if they are equal, or the first expression if they are not equal

Correct Option: A,C,D,F

#### AI Explanation

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

Option A) INSTR returns the numeric position of a named character - This option is correct because the INSTR function in SQL returns the position of a specified character or substring within a string.

Option B) NVL2 returns the first non-null expression in the expression list - This option is incorrect because the NVL2 function in SQL returns the second expression if the first expression is not null, otherwise it returns the third expression.

Option C) DECODE translates an expression after comparing it to each search value - This option is correct because the DECODE function in SQL compares an expression to a list of search values and returns a result based on the matching value.

Option D) TRIM trims the heading or trailing characters (or both) from a character string - This option is correct because the TRIM function in SQL removes leading and/or trailing characters (or both) from a character string.

Option E) NVL compares two expressions and returns null if they are equal, or the first expression if they are not equal - This option is incorrect because the NVL function in SQL compares an expression to null and returns a specified value if the expression is null, otherwise it returns the expression itself.

Option F) NULLIF compares two expressions and returns null if they are equal, or the first expression if they are not equal - This option is correct because the NULLIF function in SQL compares two expressions and returns null if they are equal, otherwise it returns the first expression.

The correct answers are A, C, D, and F.

Which two tasks can your perform by using the TO_CHAR function? (Choose two)

1. Convert 10 to 'TEN'

2. Convert '10' to 10

3. Convert '10' to '10'

4. Convert 'TEN' to 10

5. Convert a date to a character expression

6. Convert a character expression to a date

Correct Option: C,E

#### AI Explanation

To answer this question, let's go through each option to understand which tasks can be performed using the TO_CHAR function:

Option A) Convert 10 to 'TEN' - The TO_CHAR function is used to convert a value to a character expression. It is not used to convert a numeric value to a string representation, so this option is incorrect.

Option B) Convert '10' to 10 - The TO_CHAR function is not used to convert a string representation of a number to a numeric value. This option is incorrect.

Option C) Convert '10' to '10' - The TO_CHAR function is used to convert a value to a character expression. In this case, it can be used to convert the string '10' to the string '10'. This option is correct.

Option D) Convert 'TEN' to 10 - The TO_CHAR function is not used to convert a string representation of a number to a numeric value. This option is incorrect.

Option E) Convert a date to a character expression - The TO_CHAR function can be used to convert a date to a character expression. This option is correct.

Option F) Convert a character expression to a date - The TO_CHAR function is used to convert a value to a character expression, not to convert a character expression to a date. This option is incorrect.

The correct answers are: C) Convert '10' to '10', and E) Convert a date to a character expression.

Which two statements are true regarding the ORDER BY clause? (Choose two)

1. The sort is in ascending by order by default

2. The sort is in descending order by default

3. The ORDER BY clause must precede the WHERE clause

4. The ORDER BY clause is executed on the client side

5. The ORDER BY clause comes last in the SELECT statement

6. The ORDER BY clause is executed first in the query execution

Correct Option: A,E

#### AI Explanation

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

Option A) The sort is in ascending order by default - This option is correct. If the ORDER BY clause does not specify ASC or DESC, the sort order is ascending by default.

Option B) The sort is in descending order by default - This option is incorrect. As mentioned above, the sort order is ascending by default.

Option C) The ORDER BY clause must precede the WHERE clause - This option is incorrect. The WHERE clause is used to filter rows based on a condition, and it comes before the ORDER BY clause in the SELECT statement.

Option D) The ORDER BY clause is executed on the client side - This option is incorrect. The ORDER BY clause is executed on the database server side, not on the client side.

Option E) The ORDER BY clause comes last in the SELECT statement - This option is correct. The ORDER BY clause is typically the last clause in a SELECT statement, used to sort the result set based on one or more columns.

Option F) The ORDER BY clause is executed first in the query execution - This option is incorrect. The ORDER BY clause is executed after the WHERE clause and other clauses (such as GROUP BY and HAVING) that may be present in the query.

The correct answers are A and E. The sort is in ascending order by default, and the ORDER BY clause comes last in the SELECT statement.

The UPDATE SQL clause can…

1. update more than one row at a time.

2. delete more than one row at a time.

3. update only one row at a time.

4. delete only one row at a time.

Correct Option: A

Which are DML statements? (Choose all that apply)

1. COMMIT

2. MERGE

3. UPDATE

4. DELETE

5. CREATE

6. DROP

Correct Option: B,C,D

#### AI Explanation

To answer this question, we need to understand what DML (Data Manipulation Language) statements are. DML statements are used to modify and manipulate data within a database.

Let's go through each option to determine if it is a DML statement:

Option A) COMMIT - This statement is not a DML statement. COMMIT is used to permanently save any changes made within a transaction.

Option B) MERGE - This statement is a DML statement. MERGE is used to combine data from two tables based on a specified condition.

Option C) UPDATE - This statement is a DML statement. UPDATE is used to modify existing data in a table.

Option D) DELETE - This statement is a DML statement. DELETE is used to remove data from a table.

Option E) CREATE - This statement is not a DML statement. CREATE is used to create new database objects, such as tables, indexes, or views.

Option F) DROP - This statement is not a DML statement. DROP is used to remove existing database objects.

Therefore, the correct answers are options B, C, and D.

Which of the following SQL clauses specifies a search condition?

1. FROM

2. SEARCH

3. WHERE

4. while

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) FROM - This option is incorrect because the FROM clause is used to specify the table or tables from which data will be retrieved.

Option B) SEARCH - This option is incorrect because there is no such SQL clause called SEARCH.

Option C) WHERE - This option is correct because the WHERE clause is used to specify a search condition to filter the rows returned by a SELECT statement.

Option D) WHILE - This option is incorrect because the WHILE clause is not used for specifying a search condition in SQL. It is typically used in programming languages like SQL Server's T-SQL to implement loops.

The correct answer is C) WHERE. This option is correct because the WHERE clause is used to specify a search condition in SQL.

Which of the following is the most serious type of redo log file failure?

1. The loss of an entire redo log file group, but no loss in any other group

2. The loss of one member of each redo log file group

3. The failure of the ARC0 background process

4. The failure of the LGWR background process

Correct Option: A

When the database is in ARCHIVELOG mode, database recovery is possible up to which event or time?

1. The last redo log file switch

2. The last checkpoint position

3. The last commit

4. The last incremental backup using RMAN

Correct Option: C

Which of the following commands places the database in ARCHIVELOG mode?

1. ALTER SYSTEM ARCHIVELOG;

2. ALTER DATABASE ARCHIVELOG;

3. ALTER SYSTEM SET ARCHIVELOG=TRUE;

4. ALTER DATABASE ENABLE ARCHIVELOG MODE;

5. ALTER DATABASE ARCHIVELOG MODE;

Correct Option: B

To place the database into ARCHIVELOG mode, in which state must you start the database?

1. MOUNT

2. NOMOUNT

3. OPEN

4. SHUTDOWN

5. Any of the above

Correct Option: A

What is the biggest advantage of having the control files on different disks?

1. Database performance

2. Guards against failure

3. Faster archiving

4. Writes are concurrent, so having control files on different disks speeds up control file writes

Correct Option: B
Explanation:

To answer this question, the user needs to have basic knowledge of database architecture and file systems.

The biggest advantage of having control files on different disks is to guard against failure. Option B is the correct answer.

Explanation for each option:

A. Database performance: Although disk performance can impact database performance, having control files on different disks does not necessarily improve database performance.

B. Guards against failure: When control files are located on separate disks, it provides redundancy and a safeguard against disk failure. If one disk fails, the other disk can still provide the necessary control files for the database.

C. Faster archiving: Control files are not directly related to archiving. Having control files on different disks does not necessarily speed up archiving.

D. Writes are concurrent, so having control files on different disks speeds up control file writes: While it is true that having control files on different disks can speed up control file writes, this is not the biggest advantage of having control files on different disks. The biggest advantage is the redundancy and safeguard against disk failure.

The Answer is: B. Guards against failure.

Which statement adds a member /logs/redo22.log to redo log file group 2?

1. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’ TO GROUP 2;

2. ALTER DATABASE ADD LOGFILE MEMBER ‘/logs/redo22.log’ TO GROUP 2;

3. ALTER DATABASE ADD MEMBER ‘/logs/redo22.log’ TO GROUP 2;

4. ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’;

Correct Option: B

#### AI Explanation

To answer this question, you need to understand how to add a member to a redo log file group in a database.

Let's go through each option to understand why it is correct or incorrect:

Option A) ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’ TO GROUP 2; This option is incorrect because it uses the keyword "LOGFILE" instead of "LOGFILE MEMBER". The correct syntax to add a member to a redo log file group is "LOGFILE MEMBER".

Option B) ALTER DATABASE ADD LOGFILE MEMBER ‘/logs/redo22.log’ TO GROUP 2; This option is correct because it uses the correct syntax to add a member to a redo log file group. The keyword "LOGFILE MEMBER" is used to specify the path of the member to be added.

Option C) ALTER DATABASE ADD MEMBER ‘/logs/redo22.log’ TO GROUP 2; This option is incorrect because it uses the keyword "ADD MEMBER" instead of "ADD LOGFILE MEMBER". The correct syntax to add a member to a redo log file group is "ADD LOGFILE MEMBER".

Option D) ALTER DATABASE ADD LOGFILE ‘/logs/redo22.log’; This option is incorrect because it does not specify the group to which the redo log file should be added. The correct syntax to add a member to a redo log file group requires specifying the group number.

The correct answer is Option B) ALTER DATABASE ADD LOGFILE MEMBER ‘/logs/redo22.log’ TO GROUP 2. This option is correct because it uses the correct syntax to add a member to the redo log file group 2.

Which initialization parameter contains the value used as the default for archived log file destination 10?

1. LOG_ARCHIVE_DEST

2. STANDBY_ARCHIVE_DEST

3. LOG_ARCHIVE_DUPLEX_DEST

4. DB_RECOVERY_FILE_DEST

5. USE_DB_RECOVERY_FILE_DEST

Correct Option: D

Which file records all changes made to the database and is used only when recovering an instance?

1. Archive log file

2. Redo log file

3. Control file

Correct Option: B

Which data dictionary view shows that the database is in ARCHIVELOG mode?

1. V\$INSTANCE

2. V\$LOG

3. V\$DATABASE

Correct Option: C

Following is not a part of SQL.

1. Commit

2. Cursors

3. savepoint

4. truncate

Correct Option: B

Which of the following will automatically order the result.

1. Union All

2. Distinct

3. Unique

4. Intersect

Correct Option: D

Which one will get higher priority

1. Union

2. Intersect

3. Minus

4. None of above

Correct Option: D
- Hide questions