T-SQL Exam questions and answers – Part 3

Questions

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

CREATE TABLE tbl_Test(Col1 int IDENTITY,Col2 varchar(20));
INSERT tbl_Test   VALUES ('Football'),('Cricket'),('Rugby');
SELECT @@IDENTITY;

DROP TABLE tbl_Test;

CREATE TABLE tbl_Test(Col1 int IDENTITY,Col2 varchar(20));

INSERT tbl_Test VALUES ('Football'),('Cricket'),('Rugby');

SELECT @@IDENTITY;

DROP TABLE tbl_Test;

A. 1

B. 2

C. 3

D. 4

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

SELECT CHOOSE(6, CURRENT_TIMESTAMP,SYSDATETIME(),GETDATE(),
SYSDATETIMEOFFSET(),SYSUTCDATETIME(),GETUTCDATE());

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

A. DATETIME

B. DATETIME2

C. SMALLDATE

D. DATETIMEOFFSET

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

DECLARE @value int = -30, @datetime datetime2 = '2014-01-01';
SELECT DATEADD(minute, @value, @datetime);

1 2	DECLARE @value int = -30, @datetime datetime2 = '2014-01-01'; SELECT DATEADD(minute, @value, @datetime);

A. 01/01/2014

B. 01/01/2014 01:30:00

C. 31/12/2013 23:30:00

D. 01/01/2014 00:30:00

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

DECLARE @pattern nvarchar(50)='q';
DECLARE @expression varchar(100)='Querying SQL Server 2012 - 1000 Exam questions and answers';
SELECT PATINDEX('%'+ @pattern +'%' , @expression );

DECLARE @pattern nvarchar(50)='q';

DECLARE @expression varchar(100)='Querying SQL Server 2012 - 1000 Exam questions and answers';

SELECT PATINDEX('%'+ @pattern +'%' , @expression );

A. q

B. Q

C. 1

D. 0

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

CREATE TABLE tbl_Test(Col1 int IDENTITY,Col2 varchar(20));INSERT tbl_Test   VALUES ('Football'),('Cricket'),('Rugby')
SELECT IDENT_CURRENT('tbl_Test');
DROP TABLE tbl_Test;

CREATE TABLE tbl_Test(Col1 int IDENTITY,Col2 varchar(20));INSERT tbl_Test VALUES ('Football'),('Cricket'),('Rugby')

SELECT IDENT_CURRENT('tbl_Test');

DROP TABLE tbl_Test;

A. 1

B. 2

C. 3

D. 4

Q6. What is the default date time format of the DATETIME data type ?

A. YYYY-MM-DD

B. YYYY-MM-DD hh:mm:ss

C. YYYY-MM-DD hh:mm:ss[.nnn]

D. YYYY-MM-DD hh:mm:ss[.nnnnnnn] [+|-]hh:mm

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

DECLARE @startdate datetime2 = '2014-01-01', @enddate datetime2 = '2014-04-01';
SELECT DATEDIFF(year, @startdate, @enddate);

1 2	DECLARE @startdate datetime2 = '2014-01-01', @enddate datetime2 = '2014-04-01'; SELECT DATEDIFF(year, @startdate, @enddate);

A. 0

B. 1

C. -1

D. 0.25

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

DECLARE @pattern nvarchar(50)='S';
DECLARE @expression varchar(100)='Querying SQL Server 2012 - 1000 Exam questions and answers';
SELECT PATINDEX('%'+ @pattern +'_r%' , @expression );

DECLARE @pattern nvarchar(50)='S';

DECLARE @expression varchar(100)='Querying SQL Server 2012 - 1000 Exam questions and answers';

SELECT PATINDEX('%'+ @pattern +'_r%' , @expression );

A. S

B. 10

C. 14

D. 0

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

CREATE TABLE tbl_Test(Col1 int IDENTITY(1,2),Col2 varchar(20));
INSERT tbl_Test   VALUES ('Football'),('Cricket'),('Rugby');
SELECT IDENT_CURRENT('tbl_Test');DROP TABLE tbl_Test;

CREATE TABLE tbl_Test(Col1 int IDENTITY(1,2),Col2 varchar(20));

INSERT tbl_Test VALUES ('Football'),('Cricket'),('Rugby');

SELECT IDENT_CURRENT('tbl_Test');DROP TABLE tbl_Test;

A. 1

B. 3

C. 4

D. 5

Q10. What is the default date time format of the DATETIME2 data type ?

A. YYYY-MM-DD

B. hh:mm:ss

C. YYYY-MM-DD hh:mm:ss[.nnn]

D. YYYY-MM-DD hh:mm:ss[.nnnnnnn]

Answers

Q1. Correct Answer : C

Hint : @@IDENTITY is a system function that returns the last-inserted identity value.

Q2. Correct Answer : A

Hint : GETUTCDATE() Function returns the current database system timestamp as a datetime value. The database time zone offset is not included. This value represents the current UTC time (Coordinated Universal Time).

Q3. Correct Answer : C

Hint : DATEADD() function returns a specified date with the specified number interval (signed integer) added to a specified datepart of that date.

Q4. Correct Answer : C

Hint : PATINDEX() function returns the starting position of the first occurrence of a pattern in a specified expression, or zeros if the pattern is not found, on all valid text and character data types.

Q5. Correct Answer : C

Hint : IDENT_CURRENT() function returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

Q6. Correct Answer : C

Q7. Correct Answer : A

Hint : DATEDIFF() function returns the count (signed integer) of the specified datepart boundaries crossed between the specified startdate and enddate.

Q8. Correct Answer : C

Q9. Correct Answer : D

Hint : IDENT_CURRENT() function returns the last identity value generated for a specified table or view. The last identity value generated can be for any session and any scope.

Q10. Correct Answer : D