Upwork MS SQL SERVER 2005 TEST 2016

Hello Freelancer, Welcome to allupworktestanswers.com Here to get the MS SQL SERVER 2005 TEST of Latest version 2016. We are ready here to provide your desire upwork test answer. We already tested all exam test information Available include on here. So Dear, Why Late? Read the below full exam test and get more information from our website. 
Upwork MS SQL SERVER 2005 TEST 2016

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

Answers:
  1. From
  2. Where
  3. Group by
  4. Having
  5. Order by
2. What is the function of “DBCC CHECKDB” command?
Answers:
  1. It checks the allocation of all the objects in the specified database
  2. It checks the structural integrity of all the objects in the specified database
  3. It checks for consistency in and between system tables in the specified database
  4. It checks the consistency of disk space allocation structures of a specified database
3. 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?
Answers:
  1. SELECT TOP 2 * FROM engineers ORDER BY max(salary) DESC
  2. SELECT TOP 2 * FROM engineers ORDER BY salary DESC, GROUP BY salary
  3. SELECT TOP 2 * FROM engineers GROUP BY salary DESC
  4. SELECT TOP 2 * FROM engineers ORDER BY salary DESC
  5. SELECT TOP 2 [name], salary FROM engineers ORDER BY salary DESC
4. 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?
Answers:
  1. Functions
  2. Triggers
  3. Table
  4. Indexes
  5. Assemblies
5. Which of the following are the SQL Transaction statements?
Answers:
  1. REVERT STATEMENT
  2. ROLLBACK STATEMENT
  3. START STATEMENT
  4. COMMIT STATEMENT
  5. END STATEMENT
6. Which of the following operators perform pattern matching?
Answers:
  1. %
  2. _ (underscore)
  3. =
  4. ==
  5. is =
7. Which of the following statements regarding “views” are incorrect?
Answers:
  1. A view has a data of its own
  2. Data can be inserted into the table using its own view
  3. Data can be updated in the table using its own view
  4. A table can be altered by altering its corresponding view
  5. A View is derived from another table
8. 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?
Answers:
  1. insert into employees values (1010, HR, 10000)
  2. insert into employees values (1010, ‘HR’, 10000)
  3. insert into viemployees values (1010, ‘HR’, 10000)
  4. insert into viemployees (empid, deptname, salary) values (1010, HR, 10000)
  5. insert into employees (empid, deptname, salary) set values (1010, ‘HR’, 10000)
9. 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?
Answers:
  1. SELECT * FROM employees where salary > (SELECT MIN(salary) FROM employees GROUP BY department_id)
  2. SELECT * FROM employees WHERE salary = (SELECT AVG(salary) FROM employees GROUP BY department_id)
  3. SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id)
  4. SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id)
  5. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY))
10. Which of the following are not Database Models?
Answers:
  1. Hierarchical model
  2. Network model
  3. Composite model
  4. Relational model
  5. Recursive model
11. Which functions does the following SQL QUERY perform?
DBCC CHECKIDENT (‘table_name’, RESEED, new_reseed_value)
Answers:
  1. It checks the current identity field value of the specified table
  2. It sets the identity field value to the new reseed value
  3. It sets the identity field value to 1
  4. None of the above
12. 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?
Answers:
  1. The above SQL query will give an error
  2. Records will be shown with the salary field displayed in the descending order
  3. Records will be shown with the deptname field in the ascending order and salary in the descending order
  4. Records will be shown with the deptname field and the salary field in the descending order
  5. Records will be shown in a jumbled manner
13. 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?
Answers:
  1. Select Customers.Customername,(Select Min(Orderdate) From Orders Where Customers.Customerid=Orders.Customerid) From Customers
  2. Select Customers.Customername,(Select Max(Orderdate) From Orders Where Customers.Customerid=Orders.Customerid) From Customers
  3. Select Customers.Customername,(Select Orderdate From Orders Where Customers.Customerid=Orders.Customerid) From Customers
  4. Select Customers.Customername,Min(Orders.Orderdate) From Orders,Customers Where Customers.Customerid=Orders.Customerid
  5. None of these
