0

databases Online Quiz - 235

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

What output will the follwing statement produce? Select NVL2(NULL,'NOT NULL', NULL) from dual?

  1. NULL

  2. NOT NULL

  3. Function NVL2 is not defined

  4. None of the above


Correct Option: A
Explanation:

To solve this question, the user needs to know the function NVL2 in SQL.

The NVL2 function in SQL returns one value if an expression is not null, or another value if it is null. The syntax for the NVL2 function is:

NVL2( expression1, expression2, expression3 )

If expression1 is not null, then NVL2 returns expression2. If expression1 is null, then NVL2 returns expression3.

In this case, expression1 is NULL, so NVL2 will return the value of expression3, which is also NULL.

Therefore, the answer is:

The Answer is: A. NULL

Functions for error trapping are contained in which section of a PL/SQL block?

  1. Header

  2. Declarative

  3. Executable

  4. Exception


Correct Option: D

AI Explanation

To answer this question, you need to understand the structure of a PL/SQL block. A PL/SQL block consists of three sections: the header, the declarative section, and the executable section. The header section contains the declaration of the PL/SQL block, including its name, parameters, and return type. The declarative section contains variable declarations, cursor declarations, and other items that are used within the block. The executable section contains the actual code that is executed when the block is invoked.

The correct answer is:

D. Exception - Functions for error trapping are contained in the exception section of a PL/SQL block. The exception section is used to catch and handle any errors that occur during the execution of the block. It contains exception handlers, which specify how to handle specific types of errors.

What is the result if two NULL values are compared to each other?

  1. TRUE

  2. FALSE

  3. Undefined

  4. NULL


Correct Option: D

PL/SQL raises an exception, in which TWO of the following cases?

  1. When a SELECT statement returns more than one row

  2. When a SELECT statement returns no rows

  3. When INTO statement is missing in the SELECT statement

  4. Both I and II


Correct Option: D
Explanation:

To answer this question, the user needs to know the basics of PL/SQL and how it handles exceptions.

A. When a SELECT statement returns more than one row: This is one of the cases where PL/SQL raises an exception. If a SELECT statement returns more than one row in a context where a single value is expected, such as in an assignment statement or a SELECT INTO statement, then PL/SQL will raise the TOO_MANY_ROWS exception.

B. When a SELECT statement returns no rows: This is another case where PL/SQL raises an exception. If a SELECT statement returns no rows in a context where a single value is expected, such as in an assignment statement or a SELECT INTO statement, then PL/SQL will raise the NO_DATA_FOUND exception.

C. When INTO statement is missing in the SELECT statement: This is not a case where PL/SQL raises an exception. If an INTO statement is missing in a SELECT statement, then a compilation error will occur, but it will not result in an exception at runtime.

D. Both I and II: This option is correct. PL/SQL raises exceptions in both cases where a SELECT statement returns more than one row (TOO_MANY_ROWS) and where a SELECT statement returns no rows (NO_DATA_FOUND).

Therefore, the correct answer is: D.

What output will the follwing statement produce? Select NVL2(NULL,NOT NULL, NULL) from dual?

  1. NULL

  2. NOT NULL

  3. Function NVL2 is not defined

  4. None of the above


Correct Option: A

Which SELECT statement will get the result 'elloworld' from the string 'HelloWorld'?

  1. SELECT INITCAP(TRIM ('HelloWorld', 1,1)) FROM dual;

  2. SELECT SUBSTR( 'HelloWorld',1) FROM dual;

  3. SELECT LOWER(SUBSTR('HelloWorld', 1, 1) FROM dual;

  4. SELECT LOWER(TRIM ('H' FROM 'HelloWorld')) FROM dual;


Correct Option: D
Explanation:

To solve this question, the user needs to know the basics of SQL SELECT statements and string manipulation functions. The user should be familiar with the INITCAP, TRIM, SUBSTR, and LOWER functions.

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

A. SELECT INITCAP(TRIM ('HelloWorld', 1,1)) FROM dual;

This option is incorrect. The TRIM function removes characters from the beginning and end of a string. In this case, the second and third arguments of the TRIM function are incorrect. INITCAP capitalizes the first letter of a string and converts the rest of the string to lowercase. Therefore, this SELECT statement will not return the correct result.

B. SELECT SUBSTR( 'HelloWorld',1) FROM dual;

This option is incorrect. The SUBSTR function returns a substring from a given string. In this case, the function only returns the first character of the string 'HelloWorld'. Therefore, this SELECT statement will not return the correct result.

C. SELECT LOWER(SUBSTR('HelloWorld', 1, 1) FROM dual;

This option is incorrect. The SUBSTR function returns a substring from a given string. In this case, the function only returns the first character of the string 'HelloWorld'. The LOWER function converts the substring to lowercase. Therefore, this SELECT statement will not return the correct result.

D. SELECT LOWER(TRIM ('H' FROM 'HelloWorld')) FROM dual;

This option is correct. The TRIM function removes the character 'H' from the beginning of the string 'HelloWorld'. The LOWER function converts the remaining string to lowercase. Therefore, this SELECT statement will return the correct result.

The Answer is: D

COALESCE (A,B)is equivalent to?

  1. CASE WHEN B IS NOT NULL THEN A ELSE B

  2. CASE WHEN A IS NOT NULL THEN A ELSE B

  3. CASE WHEN A IS NOT NULL THEN B ELSE A

  4. CASE WHEN A IS NOT NULL THEN A ELSE A


Correct Option: B

In which case would you use a FULL OUTER JOIN?

  1. Both tables have NULL values.

  2. You want all matched data from both tables.

  3. You want all unmatched data from both tables.

  4. You want all unmatched data from one table.


Correct Option: C

AI Explanation

To answer this question, you need to understand the concept of JOINs in SQL.

A FULL OUTER JOIN returns all rows from both tables, including unmatched rows from both tables. It combines the results of a LEFT OUTER JOIN and a RIGHT OUTER JOIN.

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

Option A) Both tables have NULL values - This option is not a specific condition for using a FULL OUTER JOIN. NULL values in both tables can exist regardless of the type of JOIN used.

Option B) You want all matched data from both tables - This option is not correct because a FULL OUTER JOIN returns all data from both tables, including unmatched data.

