Foundations of Querying

Question 1

Writing in a __________ way is considered a best practice.

Answer 1

standard

Question 2

True or False: You should consider a nonstandard option only when it has some important benefit to you that is not covered by the standard alternative.

Answer 2

True

Question 3

Standard SQL is based on the _________ ________, which is a mathematical model for data management and manipulation.

Answer 3

relational model

Question 4

Define: Relational Model

Answer 4

A mathematical model for data management and manipulation.

Question 5

Who created and proposed the relational model?

Answer 5

Edgar F. Codd in 1969

Question 6

A relation in the relational model is what SQL calls a _____.

Answer 6

table

Question 7

What does a relation have?

Answer 7

A heading and a body.

Question 8

Define: Heading

Answer 8

A set of attributes (what SQL attempts to represent with columns), each of a given type.

Question 9

Define: Attribute

Answer 9

An attribute is identified by name and type name.

Question 10

Define: Body

Answer 10

A set of tuples (what SQL attempts to represent with rows). Each tuple’s heading is the heading of the relation. Each value of each tuple’s attribute is of its respective type.

Question 11

Define: Set

Answer 11

A set is any collection M into a whole of definite, distinct objects m (which are called the elements of M) of our perception or of our thought.

Question 12

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

Answer 12

predicate.

Question 13

True or False: A predicate is a parameterized proposition.

Answer 13

True.

Question 14

List the conditions that must be true in order for a set to be relational.

Answer 14

1) Must not have duplicates
2) No relevance to the order of the elements
3) All attributes must have names
4) All attributes must have unique names

Question 15

SQL implements one general purpose mark called ____ for any kind of missing value.

Answer 15

NULL

Question 16

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

Answer 16

Set theory and predicate logic.

Question 17

What is the difference between T-SQL and SQL?

Answer 17

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

Question 18

Multiple Choice: 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 18

B and D

Question 19

Multiple Choice: 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 19

D

Question 20

Multiple Choice: 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 20

B

Question 21

Define: Logical Query Processing

Answer 21

The conceptual interpretation of the query that defines the correct result

Question 22

True or False: It is important to draw performance-related conclusions from logical query processing.

Answer 22

False

Question 23

Define: What is the “keyed-in order” of the main T-SQL query clauses?

Answer 23

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

Question 24

Define: What is the logical query processing order of the six main query clauses?

Answer 24

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

Question 25

This logical query processing clause indicates the tables you want to query and table operators like joins if applicable.

Answer 25

FROM

Question 26

This phase filters rows based on predicates.

Answer 26

WHERE

Question 27

Rows for which the predicate evaluate to _____, or evaluates to an unknown state, are not returned.

Answer 27

false

Question 28

Is it allowed to refer to an alias defined in the SELECT clause in a WHERE clause?

Answer 28

No. This is not allowed because the WHERE clause is evaluated before the SELECT clause.

Question 29

What is the function of the GROUP BY clause?

Answer 29

The GROUP BY clause defines a group for each distinct combination of values in the grouped elements from the input tables.

Question 30

If you want to refer to an element that is not in the GROUP BYU list, it must be contained within an _________ ________.

Answer 30

aggregate function (MAX, SUM, etc.)

Question 31

______ clause is responsible for filtering data based on a predicate, but is evaluated after the data has been grouped.

Answer 31

HAVING

Question 32

What is the difference between the WHERE and HAVING clause?

Answer 32

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

Question 33

What is the function of the SELECT clause?

Answer 33

The SELECT clause evaluates any expressions in the SELECT list and produces the result set. It also assigns attributes with names if they are derived from expressions.

Question 34

What clause removes duplicate values from a query?

Answer 34

DISTINCT

Question 35

TRUE or FALSE? The fifth phase of logical query processing returns a relational set.

Answer 35

True. Assuming all other conditions have been met, the ORDER BY clause has not been evaluated yet, meaning the rows are in no particular order.

Question 36

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

Answer 36

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

Question 37

This clause is responsible for returning the result set in a specific presentation order according to expressions.

Answer 37

ORDER BY

Question 38

Multiple Choice: Which of the following correctly represents the logical query processing 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 38

B.

Question 39

Multiple Choice: Which of the following is invalid? (Choose any 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 aggregated function.
D. Referring to an alias defined in the SELECT clause in the HAVING clause

Answer 39

C and D.

Question 40

Multiple Choice: What is true about the result set of a query without an ORDER BY clause?
A. It is relational as long as other relational 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 40

A