Selecting Data - Quiz Explanation

The answers you selected are indicated below, along with text that explains the correct answers.
 
1. Kevin wants to find out which employees in the Employees table exist in the Terminated table. What is the most efficient technique for him to do this with Transact-SQL?
Please select the best answer.
  A. Union
  B. Subquery
  C. Join
  D. Aggregate
  The correct answer is C.
A join is more efficient than a subquery for retrieving information out of a table. A is incorrect because a union is not the correct technique in this case. B is incorrect because a join is more efficient than a subquery using the EXISTS keyword. D is incorrect because aggregated data is not related to the technique for testing the existence of data in another table.

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. Steve wants to write a Transact-SQL query to determine the minimum salary for each department in the company. He writes the following query: 
SELECT Department, MIN(Salary)
FROM Employees

Please select the best answer.
  A. What is the most important reason why this query will receive an error?
  B. The Employees table does not exist
  C. MIN is not a valid aggregate
  D. The ORDER BY clause is missing
  The correct answer is D.
Any aggregate function needs a GROUP BY clause to know how to group the answers. A is incorrect because, although the Employees table may not exist, it is not the most likely cause of the error. B is incorrect because MIN is a valid aggregate. C is incorrect because, although the ORDER BY clause is missing, it is not required, but only suggested.

4. Barry issues this query: 
SELECT SoftPartID AS ID
FROM Software
WHERE Cost < 150
UNION
SELECT HardPartID AS PartID
FROM Hardware
WHERE Cost < 1000
What will SQL Server 7 return as the name of the result column in the resultset?
Please select the best answer.
  A. SoftPartID
  B. ID
  C. HardPartID
  D. PartID
  The correct answer is B.
A union query returns the column name, or alias, if used, for the first SELECT statement only. A is incorrect because the column is aliased. SoftPartID will never be returned, even if this query was not part of a union. C is incorrect because the column is aliased. HardPartID will never be returned, even if this query was not part of a union. D is incorrect because a union query will return the column name or alias name for the first SELECT statement involved in the union.

5. Marty issued a distributed query, but received an error. Although many things could cause an error, what would be the first thing he should check?
Please select the best answer.
  A. The desired servers are linked.
  B. The query contains correct column names.
  C. There is a WHERE clause.
  D. The WITH DISTRIBUTE keywords are specified.
  The correct answer is A.
A distributed query needs to have all participating servers linked. B is incorrect because, although an error will occur if there is an incorrect column name, he should first check to see if all participating servers are linked. C is incorrect because a WHERE clause has no bearing on receiving an error. D is incorrect because there is no WITH DISTRIBUTE keyword.