124 + Interview Questions in Developing With Sql Server in SQL Server Page 3 InterviewSolution

101.	How many type of cursor is present in SQL Server?(a) 3(b) 4(c) 5(d) 6This question was posed to me in an interview.I want to ask this question from Cursor topic in chapter Developing with SQL Server of SQL Server
Answer» Right answer is (B) 4 Easy explanation: SQL Server SUPPORTS four types of cursor.

Discussion

102.	Point out the correct statement.(a) A Cursor is opened and populated by executing the SQL statement defined by the cursor(b) After data manipulation, we should close the cursor explicitly(c) A cursor is declared by defining the SQL statement that returns a result set(d) All of the mentionedI have been asked this question in final exam.I would like to ask this question from Cursor in division Developing with SQL Server of SQL Server
Answer» RIGHT CHOICE is (d) All of the mentioned Explanation: We use cursor when we NEED to update records in a database table in singleton FASHION means ROW by row.

Discussion

103.	Life cycle of typical cursor involves ______ steps in SQL Server.(a) 2(b) 3(c) 4(d) 5This question was posed to me by my school principal while I was bunking the class.This interesting question is from Cursor topic in portion Developing with SQL Server of SQL Server
Answer» RIGHT answer is (d) 5 Easy explanation: The five STEPS are Declare CURSOR,Open,FETCH,CLOSE and Deallocate.

Discussion

104.	LANGUAGE variable whose return type nvarchar is specified in _____________(a) sys.name(b) syslang.name(c) syslanguages.name(d) syslanguages.idThe question was posed to me in an interview.The origin of the question is Variables in portion Developing with SQL Server of SQL Server
Answer» Right ANSWER is (c) syslanguages.NAME Easy EXPLANATION: The name of the language currently in use is specified in syslanguages.name.

Discussion

105.	Which of the following is a global variable?(a) CPU_BUSY(b) CPU_IO(c) CPU(d) I_BUSYI got this question during an interview.Question is taken from Variables topic in section Developing with SQL Server of SQL Server
Answer» Correct choice is (a) @@CPU_BUSY The explanation is: @@CPU_BUSY denotes the AMOUNT of time, in ticks, that the CPU has spent doing SQL SERVER work SINCE the last time SQL Server was started.

Discussion

106.	Which of the following keyword is not associated with initialization of variable?(a) DECLARE(b) SET(c) SELECT(d) All of the mentionedI have been asked this question during an online interview.I need to ask this question from Variables in chapter Developing with SQL Server of SQL Server
Answer» Correct choice is (d) All of the mentioned The best explanation: Variables are DECLARED in the body of a batch or procedure with the DECLARE STATEMENT and are assigned values by USING either a SET or SELECT statement.

Discussion

107.	Point out the wrong statement.(a) All the global variables represent information specific to the server or a current user session(b) SQL Server provides only three global variables(c) Global variables represent a special type of variable(d) None of the mentionedThe question was posed to me during an interview.I would like to ask this question from Variables in division Developing with SQL Server of SQL Server
Answer» The CORRECT choice is (B) SQL Server provides only three global variables To explain I would say: SQL Server provides a MASSIVE number of global variables, which are very effective to use in our regular Transact-SQL.

Discussion

108.	Variables was added newly to SQL Server version _____________(a) 2005(b) 2008(c) 2012(d) 2014I have been asked this question during an online interview.The doubt is from Variables topic in chapter Developing with SQL Server of SQL Server
Answer» The correct choice is (B) 2008 Best explanation: Variable FEATURE was INTRODUCED in SQL SERVER 2008.

Discussion

109.	Which of the following components constitutes use CASE expression?(a) The primary key name to transform(b) The BEGIN keyword(c) The CASE keyword(d) Where clause defining an alias for the CASE expressionI have been asked this question in a job interview.My question comes from Procedural Flow topic in division Developing with SQL Server of SQL Server
Answer» RIGHT choice is (c) The CASE keyword Explanation: A common use of the CASE expression is to REPLACE CODES or ABBREVIATIONS with more readable values.

Discussion

110.	The BEGIN and END statements are used when ____________(a) A WHILE loop needs to include a block of statements(b) An element of a CASE expression needs to include a block of statements(c) An IF or ELSE clause needs to include a block of statements(d) All of the mentionedThe question was posed to me in an interview.Question is from Procedural Flow in division Developing with SQL Server of SQL Server
Answer» CORRECT answer is (d) All of the mentioned The explanation is: A BEGIN and END statement block MUST CONTAIN at least one Transact-SQL statement.

Discussion

111.	Which of the following scenario makes use of BEGIN..END keyword redundant?(a) when an IF statement controls the execution of multiple Transact-SQL statement(b) when an IF statement controls the execution of only one Transact-SQL statement(c) when an IF statement controls the execution of one or more Transact-SQL statement(d) none of the mentionedThis question was addressed to me in quiz.Enquiry is from Procedural Flow topic in division Developing with SQL Server of SQL Server
Answer» The correct ANSWER is (b) when an IF statement CONTROLS the execution of only ONE Transact-SQL statement The explanation is: Use the BEGIN and END statements anywhere a control-of-flow statement must execute a BLOCK of two or more Transact-SQL statements.

