Quick Check

1.	Which join type will return matching rows from both the right and left tables?
2.	How would you retrieve data from a data source other than SQL Server?
3.	What new operator for SQL Server 2005 can be used to create cross-table reports?
4.	What does the APPLY operator enable you to accomplish?
5.	What operator(s), excluding a table join, can be used to combine and limit result sets?
6.	What function can be used to return the first non-null values from more than one expression?
7.	What is the difference between a table-valued function and a scalar function?
8.	Which predicate can be used to search a column for words or phrases near to the search word?

Answers

1.	An INNER join, which is the default join type if one is not specified, is used to return data that matches the join condition from both the right and left tables.
2.	If your data source is an OLE DB data source, then you can create a linked server. The linked server can then be referenced using a four-part name inside of a standard Transact-SQL statement.
3.	The PIVOT operator enables you to generate an output table. It can be used to replace the need to utilize CASE statements and aggregate functions to accomplish the same result.
4.	When used in the FROM clause of a SELECT statement, the APPLY operator can be used to apply a table-valued function to each row in an outer table. An OUTER APPLY will return all rows that include NULL values, and the CROSS APPLY operator will return rows from the outer table that provides a result set.
5.	The EXCEPT and INTERSECT operators can be used to combine and limit result sets. The EXCEPT operator returns distinct values from the left side. The INTERSECT operator returns distinct values from the left and right sides.
6.	The COALESCE function can be used to return the first non-null value from more than one expression. Alternatively, the ISNULL function only accepts two arguments and can be used to replace NULL values with a replacement value.
7.	A table-valued function is a user-defined function that returns a table, whereas a scalar function will return a single value, such as a string or an integer data type.
8.	CONTAINS and CONTAINSTABLE can use a proximity term and the NEAR keyword to return a word that resides close to the other one.