The main features of dotConnect for SQL Server includes :

Extra data binding capabilities

Ability of monitoring query execution

Supports the latest versions of SQL Server

Syntax for closing and opening the connection in ADO.net is :

sqlConn.Open() and sqlConn.close()

sqlConn.open() and sqlConn.Close()

sqlConn.Open() and sqlConn.Close()

The easiest way to start is to create a linked server ‘localhost’ by using :

sp_addlinkedserv ‘localhost’

sp_addlinkedsrvlogin ‘localhost’

sp_addlinkedserverlogin ‘localhost’

sp_addlinkedserver ‘localhost’

Which of the following script is an example of Quick detection in SQL injection attack?

IF condition true-part ELSE false-part (S)

SELECT loginame FROM master..sysprocesses WHERE spid = @@SPID

For integer inputs : convert(int,@@version)

IF condition true-part ELSE false-part (S)

SELECT header, txt FROM news UNION ALL SELECT name, pass FROM members

If xp_cmdshell has been disabled with sp_dropextendedproc, we can simply inject the following code:

sp_addproc ‘xp_cmdshell’,’xp_log70.dll’

sp_addextendedproc ‘xp_cmdshell’,’xp_log70.dll’

sp_addproc ‘xp_cmdshell’,’xp_log70.dll’

sp_addextendedproc ‘xp_cmdshell’,’log70.dll’

Which of the following is a disadvantage of dynamic SQL ?

Stored procedure can cache the execution plan for this dynamic query

Stored procedure can not cache the execution plan for this dynamic query

Stored procedure can cache the execution plan for this dynamic query

Flexibility in your code that you can not get with standard SQL

Which of the following is not a limitation of user defined function ?

The FOR XML clause is not allowed

User-defined functions cannot call a stored procedure, but can call an extended stored procedure

User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed

SET statements are allowed in a user-defined function

The FOR XML clause is not allowed

Which of the following does not return a table variable?

Multi-Statement Table-Valued Function

Reasons for consideration of stored procedure can be:

With which tables will you have to work? Does it make sense to create a VIEW first?

How often will this procedure actually be used?

Do you want to view data in the database (SELECT), insert new records (INSERT INTO), or do I want to change an existing record (UPDATE)?

Reasons for consideration of stored procedure can be :

With which tables will you have to work? Does it make sense to create a VIEW first?

How often will this procedure actually be used?

Do you want to view data in the database (SELECT), insert new records (INSERT INTO), or do I want to change an existing record (UPDATE)?

424 + Mcqs in Basic Sql in Structured Query Language (SQL) Page 6 McqOptions

251.	The main features of dotConnect for SQL Server includes :
A.	Extra data binding capabilities
B.	Ability of monitoring query execution
C.	Supports the latest versions of SQL Server
D.	All of the mentioned
Answer» E.

Discussion

252.	Syntax for closing and opening the connection in ADO.net is :
A.	sqlConn.Open() and sqlConn.close()
B.	sqlConn.open() and sqlConn.Close()
C.	sqlConn.Open() and sqlConn.Close()
D.	None of the mentioned
Answer» D. None of the mentioned

Discussion

253.	________object is used to fill a DataSet/DataTable with query results in ADO.net.
A.	DataReader
B.	Dataset
C.	DataAdapter
D.	DataTables
Answer» D. DataTables

Discussion

254.	The easiest way to start is to create a linked server ‘localhost’ by using :
A.	sp_addlinkedserv ‘localhost’
B.	sp_addlinkedsrvlogin ‘localhost’
C.	sp_addlinkedserverlogin ‘localhost’
D.	sp_addlinkedserver ‘localhost’
Answer» E.

Discussion

255.	Which of the following SQL syntax elements is dictated by the SQL dialect levels ?
A.	LIKE support
B.	Parameter marker support
C.	Nested query support
D.	None of the mentioned
Answer» E.

Discussion

256.	Which is default field terminator for bulk insert in SQL Server ?
A.	Comma
B.	Full stop
C.	Parenthesis
D.	Brackets
Answer» B. Full stop

Discussion