14. 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.
Answers:
  1. SQL SERVER 2005 Enterprise Edition
  2. SQL SERVER 2005 Standard Edition
  3. SQL SERVER 2005 Workgroup Edition
  4. SQL SERVER 2005 Developer Edition
  5. SQL SERVER 2005 Express Edition
15. State whether True or False.
While using the Select query, the IN clause is a better choice than the EXIST clause.
Answers:
  1. True
  2. False
16. 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?
Answers:
  1. select deptname from employees where salary =max(salary)
  2. select deptname from employees where salary =max(salary) group by deptname
  3. select deptname from employees where salary = (select salary from employees group by salary)
  4. select deptname from employees where salary =(select max(salary) from employees)
  5. select deptname from employees where salary in (select salary from employees group by salary)
  6. select deptname from employees where max(salary) in (select salary from employees group by salary)
17. State whether True or False.
Many-to-One is a type of relationship in RDBMS.
Answers:
  1. False
  2. True
18. State whether True or False.
You can send emails through the Database engine.
Answers:
  1. False
  2. True
19. State whether True or False.
The ‘Having’ clause can not be used when there is a ‘Where’ clause in the statement already.
Answers:
  1. True
  2. False
20. What exactly is a Foreign key?
Answers:
  1. A Foreign key is a column or a set of columns that uniquely identifies rows in a table
  2. A Foreign key checks the number of columns in a table which have non-null values
  3. A Foreign key is used to match columns in other tables
  4. A Foreign key checks the number of rows in a table which have non-null values
21. Which of the following is not a relational operation?
Answers:
  1. Restriction
  2. Extraction
  3. Projection
  4. Intersection
  5. Union
22. Which of the following statements regarding Trigger and Stored Procedures is correct?
Answers:
  1. Triggers are fired manually and Stored Procedures are fired automatically
  2. Triggers can call Stored Procedures but Stored Procedures cannot call Triggers
  3. Both Stored Procedures and Triggers can pass parameters
  4. None of These
23. 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
Answers:
  1. 10000, 14000, 12000, 10000, 11000
  2. 10000, 11000, 12000, 10000, 14000, 12000
  3. 10000, 11000, 14000, 12000, 13000, 10000
  4. 10000, 11000, 12000, 13000, 10000, 14000
  5. 10000, 11000, 12000, 14000, 13000
24. Which one of the following statements regarding “views” is incorrect?
Answers:
  1. A view is like a window through which data on tables can be viewed or changed
  2. A view is stored as a select statement only
  3. A view cannot be derived from another view
  4. A view has no data of its own
25. What will the following query do?
Delete Orders
Answers:
  1. It will remove the Orders Table from the database
  2. It will remove up to 1000 rows from the Orders Table
  3. It will remove all rows from the Orders Table
  4. It will remove all the rows as well as the indexes and constraints from the Orders Table
  5. It will produce error on execution
26. The – (Negative) and ~(Bitwise NOT) are examples of the _________ type of operators.
Answers:
  1. logical
  2. arithmetical
  3. assignment
  4. unary
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?
Answers:
  1. select deptname, min(salary) as Minimum, max(salary) as Maximum from engineers
  2. select deptname, min(salary) as Minimum, max(salary) as Maximum from engineers group by deptname
  3. select deptname, min(salary) as Minimum, max(salary) as Maximum from engineers group by salary
  4. select deptname, min(salary) as Minimum, max(salary) as Maximum from engineers order by deptname
  5. None of these
28. Which of the following are correct?
Answers:
  1. With DDL, you can create and remove tables, schemas, domains, indexes and views
  2. Select, Insert and Update are DCL commands
  3. Grant and Revoke are DML commands
  4. Commit and Rollback are DCL commands
29. 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?
Answers:
  1. Select ‘Customer id is’ + customerid From Customer
  2. Select ‘Customer id is’ + CAST(customerid AS varchar) From Customer
  3. Select ‘Customer id is’ + CAST(customerid AS integer) From Customer
  4. Both a and b
  5. None of these
