Chapter 2 : Getting Started with the SELECT Statement

Question 1

What are the forms of aliasing an attribute in T-SQL?

Answer 1

The forms are AS , , and = .

Question 2

What is an irregular identifier?

Answer 2

An identifier that does not follow the rules for formatting identifiers; for example, it starts with a digit, has an embedded space, or is reserved T-SQL keyword.

Question 3

What is the importance of the ability to assign attribute aliases in T-SQL? (Choose all that apply.)
A) The ability to assign attribute aliases is just an aesthetic feature.
B) An expression that is based on computation results in no attribute names unless you assign one with an alias, and this is not relational. C
C) T-SQL requires all result attributes of a query to have names.
D) Using attribute aliases, you can assign your own name to a result attribute if you need it to be different than the source attribute name. C

Answer 3

A) The ability to assign attribute aliases is just an aesthetic feature.
§ Attribute aliasing allows you to meet relational requirements, so it’s certainly more than an aesthetic.
B) An expression that is based on computation results in no attribute names unless you assign one with an alias, and this is not relational. C
§ The relational model requires that all attributes have names.
C) T-SQL requires all result attributes of a query to have names.
§ T-SQL allows a result attribute to be without a name when the expression is based on a computation without an alias.
D) Using attribute aliases, you can assign your own name to a result attribute if you need it to be different than the source attribute name. C
§ You can assign your own name to a result attribute by using an alias.

Question 4

What are the mandatory clauses in a SELECT query, according to T-SQL
A) The FROM AND SELECT clause
B) The SELECT and WHERE clause
C) The SELECT clause 
D) The FROM and WHERE clause

Answer 4

A) The FROM AND SELECT clause
§ The FROM and SELECT clause are mandatory in a SELECT query according to Standard SQL but not T-SQL.
B) The SELECT and WHERE clause
§ The WHERE clause is optional in T-SQL.
C) The SELECT clause C
§ According to T-SQL, the only mandatory clause is the SELECT clause.
D) The FROM and WHERE clause
§ The FROM and WHERE clause are both optional in T-SQL

Question 5

Which of the following practices are considered bad practices? (Choose all that apply.)
A) Aliasing columns by using the AS clause
B) Aliasing tables by using the AS clause
C) Not assigning column aliases when the column is a result of a computation
D) Using * in the SELECT list

Answer 5

A) Aliasing columns by using the AS clause
§ Aliasing columns with the AS clause is standard and considered a best practice.
B) Aliasing tables by using the AS clause
§ Aliasing tables with the AS clause is standard and considered a best practice.
C) Not assigning column aliases when the column is a result of a computation C
§ Not aliasing a column that is a result of a computation is nonrelational and is considered a bad practice.
D) Using * in the SELECT list C
§ Using * in the SELECT list is considered a bad practice.

Question 6

The identity column property

Answer 6

A property that automatically generates keys in an attribute of a numeric type with a scale of 0; namely, any integer type (TINYINT, SMALLINT, INT, BIGINT) or NUMERIC/DECIMAL with a scale of 0.

Question 7

The sequence object

Answer 7

An independent object in the database from which you can obtain new sequence values. Like identity, it supports any numeric type with a scale of 0. Unlike identity, it’s not tied to a particular column; instead, as mentioned, it is an independent object in the database. You can also request a new value from a sequence object before using it. There are a number of other advantages over identity that will be covered in Chapter 11.

Question 8

Non sequential GUIDs

Answer 8

You can generate non sequential global unique identifiers to be stored in an attribute of a UNIQUEIDENTIFIER type. You can use the T-SQL function NEWID to generate a new GUILD, possibly invoking it with a default expression attached to the column. You can also generate one from anywhere - for example, the client - by using an application programming interface (API) that generates a new GUID. The GUIDs are guaranteed to be unique across space and time.

Question 9

Sequential GUIDs

Answer 9

You can generate sequential GUIDs within the machine by using the T-SQL function NEWSEQUENTIALID.

Question 10

Custom solutions

Answer 10

If you do not want to use the built-in tools that SQL Server provides to generate keys, you need to develop your own custom solution. The data type for the key depends on your solution.

Question 11

