0

databases Online Quiz - 119

Description: databases Online Quiz - 119
Number of Questions: 20
Created by:
Tags: databases
Attempted 0/20 Correct 0 Score 0
  1. It consists of Redo Log files that have archived before being reused.

  2. It consists of Redo Log files that have archived after being reused.

  3. It consists of Redo Log files that is archiving when currently being reused.

  4. It consists of Redo Log files that have archived not being reused.


Correct Option: A

Examine the syntax for creating a DEPARTMENT table: CREATE TABLE ABCD( A NUMBER(4), B VARCNAR2(30), C NUMBER(6), D NUMBER(4)) STORAGE(INITIAL 200K NEXT 200K PCTINCREASE 50 MINEXTENTS 1 MAXEXTENTS 5) TABLESPACE userdata; What is the size defined for the fifth extent?

  1. 500K

  2. 300K

  3. 675K

  4. 275K


Correct Option: C
  1. database

  2. redo logfile & datafile

  3. archivelog file location

  4. All of the above


Correct Option: D

What is a Parallel Server

  1. Multiple instances accessing the same database (Only In Multi-CPU environments)

  2. Multiple instances accessing the same database (Only In single-CPU environments)

  3. Multiple instances accessing the different database (Only In single-CPU environments)

  4. Single instance accessing the multiple database (Only In Multi-CPU environments)


Correct Option: A

To re-create a database, all existing database files should be reused. The following SQL statement is issued: CREATE DATABASE ABC DATAFILE '/u01/oradata/abc/system0l.dbf' SIZE 100M REUSE LOGFILE GROUP 1 ('/u01/oradata/abc/logla.rdo', '/u02/oradata/abc/loglb.rdo') SIZE 50K REUSE, GROUP 2 ('/u01/oradata/abc/log2a.rdo', '/u02/oradata/abc/log2b.rdo') SIZE 50K REUSE MAXLOGFILES 5 MAXLOGHISTORY 100 MAXDATAFILES 10; Why does the CREATE DATABASE statement fail?

  1. The MAXLOGFILES are set too low

  2. The CONTROLFILE REUSE clause is omitted

  3. The online redo log files cannot be reused

  4. The data file belonging to the SYSTEM tablespace cannot be reused


Correct Option: B

Which of the following tasks can be performed by using the TO_CHAR function?

  1. Convert '10'to 10

  2. Convert 'TEN' to 10

  3. Convert 10 to '10'

  4. Convert a character expression to a date


Correct Option: C

Evaluate the following SQL statement: SELECT ROUND (TRUNC (MOD (1600, 10),-1), 2) FROM dual; What will be displayed?

  1. 100

  2. 0

  3. 1

  4. 10


Correct Option: B
Explanation:

To understand the given SQL statement, the user must know the following functions:

  1. MOD - returns the remainder of a division operation
  2. TRUNC - truncates a number to a specified number of decimal places
  3. ROUND - rounds a number to a specified number of decimal places

Now let's break down the SQL statement:

  • The MOD function calculates the remainder of 1600 divided by 10, which is 0.
  • The TRUNC function truncates 0 to -1 decimal places, which is still 0.
  • Finally, the ROUND function rounds 0 to 2 decimal places, which is still 0.

Therefore, the answer is option B: 0.

What is the effect of setting the value "ALL_ROWS" for OPTIMIZER_GOAL parameter of the ALTER SESSION command ?

  1. This value causes the optimizer to the rule-based approach for all SQL statements in the session regardless of the presence of statistics and to optimize with a goal of best throughput

  2. This value causes the optimizer to the rule-based approach for all SQL statements in the session in regard of the presence of statistics and to optimize with a goal of best throughput

  3. This value causes the optimizer to the cost-based approach for all SQL statements in the session regardless of the presence of statistics and to optimize with a goal of best throughput

  4. This value causes the optimizer to the cost-based approach for all SQL statements in the session regard of the presence of statistics and to optimize with a goal of best throughput


Correct Option: C

