Microsoft SQL Server MCQ Questions Answers

1. What will be the output of the following SQL query? SET SHOWPLAN_TEXT ON GO SELECT * FROM abcsite.abctable GO

2. Which of the following 'insert queries' will be inserted in the XML field? The creation query of the table is: CREATE TABLE Branches(recordID int, description XML)

3. Consider the following tables: Customers - - - - - - - - Customerid Customername Address Orders - - - - - Orderid Customerid Orderdate Comments What will be the query to fetch Customername and 1st Orderdate for order placed by each customer?

4. Which of the following commands will be used to see the structure of the table named 'salary'?

5. We are creating Stored Procedure in Database and using 'sp_' prefix in its name. Which of the following statements is correct in accordance with the performance of the Stored Procedure execution?

6. The commands GRANT and REVOKE are examples of :

7. What exactly is the Full-Text Catalog?

8. Consider the following table 'Students': Students - - - - - - Name Hobbies John Driving Miller Fishing David Writing Robert Singing What will be the output of the following query? Select Substring(Hobbies,3) From students Where name= 'john"

9. Why are Joins generally better than the Correlated sub-query?

10. Starting from the slowest, arrange the following operators in the order in which they produce results in 'SQL WHERE' query. 1. >, >=, <, <= 2. <> 3. = 4. LIKE

11. Which one of the following statements is correct?

12. State whether True or False. Triggers can be created in Assemblies.

13. What exactly is a Super key?

14. __________ is a container that holds tables, views, procedures, functions and so on.

15. Which one of the following statements about Indexes is wrong?

16. Which of the following mechanisms is used by 'ENCRYPTION by passphrase' to encrypt the data in the SQL SERVER 2005?

17. Which command is used to create User defined Data Type in the SQL SERVER 2005?

18. Which of the following is not a Data type in the SQL SERVER 2005?

19. Which is the best method to handle errors and deadlocks?

20. Columns are called _____________ of a table in a relational model.

21. What is the maximum storage capacity of XML data type in SQL SERVER 2005?

22. Consider the following table: Order - - - - - Orderid Customerid Orderdate Comments Select ISNULL (Comments, 'No comments') AS comments From Order What will be the output of the above query?

23. The table Comments has an auto incremented column called 'Commentid' and a 'UserComments' column, both of which store the comments of the users visiting the site. What will be the correct query to display the latest comments first?

24. Which of the following statements is wrong?

25. We have a table named 'Customer' which has a column named 'Customerid' having datatype Integer. We need to fetch the output with column title: 'Customer id is (value of customerid from the table)'. What will be the query?

26. Which of the following are correct?

27. You have a table 'engineers' with the following table structure: enggid int(4) deptname varchar(50) salary int(4) You want to display the minimum and maximum salaries of the individual departments. Which of the following queries will fetch the desired results?

28. The - (Negative) and ~(Bitwise NOT) are examples of the _________ type of operators.

29. What will the following query do? Delete Orders

30. Which one of the following statements regarding 'views' is incorrect?

31. A table of employees has the following values for its salary field: 10000, 11000, 12000, 10000, 14000, 12000, 13000, 10000, 14000, 10000 What will the following query return? Select distinct (salary) from employees

32. Which of the following statements regarding Trigger and Stored Procedures is correct?

33. What exactly is a Foreign key?

34. State whether True or False. The 'Having' clause can not be used when there is a 'Where' clause in the statement already.

35. State whether True or False. You can send emails through the Database engine.

36. State whether True or False. Many-to-One is a type of relationship in RDBMS.

37. Consider the following table structure of employees: empid int(4) deptname varchar(50) salary int(4) Which query will be used to display the Department (deptname) that is giving the maximum salary?

38. State whether True or False. While using the Select query, the IN clause is a better choice than the EXIST clause.

39. Which edition of the SQL SERVER 2005 will best meet the following scenario? A company has an online shopping website named www.shoppingxyz.com. It also has to handle its line of business portion. Its main server supports 4 CPUs and a multi-core processor.

40. You have a table named 'employees' with the following table structure: empid int(4) deptname varchar(50) salary int(4) The following select query is executed on this table: SELECT * FROM employees ORDER BY deptname DESC, salary DESC What will be the output of the above query?

41. Which functions does the following SQL QUERY perform? DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)

42. Which of the following are not Database Models?

43. Examine the data in the EMPLOYEES table given below: LAST_NAME DEPARTMENT_ID SALARY Allen 10 3000 Miller 20 1500 King 20 2200 Davis 30 5000 Which of the following sub-queries work?

44. You have a table named 'employees', having the following structure. empid int(4) deptname varchar(50) salary int(4) And a view is created as follows: create view viemployees as select * from employees You want to insert a new row into the table having the following values: empid=1010, deptname=HR, salary=10000. Which of the following are the correct insert SQL queries?

45. Which of the following statements regarding 'views' are incorrect?

46. Which of the following operators perform pattern matching?

47. Which of the following are the SQL Transaction statements?

48. SQL SERVER 2005 provides a new feature to enable partitioning of the data in the database. But only those database objects can be partitioned which store data in the database. In accordance with the above statement, which of the following database objects can be partitioned?

49. You have a table 'engineers' with the following table structure: enggid int(4) name varchar(50) salary int(4) You want to select the top 2 engineers in the decreasing order of their salaries, starting with the maximum salary. Which of the following SQL queries will fetch this data?

50. What is the function of 'DBCC CHECKDB' command?

51. Which of the following clauses are not allowed in a single row sub-query?

52. Which of the following statements is correct?

