0

databases Online Quiz - 31

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

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. TRUNCATE rounds the column, expression, or value to n decimal places.

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

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

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


Correct Option: A,D,E,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. 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. The NVL2 function in SQL returns the second expression if the first expression is not null, otherwise, it returns the third expression.

Option C) TRUNCATE rounds the column, expression, or value to n decimal places. - This option is incorrect. The TRUNCATE function in SQL is used to remove all rows from a table or a partition, not to round values.

Option D) DECODE translates an expression after comparing it to each search value. - This option is correct. The DECODE function in SQL compares an expression to each search value and returns a result based on the match.

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

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. The NULLIF function in SQL compares two expressions and returns null if they are equal, or the first expression if they are not equal.

The four correct statements are A) INSTR, D) DECODE, E) TRIM, and F) NULLIF.

Which statement creates a new user?

  1. CREATE USER Susan;

  2. CREATE OR REPLACE USER Susan;

  3. CREATE NEW USER Susan DEFAULT;

  4. CREATE USER Susan IDENTIFIED BY blue;

  5. CREATE NEW USER Susan IDENTIFIED by blue;

  6. CREATE OR REPLACE USER Susan IDENTIFIED BY blue;


Correct Option: D

Mark for review Which SQL statement accepts user input for the columns to be displayed, the table name, and the WHERE condition?

  1. SELECT &1, "&2"FROM &3 WHERE last_name = '&4';

  2. SELECT &1, '&2' FROM &3 WHERE '&last_name = '&4'';

  3. SELECT &1, &2 FROM &3 WHERE last_name = '&4';

  4. SELECT &1, '&2' FROM EMP WHERE last_name = '&4';


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) SELECT &1, "&2" FROM &3 WHERE last_name = '&4'; This option is incorrect because it uses double quotation marks around &2, which would treat the input value as a string literal instead of a column name.

Option B) SELECT &1, '&2' FROM &3 WHERE '&last_name = '&4''; This option is incorrect because it contains syntax errors. The single quotation mark before "last_name" is misplaced, and there is an extra single quotation mark at the end.

Option C) SELECT &1, &2 FROM &3 WHERE last_name = '&4'; This option is correct because it allows user input for the columns to be displayed, the table name, and the WHERE condition. The input values &1, &2, &3, and &4 will be replaced with the user's input during runtime.

Option D) SELECT &1, '&2' FROM EMP WHERE last_name = '&4'; This option is incorrect because it does not accept user input for the table name. It specifically mentions the "EMP" table instead of using a variable to accept user input.

The correct answer is C. This option is correct because it accepts user input for the columns to be displayed, the table name, and the WHERE condition.

The user Sue issues this SQL statement: GRANT SELECT ON sue.EMP TO Alice WITH GRANT OPTION; The user Alice issues this SQL statement: GRANT SELECT ON sue.EMP TO Rena WITH GRANT OPTION; The user Rena issues this SQL statement: GRANT SELECT ON sue.EMP TO timber; The user Sue issues this SQL statement: REVOKE select on sue.EMP FROM Alice; For which users does the revoke command revoke SELECT privileges on the SUE.EMP table?

  1. Alice only

  2. Alice and Rena

  3. Alice, Rena, and Timber

  4. Sue, Alice, Rena, and Timber


Correct Option: C

AI Explanation

To determine which users have their SELECT privileges revoked on the SUE.EMP table, we need to analyze the SQL statements provided.

Initially, Sue grants SELECT privileges on sue.EMP to Alice with the GRANT OPTION. This means that Alice can grant these privileges to other users.

Then, Alice grants SELECT privileges on sue.EMP to Rena with the GRANT OPTION. Rena can also grant these privileges to other users.

Finally, Rena grants SELECT privileges on sue.EMP to Timber without the GRANT OPTION.

Afterward, Sue revokes the SELECT privileges on sue.EMP from Alice.

