Chapter 2 Using The From And Select Clauses Flashcards
What is implicit schema name resolution?
T-SQL supports omitting the schema name as in “FROM Employees”. T-SQL will use implicit schema name resolution to look it up if it is not provided. It is considered a best practice to always include the schema name as in “FROM HR.Employees”. Including schema name can prevent you from ending up with a schema name that you did not intend to be used, and can also remove the cost involved in the implicit resolution
process, although this cost is minor.
How do you alias a queried table?
In the FROM clause, you can alias a queried table using the form as in “HR.Employees E” or as as in “HR.Employees AS E”. Using AS is preferred. If you assign an alias to a table, the table is renamed for the duration of the query. The original table name isn’t visible any more, only the alias.
Why is the asterisks a bad practice as an alternative to listing all attributes in the SELECT clause?
You typically only need to return a subset of the attributes. (1) By returning more, you can prevent SQL from using covering indexes in respect to an interesting set of attributes. (2) Also, this results in sending more data over the network. (3) Also, the table definition could change over time. * might have been all the attributes you needed at one point, but it might not be the case if the table changes.
What are the supported forms of column aliases?
AS , , = . The first form with AS is both standard and most readable.
Why would you intentionally alias an column?
(1) When you need the result attribute to be named differently than the source. (2) When you need to assign a name to an attribute that results from an expression that would otherwise be unnamed (creates a relational result). *(Note: T-SQL allows a result attribute to be without a name when the expression is based on a computation without an alias.)
What are the mandatory clauses in a valid T-SQL SELECT query?
T-SQL supports a SELECT query with only a SELECT clause and without a FROM clause, e.g. “SELECT 10 AS col1, ‘abc’ AS col2”. Standard SQL requires both a SELECT and FROM.
How do you delimit identifiers in T-SQL?
T-SQL supports both a standard form using double quotation marks as in “Sales”.”Orders”. T-SQL also supports a proprietary form using square brackets as in [Sales].[Orders].
When do you delimit identifiers in T-SQL?
When the identifier is regular delimiting is optional. Otherwise, the identifier must be delimited, e.g. [2006].
A regular identifier must have a letter in the range A-Z (upper or lower), _, @, or # as the first character. Subsequent characters can include letters, numbers, @, $, #, _. The identifier must not be a reserved keyword in SQL. The identifier must not have spaces.
How large is an int?
4 bytes (-2^31 to 2^31-1)
How large is a tinyint?
1 byte (0 to 255)
How large is a datetime data type?
8 bytes; January 1, 1753, through December 31, 9999 23:59:59.997
How large is a date data type? What is it used for?
3 bytes; used to store just date without time; January 1, 1 A.D. through December 31, 9999
How large is a datetime2 data type? What is it used for?
6 to 8 bytes depending on precision; it’s an extension of the datetime type, January 1, 1 A.D. through December 31, 9999 00:00:00 through 23:59:59.9999999; it has a larger date range, a larger default fractional precision and an optional user-specified precision.
How large is a smalldatetime data type? What is it used for?
4 bytes; used to store dates/times within the following range: January 1, 1900, through June 6, 2079 23:59 (seconds are always 00)
How large is a real data type?
4 bytes
How large is a float data type?
8 bytes
What are the advantages and disadvantages of float and real data types?
The benefit in these types is that they can represent very large and very small numbers beyond what any other numerical type in SQL can represent. The downside is that they are just approximations. Not all values in the data range can be represented exactly. Use them to represent very large or very small numbers for scientific numbers that don’t need complete accuracy. Do not use them for precise values.
What are the differences between fixed types and dynamic types?
Fixed types (char, nchar, binary) use the storage for the indicated size, e.g. char(30) uses storage for 30 characters whether all are used or not. Updates will not require the row to physically expand - no data shifting is required. Good for attributes that get updated frequently as far as performance. Variable types (varchar, nvarchar, varbinary) use storage for what you enter plus a couple of bytes for offset information. Good for widely varying sizes of strings - you can save a lot on storage.
What are the two main roles of the FROM clause?
(1) Where you indicate tables you want to query. (2) Where you can apply table operators like join to input tables.
What are the two main roles of the SELECT clause?
(1) Evaluates expressions that define the attributes in the query’s result - assigning them with aliases if needed. (2) Using a DISTINCT clause, you can eliminate duplicate rows in the result if needed.
What does the USE statement do?
The USE statement (e.g. USE TSQL2012;) ensures that you are connected to the target database.
How large is a smallint?
2 bytes ( -2^15 to 2^15-1)
How large is a bigint?
8 bytes (-2^63 to 2^63-1)
What is physical data independence?
The data type is a property of the value stored in the database, and the internal storage shouldn’t be your concern. The formatting of the value is supposed to be the responsibility of the application when the data is presented.
What’s the difference between regular character types and unicode character types?
Regular character types (CHAR and VARCHAR) use 1 byte per character and support only one language besides English.Unicode character types (NCHAR and NVARCHAR) use 2 bytes of storage per character and support multiple languages.
When using types that can have a length, such as CHAR and VARCHAR, can the length be omitted?
Yes. T-SQL supports omitting the length and then uses the default length; however, the defaults can be different depending on the context, so it’s considered a best practice to always be explicit.
What’s the difference between the conversion functions with try and those without (CAST vs TRY_CAST)?
Those without TRY fail if the value isn’t convertible whereas those with the TRY return a NULL in such as case.
Explain CAST and TRY_CAST.
Indicate the expression and target type: e.g. SELECT CAST(‘abc’ AS INT); or SELECT TRY_CAST(‘abc’ AS INT); CAST is standard SQL.
Explain CONVERT and TRY_CONVERT.
Indicate the expression, target type, and a style for the conversion which is used for some conversions such as between character string and date/time values. e.g. CONVERT(DATE, ‘1/2/2012’, 101) (uses style 101 representing US standard).
Explain PARSE and TRY_PARSE.
Indicate the culture by using any culture supported by .NET, e.g, PARSE(‘1/2/2012’ AS DATE USING ‘en-US’).
When using expressions that involve operands of different types (1 + ‘1’), how does T-SQL handle this?
T-SQL converts the type that has the lower precedence to the one with higher. INT precedes VARCHAR, therefore SQL converts the VARCHAR value ‘1’ into an INT 1.The result of the expression is 2 and not ‘11’.
When using expressions that involve operands of the same type, what is the type of the result?
If all operands of the expression are of the same type, that’s also going to be the type of the result. 5 / 2 will be 2 (INT) and not 2.5 (NUMERIC) because 5 and 2 are considered INT.You must CAST the 5 and 2 as NUMERIC to get 2.5.
What are surrogate keys?
Surrogate keys are not derived from application data - it is purely a key, e.g. Id.
What are “intelligent” keys/natural keys?
Intelligent keys are derived from application data, e.g. CustomerNumber.
What are the typical options people use to generate surrogate keys?
(1) The identity column property (2) The sequence object (3) Nonsequential GUIDs (4) Sequential GUIDs (5) Custom solutions.