Discussion

112.	Which of the following two Transact-SQL statements are commonly used with WHILE?(a) BREAK and CONTINUE(b) BREAK and IF(c) IF and ELSE(d) All of the mentionedThis question was addressed to me in homework.Question is taken from Procedural Flow topic in section Developing with SQL Server of SQL Server
Answer» CORRECT option is (a) BREAK and CONTINUE Easiest explanation: The BREAK STATEMENT exits the INNERMOST WHILE LOOP and the CONTINUE statement restarts a WHILE loop.

Discussion

113.	Point out the wrong statement.(a) GOTO can exist within conditional control-of-flow statements, statement blocks, or procedures(b) GOTO permissions need not be granted to any user(c) GOTO statements can be nested(d) None of the mentionedThe question was posed to me in my homework.This key question is from Procedural Flow in section Developing with SQL Server of SQL Server
Answer» The CORRECT option is (b) GOTO PERMISSIONS need not be granted to any user Best EXPLANATION: GOTO permissions default to any VALID user.

Discussion

114.	Which of the following statement decrypts a symmetric key?(a) OPEN SYMMETRIC KEY(b) DECRYPT SYMMETRIC KEY(c) CLOSE SYMMETRIC KEY(d) None of the mentionedI have been asked this question during an interview.I'm obligated to ask this question of Transact SQL in section Developing with SQL Server of SQL Server
Answer» Right ANSWER is (a) OPEN SYMMETRIC KEY To explain: OPEN SYMMETRIC KEY decrypts a symmetric key and makes it available for USE.

Discussion

115.	Purpose of CLOSE MASTER KEY is _______________(a) Deletes the master key of the current database(b) Closes the private key of the current database(c) Closes the public key of the current database(d) None of the mentionedThis question was posed to me by my school principal while I was bunking the class.I'm obligated to ask this question of Transact SQL topic in division Developing with SQL Server of SQL Server
Answer» Right CHOICE is (a) Deletes the master key of the current database Explanation: CLOSE MASTER KEY STATEMENT CLOSES the master key of the current database.

Discussion

116.	Which of the following language is used for procedural flow in SQL Server?(a) Flow language(b) Control language(c) Control-of-flow language(d) None of the mentionedI got this question by my college director while I was bunking the class.I would like to ask this question from Procedural Flow topic in division Developing with SQL Server of SQL Server
Answer» The correct answer is (c) Control-of-FLOW LANGUAGE Easiest explanation: Transact-SQL provides special WORDS CALLED control-of-flow language that control the flow of execution of Transact-SQL statements.

Discussion

117.	Which of the following is- a security statement?(a) ADD SIGNATURE(b) EXECUTE AS(c) SETUSER(d) All of the mentionedThe question was asked in class test.My enquiry is from Transact SQL topic in chapter Developing with SQL Server of SQL Server
Answer» Right OPTION is (d) All of the mentioned To EXPLAIN I would say: SQL Server provides many SECURITY statements for a secure DATABASE.

Discussion

118.	Which of the following Statement ends one side of an existing conversation?(a) BEGIN CONVERSATION TIMER(b) END CONVERSATION TIMER(c) END CONVERSATION(d) GET CONVERSATION GROUPThe question was posed to me in an interview.Query is from Transact SQL topic in division Developing with SQL Server of SQL Server
Answer» The CORRECT choice is (b) END CONVERSATION TIMER For explanation I would say: Service BROKER is designed for dialogs, not monolog CONVERSATIONS.

Discussion

119.	Point out the correct statement.(a) T-SQL is SQL language for SYBASE(b) GET_TRANSMISSION_STATUS does provide information for messages that do not have a conversation endpoint in the current instance(c) CREATE USER transfers a securable between schemas(d) CREATE LOGIN modifies the login detailsThis question was posed to me during an interview.My query is from Transact SQL topic in section Developing with SQL Server of SQL Server
Answer» The correct choice is (a) T-SQL is SQL LANGUAGE for SYBASE The EXPLANATION is: PRIOR to version 7.0 the code base for MS SQL SERVER was sold by Sybase SQL. Microsoft SQL and Sybase both support T-SQL statements.

Discussion

120.	RECEIVE service broker statement applies to which of the versions?(a) 2000(b) 2005(c) 2008(d) All of the mentionedI got this question during an interview.My question is based upon Transact SQL in division Developing with SQL Server of SQL Server
Answer» Correct choice is (c) 2008 Easiest explanation: RECEIVE RETRIEVES ONE or more messages from a queue. DEPENDING on the retention setting for the queue, either remove the message from the queue or UPDATES the STATUS of the message in the queue.

Discussion