When a user grants privileges with the GRANT OPTION, they can pass on those privileges to other users. However, when a user grants privileges without the GRANT OPTION, they cannot pass on those privileges.

Therefore, the REVOKE command issued by Sue will revoke the SELECT privileges on sue.EMP from Alice. Since Alice granted these privileges to Rena with the GRANT OPTION, Rena will still have the SELECT privileges on sue.EMP. However, since Rena granted these privileges to Timber without the GRANT OPTION, Timber will also lose the SELECT privileges on sue.EMP.

Hence, the correct answer is:

C. Alice, Rena, and Timber

IDMS stands for _____________?

  1. Integrated Data Management System

  2. Integrated Database Management System

  3. Integrated Database Messaging System

  4. Internal Database Management System


Correct Option: B

IDMS is ______ type of database.

  1. Hierarchical

  2. Relational

  3. Network

  4. Other


Correct Option: C

In IDMS terminology, a parent record is called an ________ and a child record is called a ______.

  1. Owner and Child

  2. Head and Child

  3. Owner and Member

  4. Head and Tail


Correct Option: C

The unit of data that is passed by IDMS from database to application program is known as ________.

  1. Set

  2. Page

  3. Area

  4. Record Type


Correct Option: D

The relationship between 2 or more record types is defined by ___?

  1. Area

  2. Set Type

  3. Record Type

  4. Relation


Correct Option: B
  1. RMAN prompt

  2. SQLPLUS prompt

  3. Oracle Enterprise Manager 11g (OEM)

  4. DRA prompt


Correct Option: A,C
  1. rman > list failure;

  2. rman > report failure;

  3. rman > diagnose failure;

  4. rman > advise failure;

  5. rman > repair failure preview;

  6. rman > repair failure;


Correct Option: A,D,E,F
  1. database

  2. datafile

  3. tracefile

  4. spfile

  5. controlfilecopy

  6. flash recovery area


Correct Option: C
  1. ZLIB compression gives better compression ratio in comparison to BZIP2 compression

  2. BZIP2 compression gives better compression ratio in comparison to ZLIB compression

  3. ZLIB compression consumes much lesser CPU in comparison to BZIP2 compression

  4. BZIP2 compression consumes much lesser CPU in comparison to ZLIB compression

  5. ZLIB compression comes with additional licensing cost. BZIP2 compression comes with no additional cost

  6. BZIP2 compression comes with additional licensing cost. ZLIB compression comes with no additional cost


Correct Option: B,C,E
  1. rman > configure compression algorithm ‘zlib’;

  2. rman > configure compression algorithm ‘zlib11g’;

  3. rman > set compression algorithm ‘zlib’;

  4. rman > set compression algorithm ‘zlib11g’;


Correct Option: A
  1. Scenario 1: Backup 2 datafiles each of 20 GB datafile using 4 RMAN channels. Use the default setting for all RMAN channels

  2. Scenario 2: Backup 2 datafiles each of 20 GB datafile using 4 RMAN channels. Each channel backs up 2 datafiles at the same time with “parallel=2”

  3. Scenario 3: Backup 2 datafiles each of 20 GB size using 4 RMAN channels. Each channel backs up data with “section size=5GB"

  4. Scenario 4: Backup 2 datafiles each of 20 GB datafile using 4 RMAN channels. Each channel backs up 2 datafiles at the same time with “parallel backups=2”


Correct Option: C
  1. You can use group functions in any clause of a select statement

  2. You can use group functions only in the column list of the SELECT statement by grouping on the single row columns

  3. You can pass column names, expressions, constants or functions as parameters to a group function

  4. You cannot group the rows of a table by more than one column while using group functions


Correct Option: C
  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
  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

For which two constraints,does the oracle server implicitly creates an index ?

  1. NOT NULL

  2. PRIMARY KEY

  3. FOREIGN KEY

  4. CHECK

  5. UNIQUE


Correct Option: B,E
- Hide questions