30. Which of the following statements is wrong?
Answers:
  1. Truncate is faster than Delete
  2. Truncate is a DDL command
  3. Truncate retains the identity of the table
  4. Truncate cannot be rolled back
  5. Truncate removes all rows from the table
31. 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?
Answers:
  1. Select usercomments From Comments Order by commentid
  2. Select usercomments From Comments Order by commentid desc
  3. Select usercomments From Comments Order by usercomments
  4. Select usercomments From Comments Order by usercomments desc
32. 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?
Answers:
  1. The output will always be “No comments” irrespective of the value in the Comments field
  2. The output will be null in all the rows
  3. The output will be “No comments”, when the database entry is Null
  4. The output will be “No comments”, when the database entry is Not Null
  5. This query is erroneous
33. What is the maximum storage capacity of XML data type in SQL SERVER 2005?
Answers:
  1. 1GB
  2. 2GB
  3. 4GB
34. Columns are called _____________ of a table in a relational model.
Answers:
  1. Tuples
  2. Elements
  3. Objects
  4. Attributes
  5. Constraints
35. Which is the best method to handle errors and deadlocks?
Answers:
  1. Errors and deadlocks can be handled by using stored procedures
  2. Errors and deadlocks can be handled by using cursors
  3. Errors and deadlocks can be handled by using Try and Catch constructs
  4. Errors and deadlocks can be handled by using inline queries
36. Which of the following is not a Data type in the SQL SERVER 2005?
Answers:
  1. ntext
  2. nvarchar(n)
  3. xml
  4. nsmallmoney
37. Which command is used to create User defined Data Type in the SQL SERVER 2005?
Answers:
  1. sp_addtype
  2. Create Type
  3. Create Data Type
  4. None of these
38. Which of the following mechanisms is used by “ENCRYPTION by passphrase” to encrypt the data in the SQL SERVER 2005?
Answers:
  1. DES (Data Encryption Standard)
  2. AES (Advanced Encryption Standard)
  3. user defined password
39. Which one of the following statements about Indexes is wrong?
Answers:
  1. Indexes are created in an existing table to locate rows more quickly and efficiently
  2. It is possible to create an index on one or more columns of a table
  3. A table is scanned when index is not available
  4. Indexes can only be implemented on integer datatypes
40. What will be the output of the following SQL query?
SET SHOWPLAN_TEXT ON
GO
SELECT * FROM abcsite.abctable
GO
Answers:
  1. It will show the query plan and the estimated cost of running
  2. It will show all the rows of the “abctable” table
  3. It will show all the rows of the “abctable” table and the table structure
  4. It will show the identities and Constraints applied on this table
41. __________ is a container that holds tables, views, procedures, functions and so on.
Answers:
  1. Instance
  2. Cluster
  3. Metadata
  4. Schema
  5. None of the above
42. What exactly is a Super key?
Answers:
  1. A Super key is a column or a set of columns that uniquely identifies rows in a table
  2. A Super key checks the number of columns in a table which have non-null values
  3. A Super key is used to match columns in other tables
  4. A Super key checks the number of rows in a table which have non-null values
43. State whether True or False.
Triggers can be created in Assemblies.
Answers:
  1. True
  2. False
44. Which one of the following statements is correct?
Answers:
  1. You can create DDL triggers
  2. You can create DML triggers
  3. Both a and b are correct
  4. Neither a nor b is correct
45. 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
Answers:
  1. 1,4,3,2
  2. 4,1,2,3
  3. 3,2,1,4
  4. 2,4,1,3
46. Why are Joins generally better than the Correlated sub-query?
Answers:
  1. The Correlated sub-query scans the whole table
  2. Joins pick up the rows according to the condition of the queries
  3. The Correlated sub-query creates a nested loop
  4. The Correlated sub-query makes the statement complex
47. 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”
Answers:
  1. Dri
  2. ivi
  3. iving
  4. ving
  5. It will give an error
48. What exactly is the Full-Text Catalog?
Answers:
  1. The Full-Text catalog is an index for searching specific keywords
  2. The Full-Text catalog is a file which contains full-text indexes
  3. The Full-Text catalog is basically an inbuilt function for checking the validation of a text
  4. The Full-Text catalog is a system trigger for checking text validation
