T-SQL Exam questions and answers – Part 2

Questions

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

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

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

A. 08/01/2014

B. 08/01/2014 00:00:00

C. 07/01/2014 00:00:00

D. 08/01/2014 00:00:00

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

DECLARE @expression nvarchar(100) ='         Querying SQL Server 2012';
SELECT LTRIM(@expression);

1 2	DECLARE @expression nvarchar(100) =' Querying SQL Server 2012'; SELECT LTRIM(@expression);

A. Querying SQL Server 2012

B. Querying SQL Server 2012

C. Querying

D. 2012

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

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

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

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

SELECT @@IDENTITY;

DROP TABLE tbl_Test;

A. 1

B. 2

C. 3

D. 0

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

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

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

A. DATETIME2

B. DATETIME

C. SMALLDATETIME

D. DATE

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

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

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

A. 01/01/2014

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

C. 02/01/2014 01:00:00

D. 01/01/2014 10:00:00

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

DECLARE @expression nvarchar(100) ='     £150,000.00 ';
SELECT LTRIM(@expression);

1 2	DECLARE @expression nvarchar(100) =' £150,000.00 '; SELECT LTRIM(@expression);

A. 150,000.00

B. £150,000.00

C. £150,000.00

D. 150000

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

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

DROP TABLE tbl_Test;

CREATE TABLE tbl_Test(Col1 int IDENTITY(1,10) PRIMARY KEY,Col2 varchar(20));

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

SELECT @@IDENTITY;

DROP TABLE tbl_Test;

A. 1

B. 20

C. 21

D. 22

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

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

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

A. DATETIME

B. DATETIME2

C. SMALLDATETIME

D. DATETIMEOFFSET

Q9. 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. 1/01/2014

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

C. 02/01/2014 01:00:00

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

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

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

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

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

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

A. SQL

B. Server 2012 – 1000 Exam questions and answers

C. 10

D. 12

Answers

Q1. Correct Answer : B

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

Q2. Correct Answer : B

Hint : LTRIM() function returns a character expression after it removes leading blanks.

Q3. Correct Answer : C

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

Q4. Correct Answer : A

Hint : SYSDATETIME() Function returns a datetime2(7) value that contains the date and time of the computer on which the instance of SQL Server is running.

Q5. Correct Answer : B

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

Q6. Correct Answer : C

LTRIM() function returns a character expression after it removes leading blanks.

Q7. Correct Answer : C

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

Q8. Correct Answer : D

Hint : SYSDATETIMEOFFSET() Function returns a datetimeoffset(7) value that contains the date and time of the computer on which the instance of SQL Server is running. The time zone offset is included.

Q9. Correct Answer : D

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

Q10. 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.