SQL SERVER: QUERYING Q & A

http://www.sql-server-helper.com/free-test/sql-question.aspx

70-461: Querying Microsoft SQL Server 2012

 

Introduced in SQL Server 2012, the TIMEFROMPARTS function returns a time value for the specified time and with the specified precision. What is the output of the following TIMEFROMPARTS function?

SELECT TIMEFROMPARTS( 1, 2, 3, 4, NULL)

A. NULL

B. 01:02:03.4

C. 01:02:03.04

D. 01:02:03.004

E. 01:02:03.0004

F. None of the above.

 

(Answer) F

The last parameter of the TIMEFROMPARTS function is the precision of the time value to be returned.  If a NULL value is passed to this parameter, an error will be raised.

 

In SQL Server 2012, which of the following is NOT an attribute of a valid geography CurvePolygon instance?

A. No ring can cross itself or another ring.

B. The interior of the polygon must be connected.

C. The interior of the polygon is connected using the left-hand rule.

D. Rings can only touch at single tangent points (number of points where rings touch must be finite).

E. All the interior rings must be contained within the exterior ring.

F. None of the Above.

 

(Answer) E

All of these are attributes of a valid geography CurvePolygon instance except for "all the interior rings must be contained within the exterior ring" as this is an attribute of a valid geometry CurvePolygon instance.

 

Which of the following new analytic functions in SQL Server 2012 accesses data from a subsequent row in the same result set without the use of a self-join and provides access to a row at a given physical offset that follows the current row?

A. LAST_VALUE

B. NEXT_VALUE

C. NEXT_ROW

D. LEAD

E. LAG

F. None of the above

 

(Answer) D

In SQL Server 2012, the new analytic function LEAD accesses data from a subsequent row in the same result set without the use of a self-join and provided access to a row at a given physical offset that follows the current row.  This analytic function in a SELECT statement is used to compare values in the current row with values in a following row.

 

In SQL Server 2012, what is the data type of the output of the following SELECT statement?

SELECT IIF(@TodaysDate > @ComparisonDate, 100, GETDATE()) AS [Output]

A. INT

B. DATETIME

C. SQL_VARIANT

D. Depends on the result of the Boolean expression (@TodaysDate > @ComparisonDate).

E. None of the above.

 

(Answer) B

The IIF logical function introduced in SQL Server 2012 returns the data type with the highest precedence between the second parameter (true value) and the third parameter (false value).  From the given question, DATETIME has a higher precedence than INT.

 

The THROW statement raises an exception and transfers execution to a CATCH block of a TRY... CATCH construct in SQL Server 2012.  Which of the following code snippets is a valid way of using the THROW statement?

A.

BEGIN TRY

    IF @Denominator = 0

        THROW

 

    SELECT @Numerator / @Denominator

END TRY

BEGIN CATCH

    PRINT 'Division by zero error will occur.'

END CATCH

B.

BEGIN TRY

    IF @Gender NOT IN ('M', 'F')

        THROW 50000, 'Invalid Gender', 1

END TRY

BEGIN CATCH

    PRINT 'Please enter a valid Gender'

END CATCH

C.

BEGIN TRY

    IF NOT EXISTS (SELECT 'X' FROM [dbo].[Customer]

                   WHERE [CustomerID] = @CustomerID)

    BEGIN

        PRINT 'Customer not found'

        THROW 50000, 'Customer not found', 1

    END

END TRY

BEGIN CATCH

    PRINT 'Invalid customer number.'

END CATCH

D. Options A, B and C are valid.

E. Options B and C only are valid.

F. None of the above.

 

 

(Answer) B

If the THROW statement is specified without parameters, it must appear inside a CATCH block.  This causes the caught exception to be raised.  Given this, option A will generate the following error message:

Msg 10704, Level 15, State 1, Line 8
To rethrow an error, a THROW statement must be used inside a CATCH block. 
Insert the THROW statement inside a CATCH block, or add error parameters to the THROW statement.

The statement before the THROW statement must be followed by the semicolon (;) statement terminator.  Given this, option C will generate the following error message:

Msg 102, Level 15, State 1, Line 7
Incorrect syntax near 'THROW'.

 

 

In SQL Server 2012, what is the output of the following SELECT statement?

SELECT CHOOSE (0, 1, 2, 3) AS [Output]

A. 0

B. 1

C. 2

D. 3

E. NULL

F. An error will be generated.

 

(Answer) E

The CHOOSE logical function introduced in SQL Server 2012 returns the item at the specified index from a list of values.  If the index value exceeds the bounds of the array of values, then CHOOSE returns NULL.

 

Which of the following new analytic functions introduced in SQL Server 2012 returns the last value in an ordered set of values?

A. LAST_ROW

B. LAST_VALUE

C. LEAD_VALUE

D. LAG_VALUE

E. None of the above

 

(Answer) B

LAST_VALUE is a new analytic function introduced in SQL Server 2012 which returns the last value in an ordered set of values in SQL Server 2012.

LAST_ROW, LEAD_VALUE and LAG_VALUE are incorrect because these are not valid analytic functions in SQL Server 2012.

 

Introduced in SQL Server 2012, a SEQUENCE is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. The sequence of numeric values is generated in an ascending or descending order at a defined interval and may cycle (repeat) as requested.

Given the following sequence definition:

CREATE SEQUENCE [dbo].[SQLSequence] AS TINYINT MAXVALUE 4 CYCLE

What is the output of the following statement?

SELECT CASE NEXT VALUE FOR [dbo].[SQLSequence]

            WHEN 0 THEN 100

            WHEN 1 THEN 200

            WHEN 2 THEN 300

            ELSE NULL

       END AS [Score]

A. 100

B. 200

C. 300

D. NULL

E. Depends on the output of the NEXT VALUE FOR function.

F. None of the above.

 

(Answer) F

The following error message will be raised by the SELECT statement:

Msg 11741, Level 15, State 1, Line 4

NEXT VALUE FOR function cannot be used within CASE, CHOOSE, COALESCE, IIF, ISNULL and NULLIF.

 

In SQL Server 2012, which of the following is NOT an attribute of a valid CircularString instance?

A. A CircularString instance cannot overlap itself over an interval.

B. A CircularString instance must have an odd number of points.

C. A CircularString instance must contain at least one circular arc segment.

D. The last endpoint for each circular arc segment in the sequence of a CircularString instance, except for the last segment, must be the first endpoint for the next segment in the sequence.

E. Although CircularString instances may contain line segments, these line segments must be defined by three collinear points.

F. None of the above.  All of these are attributes of a valid CircularString instance.

 

(Answer) F

 

In SQL Server 2012, all of the following will return the date part of a DATETIME value except for which one?

A. DATEFROMPARTS(MONTH(GETDATE()), DAY(GETDATE()), YEAR(GETDATE()))

B. DATEADD(DD, 0, DATEDIFF(DD, 0, GETDATE()))

C. CAST(GETDATE() AS DATE)

D. CAST(CONVERT(VARCHAR(10), GETDATE(), 111) AS DATETIME)

E. CAST(FLOOR(CAST(GETDATE() AS DECIMAL(12, 5))) AS DATETIME)

F. None of the above.  All of these return the date part of a DATETIME value.

 

 

(Answer) A

All of these will return the date part of a DATETIME value except for option A as this will generate an error.  The correct sequence of the parameters passed to the DATEFROMPARTS date function is YEAR, MONTH and DAY and not MONTH, DAY and YEAR.