T-SQL Exam questions and answers – Part 1

Questions

Q1. What is the data type of the output of the following SELECT Query?

SELECT CHOOSE(2, SYSDATETIME(), GETDATE(), SYSDATETIMEOFFSET(), SYSUTCDATETIME(), GETUTCDATE());

1	SELECT CHOOSE(2, SYSDATETIME(), GETDATE(), SYSDATETIMEOFFSET(), SYSUTCDATETIME(), GETUTCDATE());

A. DATETIME2

B. DATETIME

C. SMALLDATETIME

D. DATE

Q2. What is the default date range of the DATETIME2 data type ?

A. 1900-01-01 through 2079-06-06

B. 1753-01-01 through 9999-12-31

C. 0001-01-01 through 9999-12-31

D. 1700-01-01 through 2999-06-06

Q3. Which date and time Function can be used to return an integer that represents the year of the specified date?

A. MONTH()

B. DATENAME()

C. DAY()

D. YEAR()

Q4. Which of the following function would you consider using to get last identity value inserted into an identity column in the same scope ?

A. SCHEMA_NAME()

B. SCOPE_IDENTITY()

C. APP_NAME()

D. SCHEMA_ID()

Q5. Which of the following function would you consider using to get the line number at which an error occurred that caused the CATCH block of a TRY CATCH construct ?

A. @@IDENTITY

B. CONTEXT_INFO()

C. @@ROWCOUNT

D. ERROR_LINE()

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

A. CONVERT()

B. COS()

C. COT()

D. CHOOSE()

Q7. What function would you use to get the statistical standard deviation for the population for all values in the specified expression ?

A. VAR()

B. COUNT()

C. STDEVP()

D. AVG()

Q8. Which of the following function would you consider using to get the version and processor architecture for the current installation of SQL Server.?

A. @@VERSION

B. @@DBTS

C. @@SPID

D. @@SERVERNAME

Q9. Which of the following function would you consider using to repeat a string value a specified number of times.

A. CHARINDEX()

B. REPLACE()

C. REPLICATE()

D. CHAR()

Q10. What function would you use to get the number of network packet errors that have occurred on SQL Server connections ?

A. @@CONNECTIONS

B. @@IDLE

C. @@PACKET_ERRORS

D. @@TOTAL_ERRORS

Answers

Q1. Correct Answer : B

Hint : GETDATE() Function returns the current database system timestamp as a datetime value without the database time zone offset.

Q2. Correct Answer : C

Q3.Correct Answer : D

Hint : YEAR() function returns an integer that represents the year of the specified date

Q4. Correct Answer : B

Hint :SCOPE_IDENTITY() funtion returns the last identity value inserted into an identity column in the same scope. A scope is a module: a stored procedure, trigger, function, or batch. Therefore, two statements are in the same scope if they are in the same stored procedure, function, or batch.

Q5. Correct Answer : D

Hint : ERROR_LINE() funtion returns the line number at which an error occurred that caused the CATCH block of a TRY CATCH construct.

Q6. Correct Answer : A

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

Q7. Correct Answer : C

Hint : STDEVP() function returns the statistical standard deviation for the population for all values in the specified expression.

Q8. Correct Answer : A

Hint : @@VERSION function returns version, processor architecture, build date, and operating system for the current installation of SQL Server.

Q9. Correct Answer : C

Hint : REPLICATE() function repeats a string value a specified number of times.

Q10. Correct Answer : C

Hint : @@PACKET_ERRORS function returns the number of network packet errors that have occurred on SQL Server connections since SQL Server was last started.