What is the correct use of the Trunc command on a date?

  1. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') "Date " FROM DUAL;

  2. SELECT TRUNC(TO_DATE('12-Feb-99','DD-MON-YY'), 'YEAR') FROM DUAL;

  3. TRUNCATE=To_Date('09-Jan-02,DD-MON-YY,'YEAR',"Date" from Dual;

  4. Date =TRUNCATE(To_DATE('09-Jan-02','DD-MON-YY'),'YEAR'),'YEAR)"DATE: from DUAL;


Correct Option: A

How do I eliminate duplicate rows in an Oracle database?

  1. DELETE FROM table_name WHERE ROWID IN (SELECT MAX (ROWID) FROM table_name GROUP BY duplicate_values_field_name);

  2. DELETE FROM table_name WHERE ROWID NOT IN (SELECT MAX (ROWID) FROM table_name GROUP BY duplicate_values_field_name);

  3. DELETE FROM table_name WHERE ROWID IN (SELECT MIN (ROWID) FROM table_name GROUP BY duplicate_values_field_name);

  4. DELETE FROM table_name WHERE ROWID IN (SELECT AVG (ROWID) FROM table_name GROUP BY duplicate_values_field_name);


Correct Option: B

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

Which command will delete all data from a table and will not write to the rollback segment?

  1. DROP

  2. DELETE

  3. CASCADE

  4. TRUNCATE


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) DROP - This option is incorrect because the DROP command is used to delete an entire table, including its structure, from the database. It does not delete data from a table without writing to the rollback segment.

Option B) DELETE - This option is incorrect because the DELETE command is used to delete specific rows from a table based on a condition. It does not delete all data from a table without writing to the rollback segment.

Option C) CASCADE - This option is incorrect because the CASCADE keyword is used in conjunction with the DROP command to delete dependent objects along with the table. It does not delete all data from a table without writing to the rollback segment.

Option D) TRUNCATE - This option is correct because the TRUNCATE command is used to delete all data from a table without writing to the rollback segment. It is a faster operation than DELETE as it does not generate undo logs for each deleted row.

The correct answer is D) TRUNCATE. This option is correct because it deletes all data from a table without writing to the rollback segment.

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

Which of the following SQL functions can operate on any datatype?

  1. TO_CHAR

  2. LOWER

  3. LPAD

  4. MAX

  5. CEIL


Correct Option: D
Explanation:

The correct answer is D. MAX.

The MAX function in SQL can operate on any datatype. It is used to retrieve the maximum value from a column or a set of values. Regardless of the datatype of the column or the values, the MAX function will return the maximum value.

The other functions mentioned:

A. LOWER: It is used to convert a string to lowercase. It can only operate on string or text datatypes.

B. TO_CHAR: It is used to convert a value to a character string. It is typically used to convert a date or a number to a specific string format. It can operate on different datatypes but is primarily used for date and number conversions.

C. LPAD: It is used to pad a string with specified characters on the left side. It can only operate on string or text datatypes.

E. CEIL: It is used to round up a number to the nearest integer. It can only operate on numeric datatypes.

Therefore, out of all the options, only the MAX function can operate on any datatype.

  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


Correct Option: D

What best describes the relationship between indexes and SQL performance?

  1. Indexes are only used in special cases

  2. Indexes are used to make table storage more efficient

  3. Indexes rarely make a difference in SQL performance

  4. Indexes exist solely to improve query speed


Correct Option: D
Explanation:

To understand the relationship between indexes and SQL performance, the user needs to know that indexes are used to improve the performance of queries that search large tables.

Now, let's go through each option and explain why it is right or wrong:

A. Indexes are only used in special cases: This option is incorrect. Indexes are commonly used in SQL databases to improve query performance, not just in special cases.

B. Indexes are used to make table storage more efficient: This option is partially correct. Indexes help make table storage more efficient by allowing for faster searching of the data, but their main purpose is to improve query performance.

C. Indexes rarely make a difference in SQL performance: This option is incorrect. Indexes can make a significant difference in the performance of SQL queries, especially when dealing with large tables.

D. Indexes exist solely to improve query speed: This option is partially correct. The primary purpose of indexes is to improve query speed, but they also help with other aspects of database performance such as data storage and retrieval.

The Answer is: D

Which of the following SQL clauses is used to select data from 2 or more tables?

  1. HAVING

  2. JOIN

  3. WHERE

  4. BETWEEN


Correct Option: B
- Hide questions