T-SQL Exam questions and answers – Part 6

Questions

Q1. Which of the following function would you consider using to converts an expression of one data type to another ?

A. CAST()

B. RANK()

C. NTILE()

D. CHOOSE()

Q2. What will be the correct output of following T-SQL statement?

SELECT RAND(100);

1	SELECT RAND(100);

A. 0.415427657

B. 1.672458909

C. 1

D. -1

Q3. What will be the correct output of following T-SQL statement?

SELECT ROUND(150.01, -2) ;

1	SELECT ROUND(150.01, -2) ;

A. 200

B. 100

C. 150

D. 151

Q4. What will be the correct output of following T-SQL statement?

CREATE TABLE tbl_Test(Col1 nvarchar(max));

SELECT COL_LENGTH('tbl_Test','Col1');

DROP TABLE tbl_Test;

CREATE TABLE tbl_Test(Col1 nvarchar(max));

SELECT COL_LENGTH('tbl_Test','Col1');

DROP TABLE tbl_Test;

A. 10

B. 20

C. 40

D. -1

Q5. What will be the correct output of following T-SQL statement?

CREATE TABLE tbl_Test(Col1 int,Col2 nvarchar(100));

SELECT COL_NAME('tbl_Test', 1);

DROP TABLE tbl_Test;

CREATE TABLE tbl_Test(Col1 int,Col2 nvarchar(100));

SELECT COL_NAME('tbl_Test', 1);

DROP TABLE tbl_Test;

A. 1

B. NULL

C. Col1

D. Error Message: Conversion failed when converting the varchar value ‘tbl_Test’ to data type int.

Q6. What will be the possible output of following T-SQL statement?

DECLARE @value nvarchar(10)='FALSE';

SELECT

CASE WHEN TRY_CAST(@value AS bit) IS NULL THEN 'Cast Failed'

ELSE TRY_CAST(@value AS bit)

END;

DECLARE @value nvarchar(10)='FALSE';

SELECT

CASE WHEN TRY_CAST(@value AS bit) IS NULL THEN 'Cast Failed'

ELSE TRY_CAST(@value AS bit)

END;

A. 0

B. 1

C. FALSE

D. Cast Failed

Q7. What will be the possible output of following T-SQL statement?

DECLARE @value datetimeoffset='2014-07-21 21:19:01.9414542 +01:00';

SELECT CASE WHEN TRY_CONVERT(time,@value) IS NULL THEN 'Cast failed'

ELSE TRY_CONVERT(time,@value)

END;

DECLARE @value datetimeoffset='2014-07-21 21:19:01.9414542 +01:00';

SELECT CASE WHEN TRY_CONVERT(time,@value) IS NULL THEN 'Cast failed'

ELSE TRY_CONVERT(time,@value)

END;

A. TRUE

B. 21:19:02

C. FALSE

D. Cast failed

Q8. Which of the following function would you consider using to insert a string into another string?

A. REVERSE()

B. STUFF()

C. FORMAT()

D. STR()

Q9. What will be the correct output of following T-SQL statement?

DECLARE @value bigint = 123456789;

SELECT FORMAT( @value, '###-##-####' );

DECLARE @value bigint = 123456789;

SELECT FORMAT( @value, '###-##-####' );

A. ###-##-####

B. 123456789

C. 123-45-6789

D. 12345-6789

Q10. What will be the correct output of following T-SQL statement?

DECLARE @date DATETIME = '01/30/2014';

SELECT FORMAT ( @date, 'MMMM dd, yyyy (dddd)', 'en-GB' );

DECLARE @date DATETIME = '01/30/2014';

SELECT FORMAT ( @date, 'MMMM dd, yyyy (dddd)', 'en-GB' );

A. 01/30/2014

B. 30/01/2014

C. 30/01/2014

D. January 30, 2014 (Thursday)

Answers

Q1. Correct Answer : A

CAST() Function Converts an expression of one data type to another in SQL Server 2012.

Q2. Correct Answer : A

RAND() mathematical function returns a pseudo-random float value from 0 through 1, exclusive.

Q3. Correct Answer : A

ROUND() mathematical function returns a numeric value, rounded to the specified length or precision.

Q4. Correct Answer : D

COL_LENGTH() function returns the defined length, in bytes, of a column.

Q5. Correct Answer : D

COL_NAME() function returns the name of a column from a specified corresponding table identification number and column identification number.

Q6. Correct Answer : A

TRY_CAST() function returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Q7. Correct Answer : B

TRY_CONVERT() function returns a value cast to the specified data type if the cast succeeds; otherwise, returns null.

Q8. Correct Answer : B

STUFF() function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

Q9. Correct Answer : C

FORMAT() function returns a value formatted with the specified format and optional culture in SQL Server 2012. Use the FORMAT function for locale-aware formatting of date/time and number values as strings. For general data type conversions, use CAST or CONVERT. – Syntax FORMAT ( value, format [, culture ] )

Q10. Correct Answer : D