SQL- Server Query Basics - Quiz Explanation

The answers you selected are indicated below, along with text that explains the correct answers.
 
1. What will happen if Fred executes the following Transact-SQL code and the value of @x is 35? 
SELECT @y = 0
SELECT @z = 0
IF @x >= 40
 
SELECT @y = 5
SELECT @z = 10

Please select the best answer.
  A. @y will be equal to 5 and @z will be equal to 10
  B. @y will be equal to 0 and @z will be equal to 10
  C. @y will be equal to 0 and @z will be equal to 0
  D. @y will be equal to 5 and @z will be equal to 0
  The correct answer is B.
@y will be equal to 0 because it is first initialized.
It fails the test for @x >= 40 so doesn’t assign @y to be equal to 5, but does assign @z to be equal to 10 because there is no BEGIN…END block around the two statements, so only the first one is considered to be linked to the IF logic. Answers A, C, and D are incorrect for the opposite reason.

2. What tool comes with SQL Server 2012 that allows you to issue interactive queries and view results of those queries?
Please select the best answer.
  A. Enterprise Manager
  B. Service Manager
  C. Query Analyzer
  D. Query Manager
  The correct answer is C.
The Query Analyzer is the correct tool that comes with SQL Server 2012. A is incorrect because Enterprise Manager is not used to issue interactive queries. B is incorrect because Service Manager is used to start, stop, and pause services only. D is incorrect because there is no such tool as the Query Manager.

3. What keyword would you use to separate, or delimit, Transact-SQL batches?
Please select the best answer.
  A. CONTINUE
  B. GO
  C. BEGIN BATCH
  D. GO BATCH
  The correct answer is B.
The GO keyword is used to delimit batches. A is incorrect because CONTINUE is used in Transact-SQL loops, not separate batches. C is incorrect because the BEGIN BATCH set of keywords does not exist. D is incorrect because the GO BATCH set of keywords does not exist.


4. What will be the result of the following Transact-SQL? 
DECLARE @TableName varchar(20)
DECLARE @SQL varchar(1000)
SET @TableName = 'Employee'
SET @SQL = 'SELECT * FROM + @TableName’
EXEC @SQL
NOTE: Pay close attention to the syntax.
Please select the best answer.
  A. The correct data will be selected from the Employee table.
  B. The correct data will be selected from the @TableName table.
  C. A syntax error will result when SQL Server checks the syntax of the batch.
  D. A syntax error will result when SQL Server executes the EXEC line in the batch.
  The correct answer is D.
A syntax error will result when the EXEC line is executed because the quote containing the string literal includes the variable name. It will not be evaluated correctly. A is incorrect because the variable @TableName was never evaluated. B is incorrect because there cannot be a table named @TableName. C is incorrect because the error will not occur when the syntax of the batch is checked.