1. Which of the following aggregate functions are supported by the Transact-SQL language?
2. Microsoft SQL Server 2012 includes TRY_CAST() Function, what is the advantage of using this function
3. True or false: You can modify data in a table using a User Defined Function.
4. Which of the following is not considered a Boolean Operator?
5. Which of the following operators allows the specification of two or more expressions to be used for a query search?
6. In SQL Server 2012 introduces what new feature?
7. Which of the following storage options can be used to store LOBs and save storage?
8. A ________ is an alphanumerical, hexadecimal, or numberic constant.
9. True or false? Adding TOP to a select always improves performance.
10. Which Transact-SQL statement should you include at the beginning of a stored procedure to ensure that if the stored procedure raises a run-time error, the entire transaction is terminated and rolled back?
11. GETDATE() is a ....... function?
12. Given a table with the following schema: create table table1 (field1 int) The table contains 1000 records. What would the following sql statement result in: update top (100) table1 set field1 = 1
13. Which of the following SQL Server built-in functions is nondeterministic?
14. What is the correct syntax for a CTE?
15. What does DBCC stand for?
16. To Improve stored procedure performance,which statement below is used to write beginning of SP?
17. When you are dealing with lots of NULL Values in columns,which datatype is better?
18. True or False? Every row in a FileTable represents a file or a directory.
19. In which context is the ROW_NUMBER() function valid?
20. What is the default port of Microsoft SQL Server (for TCP)?
21. Which sql function finds the length of a string field?
22. What is a 'CROSS APPLY' used for?
23. When creating indexes in SQL, what two main factors must be considered?
24. What is the name of the service that provides ETL capabilities for SQL Server for data import, data integration and data warehousing needs?
25. Which of the following allows you to modify (update or delete) all values of the parent table?
26. What is the 'WITH RECOMPILE' statement used for?
27. Which of the following are not default database schemas?
28. The ________ data type can be used to store values of different data types at the same time, including numeric values, strings, and date values.
29. Which of the following SQL Server 2012 date functions return a value of date and/or time data type from the given parts?
30. Which transaction isolation level allows dirty reads?
31. What does COALESCE do?
32. You can create index on views.
33. Consider the following queries: A: select * from [tbl1] union select * from [tbl2]; B: select * from [tbl1] union all select * from [tbl2]; What is the difference between these two queries?
34. Which of the following statements will remove a UDF?
35. What is the default isolation level in SQL Server?
36. What is the basic difference between varchar and nvarchar?
37. Which of the following is a benefit of SQL Server replication?
38. a FOREIGN KEY constraint can reference a UNIQUE constraint
39. Data Transformation Services do what?
40. In this statement, 'SELECT * FROM employees e JOIN timesheets t ON t.employeeid = e.employeeid ', the JOIN term is the same as:
41. Differential backups do what?
42. What is the role of a processadmin user?
43. What does the Query Analyzer, or SQL Server Management Studio, tool do?
44. In regard to SQL Server Mirroring - A server instance can be a partner in some sessions and a witness in other sessions.
45. What does the 'IN' clause do?
46. What is the default port number for SQL Server?
47. Which of the following acts as the Database Engine's security system?
48. Which is the slowest?
49. What is a cursor in SQL Server?
50. A sysadmin user is allowed to perform what actions?
51. What is CTE
52. Which clause would you use to perform conditional filtering on aggregate data in a query that uses 'GROUP BY'?
53. Which of the following statements are related to transactions?
54. 'Primary Key' can only be created on a column that doesn't accept NULL
55. What does 'UDF' stand for?
56. Which statement adds a row to a table?
57. Which of the following indices does NOT change the physical order of the rows in the table?
58. True or False? The EXEC keyword and the EXEC() function are equivalent to each other.
59. 'SELECT UNIQUE city FROM customers' will return what data set?
60. Which of the following is NOT an aggregate function in SQL Server?
61. 'SELECT name FROM t1 UNION SELECT name FROM t2 ' is the same as 'SELECT DISTINCT name FROM t1 UNION ALL SELECT DISTINCT name FROM t2'
62. A relationship that identifies that a column contains primary key values from another table is referred as what?
63. All Database Backup/Restore Related information stored in which system database?
64. 'Unique Constraint' can be created on a column that accepts NULL
65. True or False? You cannot execute dynamic SQL code from a function.
66. Which statement is allowed inside of a User Defined Function?
67. Which of the following statements removes one or more previously granted or denied permissions?
68. Which of the following is used to change tracking?
69. TRUE OR FALSE: Is it possible to execute a Stored Procedure in a UDF.
70. Which of the following best describes the flow for the execution of a CLR stored procedure?
71. Which versions of SQL Server support table partitioning?
72. Which of the following relationships requires a reference table?
73. True or false? Intersection tables should have an autoincrement column as the primary index
74. You are querying a table that contains a field with the data type XML and need only rows that have a particular value in the XML data. Which of the following would not result in an error
75. Which of the following stores FileStream data?
76. The LAG analytic function is used in a ______ statement to compare values in the current row with values in a previous row.
77. What is the name of the database that gets created in SQL Server 2012 when you create an 'Integration Services Catalog'?
78. Which of the following standard reports will show you the fragmentation of the physical indexes?
79. Can a unique key with not null constraint make it a primary key?
80. Which of the following is not a valid string constant or hexadecimal constant?
81. What would be the output of following statement: Select * from tbl_Employee where Emp_Name=NULL
82. Which of the following is not a valid data type in SQL Server 2012?
83. What is a collection of zero or more continuous CircularString and/or LineString instances of either geometry or geography types called?
84. The command DBCC CHECKDB allows you to do what?
85. The SETCERT.EXE utility can be used to do what?
86. What does the EOMONTH function do?
87. Can you select the built in Server Roles for SQL Server from the list below?
88. When retrieving data, which command may be used to rotate a set of column names from horizontal across the top to vertically, appearing as values among the result set
89. By default, SQL Server treats NULL as what data type
90. True or false? Foreign keys always point to other tables.
91. Which of the following statements prevents users from performing actions?
92. Which service is used to run scheduled jobs?
93. To find all employees whose name ends with 'ith', complete this sql statement: 'SELECT * FROM employees WHERE name ' ...
94. A primary key can be created using a column that allows NULL values.
95. What is the order of precedence of ORDER BY and GROUP BY?
96. A member of the sysadmin role is allowed to perform which actions?
97. Which constraint type specifies that a column contains column values from another table?
98. In SQL Server 2012 and later versions, the sysadmin password requires at least 8 characters and which of the following character types?
99. What are the three Transact-SQL statements that are related to authorization?
100. In database mirroring, an instance can be a mirror in some mirroring sessions and a witness in other mirroring sessions.
101. A stored procedure can recursively call itself.
102. A table can have more than one primary key.
103. What does CTE stand for?
104. Which clause allows you to filter the result of a column that has been aggregated?
105. What does the ROLLBACK TRANSACTION statement do?
106. With automatic conflict resolution in peer-to-peer replication, which update wins the conflict?
107. How is FILESTREAM data stored?
108. What is the benefit of using the TRY_CAST() function?
109. Which system database holds information on SQL Server Agent jobs?
110. Which data extents are backed up by a differential database backup?
111. Backup and restore history is stored in which database?
112. SQL Server will create a unique nonclustered index, by default, when a primary key is created.
113. What does DBCC stand for?
114. Which of the following is not a relational operation?
115. Which of the following statements regarding 'views' are incorrect?
116. Which of the following is not a logical operator?
117. Which of the following is not a Method call of .NET class for Assemblies in the SQL Server?
118. What is wrong with the following query in accordance with performance? SELECT * FROM [Table_Name] WHERE LOWER(Field_Name) = 'name'
119. How can you change 'Hansen' into 'Nilsen' in the LastName column in the Persons Table?
120. Examine the code given below: SELECT employee_id FROM employees WHERE commission_pct=.5 OR salary > 23000 Which of the following statements is correct with regard to this code?
121. ___________ is the highest level of a transaction isolation implemented by SQL Server.
122. Which query will display data from the Pers table relating to Analysts, Clerks and Salesmen who joined between 1/1/2005 and 1/2/2005?
123. Which of the following options is not correct about the DATEDIFF() function?
124. Consider the following statements and pick the correct answer: 1. ceiling() - returns the smallest integer greater than or equal to the specified value 2. floor() - returns the largest integer less than or equal to the specified value
125. In which sequence are queries and sub-queries executed by the SQL Engine?
126. The simplest query must include at least ________ and _________.
127. Is this statement true or false: A cursor is a pointer that identifies a specific working row within a set
128. A table has following values for its department field: marketing, production, production, sales, NULL, NULL, Marketing, Null What will the following query return: Select distinct(department) from employees
129. Consider the following two tables: 1. customers( customer_id, customer_name) 2. branch ( branch_id, branch_name ) What will be the output if the following query is executed: Select *, branch_name from customers,branch
130. Select the correct option:
131. Which of the following is false with regards to sp_help?
132. Which of the following is the syntax for creating an Index?
133. Study the situation described below and identify the nature of relationship? Each student can enroll into more than one class. Each class can accommodate more than one student.
134. A company has the following departments: marketing, Designing, production, Packing What will be the result of the following query? select * from table where department < 'marketing';
135. What is the correct order of clauses in the select statement? 1 select 2 order by 3 where 4 having 5 group by
136. When designing a database table, how do you avoid missing column values for non-primary key columns?
137. Which of the following is/are true with reference to Triggers?
138. Which of the following is not a control statement?
139. Which of the following statements about SQL Server comments is false?
140. Which of the following options is correct about identity(seed, increment)?
141. What is the maximum value that can be stored for a datetime field?
142. Which operator will be evaluated first in the following statement:select (age + 3 * 4 / 2 - 8) from emp
143. Choose the appropriate query for the Products table where data should be displayed primarily in ascending order of the ProductGroup column. Secondary sorting should be in descending order of the CurrentStock column.
144. Is the FROM clause necessary in every SELECT statement?
145. Which one of the following fields is the ideal candidate for the primary key in a student record base?
146. Which of the following is not a valid Arithmetic operator in SQL Server?
147. Which of the following is not a global variable?
148. What is the order of precedence among the following operators? 1 IN 2 NOT 3 AND 4 OR
149. A production house needs a sale report where total sale of the day is more than $20,000. Which of the following query should be used?
150. Which of the following is not a column property?
151. View the following Create statement: 1 Create table Pers 2(EmpNo Int not null, 3 EName Char not null, 4 Join Datetime not null, 5 Pay Smallmoney) Which line contains an error?
152. You should avoid the use of cursors because:
153. How can you view the structure of a table named 'myTable' in SQL Server?
154. Which of the following statements are false?
155. Which of the following statements are true?
156. You want to display the titles of books that meet the following criteria: 1. Purchased before November 11, 2002 2. Price is less than $500 or greater than $900 You want to sort the result by the date of purchase, starting with the most recently bought book. Which of the following statements should you use?
157. Evaluate the following SQL statement: SELECT e.employee_id, (.15* e.salary) + (.5 * e.commission_pct) + (s.sales_amount * (.35 * e.bonus)) AS CALC_VALUE FROM employees e, sales s WHERE e.employee_id = s.emp_id; What will happen if all the parentheses are removed from the calculation?
158. Is it possible to insert several rows into a table with a single INSERT statement?
159. Examine the two SQL statements given below: SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY salary DESC SELECT last_name, salary, hire_date FROM EMPLOYEES ORDER BY 2 DESC What is true about them?
160. Which of the following are false for batches (batch commands)?
161. Which of the following are not date parts?
162. Which one of the following must be specified in every DELETE statement?
163. What does referential integrity (also called relational integrity) prevent?
164. Which of the following is an invalid statement for manipulation of binary data?
165. Which of the following are aggregate functions in SQL?
166. What happens if you type the following statement at the T-SQL prompt? SELECT getdate()
167. Which of the following statements is not true about the table object in SQL Server?
168. What does BLOB stand for?
169. Consider the transaction: Begin Transaction Create table A ( x smallint , y smallint ) Create table B ( p smallint , q smallint ) Update A set x=600 where y > 700 Update B set p=78 where q=99 If @@ error != 0 Begin RollBack Transaction Return End Commit Transaction Select the correct option:
170. Consider the following table structure of students: rollno int name varchar(20) course varchar(20) What will be the query to display the courses in which the number of students enrolled is more than 5?
171. Examine the description of the STUDENTS table: STD_ID INT COURSE_ID VARCHAR (10) START_DATE DATETIME END_DATE DATETIME The aggregate functions valid on the START_DATE column are:
172. Sample Code CREATE TABLE table1( column1 varchar(50), column2 varchar(50), column3 varchar(50), column4 varchar(50)); Which one of the following is the correct syntax for adding the column named 'column2a' to the table shown above?
173. What will happen if you query the emp table as shown below: select empno, DISTINCT ename, Salary from emp;
174. The IF UPDATE (column_name) parameter in a trigger definition will return TRUE in case of an INSERT statement being executed on the triggered table:
175. Is the following statement true or false? A column that allows NULLs requires more space to store a value.
176. Which of the following datatypes is not supported by SQL-Server?
177. The STUDENT_GRADES table has these columns: STUDENT_ID INT SEMESTER_END DATETIME GPA FLOAT Which of the following statements finds the highest Grade Point Average (GPA) per semester?
178. Which one of the following correctly selects rows from the table myTable that have null in column column1?
179. Which of the following queries is valid?
180. Which of the following is/are not (a) valid binary datatype in SQL Server?
181. What is the numeric range that is supported by the datatype tinyint?
182. Which of the following is not a SQL operator?
183. Examine the query:- select (2/2/4) from tab1; where tab1 is a table with one row. This would give a result of:
184. State which of the following are true
185. Consider the following queries: 1. select * from employee where department LIKE '[^F-M]%'; 2. select * from employee where department = '[^F-M]%'; Select the correct option:
186. What is the correct SQL syntax for returning all the columns from a table named 'Persons' sorted REVERSE alphabetically by 'FirstName'?
187. Which of the following is not a valid character datatype in SQL Server?
188. The sales database contains a customer table and an order table. For each order there is one and only one customer, and for each customer there can be zero or more orders. How should primary and foreign key fields be placed into the design of this database?
189. What is the correct SQL syntax for selecting all the columns where the 'LastName' is alphabetically between (and including) 'Hansen' and 'Pettersen'?
190. Which of the following constraints can be used to enforce the uniqueness of rows in a table?
191. The AND operator displays a row if ANY conditions listed are true. The OR operator displays a row if ALL of the conditions listed are true
192. What is the return type of the value returned by @@DATEFIRST in SQL Server 2008?
193. Which of the following features is used by SQL Server Audit to create and manage audits in SQL Server 2008?
194. What is the default maximum precision of decimal data type in SQL Server 2008?
195. Each table in SQL Server 2008 can have up to ______ nonclustered indexes.
196. What is the maximum number of processors that can be used in parallel plan execution using the MAXDOP option in SQL Server 2008?
197. Which of the following options is NOT supported by the ON DELETE clause of a FOREIGN KEY constraint of SQL Server 2008?
198. Which of the following methods can be specified for the lock escalation of a table in SQL Server 2008?
199. Analyze the following code using wild card characters of SQL Server 2008: SELECT Name FROM person.employees WHERE Name LIKE '[_]n' GO Which of the following values will be returned by the above code?
200. Which of the following scripting options are available while creating a script using the shortcut menu from the Object Explorer in SQL Server 2008 Management Studio?
201. What is the data type of the parameter 'Audit_file_offset' of function 'fn_get_audit_file()' in SQL Server 2008?
202. Which of the following compression features have been introduced in SQL Server 2008 that were NOT supported by SQL Server 2005?
203. Analyze the following code which uses the TOP clause of SQL Server 2008: USE Person GO DECLARE @p AS int SELECT @p=10 SELECT TOP(@p) FROM Employee GO What Will be the output of the above code?
204. All MDX, DMX and XML/A errors are displayed in the Error List window of the SQL Server 2008. Management Studio.
205. Which of the following arguments of the fn_get_audit_file() function of SQL Server 2008 specifies the directory indicating the location and audit file set to be read?
206. Which of the following statements is NOT true about the UNIQUE constraint of SQL Server 2008?
207. Which of the following Audit objects primarily describes the destination where the audit data of SQL Server 2008 gets stored?
208. Which of the following options is used to change the value of the password variable and exit using sqlcmd?
209. How many audit action groups are available for a server in SQL Server 2008 to perform different actions on audit data?
210. Which of the following statements using GROUP BY clause are allowed in SQL Server 2008?Which of the following statements using GROUP BY clause are allowed in SQL Server 2008?
211. Which of the following rules must be followed for combining the result sets of two queries by using UNION?
212. Which of the following options are available for the DATA_COMPRESSION clause specified for a table in SQL Server 2008?
213. Which of the following data types are supported by SQL Server 2008?
214. Which of the following statements are true?
215. Which of the following statements are true regarding row and page compression in SQL Server 2008?
216. Which of the following security features have been introduced in SQL Server 2008?
217. Which of the following encryption algorithms are used by the Transparent Data Encryption security mechanism of SQL Server 2008?
218. Which of the following formats are supported by the language_term argument while altering a full-text stoplist in SQL Server 2008?
219. Which of the following are valid DATETIME functions in SQL Server 2008?
220. Which of the following counters are used to monitor compression of the whole instance of SQL Server?
221. Which of the following statements are true about the FILESTREAM argument of SQL Server 2008?
222. Which of the following spatial data types are used in SQL Server 2008?
223. Which of the following statements are true about FILESTREAM storage in SQL Server 2008?
224. Which of the following permissions are required to create a view in SQL Server 2008?
225. For which of the following data types can a COLLATE clause be applied?
226. Columns marked with which of the following constraints/properties allow null values to be inserted in them?
227. Analyze the following code used to update a Common Table Expression: USE demodb GO DECLARE @a TABLE (ID int, Value int); DECLARE @b TABLE (ID int, Value int); INSERT @a VALUES (1, 10), (2, 20); INSERT @b VALUES (1, 100),(2, 200); WITH cte AS (SELECT * FROM @a) UPDATE cte SET Value = b.Value FROM cte AS a INNER JOIN @b AS b ON b.ID = a.ID SELECT * FROM @a GO What Will be the output of the above code?
228. The Server Audit object and the Server Audit Specification object of SQL Server 2008 share a __________ relationship.
229. What is the maximum value for the sum of the values of MIN_CPU_PERCENT and MIN_MEMORY_PERCENT for all the resource pools of SQL Server?
230. An identifier denoting a global temporary object in SQL Server 2008 starts with a ______ sign.
231. What is returned by NULLIF if the two expressions that it compares are NOT equal?
232. User defined data type columns can be designated with ROWGUIDCOL.
233. Which of the following commands is used to save the output to a text file after you run a Transact-SQL script using sqlcmd?
234. Analyze the following code snippet: DECLARE @myTime TIME(4) = '02:02:02.234567 +01:01' SELECT @myTime AS '@myTime' What is the output of the above code?
235. Which of the following is used by SQL Server 2008 Management Studio to execute scripts in regular mode in the Query Editor?
236. What is the default value of the SORT_IN_TEMPDB clause of the relational_index_option specified at the time of creation of an index on a table in SQL Server 2008?
237. The regular identifiers of SQL Server 2008 may contain a range of _____________ characters.
238. Which of the following evaluation modes of Policy-Based Management CANNOT be automated in SQL Server 2008?
239. Which of the following statements is true if you create a global temporary table in SQL Server 2008 and specify a FOREIGN KEY with the CREATE TABLE command?
240. Which of the following features of SQL Server 2008 evaluates servers for compliance with a set of predefined conditions and prevents making undesirable changes to servers?
241. How many groupings are generated when CUBE is used with the GROUP BY clause of SQL Server 2008?
242. Windows Security log can be specified as a target for Audit in SQL Server 2008.
243. Which of the following is a valid code to delete a clustered index with a PRIMARY KEY constraint in SQL Server 2008?
244. What is the default timeout for login to sqlcmd while you try to connect to a server?
245. What is the default precision of the date data type in SQL Server 2008?
246. What amount of storage is required by a date variable in SQL Server 2008?
247. PRIMARY KEY constraint defaults to __________ index.
248. Which of the following is a valid code to change the index created on a column of a table to page compression?
249. Which of the following features of SQL Server 2008 enables you to limit the memory used by incoming application requests?
250. Analyze the following command: -o 'C:\Folder\' Which of the following statements is true if a file with the file name specified in the above command already exists?
251. Multiple CHECK constraints can be applied to a single column and a single CHECK constraint can be applied to multiple columns in SQL Server 2008.
252. Which of the following is the default authentication mode for sqlcmd in SQL Server 2008?
253. Which of the following stored procedures is used to delete data from the management data warehouse of a Data Collector?
254. Analyze the following code snippet: DECLARE @myDate DATETIME2 SELECT @myDate = '2009/10/08 12:35:29.2348 +12:15' SELECT @myDate AS '@myDate' What is the output of the above code?
255. Which of the following clauses of SQL Server 2008 avoids enforcing a constraint when repeated inserts are performed?
256. Which of the following roles is required to connect to a Central Management Server?
257. What range is supported by the TIME data type in SQL Server 2008?
258. Which of the following is a valid syntax of the ALTER SERVER AUDIT statement used to enable a server audit?
259. What is the name of the default instance installed by SQL Server 2008 Express in your computer?
260. Which of the following data types can be specified as SPARSE?
261. What is the maximum number of columns that can be combined into a single composite index key?
262. Which of the following values specifies that each instance of the XML data type in column_name can contain multiple top-level elements?
263. 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?
264. Which of the following commands is used to connect to a named instance of SQL Server using the sqlcmd utility of SQL Server?
265. Which of the following commands is used to run a Transact-SQL script file by using sqlcmd?
266. What is the default precision of the TIME data type in SQL Server 2008?
267. Which of the following permissions is required to drop a stored procedure in SQL Server 2008?
268. The login timeout when you try to connect to a server must be a number between __________.
269. 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?
270. Which of the following namespaces is used to manage the audit configuration programmatically in SQL Server 2008?
271. What happens to the audit records if a failure to write the Audit event does NOT trigger the SQL Server instance to shut down?
272. Which of the following code snippets causes only partition number 1 to be rebuilt while changing the compression of a partitioned table?
273. 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?
274. Which of the following is a valid code to remove the key used to encrypt a database using Transparent Data Encryption algorithm?
275. What should a computed column be marked as so that SQL Server Database Engine can physically store values in a table?
276. The output of a Transact SQL query using the GROUPING function of SQL Server 2008 is of __________ return type.
277. Which of the following queries is used to rename a database in SQL Server 2008?
278. 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
279. Which of the following is used to comment a portion of an SQL Server 2008 script?
280. Which of the following statements using GROUP BY clause are allowed in SQL Server 2008?
281. FILESTREAM storage is best used when the BLOB file sizes average 1MB or higher.
282. A local temporary stored procedure name can contain a maximum of __________ characters
283. 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?
284. 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?
285. Which of the following commands is used to start the Resource Governor in SQL Server 2008?
286. In which of the following statements can TOP clause be used in SQL Server 2008?
287. 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?
288. Which of the following operators will be evaluated first in the following statement? Select (salary+40^2*30/5) from employees
289. State whether True or False. The 'text' and 'ntext' data type can store character based data. These data types can be used in joins
290. Which of the following is not a type of Database backup in the SQL SERVER 2005?
291. Which clause will be used to extract data from Tables A and B having a similar structure but no relation between themselves?
292. 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?
293. Which among the following are the new datatypes introduced in the SQL Server 2005?
294. 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?
295. State whether True or False. The plus (+) and minus (-) operators cannot be used to perform increment/decrement operations on datetime and smalldatetime values.
296. Which of the following is the new sample database in the SQL SERVER 2005?
297. Which of the following SQL queries correctly selects the rows from the table 'employees' that have NULL value in the 'salary' column?
298. Which level of data abstraction describes the data that is stored in the database and their corresponding relationships?
299. Which of the following is not an Aggregate function?
300. Consider the following query: Select name from employees where name like '%_n_' Which names will be displayed?
301. 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?
302. Which query will be used for a particular column of a table, if 'Reading' is to be replaced by 'Writing'?
303. How is the following query incorrect? select * from Orders where OrderID = (select OrderID from OrderItems where ItemQty > 50)
304. Which data type in the SQL SERVER 2005 converts itself into the type of data that is inserted into it?
305. 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?
306. Which of the following statements is/are wrong?
307. Which of the following is a database object?
308. 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
309. Which of the following statements is correct?
310. Which of the following clauses are not allowed in a single row sub-query?
311. What is the function of 'DBCC CHECKDB' command?
312. 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?
313. 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?
314. Which of the following are the SQL Transaction statements?
315. Which of the following operators perform pattern matching?
316. Which of the following statements regarding 'views' are incorrect?
317. 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?
318. 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?
319. Which of the following are not Database Models?
320. Which functions does the following SQL QUERY perform? DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)
321. 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?
322. 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.
323. State whether True or False. While using the Select query, the IN clause is a better choice than the EXIST clause.
324. 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?
325. State whether True or False. Many-to-One is a type of relationship in RDBMS.
326. State whether True or False. You can send emails through the Database engine.
327. State whether True or False. The 'Having' clause can not be used when there is a 'Where' clause in the statement already.
328. What exactly is a Foreign key?
329. Which of the following statements regarding Trigger and Stored Procedures is correct?
330. 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
331. Which one of the following statements regarding 'views' is incorrect?
332. What will the following query do? Delete Orders
333. The - (Negative) and ~(Bitwise NOT) are examples of the _________ type of operators.
334. 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?
335. Which of the following are correct?
336. 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?
337. Which of the following statements is wrong?
338. 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?
339. 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?
340. What is the maximum storage capacity of XML data type in SQL SERVER 2005?
341. Columns are called _____________ of a table in a relational model.
342. Which is the best method to handle errors and deadlocks?
343. Which of the following is not a Data type in the SQL SERVER 2005?
344. Which command is used to create User defined Data Type in the SQL SERVER 2005?
345. Which of the following mechanisms is used by 'ENCRYPTION by passphrase' to encrypt the data in the SQL SERVER 2005?
346. Which one of the following statements about Indexes is wrong?
347. __________ is a container that holds tables, views, procedures, functions and so on.
348. What exactly is a Super key?
349. State whether True or False. Triggers can be created in Assemblies.
350. Which one of the following statements is correct?
351. 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
352. Why are Joins generally better than the Correlated sub-query?
353. 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"
354. What exactly is the Full-Text Catalog?
355. The commands GRANT and REVOKE are examples of :
356. 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?
357. Which of the following commands will be used to see the structure of the table named 'salary'?
358. 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?
359. 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)
360. What will be the output of the following SQL query? SET SHOWPLAN_TEXT ON GO SELECT * FROM abcsite.abctable GO