257.	_______ parameter specifies the number of rows in a batch in bulk insert script.
A.	KEEPNULLS
B.	KEEPIDENTITY
C.	FIRE_TRIGGERS
D.	None of the mentioned
Answer» E.

Discussion

258.	_________ does not allow bulk export operation in SQL Server.
A.	INSERT … SELECT
B.	DELETE
C.	UPDATE
D.	None of the mentioned
Answer» B. DELETE

Discussion

259.	_____________ is time based SQL injection attack.
A.	Quick detection
B.	Initial Exploitation
C.	Blind SQL Injection
D.	Inline Comments
Answer» D. Inline Comments

Discussion

260.	Which of the following script is an example of Quick detection in SQL injection attack?
A.	SELECT loginame FROM master..sysprocesses WHERE spid = @@SPID
B.	For integer inputs : convert(int,@@version)
C.	IF condition true-part ELSE false-part (S)
D.	SELECT header, txt FROM news UNION ALL SELECT name, pass FROM members
Answer» C. IF condition true-part ELSE false-part (S)

Discussion

261.	If xp_cmdshell has been disabled with sp_dropextendedproc, we can simply inject the following code:
A.	sp_addextendedproc ‘xp_cmdshell’,’xp_log70.dll’
B.	sp_addproc ‘xp_cmdshell’,’xp_log70.dll’
C.	sp_addextendedproc ‘xp_cmdshell’,’log70.dll’
D.	None of the mentioned
Answer» B. sp_addproc ‘xp_cmdshell’,’xp_log70.dll’

Discussion

262.	Which of the following is a disadvantage of dynamic SQL ?
A.	Stored procedure can not cache the execution plan for this dynamic query
B.	Stored procedure can cache the execution plan for this dynamic query
C.	Flexibility in your code that you can not get with standard SQL
D.	All of the mentioned
Answer» B. Stored procedure can cache the execution plan for this dynamic query

Discussion

263.	Which of the stored procedure used for dynamic SQL is prone to attacks ?
A.	xp_executesql
B.	executesql
C.	sp_execute
D.	sp_executesql
Answer» E.

Discussion

264.	The basic syntax for using EXECUTE command:
A.	SP_EXECUTE(@SQLStatement)
B.	EXEC_SQL(@SQLStatement)
C.	EXECUTE(@SQLStatement)
D.	All of the mentioned
Answer» D. All of the mentioned

Discussion

265.	The Dynamic SQL Queries in a variable are __________ until they are executed.
A.	Compiled
B.	Parsed
C.	Checked for errors
D.	All of the mentioned
Answer» E.

Discussion

266.	___________ triggers do not create the special inserted and deleted tables.
A.	DML
B.	CLR
C.	DDL
D.	All of the mentioned
Answer» D. All of the mentioned

Discussion

267.	Scope of DDL triggers in SQL Server can be :
A.	Server
B.	Client
C.	Views
D.	None of the mentioned
Answer» B. Client

Discussion

268.	___________ is special type of trigger based on CLR environment.
A.	DML
B.	DDL
C.	LOGON
D.	CLR
Answer» E.

Discussion

269.	Triggers can be enabled or disabled with the ________ statement.
A.	ALTER TABLE statement
B.	DROP TABLE statement
C.	DELETE TABLE statement
D.	None of the mentioned
Answer» B. DROP TABLE statement

Discussion

270.	Triggers created with FOR or AFTER keywords is :
A.	AFTER
B.	INSTEAD OF
C.	CLR
D.	All of the mentioned
Answer» B. INSTEAD OF

Discussion

271.	Which of the following is not a typical trigger action ?
A.	Insert
B.	Select
C.	Delete
D.	All of the mentioned
Answer» C. Delete

Discussion

272.	How many types of DML triggers are present in SQL Server ?
A.	1
B.	3
C.	5
D.	None of of the mentioned
Answer» E.

Discussion

273.	The ability to create a user-defined function (UDF) is a new feature added to :
A.	SQL Server 6.5
B.	SQL Server 7.0
C.	SQL Server 2000
D.	None of the mentioned
Answer» D. None of the mentioned

Discussion

274.	Which of the user defined function will be preferred for adding two numbers ?
A.	Scalar
B.	Inline table valued
C.	Max()
D.	Min()
Answer» B. Inline table valued