53. State whether True or False. A User Defined Function 'findsum' is to be created as follows: Create function findsum(@x int, @y int) As Begin Return(@x+@y) End But the SQL SERVER is unable to create the desired function and gives an error. The error can be removed by specifying the return type of the function

54. Which of the following is a database object?

55. Which of the following statements is/are wrong?

56. Which of the following methods is used to backup the Transaction log on to a secondary database in order to backup and recover data in a synchronized manner?

57. Which data type in the SQL SERVER 2005 converts itself into the type of data that is inserted into it?

58. How is the following query incorrect? select * from Orders where OrderID = (select OrderID from OrderItems where ItemQty > 50)

59. Which query will be used for a particular column of a table, if 'Reading' is to be replaced by 'Writing'?

60. You want to select a row using cursors in the table 'qlist'. Which of the following statements DECLARES a cursor 'qselect' to perform the desired operation?

61. Consider the following query: Select name from employees where name like '%_n_' Which names will be displayed?

62. Which of the following is not an Aggregate function?

63. Which level of data abstraction describes the data that is stored in the database and their corresponding relationships?

64. Which of the following SQL queries correctly selects the rows from the table 'employees' that have NULL value in the 'salary' column?

65. Which of the following is the new sample database in the SQL SERVER 2005?

66. State whether True or False. The plus (+) and minus (-) operators cannot be used to perform increment/decrement operations on datetime and smalldatetime values.

67. You have two tables emp_1 and emp_2, having the following data: Table emp_1 Name Date Robert 1/5/2008 John 1/6/2008 Michael 1/7/2008 Rachael 1/10/2008 Joey 1/10/2008 Table emp_2 Name Date Lewis 1/05/2008 David 1/07/2008 George 1/11/2008 Jerry 1/12/2008 Monica 1/12/2008 The following query is run in the Query Analyzer: SELECT Date FROM emp_1 EXCEPT SELECT Date FROM emp_2 What will be the result of the query?

68. Which among the following are the new datatypes introduced in the SQL Server 2005?

69. You have a database named 'marketnikkie', the backup of which is stored at 'D:\marketnikkie.bak' location on your server. Due to some electric fluctuations, the database gets corrupt. Which of the following is the correct query to restore your database?

70. Which clause will be used to extract data from Tables A and B having a similar structure but no relation between themselves?

71. Which of the following is not a type of Database backup in the SQL SERVER 2005?

72. State whether True or False. The 'text' and 'ntext' data type can store character based data. These data types can be used in joins

73. Which of the following operators will be evaluated first in the following statement? Select (salary+40^2*30/5) from employees

74. Suppose e1 and e2 denote two decimal expressions with precisions p1 and p2 and scales s1 and s2, respectively. What is the result precision of the following operation on the two expressions?

75. In which of the following statements can TOP clause be used in SQL Server 2008?

76. Which of the following commands is used to start the Resource Governor in SQL Server 2008?

77. Analyze the following code snippet for creating a stored procedure in SQL Server 2008: CREATE PROCEDURE Person.GetEmployees @LastName nvarchar(50), @FirstName nvarchar(50) AS SET NOCOUNT ON SELECT FirstName, LastName, JobTitle, Department FROM Person.EmployeeDepartment WHERE FirstName = @FirstName AND LastName = @LastName GO Which of the following is a valid code to execute the GetEmployees stored procedure?

78. Analyze the following permissions: 1.ALTER permission on the database. 2.CONTROL permission in the database. 3.ALTER ANY SCHEMA permission and CREATE XML SCHEMA COLLECTION permission in the database. Which of the above permissions is/are required to create an XML SCHEMA COLLECTION?

79. A local temporary stored procedure name can contain a maximum of __________ characters

80. FILESTREAM storage is best used when the BLOB file sizes average 1MB or higher.

81. Which of the following statements using GROUP BY clause are allowed in SQL Server 2008?

82. Which of the following is used to comment a portion of an SQL Server 2008 script?

83. Which of the following is the correct precedence order of the data types in SQL Server 2008? 1.xml 2.text 3.bigint 4.image 5.float 6.datetime

84. Which of the following queries is used to rename a database in SQL Server 2008?

85. The output of a Transact SQL query using the GROUPING function of SQL Server 2008 is of __________ return type.

86. What should a computed column be marked as so that SQL Server Database Engine can physically store values in a table?

87. Which of the following is a valid code to remove the key used to encrypt a database using Transparent Data Encryption algorithm?

88. Analyze the following data types and schemas: 1.The SQL Server system data type. 2.The default schema of the current user in the current database. 3.The dbo schema in the current database. In which order does the SQL Server Database Engine refer to type_name when a type_schema_name is NOT specified while creating a table in SQL Server 2008?

89. Which of the following code snippets causes only partition number 1 to be rebuilt while changing the compression of a partitioned table?

90. What happens to the audit records if a failure to write the Audit event does NOT trigger the SQL Server instance to shut down?

91. Which of the following namespaces is used to manage the audit configuration programmatically in SQL Server 2008?

92. Which of the following default passwords is used by sqlcmd if -P option is used at the end of the command prompt without a password?

93. The login timeout when you try to connect to a server must be a number between __________.

94. Which of the following permissions is required to drop a stored procedure in SQL Server 2008?

95. What is the default precision of the TIME data type in SQL Server 2008?

96. Which of the following commands is used to run a Transact-SQL script file by using sqlcmd?

97. Which of the following commands is used to connect to a named instance of SQL Server using the sqlcmd utility of SQL Server?

98. Which of the following is the correct order of steps to be followed while using the Transparent Data Encryption security feature of SQL Server 2008?

99. Which of the following values specifies that each instance of the XML data type in column_name can contain multiple top-level elements?

100. What is the maximum number of columns that can be combined into a single composite index key?