Would you use the type FLOAT to represent a product unit price?

Answer 11

No, because FLOAT is an approximate date type and cannot represent all values precisely.

Question 12

What is the difference between NEWID and NEWSEQUENTIALID?

Answer 12

The NEWID function generates GUID values in random order, whereas the NEWSEQUENTIAL ID function generates GUILDs that increase in a sequential order.

Question 13

Which function returns the current date and time values as a DATETIME2 type?

Answer 13

The SYSDATETIME functions

Question 14

When concatenating character strings, what is the difference between the plus (+) operator and the CONCAT function?

Answer 14

The + operator by default yields a NULL result on NULL input, whereas the CONCAT function treats NULLs as empty strings.

Question 15

Why is it important to use the appropriate type of attributes?
A) Because the type of your attribute enables you to control the formatting of the values.
B) Because the type constrains the values to a certain domain of supported values
C) Because the type prevents duplicates.
D) Because the type prevents NULLs.

Answer 15

Why is it important to use the appropriate type of attributes?
A) Because the type of your attribute enables you to control the formatting of the values.
§ Formatting isn’t a responsibility of the type or the data layer in general; rather, it is the responsibility of the presentation layer.
B) Because the type constrains the values to a certain domain of supported values C.
§ The type should be considered a constraint because it limits the values allowed.
C) Because the type prevents duplicates.
§ The type itself doesn’t prevent duplicates. If you need to prevent duplicates, you use the primary key or unique constraint.
D) Because the type prevents NULLs.
§ A type doesn’t prevent NULLs. For this, you use a NOT NULL constraint.

Question 16

Which of the following functions would you consider using to generate surrogate keys? (Choose all that apply.)
A) NEWID
B) NEWSEQUENTIALID
C) GETDATE
D) CURRENT_TIMESTAMP

Answer 16

Which of the following functions would you consider using to generate surrogate keys? (Choose all that apply.)
A) NEWID C
§ The NEWID function creates GUIDs in random order. You would consider it when the size overhead is not a major issue and the ability to generate a unique value across time and space, from anywhere, in random order is a high priority.
B) NEWSEQUENTIALID C
§ The NEWSEQUENTIALID function generates GUIDs in increasing order within the machine. It helps reduce fragmentation and works well when a single session loads the data, and the number of drives is small. However, you should carefully consider an alternative using another key generator, like a sequence object, with a smaller type when possible.
C) GETDATE
§ There’s no assurance that GETDATE will generate unique values; therefore, it’s not a good choice to generate keys.
D) CURRENT_TIMESTAMP
§ The CURRENT_TIMESTAMP function is simply the standard version of GETDATE, so it also doesn’t guarantee uniqueness.

Question 17

What is the difference between the simple CASE expression and the searched CASE expression?
A) The simple CASE expression is used when the database recovery model is simple, and the searched CASE expression is used when it’s full or bulk logged.
B) The simple CASE expression compares an input expression to multiple possible expressions in the WHEN clauses, and the search CASE expression uses independent predicates in the WHEN clauses. C
C) The simple CASE expression can be used anywhere in a query, and the searched CASE expression can be used only in the WHERE clause.
DThe simple CASE expression can be used anywhere in a query, and the searched CASE expression can be used only in query filters (ON, WHERE, HAVING).

Answer 17

What is the difference between the simple CASE expression and the searched CASE expression?
A) The simple CASE expression is used when the database recovery model is simple, and the searched CASE expression is used when it’s full or bulk logged.
§ CASE expressions have nothing to do with the database recovery model.
B)The simple CASE expression compares an input expression to multiple possible expressions in the WHEN clauses, and the search CASE expression uses independent predicates in the WHEN clauses. C
§ The difference between the two is that the simple form compares expressions and the search form uses predicates.
C)The simple CASE expression can be used anywhere in a query, and the searched CASE expression can be used only in the WHERE clause.
§ Both CASE expressions are allowed wherever a scalar expression is allowed - anywhere in the query.
D) The simple CASE expression can be used anywhere in a query, and the searched CASE expression can be used only in query filters (ON, WHERE, HAVING).
Both Case expressions are allowed whenever a scalar expression is allowed - anywhere in the query.