Discussion

275.	UDFs in SQL Server is composed of _______ parts .
A.	1
B.	2
C.	3
D.	4
Answer» C. 3

Discussion

276.	Which of the following is not a limitation of user defined function ?
A.	User-defined functions cannot call a stored procedure, but can call an extended stored procedure
B.	User-defined functions cannot make use of dynamic SQL or temp tables. Table variables are allowed
C.	SET statements are allowed in a user-defined function
D.	The FOR XML clause is not allowed
Answer» D. The FOR XML clause is not allowed

Discussion

277.	User defined scalar function can return only __________ data type values.
A.	Numerical
B.	String
C.	Float
D.	All of the mentioned
Answer» E.

Discussion

278.	Which of the following does not return a table variable?
A.	System-defined function
B.	Scalar Function
C.	Inline Table-Valued Function
D.	Multi-Statement Table-Valued Function
Answer» C. Inline Table-Valued Function

Discussion

279.	Nesting limit OF stored procedure is up to ________level.
A.	30
B.	32
C.	34
D.	36
Answer» C. 34

Discussion

280.	Type of procedure that are based on the CLR (Common Language Runtime) is :
A.	User Defined Stored Procedure
B.	Extended Procedure
C.	CLR Stored Procedure
D.	System defined Procedure
Answer» D. System defined Procedure

Discussion

281.	Extended procedures starts with the __________ prefix.
A.	sp_
B.	xp_
C.	clr_
D.	all of the mentioned
Answer» B. xp_

Discussion

282.	Stored procedure used to remember the exact number of bytes is :
A.	sp_datatype
B.	sp_datatype_info
C.	sp_info
D.	None of the mentioned
Answer» C. sp_info

Discussion

283.	__________ sets procedure options in SQL Server.
A.	sp_procoption
B.	sp_dropmessage
C.	sp_altermessage
D.	sp_executesql
Answer» B. sp_dropmessage

Discussion

284.	_______ is used to build the code dynamically and execute it.
A.	sp_addmessage
B.	sp_dropmessage
C.	sp_altermessage
D.	sp_executesql
Answer» E.

Discussion

285.	Which of the following stored procedure is used for error messages ?
A.	sp_addmessage
B.	sp_dropmessage
C.	sp_altermessage
D.	All of the mentioned
Answer» B. sp_dropmessage

Discussion

286.	_______create the removable database.
A.	sp_certify_removable
B.	sp_certify
C.	sp_removable
D.	All of the mentioned
Answer» B. sp_certify

Discussion

287.	Which of the following has support for transaction ?
A.	sp_monitor
B.	sp_bindsession
C.	sp_status
D.	All of the mentioned
Answer» C. sp_status

Discussion

288.	________ takes no parameters and returns all kinds of interesting information.
A.	sp_monitor
B.	sp_on
C.	sp_status
D.	None of the mentioned
Answer» B. sp_on

Discussion

289.	Reasons for consideration of stored procedure can be:
A.	With which tables will you have to work? Does it make sense to create a VIEW first?
B.	How often will this procedure actually be used?
C.	Do you want to view data in the database (SELECT), insert new records (INSERT INTO), or do I want to change an existing record (UPDATE)?
D.	All of the mentioned
Answer» E.

Discussion

290.	Reasons for consideration of stored procedure can be :
A.	With which tables will you have to work? Does it make sense to create a VIEW first?
B.	How often will this procedure actually be used?
C.	Do you want to view data in the database (SELECT), insert new records (INSERT INTO), or do I want to change an existing record (UPDATE)?
D.	All of the mentioned
Answer» E.

Discussion

291.	Nesting level of a stored procedures execution is stored in the _________ function.
A.	@@NEST
B.	@@NESTLEVEL
C.	@@LEVEL
D.	None of the mentioned
Answer» C. @@LEVEL

Discussion

292.	Nesting level of stored procedure is applicable for :
A.	Triggers
B.	Views
C.	Functions
D.	All of the mentioned
Answer» E.

Discussion

293.	___________ procedure cannot be created in Resource database.
A.	System
B.	User defined
C.	Extended
D.	All of the mentioned
Answer» C. Extended

