Inserting Data - Quiz Explanation

The correct answers are indicated below, along with text that explains the correct answers.
 
1. The Department table has the following definition:
Column Name Datatype
DeptID Int
Description Varchar(50)

Please select the best answer.
  A. The statement will succeed because the syntax is correct.
  B. The statement will succeed because the DeptID of 5 does not exist.
  C. The statement will fail because the DeptID of 5 does exist.
  D. The statement will fail because literal values are not properly entered.
  The correct answer is D. The statement will fail because Accounting is not enclosed in quotes for a varchar datatype. A is incorrect because the statement will fail. B is incorrect because it makes no difference whether the DeptID of 5 exists if there is a problem with the literal values. C is incorrect because the instructions did not specify anything about DeptID needing to be unique and, therefore, this conclusion cannot be drawn.

2. Paul has a table named DistributedTransactionHistory. He needs to write a long query that references this table name many times. What is the best technique for dealing with long names such as this?
Please select the best answer.
  A. Alias the table name so that a shorter name can be referenced in the query
  B. Rename the table to something smaller
  C. Place the data into a table of a different name
  D. Simply reference the long table name in the query
  The correct answer is A.
An alias alleviates the problem of dealing with long table or column names in queries. B is incorrect because renaming the table is not always possible without investigating the ramifications of doing so. C is incorrect because placing data into a different table is an irrational notion. D is incorrect because, although Paul could reference the long table name, an alias allows him to use a shorter name within his queries.

3. Victoria needs to insert data into the SalaryHistory table from the Salary table. What keyword would be used to retrieve data from the Salary table?
Please select the best answer.
  A. SELECT
  B. EXISTS
  C. INSERT
  D. FROM
  The correct answer is A.
The data must be selected from the Salary table with the SELECT keyword. B is incorrect because the EXISTS keyword may be used, but it does not actually retrieve the data from the Salary table. C is incorrect because although the INSERT keyword is used to insert the data, it is not used to retrieve the data from the Salary table. D is incorrect because the FROM keyword does not retrieve the data from the Salary table.

4. Alison is about to issue a subquery to insert data into the DepartmentManager table for each of the three records in the Department table. What other way can she issue the query?
Please select the best answer.
  A. There is no other way to issue the query.
  B. She can use an IN clause and explicitly list the three values.
  C. She can join the two tables together.
  D. She can create a new table and insert the contents of both the DepartmentManager and Department tables.
  The correct answer is B.
She can use an IN clause and list the three values. A is incorrect because there is another way to issue the query. C is incorrect because although she can join the two tables together, this would not insert the three records. D is incorrect because creating a new table serves no purpose to inserting the three records.

5. David wants to write a distributed INSERT statement. What must he do first?
Please select the best answer.
  A. Link the server with the sp_linkserver system stored procedure
  B. Simply include the server in the FROM clause
  C. Link the server with the sp_addlinkserver system stored procedure
  D. Simply include the server in the LINK clause
  The correct answer is C.
The sp_addlinkserver system stored procedure will link a server to allow you to write distributed queries. A is incorrect because there is no sp_linkserver system stored procedure. B is incorrect because, although the server needs to be referenced in the FROM clause, it is not the first thing to do. D is incorrect because there is no clause called LINK.

6. What type of index hurts queries the most when inserting many rows of data with values that are in between existing rows of data?
Please select the best answer.
  A. Clustered
  B. Non-clustered
  C. Table scan
  D. Query Plan
  The correct answer is A.
A clustered index can hurt performance in this situation because SQL Server has to physically order the data upon every insert statement. B is incorrect because a non-clustered index does not hurt queries the most.
C is incorrect because a table scan is a type of query plan, not an index. D is incorrect because a query plan shows what type of index will be used. It is not a type of index itself.