The BEGIN and END statements are used when:

A WHILE loop needs to include a block of statements

An element of a CASE expression needs to include a block of statements

An IF or ELSE clause needs to include a block of statements

Which of the following components constitutes use CASE expression ?

Where clause defining an alias for the CASE expression

The primary key name to transform

Where clause defining an alias for the CASE expression

Which of the following scenario makes use of BEGIN..END keyword redundant ?

when an IF statement controls the execution of one or more Transact-SQL statement

when an IF statement controls the execution of multiple Transact-SQL statement

when an IF statement controls the execution of only one Transact-SQL statement

when an IF statement controls the execution of one or more Transact-SQL statement

Purpose of CLOSE MASTER KEY is :

Closes the private key of the current database

Deletes the master key of the current database

Closes the private key of the current database

Closes the public key of the current database

Purpose of ALTER CERTIFICATE is :

Changes the public key of a certificate

Adds a private key to a certificate

Changes the public key of a certificate

Removes the certificate from the database

Point out the wrong statement with respect to ALTER INDEX statement :

Adds new format for specifying index and table names

Why do we use exist method in Xquery ?

To examine the XML and return back a scalar value

To determine if the XML data contains a certain node

To examine the XML and return back a scalar value

To Shred the XML nodes of the XML data into relational columns

To search inside xml data types

Following content of XML elements are indexed in full text :

Non numerical value in content of XML attributes

SQL Server 2008 has made several extensions to the XML Schema support which includes :

Added support for list and union types

Use untyped XML data type in the following situations:

You have schema for your XML data and you want the server to validate your XML data according to the XML schema

You want to take advantage of storage and query optimizations based on type information

You want to take better advantage of type information during compilation of your queries

You do not have a schema for your XML data

Which of the code deletes node H using hierarchical data type?

DROP FROM H WHERE Name = ‘Steve’

DELETE FROM H WHERE Name = ‘Steve’

DROP FROM H WHERE Name = ‘Steve’

DELETE H WHERE Name = ‘Steve’

Select * from employee where salary>10000 and dept_id=101; Which of the following fields are displayed as output?

All the field of employee relation

Select * from student join takes using (ID); The above query is equivalent to :

Select * from student outer join takes using (ID);

Select * from student inner join takes using (ID);

Select * from student outer join takes using (ID);

Select * from student left outer join takes using (ID);

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

301.	LANGUAGE variable whose return type nvarchar is specified in :
A.	sys.name
B.	syslang.name
C.	syslanguages.name
D.	syslanguages.id
Answer» D. syslanguages.id

Discussion

302.	_________ gives amount of time, in ticks, that SQL Server has been idle since it was last started .
A.	@@ERROR
B.	@@IDLE
C.	@@LANGID
D.	@@LANGUAGE
Answer» C. @@LANGID

Discussion

303.	_________ gives amount of time, in ticks, that SQL Server has been idle since it was last started.
A.	@@ERROR
B.	@@IDLE
C.	@@LANGID
D.	@@LANGUAGE
Answer» C. @@LANGID

Discussion

304.	Which of the following global has return type ‘numeric’ ?
A.	@@ERROR
B.	@@MAX_CONNECTIONS
C.	@@CPU_BUSY
D.	@@IDENTITY
Answer» E.

Discussion

305.	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 mentioned
Answer» E.

Discussion

306.	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 expression
Answer» D. Where clause defining an alias for the CASE expression

Discussion

307.	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 mentioned
Answer» C. when an IF statement controls the execution of one or more Transact-SQL statement

Discussion

308.	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 mentioned
Answer» B. BREAK and IF

Discussion

309.	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 mentioned
Answer» B. DECRYPT SYMMETRIC KEY

Discussion

310.	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 mentioned
Answer» B. Closes the private key of the current database

Discussion

311.	Which of the following statement drops the synonym ?
A.	DELETE SYNONYM
B.	DROP SYNONYM
C.	REMOVE SYNONYM
D.	None of the mentioned
Answer» E.