Discussion

294.	Which of the following is extended procedure ?
A.	xp_change
B.	sp_owner
C.	sp_change
D.	xp_logininfo
Answer» B. sp_owner

Discussion

295.	Which of the following is global variable for error handling ?
A.	@@ERRORS
B.	@@ERROR
C.	@@ERR
D.	None of the mentioned
Answer» C. @@ERR

Discussion

296.	ERROR_SEVERITY() returns the ________level of the error.
A.	State number
B.	Full text
C.	Severity
D.	None of the mentioned
Answer» D. None of the mentioned

Discussion

297.	Purpose of TRY…CATCH block in SQL Server is :
A.	Error handling
B.	Stored Procedure handling
C.	Message handling
D.	None of the mentioned
Answer» B. Stored Procedure handling

Discussion

298.	SQL Server static cursors are always ____
A.	Read-only
B.	Write-only
C.	Read,Write
D.	None of the mentioned
Answer» B. Write-only

Discussion

299.	__________ cursor is sensitive to any changes to the data source.
A.	Static Cursors
B.	Dynamic Cursors
C.	Keyset Driven Cursors
D.	None of the mentioned
Answer» D. None of the mentioned

Discussion

300.	Which of the following blocks are used for error handling in SQL Server ?
A.	TRY…CATCH
B.	TRY…FINAL
C.	TRY…END
D.	CATCH…TRY
Answer» B. TRY…FINAL

Discussion

Explore topic-wise MCQs in Structured Query Language (SQL).

The main features of dotConnect for SQL Server includes :

Syntax for closing and opening the connection in ADO.net is :

________object is used to fill a DataSet/DataTable with query results in ADO.net.

The easiest way to start is to create a linked server ‘localhost’ by using :

Which of the following SQL syntax elements is dictated by the SQL dialect levels ?

Which is default field terminator for bulk insert in SQL Server ?

_______ parameter specifies the number of rows in a batch in bulk insert script.

_________ does not allow bulk export operation in SQL Server.

_____________ is time based SQL injection attack.

Which of the following script is an example of Quick detection in SQL injection attack?

If xp_cmdshell has been disabled with sp_dropextendedproc, we can simply inject the following code:

Which of the following is a disadvantage of dynamic SQL ?

Which of the stored procedure used for dynamic SQL is prone to attacks ?

The basic syntax for using EXECUTE command:

The Dynamic SQL Queries in a variable are __________ until they are executed.

___________ triggers do not create the special inserted and deleted tables.

Scope of DDL triggers in SQL Server can be :

___________ is special type of trigger based on CLR environment.

Triggers can be enabled or disabled with the ________ statement.

Triggers created with FOR or AFTER keywords is :

Which of the following is not a typical trigger action ?

How many types of DML triggers are present in SQL Server ?

The ability to create a user-defined function (UDF) is a new feature added to :

Which of the user defined function will be preferred for adding two numbers ?

UDFs in SQL Server is composed of _______ parts .

Which of the following is not a limitation of user defined function ?

User defined scalar function can return only __________ data type values.

Which of the following does not return a table variable?

Nesting limit OF stored procedure is up to ________level.

Type of procedure that are based on the CLR (Common Language Runtime) is :

Extended procedures starts with the __________ prefix.

Stored procedure used to remember the exact number of bytes is :

__________ sets procedure options in SQL Server.

_______ is used to build the code dynamically and execute it.

Which of the following stored procedure is used for error messages ?

_______create the removable database.

Which of the following has support for transaction ?

________ takes no parameters and returns all kinds of interesting information.

Reasons for consideration of stored procedure can be:

Reasons for consideration of stored procedure can be :

Nesting level of a stored procedures execution is stored in the _________ function.

Nesting level of stored procedure is applicable for :

___________ procedure cannot be created in Resource database.

Which of the following is extended procedure ?

Which of the following is global variable for error handling ?

ERROR_SEVERITY() returns the ________level of the error.

Purpose of TRY…CATCH block in SQL Server is :

SQL Server static cursors are always ____

__________ cursor is sensitive to any changes to the data source.

Which of the following blocks are used for error handling in SQL Server ?