Option C) You want all unmatched data from both tables - This option is correct because a FULL OUTER JOIN returns all rows from both tables, including unmatched rows from both tables.

Option D) You want all unmatched data from one table - This option is not correct because a FULL OUTER JOIN returns all unmatched data from both tables, not just one.

The correct answer is C. A FULL OUTER JOIN is used when you want to retrieve all unmatched data from both tables.

mod(-15, 4)would return ?

  1. 15

  2. -15

  3. -3

  4. 3


Correct Option: C

instr(Tech on the net, e, -3, 2) would retrun

  1. 10

  2. 8

  3. 6

  4. 2


Correct Option: D

power(-5, 3) would return ?

  1. -15

  2. -125

  3. 125

  4. 15


Correct Option: B

TRUNC(44.647, -1) would retrun ?

  1. 44.6

  2. 44

  3. 40

  4. 43


Correct Option: C

Your company has created a custom application that will require the use of several stored procedures. The lifetime of this application is relatively short and you wish to create the stored procedures such that they can all be dropped with a single DROP PROCEDURE statement command at the end of the application's lifetime. How can you group the procedures together such that they can all be dropped with a single DROP PROCEDURE statement?

  1. Create each procedure with a distinct name followed by a semi-colon and a group name.

  2. Create each procedure with a distinct name followed by a semi-colon and a group number.

  3. Create each procedure with the same name followed by a semi-colon and a group name.

  4. Create each procedure with the same name followed by a semi-colon and a distinct number.


Correct Option: D

You have a mission critical database named MCDB1 that you want to configure for automatic failover. You have decided to utilize database mirroring. Which database mirroring operating mode should you use?

  1. High safety mode

  2. Asynchronously

  3. High performance mode

  4. Synchronously


Correct Option: A

You have been assigned the task of designing a relational database for a client. The database will be used to store an exact copy of specific XML documents that the client needs to retrieve on a regular basis. The client wants to be able to use an application that they created to enter the documents into the database as well as retrieve them. You are in the process of designing a table for the database. What should you do?

  1. Convert the XML documents to relational data and then store them within the database

  2. Create a table that uses XML as the data type for the column that will store the XML documents.

  3. Nothing. You can not store XML documents within a SQL Server 2005 database

  4. Create a table that uses nvarchar(max) as the data type for the column that will store the XML documents


Correct Option: D

EasyYatra is a travel booking company that has a web site available for customers to view and book their vacations online. You have been hired to look after the back-end SQL Server that is used to store customer and booking information. You are creating a table called Booking to keep track of vacations booked by customers. This table will contain a computed column called Total that is derived from other columns in the table. You want to be able to create an index on this Total column to increase performance when any queries are executed using this column. To do this, you want to physically store the computed column in the table. How can this be done?

  1. Mark the computed column as PERSISTED.

  2. Define the computed column as NULL.

  3. Specify the collation for the column using COLLATE Physical.

  4. Do nothing. Computed columns are stored in the table by default.


Correct Option: A

You are gathering performance and optimization data for your SQL Server 2005 database. Your company has three custom applications that routinely make use of the database. Each application accesses the database using separate login credentials. You wish to gather information concerning the queries executed by each custom application. To gather this data you decide to create a trace using the SQL Server Profiler. You decide to use one of the predefined SQL Server Profiler templates to define what data should be gathered. Which predefined SQL Server Profiler template would best suit your requirements?

  1. TSQL_Duration

  2. TSQL_Grouped

  3. TSQL_Replay

  4. TSQL_SPs


Correct Option: B

NA

  1. Na

  2. An

  3. fe

  4. fef


Correct Option: A

Which task can you perform using only the TO_CHAR function?

  1. convert 10 to 'TEN'

  2. convert '10' to 10

  3. convert '10' to '10'

  4. convert 'TEN' to 10


Correct Option: C

AI Explanation

To answer this question, you need to understand the TO_CHAR function in SQL.

The TO_CHAR function is used to convert a value to a character data type. It takes a value as input and returns a character representation of that value. However, it does not perform any other type conversions or transformations.

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

Option A) convert 10 to 'TEN' - This option is incorrect because the TO_CHAR function cannot convert a numeric value to a character string representing the word 'TEN'.

Option B) convert '10' to 10 - This option is incorrect because the TO_CHAR function does not perform any type conversions. It only converts a value to a character data type, so it cannot convert a character string to a numeric value.

Option C) convert '10' to '10' - This option is correct because the TO_CHAR function can be used to convert a character string to another character string. In this case, it would simply return the same character string '10'.

Option D) convert 'TEN' to 10 - This option is incorrect because the TO_CHAR function cannot convert a character string representing the word 'TEN' to a numeric value.

The correct answer is Option C) convert '10' to '10'. This option is correct because the TO_CHAR function can be used to convert a character string to another character string.

In which scenario would an index be most useful?

  1. The indexed column is declared as NOT NULL.

  2. The indexed columns are used in the FROM clause.

  3. The indexed columns are part of an expression.

  4. The indexed column contains a wide range of values.


Correct Option: D
- Hide questions