Chapter 1 Foundations Of Querying

Question 1

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

Answer 1

Set theory and predicate logic

Question 2

What is the difference between T-SQL and SQL?

Answer 2

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

Question 3

What is a predicate?

Answer 3

A predicate is an expression that when attributed to some object, makes a proposition true or false. e.g. “salary greater than $50k”. Used for enforcing data integrity, filtering, etc.

Question 4

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

Answer 4

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

Question 5

Why is the term “null value” incorrect?

Answer 5

Because null isn’t a value; rather, its a mark for a missing value.

Question 6

What is a relation?

Answer 6

A relation has a heading and a body. The heading is a set of attributes (columns). An attribute is identified by name and type. The body is a set of tuples (rows). Each tuple’s heading is the heading of the relation. The value of each tuple’s attribute is of its respective type.

Question 7

What are the properties of a relational model?

Answer 7

(1) A relation should be considered as a whole - don’t work with one row at a time.
(2) A relation should not have duplicates.
(3) A relation should have no relevance to the order. (4) All attributes must have names.
(5) All attribute names must be unique.

Question 8

What are the main query clauses in the “keyed-in” order?

Answer 8

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

Question 9

What are the main query clauses in logical query processing order?

Answer 9

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

Question 10

What is the FROM clause?

Answer 10

Indicates the tables you want to query and table operators like joins if applicable.

Question 11

What is the WHERE clause?

Answer 11

Filters rows based on a predicate. Only rows for which the predicate evaluates to true are returned. Rows for which the predicate evaluates to false or an unknown state are not returned.

Question 12

What is the GROUP BY clause?

Answer 12

Defines a group for each distinct combination of values from the input tables columns in the GROUP BY list. All expressions processed in subsequent phases must guarantee a single value per group. Column references must be in the GROUP BY list or they must be contained within an aggregate function like MAX or SUM.

Question 13

What is the HAVING clause?

Answer 13

This phase is responsible for filtering data based on a predicate, but it is evaluated after the data has been grouped. “HAVING COUNT(*) > 1” means to filter groups where there are multiple detail rows.

Question 14

What is the difference between the WHERE and HAVING clauses?

Answer 14

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

Question 15

What is the SELECT clause?

Answer 15

Evaluates the expressions in the SELECT list and produces the result attributes. Also, assigns attributes with names if they are aliased. Also, allows you to use a distinct clause to remove duplicates.

Question 16

Why isn’t an alias created by the SELECT phase not visible to other expressions that appear in the same SELECT list?

Answer 16

T-SQL evaluates all expressions that appear in the same logical query processing phase in an all at once manner.

Question 17

What is the ORDER BY clause?

Answer 17

Returns the result in a specific presentation order according to the expressions that appear in the ORDER BY list. ORDER BY is the first and only clause that is allowed to refer to column aliases defined in the select clause. Output of this phase is not relational.

Question 18

How does SQL allow you to deviate from each property of the relational model?

Answer 18

(1) SQL has provisions in place that allow you to work with one row at a time, e.g. cursors, loops, etc. (2) SQL allows you to create a table without a unique key. (3) SQL gives you an order by clause. (4) Result columns based on expressions don’t require an alias (5) SQL allows a query to return multiple result columns with the same name.

Question 19

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

Answer 19

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

Question 20

Why is it important to use standard SQL code when possible and know what is standard and what isn’t?

Answer 20

Use of standard code makes it easier to port code between platforms because fewer revisions are required. Also, your knowledge is more portable too because it is easy for your to start working with new platforms.

When the dialect you’re working with supports both a standard and nonstandard way to do something, you should always prefer the standard form as your default choice. You should consider the nonstandard option only when it has some important benefit to you that is not covered by the standard, e.g. CAST vs CONVERT (w/ style argument).

Question 21

Does T-SQL keep track of the ordinal positions of columns?

Answer 21

Yes. T-SQL keeps track of the ordinal positions of columns based on their order of appearance in the table definition. When you issue a query with SELECT *, you are guaranteed to get the columns in the result based on the definition order.

Also, T-SQL allows referring to ordinal positions of columns from the result in the ORDER BY clause:

SELECT empid, lastname
FROM HR.Employees
ORDER BY 1;

Note that this is not relational.

Question 22

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

Answer 22

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.