Discussion

312.	Purpose of ALTER CERTIFICATE is :
A.	Adds a private key to a certificate
B.	Changes the public key of a certificate
C.	Removes the certificate from the database
D.	None of the mentioned
Answer» B. Changes the public key of a certificate

Discussion

313.	DDL statements not used for Synonym is :
A.	CREATE SYNONYM
B.	DROP SYNONYM
C.	ALTER SYNONYM
D.	None of the mentioned
Answer» D. None of the mentioned

Discussion

314.	Creation of new database role in the current database is made by :
A.	CREATE APPLICATION ROLE
B.	CREATE ROLE
C.	CREATE USER
D.	CREATE LOGIN
Answer» C. CREATE USER

Discussion

315.	Command used to modify the schema is :
A.	ALTER SCHEMA
B.	ALTER STRUCTURE
C.	ALTER VIEW
D.	DBCC SHRINKDB
Answer» B. ALTER STRUCTURE

Discussion

316.	Which of the following creates a new Microsoft Windows or SQL Server login account ?
A.	CREATE LOGIN
B.	DROP LOGIN
C.	UPDATE LOGIN
D.	REMOVE LOGIN
Answer» B. DROP LOGIN

Discussion

317.	Statement used to remove a user from the current database is :
A.	DROP ROLE
B.	DROP USER
C.	DROP LOGIN
D.	None of the mentioned
Answer» C. DROP LOGIN

Discussion

318.	Which is a new DDL statement ?
A.	DROP ROLES
B.	CREATE APPLICATION ROLE
C.	CREATE USERS
D.	None of the mentioned
Answer» C. CREATE USERS

Discussion

319.	Point out the wrong statement with respect to ALTER INDEX statement :
A.	Adds new format for specifying index and table names
B.	Adds the index option:MAXDOP
C.	Adds the index option:ONLINE
D.	None of the mentioned
Answer» E.

Discussion

320.	Which of the following index options are added by CREATE INDEX statement ?
A.	XML INDEX
B.	INCLUDED (column)
C.	PARTITION SCHEME
D.	ONLINE
Answer» E.

Discussion

321.	Which of the following adds MOVE TO clause ?
A.	DROP INDEX
B.	ALTER INDEX
C.	UPDATE INDEX
D.	CREATE INDEX
Answer» B. ALTER INDEX

Discussion

322.	Which of the following statement modifies view index ?
A.	ALTER INDEX
B.	ALTER VIEW INDEX
C.	ALTER INDEX_VIEW
D.	None of the mentioned
Answer» B. ALTER VIEW INDEX

Discussion

323.	View used used for displaying current status of database is :
A.	system.databases
B.	current.databases
C.	sys.databases
D.	status.databases
Answer» D. status.databases

Discussion

324.	Syntax for dropping a database snapshot named “Sushant” would be :
A.	DROP DATABASE Sushant
B.	DROP DATABASE SNAP Sushant
C.	DROP SNAPSHOT Sushant
D.	None of the mentioned
Answer» B. DROP DATABASE SNAP Sushant

Discussion

325.	CONTAINS and CONTAINSTABLE look for an _______ match for the phrase.
A.	exact
B.	similar
C.	different
D.	none of the mentioned
Answer» B. similar

Discussion

326.	The CONTAINS and FREETEXT predicates return a _________ value.
A.	TRUE or FALSE
B.	Scalar
C.	One
D.	All of the mentioned
Answer» B. Scalar

Discussion

327.	FREETEXTTABLE is a full text _________
A.	Predicate
B.	Function
C.	Procedure
D.	All of the mentioned
Answer» C. Procedure

Discussion

328.	Full text indexing is a great feature that solves a database problem, the searching of _________ columns for specific words and phrases in SQL Server databases.
A.	Textual data
B.	Character data
C.	String
D.	All of the mentioned
Answer» E.

