Chapter 1 Foundations Of Querying Flashcards
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.