Chapter 1 : Foundation of Querying

Question 1

Predicate Logic

Answer 1

• A predicate is an expression that when attributed to some object, makes a proposition either true or false.

Question 2

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

Answer 2

Set theory and predicate logic

Question 3

What is the difference between T-SQL and SQL?

Answer 3

SQL is standard; T-SQL is the dialect of and extension to SQL that Microsoft implements in its RDBMS - SQL Server

Question 4

Multiset

Answer 4

A multiset (also known as a bag or a superset) in many respects is similar to a set, but can have duplicates.

Question 5

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

Answer 5

A relation has a body with a distinct set of tuples. A table doesn’t have to have a key. T-SQL allows referring to ordinal positions of columns in the ORDER BY clause.

Question 6

Explain how you can address the two items in question 1 and use T-SQL in a relational way.

Answer 6

Define a key in every table. Refer to attribute names - not their ordinal position - in the ORDER BY clause.

Question 7

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

Answer 7

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

Question 8

Why is the term “Null value” incorrect?

Answer 8

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

Question 9

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 9

It is not important to code using standard SQL. F
§ It is important to use standard code.

Standard SQL code is more portable between platforms. C
§ Use of standard code makes it easier to port code between platforms because fewer revision are required.

Standard SQL code is more efficient.
§ There’s no assurance that standard code will be more efficient.

Knowing what standard SQL code is makes your knowledge more portable C
§ When using standard code, you can adapt to a new environment more easily because standard code elements look similar in the different platforms.

Question 10

Which of the following is not a violation of the relational model?
A) Using ordinal position 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 10

Using ordinal position for columns. F
§ A relation has a header with a set of attributes, and tuples of the relation have the same heading. A set has no order, or ordinal positions do not have meaning and constitute a violation of the relational model. You should refer to attributes by their name.

Returning duplicate rows. F
§ 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.

Not defining a key in a table. F
§ Not defining a key in the table allows duplicate rows in the table, and like the answer to B, what’s a violation of the relational model.

Ensuring that all attributes in the result of a query have names. C
§ Because attributes are supposed to be identified by name, ensuring that all attributes have name is relational, and hence not a violation of the relational model.

Question 11

What is the relationship between SQL and T-SQL?
A) T-SQL is the standard language and SQL is the dialect of Microsoft SQL Server.
B) SQL is the standard language and T-SQL is the dialect in Microsoft SQL Server. C
C) Both SQL and T-SQL are standard languages.
D) Both SQL and T-SQL are dialects in Microsoft SQL Server.

Answer 11

T-SQL is the standard language and SQL is the dialect of Microsoft SQL Server. F
§ T-SQL isn’t standard and SQL isn’t a dialect in Microsoft SQL Server.

SQL is the standard language and T-SQL is the dialect in Microsoft SQL Server. C
§ SQL is standard and T-SQL is a dialect in Microsoft SQL Server.

Both SQL and T-SQL are standard languages. F
§ T-SQL isn’t standard.
Both SQL and T-SQL are dialects in Microsoft SQL Server. F
§ SQL isn’t a dialect in Microsoft SQL Server.

Question 12

Name all the steps of SQL keyed in order

Answer 12

SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY

Question 13

Name all the steps in the logical query process

Answer 13

FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY

Question 14

What is the difference between WHERE and HAVING clause?

Answer 14

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 15

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

Answer 15

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

Question 16

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

Answer 16

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

Question 17

What does declarative language mean when applied to SQL Server?

Answer 17

T-SQL was designed as a declarative language where the instructions are provided in an English-like manner. Therefore, the keyed-in order of the query clauses starts with the SELECT clause.

Question 18

Which of the following correctly represents the logical query process order of the various query clauses?
A) SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY
B) FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY
C FROM > WHERE > GROUP BY > HAVING > ORDER BY > SELECT
D) SELECT > ORDER BY > FROM > WHERE > GROUP BY > HAVING

Answer 18

A) SELECT > FROM > WHERE > GROUP BY > HAVING > ORDER BY
§ Logical query processing doesn’t start with the SELECT clause.
B) FROM > WHERE > GROUP BY > HAVING > SELECT > ORDER BY C
§ Logical query processing starts with the FROM clause, and then moves to WHERE, GROUP BY, HAVING, SELECT, and ORDER BY.
C) FROM > WHERE > GROUP BY > HAVING > ORDER BY > SELECT
§ The ORDER BY clause isn’t evaluated before the SELECT clause.
D) SELECT > ORDER BY > FROM > WHERE > GROUP BY > HAVING
§ Logical query processing doesn’t start with the SELECT clause.

Question 19

Which of the following is invalid (Choose all that apply.)
A) Referring to an attribute that you group by in the WHERE clause.
B) Referring to an expression in the GROUP BY clause; for example, GROUP BY YEAR(orderdate).
C) 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.
D) Referring to an alias defined in the SELECT clause in the HAVING clause.

Answer 19

A) Referring to an attribute that you group by in the WHERE clause.
§ T-SQL allows you to refer to an attribute that you group by in the WHERE clause.
B) Referring to an expression in the GROUP BY clause; for example, GROUP BY YEAR(orderdate).
§ T-SQL allows grouping by an expression.
C) 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. C
§ 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.
D) Referring to an alias defined in the SELECT clause in the HAVING clause. C
§ 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.

Question 20

What is true about the result of a query without an ORDER BY clause?
A) It is relational as long as other relation requirements are met.
B) It cannot have duplicates.
C) The order of the rows in the output is guaranteed to be the same as the insertion order.
D) The order of the rows in the output is guaranteed to be the same as that of the clustered index.

Answer 20

A) It is relational as long as other relation requirements are met. C
§ 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 a ORDER BY clause, all attributes must have names, all attribute names must be unique, and duplicates must not appear in the results.
B) It cannot have duplicates.
§ A query without a DISTINCT clause in the SELECT clause can return duplicates.
C) The order of the rows in the output is guaranteed to be the same as the insertion order.
§ A query without an ORDER BY clause does not guarantee the order of rows in the output.
D) The order of the rows in the output is guaranteed to be the same as that of the clustered index.
§ A query without an ORDER BY clause does not guarantee the order of rows in the output.