49. The commands GRANT and REVOKE are examples of :
Answers:
  1. DDL (Data Definition Language)
  2. DML (Data Manipulation Language)
  3. TCL (Transaction Control Language)
  4. DCL (Data Control Language)
50. 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?
Answers:
  1. The prefix will speed up the execution as it is in cache and will always get executed with high priority
  2. The prefix will slow down the execution as the prefix is reserved for system stored procedures
  3. The performance will not be affected as “sp_” is just a prefix for a stored procedure
  4. The prefix will speed up the execution as prefix “sp_” is used for stored procedures by default
51. Which of the following commands will be used to see the structure of the table named ‘salary’?
Answers:
  1. sp_helptext salary
  2. sp_define salary
  3. sp_help salary
  4. define salary
52. Which of the following operators will be evaluated first in the following statement?
Select (salary+40^2*30/5) from employees
Answers:
  1. +
  2. *
  3. /
  4. ^
53. State whether True or False.
The “text” and “ntext” data type can store character based data. These data types can be used in joins.
Answers:
  1. True
  2. False
54. Which of the following is not a type of Database backup in the SQL SERVER 2005?
Answers:
  1. Complete backup
  2. Differential backup
  3. Transaction Log backup
  4. Partial backup
  5. File(s) and Filegroup(s) backup
55. Which clause will be used to extract data from Tables A and B having a similar structure but no relation between themselves?
Answers:
  1. Join
  2. Group By
  3. Order By
  4. Union All
  5. Where
56. 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?
Answers:
  1. RESTORE DATABASE marketnikkie FROM DISK = ‘D:marketnikkie.bak’
  2. RESTORE DATABASE FROM DISK = ‘D:marketnikkie.bak’
  3. RESTORE DATABASE marketnikkie FROM = ‘D:marketnikkie.bak’
  4. RESTORE marketnikkie FROM DISK = ‘D:marketnikkie.bak’
  5. RESTORE DATABASE marketnikkie.mdf FROM DISK = ‘D:marketnikkie.bak’
57. Which among the following are the new datatypes introduced in the SQL Server 2005?
Answers:
  1. varchar (max)
  2. varbinary (max)
  3. nvarchar (max)
  4. nvarbinary (max)
  5. image
  6. uniqueidentifier
  7. xml
58. Which of the following is not a Method call of .NET class for Assemblies in the SQL Server?
Answers:
  1. Init
  2. Accumulate
  3. Aggregate
  4. Merge
  5. Terminate
59. 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?
Answers:
  1. 1/6/2008, 1/10/2008, 1/11/2008, 1/12/2008
  2. 1/6/2008, 1/10/2008, 1/10/2008
  3. 1/6/2008, 1/10/2008, 1/11/2008, 1/12/2008, 1/12/2008
  4. 1/6/2008, 1/10/2008
  5. The query will give an error as EXCEPT is only concerned with varchar values
60. State whether True or False.
The plus (+) and minus (-) operators cannot be used to perform increment/decrement operations on datetime and smalldatetime values.
Answers:
  1. True
  2. False
61. Which of the following is the new sample database in the SQL SERVER 2005?
Answers:
  1. Northwind
  2. pubs
  3. AdventureWorks
  4. master
  5. tempdb
62. Which of the following SQL queries correctly selects the rows from the table “employees” that have NULL value in the “salary” column?
Answers:
  1. SELECT * FROM employees WHERE salary=null
  2. SELECT * FROM employees WHERE salary equals null
  3. SELECT * FROM employees WHERE salary is null
  4. SELECT * FROM employees WHERE salary contains null
63. Which level of data abstraction describes the data that is stored in the database and their corresponding relationships?
Answers:
  1. View Level
  2. Procedural Level
  3. Physical Level
  4. Logical Level
  5. Conceptual Level
