Flashcards in Chapter 1 Foundations Of Querying Deck (22)
What are the mathematical branches that the relational model is based on?
Set theory and predicate logic
What is the difference between T-SQL and SQL?
SQL is standard; T-SQL is the dialect of and extension to SQL that Microsoft implements in its RDBMS - SQL server.
What is a predicate?
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.
Why are the terms "field" and "record" incorrect when referring to a column and row?
Because "field" and "record" describe physical things whereas columns and rows are logical elements of a table.
Why is the term "null value" incorrect?
Because null isn't a value; rather, its a mark for a missing value.
What is a relation?
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.
What are the properties of a relational model?
(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.
What are the main query clauses in the "keyed-in" order?
(1) SELECT (2) FROM (3) WHERE (4) GROUP BY (5) HAVING (6) ORDER BY
What are the main query clauses in logical query processing order?
(1) FROM (2) WHERE (3) GROUP BY (4) HAVING (5) SELECT (6) ORDER BY
What is the FROM clause?
Indicates the tables you want to query and table operators like joins if applicable.
What is the WHERE clause?
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.
What is the GROUP BY clause?
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.
What is the HAVING clause?
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.
What is the difference between the WHERE and HAVING clauses?
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.
What is the SELECT clause?
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.
Why isn't an alias created by the SELECT phase not visible to other expressions that appear in the same SELECT list?
T-SQL evaluates all expressions that appear in the same logical query processing phase in an all at once manner.
What is the ORDER BY clause?
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.
How does SQL allow you to deviate from each property of the relational model?
(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.
Why are you not allowed to refer to a column alias defined by the SELECT clause in the WHERE clause?
Because the WHERE clause is evaluated in a phase earlier to the one that evaluates the SELECT clause.
Why is it important to use standard SQL code when possible and know what is standard and what isn't?
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).
Does T-SQL keep track of the ordinal positions of columns?
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
ORDER BY 1;
Note that this is not relational.