Discussion

329.	Why do we use exist method in Xquery ?
A.	To determine if the XML data contains a certain node
B.	To examine the XML and return back a scalar value
C.	To Shred the XML nodes of the XML data into relational columns
D.	To search inside xml data types
Answer» B. To examine the XML and return back a scalar value

Discussion

330.	With XML DML,which of the operations can be performed on nodes ?
A.	Insert nodes
B.	Change nodes
C.	Delete nodes
D.	None of the mentioned
Answer» D. None of the mentioned

Discussion

331.	Which of the following function is contained by both full text search and Xquery ?
A.	search()
B.	contain()
C.	contains()
D.	query()
Answer» D. query()

Discussion

332.	Following content of XML elements are indexed in full text :
A.	Content of the XML value
B.	Non numerical value in content of XML attributes
C.	The content of XML elements.
D.	None of the mentioned
Answer» C. The content of XML elements.

Discussion

333.	Which of the following is not a xml data type method ?
A.	query()
B.	value()
C.	nodes()
D.	None of the Mentioned
Answer» E.

Discussion

334.	SQL Server 2008 has made several extensions to the XML Schema support which includes :
A.	Support for lax validation
B.	Added support for list and union types
C.	Improved handling of xs:time
D.	All of the mentioned
Answer» E.

Discussion

335.	Use untyped XML data type in the following situations:
A.	You have schema for your XML data and you want the server to validate your XML data according to the XML schema
B.	You want to take advantage of storage and query optimizations based on type information
C.	You want to take better advantage of type information during compilation of your queries
D.	You do not have a schema for your XML data
Answer» E.

Discussion

336.	What are Catalog Views for XML ?
A.	Schema collections
B.	View Collections
C.	Unstructured data
D.	None of the Mentioned
Answer» B. View Collections

Discussion

337.	Which of the following feature of SQL Server was used before XML technology for semi structured data?
A.	Stored Procedure
B.	Dynamic management views
C.	In memory database
D.	None of the mentioned
Answer» B. Dynamic management views

Discussion

338.	XML datatype was introduced in which of the following versions of SQL Server ?
A.	SQL Server 2000
B.	SQL Server 2005
C.	SQL Server 2008
D.	SQL Server 2012
Answer» C. SQL Server 2008

Discussion

339.	Which of the code deletes node H using hierarchical data type?
A.	DELETE FROM H WHERE Name = ‘Steve’
B.	DROP FROM H WHERE Name = ‘Steve’
C.	DELETE H WHERE Name = ‘Steve’
D.	All of the mentioned
Answer» B. DROP FROM H WHERE Name = ‘Steve’

Discussion

340.	Which of the constraint can be enforced one per table ?
A.	Primary key constraint
B.	Not Null constraint
C.	Foreign Key constraint
D.	Check constraint
Answer» B. Not Null constraint

Discussion

341.	How many types of constraints are present in SQL Server ?
A.	4
B.	5
C.	6
D.	7
Answer» D. 7

Discussion

342.	Which of the following is not a class of constraint in SQL Server ?
A.	NOT NULL
B.	CHECK
C.
D.	UNIQUE
Answer» D. UNIQUE

Discussion

343.	Which is duplication of computer operations and routine backups to combat any unforeseen problems:
A.	Concurrency
B.	Deadlock
C.	Backup
D.	Recovery
Answer» E.

Discussion

344.	To alter a database ___________ command is used
A.	ALTER database database_name
B.	ALTER database_name
C.	ALTER database database_name
D.	ALTER database_name
Answer» D. ALTER database_name

Discussion

345.	______________ joins are SQL server default
A.	Outer
B.	Inner
C.	Equi
D.	None of the Mentioned
Answer» C. Equi

Discussion

346.	Insert into employee _________ (1002,Joey,2000); In the given query which of the keyword has to be inserted ?
A.	Table
B.	Values
C.	Relation
D.	Field
Answer» C. Relation

