Foundations of Querying Microsoft SQL Server 2012

Answers

This section contains the answers to the lesson review questions and solutions to the case scenarios in this chapter.

Lesson 1

1. Correct Answers: B and D

   1. Incorrect: It is important to use standard code.

   2. Correct: Use of standard code makes it easier to port code between platforms because fewer revisions are required.

   3. Incorrect: There’s no assurance that standard code will be more efficient.

   4. Correct: When using standard code, you can adapt to a new environment more easily because standard code elements look similar in the different platforms.

2. Correct Answer: D

   1. Incorrect: A relation has a header with a set of attributes, and tuples of the relation have the same heading. A set has no order, so ordinal positions do not have meaning and constitute a violation of the relational model. You should refer to attributes by their name.

   2. Incorrect: A query is supposed to return a relation. A relation has a body with a set of tuples. A set has no duplicates. Returning duplicate rows is a violation of the relational model.

   3. Incorrect: Not defining a key in the table allows duplicate rows in the table, and like the answer to B, that’s a violation of the relational model.

   4. Correct: Because attributes are supposed to be identified by name, ensuring that all attributes have names is relational, and hence not a violation of the relational model.

3. Correct Answer: B

   1. Incorrect: T-SQL isn’t standard and SQL isn’t a dialect in Microsoft SQL Server.

   2. Correct: SQL is standard and T-SQL is a dialect in Microsoft SQL Server.

   3. Incorrect: T-SQL isn’t standard.

   4. Incorrect: SQL isn’t a dialect in Microsoft SQL Server.

Lesson 2

1. Correct Answer: B

   1. Incorrect: Logical query processing doesn’t start with the SELECT clause.

   2. Correct: Logical query processing starts with the FROM clause, and then moves on to WHERE, GROUP BY, HAVING, SELECT, and ORDER BY.

   3. Incorrect: The ORDER BY clause isn’t evaluated before the SELECT clause.

   4. Incorrect: Logical query processing doesn’t start with the SELECT clause.

2. Correct Answer: C and D

   1. Incorrect: T-SQL allows you to refer to an attribute that you group by in the WHERE clause.

   2. Incorrect: T-SQL allows grouping by an expression.

   3. Correct: If the query is a grouped query, in phases processed after the GROUP BY phase, each attribute that you refer to must appear either in the GROUP BY list or within an aggregate function.

   4. Correct: Because the HAVING clause is evaluated before the SELECT clause, referring to an alias defined in the SELECT clause within the HAVING clause is invalid.

3. Correct Answer: A

   1. Correct: A query with an ORDER BY clause doesn’t return a relational result. For the result to be relational, the query must satisfy a number of requirements, including the following: the query must not have an ORDER BY clause, all attributes must have names, all attribute names must be unique, and duplicates must not appear in the result.

   2. Incorrect: A query without a DISTINCT clause in the SELECT clause can return duplicates.

   3. Incorrect: A query without an ORDER BY clause does not guarantee the order of rows in the output.

   4. Incorrect: A query without an ORDER BY clause does not guarantee the order of rows in the output.

Case Scenario 1

1. One of the most typical mistakes that T-SQL developers make is to assume that a query without an ORDER BY clause always returns the data in a certain order—for example, clustered index order. But if you understand that in set theory, a set has no particular order to its elements, you know that you shouldn’t make such assumptions. The only way in SQL to guarantee that the rows will be returned in a certain order is to add an ORDER BY clause. That’s just one of many examples for aspects of T-SQL that can be better understood if you understand the foundations of the language.

2. Even though T-SQL is based on the relational model, it deviates from it in a number of ways. But it gives you enough tools that if you understand the relational model, you can write in a relational way. Following the relational model helps you write code more correctly. Here are some examples:

   • You shouldn’t rely on order of columns or rows.

   • You should always name result columns.

   • You should eliminate duplicates if they are possible in the result of your query.

Case Scenario 2

1. It is important to use standard SQL code. This way, both the code and people’s knowledge is more portable. Especially in cases where there are both standard and nonstandard forms for a language element, it’s recommended to use the standard form.

2. Using ordinal positions in the ORDER BY clause is a bad practice. From a relational perspective, you are supposed to refer to attributes by name, and not by ordinal position. Also, what if the SELECT list is revised in the future and the developer forgets to revise the ORDER BY list accordingly?

3. When the query doesn’t have an ORDER BY clause, there are no assurances for any particular order in the result. The order should be considered arbitrary. You also notice that the interviewer used the incorrect term record instead of row. You might want to mention something about this, because the interviewer may have done so on purpose to test you.

4. From a pure relational perspective, this actually could be valid, and perhaps even recommended. But from a practical perspective, there is the chance that SQL Server will try to remove duplicates even when there are none, and this will incur extra cost. Therefore, it is recommended that you add the DISTINCT clause only when duplicates are possible in the result and you’re not supposed to return the duplicates.