Upwork Test Answers: Get all the correct answers of most recent and possible Upwork Tests A to Z (Updated on Jan, 2016)
Cover Letter Templates: These cover letter samples are not only for Upwork job, but also you will have some idea about your real life job
 
Freelance Profile Overviews: Different Profile samples and overviews of experts, advanced and intermediate level freelancers
For Newbie of Upwork: Upwork Help - How to apply for a job in Upwork with 10 most important articles about Upwork

A to Z View - All Upwork Test Answers

Upwork MS SQL Server 2005 Test Answer

Here you will find all the Upwork MS SQL Server 2005 Test answers of Upwork Database category, please press Ctrl + F to find your desired answers of the test questions. Updated on 5 January 2016
----------------------------------------------------------------------------------------------------------------------

1. Which of the following clauses are not allowed in a single row sub-query?
 Answers: • Group by --- or ----- • Having ---- or ---- • Order by

 2. What is the function of "DBCC CHECKDB" command?
 Answers: • It checks the allocation of all the objects in the specified database ----- or ----- • It checks the structural integrity of all the objects in the specified database ---- or ----- • It checks for consistency in and between system tables in the 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: • SELECT TOP 2 * FROM engineers ORDER BY salary DESC ---- or ---- • 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: • Table --- or----- • Indexes

 5. Which of the following are the SQL Transaction statements?
 Answers: • ROLLBACK STATEMENT ---- or ---- • COMMIT STATEMENT

 6. Which of the following operators perform pattern matching?
 Answers: • % ---or---- • _ (underscore)

 7. Which of the following statements regarding "views" are incorrect?
 Answers: • A view has a data of its own ---or--- • A table can be altered by altering its corresponding view

 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: • insert into employees values (1010, 'HR', 10000) --- or --- • insert into viemployees 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: • SELECT distinct department_id FROM employees Where salary > ANY (SELECT AVG(salary) FROM employees GROUP BY department_id) --- or ---- • SELECT department_id FROM employees WHERE SALARY > ALL (SELECT AVG(salary) FROM employees GROUP BY department_id)

 10. Which of the following are not Database Models?
 Answers: • Composite model --- or --- • Recursive model

 11. Which functions does the following SQL QUERY perform?

 DBCC CHECKIDENT ('table_name', RESEED, new_reseed_value)
 Answers: • It sets the identity field value to the new reseed value

 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: • Records will be shown with the deptname field and the salary field in the descending order

 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: • Select Customers.Customername,(Select Min(Orderdate) From Orders Where Customers.Customerid=Orders.Customerid) From Customers

 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: • SQL SERVER 2005 Standard Edition

 15. State whether True or False.

 While using the Select query, the IN clause is a better choice than the EXIST clause.
 Answers: • 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: • select deptname from employees where salary =(select max(salary) from employees)

 17. State whether True or False.

 Many-to-One is a type of relationship in RDBMS.
 Answers: • True

 18. State whether True or False.

 You can send emails through the Database engine.
 Answers: • 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: • False

 20. What exactly is a Foreign key?
 Answers: • A Foreign key is used to match columns in other tables

 21. Which of the following is not a relational operation?
 Answers: • Extraction

 22. Which of the following statements regarding Trigger and Stored Procedures is correct?
 Answers: • Triggers can call Stored Procedures but Stored Procedures cannot call Triggers

 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: • 10000, 11000, 12000, 14000, 13000

 24. Which one of the following statements regarding "views" is incorrect?
 Answers: • A view is like a window through which data on tables can be viewed or changed

 25. What will the following query do?

 Delete Orders
 Answers: • It will remove all rows from the Orders Table

 26. The - (Negative) and ~(Bitwise NOT) are examples of the _________ type of operators.
 Answers: • 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: • select deptname, min(salary) as Minimum, max(salary) as Maximum from engineers group by deptname

 28. Which of the following are correct?
 Answers: • With DDL, you can create and remove tables, schemas, domains, indexes and views

 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: • Select 'Customer id is' + CAST(customerid AS varchar) From Customer

 30. Which of the following statements is wrong?
 Answers: • Truncate retains the identity of 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: • Select usercomments From Comments Order by commentid 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: • The output will be "No comments", when the database entry is Null

 33. What is the maximum storage capacity of XML data type in SQL SERVER 2005?
 Answers: • 2GB

 34. Columns are called _____________ of a table in a relational model.
 Answers: • Attributes

 35. Which is the best method to handle errors and deadlocks?
 Answers: • Errors and deadlocks can be handled by using Try and Catch constructs

 36. Which of the following is not a Data type in the SQL SERVER 2005?
 Answers: • nsmallmoney

 37. Which command is used to create User defined Data Type in the SQL SERVER 2005?
 Answers: • Create Type

 38. Which of the following mechanisms is used by "ENCRYPTION by passphrase" to encrypt the data in the SQL SERVER 2005?
 Answers: • user defined password

 39. Which one of the following statements about Indexes is wrong?
 Answers: • 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: • It will show the query plan and the estimated cost of running

 41. __________ is a container that holds tables, views, procedures, functions and so on.
 Answers: • Schema

 42. What exactly is a Super key?
 Answers: • A Super key is a column or a set of columns that uniquely identifies rows in a table

 43. State whether True or False.

 Triggers can be created in Assemblies.
 Answers: • True

 44. Which one of the following statements is correct?
 Answers: • Both a and b are 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: • 4,1,2,3

 46. Why are Joins generally better than the Correlated sub-query?
 Answers: • Joins pick up the rows according to the condition of the queries

 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: • It will give an error

 48. What exactly is the Full-Text Catalog?
 Answers: • The Full-Text catalog is a file which contains full-text indexes

 49. The commands GRANT and REVOKE are examples of :
 Answers: • 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: • The prefix will slow down the execution as the prefix is reserved for system stored procedures

 51. Which of the following commands will be used to see the structure of the table named 'salary'?
 Answers: • sp_help salary

 52. Which of the following operators will be evaluated first in the following statement?

 Select (salary+40^2*30/5) from employees
 Answers: • *

 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: • False

 54. Which of the following is not a type of Database backup in the SQL SERVER 2005?
 Answers: • Complete 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: • Union All

 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: • RESTORE DATABASE marketnikkie FROM DISK = 'D:\marketnikkie.bak'

 57. Which among the following are the new datatypes introduced in the SQL Server 2005?
 Answers: • xml

 58. Which of the following is not a Method call of .NET class for Assemblies in the SQL Server?
 Answers: • Aggregate

 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/6/2008, 1/10/2008

 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: • True

 61. Which of the following is the new sample database in the SQL SERVER 2005?
 Answers: • AdventureWorks

 62. Which of the following SQL queries correctly selects the rows from the table "employees" that have NULL value in the "salary" column?
 Answers: • SELECT * FROM employees WHERE salary is null

 63. Which level of data abstraction describes the data that is stored in the database and their corresponding relationships?
 Answers: • Logical 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: • INSERT Into Branches(recordID, description) VALUES(1, '<log><application>Sales</application><description>The connection timed out.</description></log>')

 65. Which of the following is not an Aggregate function?
 Answers: • LEN()

 66. Consider the following query:

 Select name from employees where name like "%_n_"

 Which names will be displayed?
 Answers: • Names containing n as the second last letter

 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: • DECLARE qselect CURSOR FOR 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: • Update [Table Name] set [Field Name]= (replace([Field Name],'Reading','Writing'))

 69. What is wrong with the following query in accordance with performance?

 SELECT * FROM [Table_Name] WHERE LOWER(Field_Name) = 'name'
 Answers: • T-Sql is not case sensitive. Hence, the LOWER keyword is not required

 70. How is the following query incorrect?

 select * from Orders where OrderID = (select OrderID from OrderItems where ItemQty > 50)
 Answers: • The sub-query can return more than one row, so, '=' should be replaced with 'in'

 71. Which data type in the SQL SERVER 2005 converts itself into the type of data that is inserted into it?
 Answers: • sql_variant

 72. Which of the following is not a logical operator?
 Answers: • 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: • Using Log Shipping

 74. Which of the following statements is/are wrong?
 Answers: • Both Primary key and Unique key don't allow NULL values

 75. Which of the following is a database object?
 Answers: • 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: • True

 77. Which of the following statements is correct?
 Answers: • Modifications made in a table will be slower if the number of indexes is more