Chapter 1: Foundations of Querying

Question 1

How SQL Server attempts to represent a relation?

Answer 1

With a table. A relation has a heading and a body. A heading is a set of attributes (a set of columns in SQL Server), each one of given type. An attribute is identified by a name and a type. A body is a set of tuples (a set of rows in SQL Server). Each attribute of a tuple correspond to an attribute of the heading.

Question 2

What math areas are based SQL?

Answer 2

In set theory and predicate logic.

Question 3

What is the difference between T-SQL and SQL?

Answer 3

SQL is a standard, while T-SQL is a dialect of SQL.

Question 4

What are the properties of a relation model?

Answer 4

(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 5

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

Answer 5

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

Question 6

Why is the term “NULL value” incorrect?

Answer 6

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

Question 7

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

Answer 7

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

Question 8

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

Answer 8

1. FROM
2. ON
3. OUTER
4. WHERE
5. GROUP BY
6. CUBE | ROLLUP
7. HAVING
8. SELECT
9. DISTINCT
   10 ORDER BY
10. TOP

Question 9

What is the difference between the WHERE and HAVING clauses?

Answer 9

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 10

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

Answer 10

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

Question 11

What is the GROUP BY Clause?

Answer 11

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 12

What is the HAVING clause?

Answer 12

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.