121.	Purpose of GET_TRANSMISSION_STATUS is _____________(a) Returns the status for the first transmission for end side of a conversation(b) Returns the status for the first transmission for one side of a conversation(c) Returns the status for the last transmission for end side of a conversation(d) Returns the status for the last transmission for one side of a conversationThe question was asked in examination.The doubt is from Transact SQL in portion Developing with SQL Server of SQL Server
Answer» The correct option is (d) Returns the STATUS for the last transmission for ONE side of a conversation Easy explanation: GET_TRANSMISSION_STATUS returns a string DESCRIBING the status of the last transmission attempt for the SPECIFIED conversation.

Discussion

122.	Which of the following is not a SERVICE BROKER statement?(a) BEGIN CONVERSATION TIMER(b) END CONVERSATION(c) CREATE USERS(d) None of the mentionedThe question was posed to me in an interview for internship.The question is from Transact SQL in chapter Developing with SQL Server of SQL Server
Answer» Right OPTION is (c) CREATE USERS To explain I would say: SQL Server Service Broker provides NATIVE support for messaging and QUEUING applications in the SQL Server Database Engine.

Discussion

123.	Applications that can generate Transact-SQL can be ____________(a) General office productivity applications(b) Applications that use a graphical user interface (GUI) to let users select the tables and columns from which they want to see data(c) Applications that use general language sentences to determine what data a user wants to see(d) All of the mentionedI had been asked this question in homework.My question is based upon Transact SQL in chapter Developing with SQL Server of SQL Server
Answer» The correct option is (d) All of the mentioned The best I can EXPLAIN: Transact-SQL is central to USING SQL Server. All APPLICATIONS that communicate with an INSTANCE of SQL Server do so by sending Transact-SQL statements to the server, REGARDLESS of the user interface of the application.

Discussion

124.	Which of the following is a proprietary extension of SQL for Microsoft SQL Server?(a) PL-SQL(b) T-SQL(c) PG-SQL(d) P-SQLThe question was asked in an international level competition.This interesting question is from Transact SQL topic in chapter Developing with SQL Server of SQL Server
Answer» The CORRECT answer is (b) T-SQL To elaborate: Transact-SQL (T-SQL) is Microsoft’s and Sybase’s PROPRIETARY EXTENSION to SQL.

Discussion

Explore topic-wise InterviewSolutions in .

How many type of cursor is present in SQL Server?(a) 3(b) 4(c) 5(d) 6This question was posed to me in an interview.I want to ask this question from Cursor topic in chapter Developing with SQL Server of SQL Server

Life cycle of typical cursor involves ______ steps in SQL Server.(a) 2(b) 3(c) 4(d) 5This question was posed to me by my school principal while I was bunking the class.This interesting question is from Cursor topic in portion Developing with SQL Server of SQL Server

LANGUAGE variable whose return type nvarchar is specified in _____________(a) sys.name(b) syslang.name(c) syslanguages.name(d) syslanguages.idThe question was posed to me in an interview.The origin of the question is Variables in portion Developing with SQL Server of SQL Server

Which of the following is a global variable?(a) CPU_BUSY(b) CPU_IO(c) CPU(d) I_BUSYI got this question during an interview.Question is taken from Variables topic in section Developing with SQL Server of SQL Server

Which of the following keyword is not associated with initialization of variable?(a) DECLARE(b) SET(c) SELECT(d) All of the mentionedI have been asked this question during an online interview.I need to ask this question from Variables in chapter Developing with SQL Server of SQL Server

Variables was added newly to SQL Server version _____________(a) 2005(b) 2008(c) 2012(d) 2014I have been asked this question during an online interview.The doubt is from Variables topic in chapter Developing with SQL Server of SQL Server

Which of the following two Transact-SQL statements are commonly used with WHILE?(a) BREAK and CONTINUE(b) BREAK and IF(c) IF and ELSE(d) All of the mentionedThis question was addressed to me in homework.Question is taken from Procedural Flow topic in section Developing with SQL Server of SQL Server

Which of the following is- a security statement?(a) ADD SIGNATURE(b) EXECUTE AS(c) SETUSER(d) All of the mentionedThe question was asked in class test.My enquiry is from Transact SQL topic in chapter Developing with SQL Server of SQL Server

RECEIVE service broker statement applies to which of the versions?(a) 2000(b) 2005(c) 2008(d) All of the mentionedI got this question during an interview.My question is based upon Transact SQL in division Developing with SQL Server of SQL Server

Which of the following is not a SERVICE BROKER statement?(a) BEGIN CONVERSATION TIMER(b) END CONVERSATION(c) CREATE USERS(d) None of the mentionedThe question was posed to me in an interview for internship.The question is from Transact SQL in chapter Developing with SQL Server of SQL Server

Which of the following is a proprietary extension of SQL for Microsoft SQL Server?(a) PL-SQL(b) T-SQL(c) PG-SQL(d) P-SQLThe question was asked in an international level competition.This interesting question is from Transact SQL topic in chapter Developing with SQL Server of SQL Server