MCQOPTIONS
Saved Bookmarks
MCQOPTIONS
This section includes 424 Mcqs, each offering curated multiple-choice questions to sharpen your Structured Query Language (SQL) knowledge and support exam preparation. Choose a topic below to get started.
| 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 | |
| 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 | |
| 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 | |
| 304. |
Which of the following global has return type ‘numeric’ ? |
| A. | @@ERROR |
| B. | @@MAX_CONNECTIONS |
| C. | @@CPU_BUSY |
| D. | @@IDENTITY |
| Answer» E. | |
| 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. | |
| 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 | |
| 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 | |
| 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 | |
| 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 | |
| 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 | |
| 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. | |
| 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 | |
| 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 | |
| 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 | |
| 315. |
Command used to modify the schema is : |
| A. | ALTER SCHEMA |
| B. | ALTER STRUCTURE |
| C. | ALTER VIEW |
| D. | DBCC SHRINKDB |
| Answer» B. ALTER STRUCTURE | |
| 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 | |
| 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 | |
| 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 | |
| 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. | |
| 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. | |
| 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 | |
| 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 | |
| 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 | |
| 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 | |
| 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 | |
| 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 | |
| 327. |
FREETEXTTABLE is a full text _________ |
| A. | Predicate |
| B. | Function |
| C. | Procedure |
| D. | All of the mentioned |
| Answer» C. Procedure | |
| 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. | |
| 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 | |
| 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 | |
| 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() | |
| 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. | |
| 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. | |
| 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. | |
| 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. | |
| 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 | |
| 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 | |
| 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 | |
| 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’ | |
| 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 | |
| 341. |
How many types of constraints are present in SQL Server ? |
| A. | 4 |
| B. | 5 |
| C. | 6 |
| D. | 7 |
| Answer» D. 7 | |
| 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 | |
| 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. | |
| 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 | |
| 345. |
______________ joins are SQL server default |
| A. | Outer |
| B. | Inner |
| C. | Equi |
| D. | None of the Mentioned |
| Answer» C. Equi | |
| 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 | |
| 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. | |
| 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); | |
| 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 | |
| 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 | |