Discussion

347.	Select * from employee where salary>10000 and dept_id=101; Which of the following fields are displayed as output?
A.	Salary, dept_id
B.	Employee
C.	Salary
D.	All the field of employee relation
Answer» E.

Discussion

348.	Select * from student join takes using (ID); The above query is equivalent to :
A.	Select * from student inner join takes using (ID);
B.	Select * from student outer join takes using (ID);
C.	Select * from student left outer join takes using (ID);
D.	All of the mentioned
Answer» B. Select * from student outer join takes using (ID);

Discussion

349.	Select ID, name, dept name, salary * 1.1 where instructor; The query given below will not give an error. Which one of the following has to be replaced to get the desired output ?
A.	Salary*1.1
B.	ID
C.	Where
D.	Instructor
Answer» D. Instructor

Discussion

350.	Select ________ dept_name from instructor; Here which of the following displays the unique values of the column ?
A.	All
B.	From
C.	Distinct
D.	Name
Answer» D. Name

Discussion

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

LANGUAGE variable whose return type nvarchar is specified in :

_________ gives amount of time, in ticks, that SQL Server has been idle since it was last started .

_________ gives amount of time, in ticks, that SQL Server has been idle since it was last started.

Which of the following global has return type ‘numeric’ ?

The BEGIN and END statements are used when:

Which of the following components constitutes use CASE expression ?

Which of the following scenario makes use of BEGIN..END keyword redundant ?

Which of the following two Transact-SQL statements are commonly used with WHILE ?

Which of the following statement decrypts a symmetric key ?

Purpose of CLOSE MASTER KEY is :

Which of the following statement drops the synonym ?

Purpose of ALTER CERTIFICATE is :

DDL statements not used for Synonym is :

Creation of new database role in the current database is made by :

Command used to modify the schema is :

Which of the following creates a new Microsoft Windows or SQL Server login account ?

Statement used to remove a user from the current database is :

Which is a new DDL statement ?

Point out the wrong statement with respect to ALTER INDEX statement :

Which of the following index options are added by CREATE INDEX statement ?

Which of the following adds MOVE TO clause ?

Which of the following statement modifies view index ?

View used used for displaying current status of database is :

Syntax for dropping a database snapshot named “Sushant” would be :

CONTAINS and CONTAINSTABLE look for an _______ match for the phrase.

The CONTAINS and FREETEXT predicates return a _________ value.

FREETEXTTABLE is a full text _________

Full text indexing is a great feature that solves a database problem, the searching of _________ columns for specific words and phrases in SQL Server databases.

Why do we use exist method in Xquery ?

With XML DML,which of the operations can be performed on nodes ?

Which of the following function is contained by both full text search and Xquery ?

Following content of XML elements are indexed in full text :

Which of the following is not a xml data type method ?

SQL Server 2008 has made several extensions to the XML Schema support which includes :

Use untyped XML data type in the following situations:

What are Catalog Views for XML ?

Which of the following feature of SQL Server was used before XML technology for semi structured data?

XML datatype was introduced in which of the following versions of SQL Server ?

Which of the code deletes node H using hierarchical data type?

Which of the constraint can be enforced one per table ?

How many types of constraints are present in SQL Server ?

Which of the following is not a class of constraint in SQL Server ?

Which is duplication of computer operations and routine backups to combat any unforeseen problems:

To alter a database ___________ command is used

______________ joins are SQL server default

Insert into employee _________ (1002,Joey,2000); In the given query which of the keyword has to be inserted ?

Select * from employee where salary>10000 and dept_id=101; Which of the following fields are displayed as output?

Select * from student join takes using (ID); The above query is equivalent to :

Select ID, name, dept name, salary * 1.1 where instructor; The query given below will not give an error. Which one of the following has to be replaced to get the desired output ?

Select ________ dept_name from instructor; Here which of the following displays the unique values of the column ?