Chapter 1 - Foundations of Querying Flashcards
T-SQL supports two “not equal to” operators. What are they? Which one is standard?
<> and !=
<> is considered standard (part of the ISO/ANSI SQL standard).
Between the CAST and CONVERT functions, which is considered standard? What is the primary difference between the two?
CAST is a SQL standard to convert a source value to a target type.
The difference between the two, is the CONVERT function has a style argument that CAST does not.
TRUE or FALSE
Standard SQL uses a semi-colon to terminate statements.
TRUE
T-SQL does not make this a requirement for all statements, only in cases where there would otherwise be ambiguity of code elements (e.g. the WITH clause of a common table expression).
SQL attempts to represent a relation with a _______.
Attributes are represented as _______.
Tuples are represented as _______.
table
columns
rows
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 a dialect of and extension to SQL that Microsoft implements in SQL Server.
If a set contains duplicates, what is it called?
Multiset or Superset
What is the downside of referring to a column by ordinal position in the ORDER BY clause?
There is a potential for error at some point if you decide to change the SELECT list and forget to change the ORDER BY list accordingly.
Therefore, the recommendation is to always indicate the names of the attributes that you need to order by.
How do you assign an alias to a column name?
Use the AS keyword to assign an alias.
A missing or unknown value is represented by a ______.
NULL
Name two aspects in which T-SQL deviates from the relational model.
- A relation has a body with a distinct set of tuples. While T-SQL allows tables to not have a key and allows for duplicates.
- T-SQL allows referring to ordinal positions of columns in the ORDER BY clause.
Why are the terms “field” and “record” incorrect when referring to 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, it’s a mark for a missing value.
What non-relational elements are allowed in T-SQL queries?
T-SQL allows for:
- Duplicate values
- No Column Names
- Ordering results
- Using ordinal positions in the ORDER BY clause
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.
B and D
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
D
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.
B
If an ORDER BY clause is specified, is the result still considered relational?
No.
What is the keyed-in order for a SELECT statement?
- SELECT
- FROM
- WHERE
- GROUP BY
- HAVING
- ORDER BY
What is the logical query processing order for a SELECT statement?
- FROM
- WHERE
- GROUP BY
- HAVING
- SELECT
- ORDER BY
Can you refer to a column alias in the WHERE clause.
No, because the WHERE clause is evaluated before the SELECT clause in logical query processing.
What is the difference between the WHERE clause and the HAVING clause?
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.
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 logically evaluated in a phase earlier to the one that evaluates the SELECT clause.
Why are you not allowed to refer to a column alias defined by the SELECT clause in the same SELECT clause?
Because all expressions that appear in the same logical query processing phase are evaluated conceptually at the same point in time.
