Chapter 1 - Foundations of Querying

Question 1

T-SQL supports two “not equal to” operators. What are they? Which one is standard?

Answer 1

<> and !=

<> is considered standard (part of the ISO/ANSI SQL standard).

Question 2

Between the CAST and CONVERT functions, which is considered standard? What is the primary difference between the two?

Answer 2

CAST is a SQL standard to convert a source value to a target type.

The difference between the two, is the CONVERT function has a style argument that CAST does not.

Question 3

TRUE or FALSE

Standard SQL uses a semi-colon to terminate statements.

Answer 3

TRUE

T-SQL does not make this a requirement for all statements, only in cases where there would otherwise be ambiguity of code elements (e.g. the WITH clause of a common table expression).

Question 4

SQL attempts to represent a relation with a _______.

Attributes are represented as _______.

Tuples are represented as _______.

Answer 4

table

columns

rows

Question 5

What are the mathematical branches that the relational model is based on?

Answer 5

Set Theory and Predicate Logic

Question 6

What is the difference between T-SQL and SQL?

Answer 6

SQL is standard; T-SQL is a dialect of and extension to SQL that Microsoft implements in SQL Server.

Question 7

If a set contains duplicates, what is it called?

Answer 7

Multiset or Superset

Question 8

What is the downside of referring to a column by ordinal position in the ORDER BY clause?

Answer 8

There is a potential for error at some point if you decide to change the SELECT list and forget to change the ORDER BY list accordingly.

Therefore, the recommendation is to always indicate the names of the attributes that you need to order by.

Question 9

How do you assign an alias to a column name?

Answer 9

Use the AS keyword to assign an alias.

Question 10

A missing or unknown value is represented by a ______.

Answer 10

NULL

Question 11

Name two aspects in which T-SQL deviates from the relational model.

Answer 11

1. A relation has a body with a distinct set of tuples. While T-SQL allows tables to not have a key and allows for duplicates.
2. T-SQL allows referring to ordinal positions of columns in the ORDER BY clause.

Question 12

Why are the terms “field” and “record” incorrect when referring to column and row?

Answer 12

Because “field” and “record” describe physical things, whereas columns and rows are logical elements of a table.

Question 13

Why is the term “NULL value” incorrect?

Answer 13

Because NULL isn’t a value; rather, it’s a mark for a missing value.

Question 14

What non-relational elements are allowed in T-SQL queries?

Answer 14

T-SQL allows for:

1. Duplicate values
2. No Column Names
3. Ordering results
4. Using ordinal positions in the ORDER BY clause

Question 15

Why is it important to use standard SQL code when possible and know what is standard and what isn’t? (Choose all that apply.)

A. It is not important to code using standard SQL.
B. Standard SQL code is more portable between platforms.
C. Standard SQL code is more efficient.
D. Knowing what standard SQL code is makes your knowledge more portable.

Answer 15

B and D

Question 16

Which of the following is not a violation of the relational model?

A. Using ordinal positions for columns
B. Returning duplicate rows
C. Not defining a key in a table
D. Ensuring that all attributes in the result of a query have names

Answer 16

D

Question 17

What is the relationship between SQL and T-SQL?

A. T-SQL is the standard language and SQL is the dialect in Microsoft SQL Server.
B. SQL is the standard language and T-SQL is the dialect in Microsoft SQL Server.
C. Both SQL and T-SQL are standard languages.
D. Both SQL and T-SQL are dialects in Microsoft SQL Server.

Answer 17

B

Question 18

If an ORDER BY clause is specified, is the result still considered relational?

Answer 18

No.

Question 19

What is the keyed-in order for a SELECT statement?

Answer 19

1. SELECT
2. FROM
3. WHERE
4. GROUP BY
5. HAVING
6. ORDER BY

Question 20

What is the logical query processing order for a SELECT statement?

Answer 20

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Question 21

Can you refer to a column alias in the WHERE clause.

Answer 21

No, because the WHERE clause is evaluated before the SELECT clause in logical query processing.

Question 22

What is the difference between the WHERE clause and the HAVING clause?

Answer 22

The WHERE clause is evaluated before rows are grouped, and therefore is evaluated per row. The HAVING clause is evaluated after rows are grouped, and therefore is evaluated per group.

Question 23

Why are you not allowed to refer to a column alias defined by the SELECT clause in the WHERE clause?

Answer 23

Because the WHERE clause is logically evaluated in a phase earlier to the one that evaluates the SELECT clause.

Question 24

Why are you not allowed to refer to a column alias defined by the SELECT clause in the same SELECT clause?

Answer 24

Because all expressions that appear in the same logical query processing phase are evaluated conceptually at the same point in time.

Question 25

Once an ORDER BY clause is added to a SELECT statement, standard SQL calls the results a ________.

Answer 25

Cursor

Question 26

Which of the following correctly represents the logical query processing order of the various query clauses?

1. SELECT>FROM>WHERE>GROUP BY>HAVING>ORDER BY
2. FROM>WHERE>GROUP BY>HAVING>SELECT>ORDER BY
3. FROM>WHERE>GROUP BY>HAVING>ORDER BY>SELECT
4. SELECT>ORDER BY>FROM>WHERE>GROUP BY>HAVING

Answer 26

2.

1. FROM
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. ORDER BY

Question 27

Which of the following is invalid? (Choose all that apply)

1. Referring to an attribute that you group by in the WHERE clause
2. Referring to an expression in the GROUP BY clause; for example, GROUP BY YEAR(orderdate)
3. In a grouped query, referring in the SELECT list to an attribute that is not part of the GROUP BY list and not within an aggregate function
4. Referring to an alias defined in the SELECT clause in the HAVING clause

Answer 27

3, 4.

Question 28

What is true about the result of a query without an ORDER BY clause?

1. It is relational as long as other relational requirements are met.
2. It cannot have duplicates.
3. The order of the rows in the output is guaranteed to be the same as the insertion order.
4. The order of the rows in the output is guaranteed to be the same as that of the clustered index.

Answer 28

1.

Question 29

If a query doesn’t have an ORDER BY clause, what is the order in which the records are returned?

Answer 29

There is no order applied.

The results are returned arbitrarily based on optimization within the database engine or indexing. Once these change (for example in a new version of SQL server) it is very likely that the order will change.