Chapter 1. Manage data with Transact - SQL

Question 1

What are the mathematics roots that originated the relational model?

Answer 1

Set theory and Predicate Logic.

Question 2

How SQL attempts to represent a relation?

Answer 2

With a table.

Question 3

What does have a relation in the relational model?

Answer 3

A relation has a heading and a body. A heading is a set of attributes, each attribute has a name and a type. A body is a set of tuples, and each tuple’s heading is the relation’s heading. SQL represents the set of attributes -the heading- as columns, and the set of tuples -the body- as rows.

Question 4

What is a set?

Answer 4

A set should be considered as a whole. This means that we don’t interact with individual elements of the set, rather with the set as a whole.

Question 5

What’s a predicate?

Answer 5

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

Question 6

How a result can be relational?

Answer 6

The order needs to be arbitrary, this mean there’s not an order specified. The result set shouldn’t has duplicates. All columns or expression needs to be named, and each name needs to be unique.

Question 7

How a column can be aliased?

Answer 7

By using the keyword “AS” after the column or an expression. For example:

SELECT col1, col2 AS Columna, col3
FROM SampleTable;

Question 8

What means three valued predicate logic?

Answer 8

It means a predicate can be either true, false or unknown. It’s unknown when at least one of the operand has a missing value (NULL in SQL).

Question 9

What’s the difference between called row as record, and column as field?

Answer 9

Fields and records are physical. Fields are what you have in user interfaces in client applications, and records are what you have in files and cursors. Tables are logical, and they have logical rows and columns.

Question 10

What’s NULL?

Answer 10

It’s a mark that represent missing values.

Question 11

What’s the keyed-in-order of a query?

Answer 11

It’s the order in which the clauses of a query should be written:

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

Question 12

What’s the logical query processing?

Answer 12

It’s how logically a query is processed:

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 13

What’s the difference between the WHERE clause and the HAVING clause?

Answer 13

The WHERE it’s evaluated before the rows are grouped, so it evaluates the predicate per row. The HAVING clause it’s evaluated after the rows are grouped, so it evaluates the predicate per group.

Question 14

Why an alias created in the SELECT clause doesn’t wok in the FROM clause?

Answer 14

Because the FROM clause it’s evaluated before the SELECT clause, so the alias created in the SELECT clause isn’t yet created at the FROM clause.

Question 15

What is the difference between T-SQL and SQL?

Answer 15

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

Question 16

What are the properties of the relational model?

Answer 16

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

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

Answer 17

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

Question 18

What does do the UNION operator?

Answer 18

It unify two result sets into one.

Question 19

What are the set operators availables in SQL Server?

Answer 19

UNION (UNION ALL, which is an extend of UNION operator), INTERSECT and EXCEPT.

Question 20

What are the requirements to use a set operator in SQL Server?

Answer 20

(1) The two queries needs to have the same number of columns. In case there’s a mismatch between columns number, then NULL needs to be used as placeholder.
(2) The data types of columns from query A, for example, needs to be the same (or at least compatible) with the data types of columns from query B.

Question 21

What are some constraint for UNION operator?

Answer 21

(1) Comparing NULL against NULL returns true (isn’t like WHERE clase which returns unknown and get discarted). But NULL against a value returns false. This mean all rows from one operand will be comparated to all rows from the other operand.
(2) An ORDER BY clause can be added, but it needs to be on the end of the last query, so individual ORDER BY aren’t allowed for each query.
(3) The columns names of result columns are determined by the first query.

Question 22

What’s the difference between UNION and UNION ALL?

Answer 22

UNION doesn’t return duplicates, whereas UNION ALL does.

Question 23

Do UNION and UNION ALL produce the same query plan?

Answer 23

No, they’ don’t. UNION has an implicity DISTINCT operator once unifies the result sets. Remember UNION doesn’t allow duplicates, whereas UNION ALL does.

Question 24

What does do the INTERSECT operator?

Answer 24

It returns the rows that are common to both sets. But in case a row appears twice in a set and just once in the other set, it doesn’t mean it’ll appear three times in the result set produced by the INTERSECT operator, just one. This is because if a row appears in both set, no matter how many times appear in both set, it’ll be returned.

Question 25

What is the syntax to use a set operator?

Answer 25

[ORDER BY ];

::= {UNION [ALL] | INTERSECT | EXCEPT}

Question 26

What does do the EXCEPT operator?

Answer 26

It returns distinct rows that appear in the first set but not in the second. This mean if a row appears at least once in the first result set, and doesn’t appear in the second result set, it’ll be returned only once in the result set produced by the EXCEPT operator.

Question 27

Does the order of the input queries affect the result set produced by the set operator?

Answer 27

With UNION and INTERSECT, the order of the input
queries doesn’t matter. However, with EXCEPT, there’s different meaning to:

EXCEPT
EXCEPT

Question 28

Does exist precedence between set operators?

Answer 28

Yes, it does. Finally, set operators have precedence: INTERSECT precedes UNION and EXCEPT, and UNION and EXCEPT are evaluated from left to right based on their position in the expression.

Question 29

What’s a cross join?

Answer 29

This join performs what’s known as a Cartesian Product. In other words, it performs a multiplication between two tables: yielding a row for each combination of rows from both sides. After all, it produces a m x n virtual table, where m is the row’s quantity from a table, and n is the row’s wquantity from the other table.

Question 30

What’s an inner join?

Answer 30

An inner join let you match two tables according to a predicate, usually comparing primary’s key from a table against a foreign key from another table which actually references the primary’s key of the former table.

Question 31

What’s an equijoin?

Answer 31

It’s simply an inner join where his predicate is based on equal operator, or has this form: lefttable.column = righttable.column.