64. 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)
Answers:
  1. INSERT Into Branches(recordID, description) VALUES(1, ‘<log><application>Sales</application><description>The connection timed out.</description></log>’)
  2. INSERT UniversalLog(recordID, description) VALUES(1, ‘database unavailable’)
  3. INSERT Into Branches(recordID, description) VALUES(1, ‘<log><application>Sales<description>The connection timed out’)
  4. XML cannot be inserted into database
  5. XML can be inserted but syntax in all queries is wrong
65. Which of the following is not an Aggregate function?
Answers:
  1. AVG()
  2. LEN()
  3. COUNT()
  4. SUM()
  5. MAX(column)
66. Consider the following query:
Select name from employees where name like “%_n_”
Which names will be displayed?
Answers:
  1. Names starting with n
  2. Names containing n
  3. Names containing n as the second letter
  4. Names containing n as the second last letter
  5. Names having 3 ns
67. 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?
Answers:
  1. DECLARE qselect CURSOR ON SELECT * FROM qlist
  2. DECLARE qselect CURSOR FOR SELECT * FROM qlist
  3. DECLARE qlist CURSOR ON SELECT * FROM qselect
  4. DECLARE qselect CURSOR AS SELECT * FROM qlist
68. Which query will be used for a particular column of a table, if ‘Reading’ is to be replaced by ‘Writing’?
Answers:
  1. Update [Table Name] set [Field Name]= (replace(‘Reading’,’Writing’))
  2. Update [Table Name] set [Field Name]= (replace(‘Writing’,’ Reading’))
  3. Update [Table Name] set [Field Name]= (replace([Field Name],’ Writing’))
  4. Update [Table Name] set [Field Name]= (replace([Field Name],’ Reading ‘))
  5. Update [Table Name] set [Field Name]= (replace([Field Name],’Reading’,’Writing’))
  6. Update [Table Name] set [Field Name]= (replace([Field Name],’Writing ‘,’Reading’))
69. What is wrong with the following query in accordance with performance?
SELECT * FROM [Table_Name] WHERE LOWER(Field_Name) = ‘name’
Answers:
  1. The required keyword (‘name’) is already in the LOWER case
  2. The query requires more time to convert to the LOWER case
  3. T-Sql is not case sensitive. Hence, the LOWER keyword is not required
  4. Instead of ‘=’ the operator, ‘LIKE’ should be used
70. How is the following query incorrect?
select * from Orders where OrderID = (select OrderID from OrderItems where ItemQty > 50)
Answers:
  1. In the sub-query, ‘*’ should be used instead of ‘OrderID’
  2. The sub-query can return more than one row, so, ‘=’ should be replaced with ‘in’
  3. The sub-query should not be in parenthesis
  4. None of the above
71. Which data type in the SQL SERVER 2005 converts itself into the type of data that is inserted into it?
Answers:
  1. real
  2. rowversion
  3. type_var
  4. sql_variant
  5. monetary
72. Which of the following is not a logical operator?
Answers:
  1. between
  2. any
  3. some
  4. like
  5. with
73. 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?
Answers:
  1. Using the backup of Transaction log (ldf) file
  2. Taking Backup using flat files
  3. Using Log Shipping
  4. Taking Database Snapshot of Transaction log
74. Which of the following statements is/are wrong?
Answers:
  1. Both Primary key and Unique key enforce uniqueness
  2. Primary keys create a clustered index by default
  3. Both Primary key and Unique key don’t allow NULL values
  4. Unique keys create a non-clustered index by default
  5. It is possible to have more than one Primary key in a table
75. Which of the following is a database object?
Answers:
  1. Tables
  2. Stored Procedures
  3. Indexes
  4. UDF(User Defined Functions)
  5. All of the above
76. 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.
Answers:
  1. False
  2. True
77. Which of the following statements is correct?
Answers:
  1. Modifications made in a table will be faster if the number of indexes is more
  2. Modifications made in a table will be slower if the number of indexes is more
  3. The number of indexes does not affect the modification process of a table

Finally no more words require about the MS SQL SERVER 2005 TEST  information in this session of this content. If you are require knowing more, Please ask to us via our contact us form or comment box. Please make sure that, you don’t send Personal information via the Comment box.  Thank you very mutch for being with us.

0 comments: