You are a database developer on an instance of SQL Server 2008. Your database contains the Product and ProductPriceHistory tables defined as follows:
You have a query that references a subquery on the two tables. You decide to use a Common Table Expression (CTE) instead of using a subquery.
Which Transact-SQL successfully implements a CTE?
A. WITH LowPricedProducts (ProductID, ProductName, Price)
AS
SELECT * FROM Product;
SELECT * FROM LowPricedProducts ORDER BY ProductName;
(SELECT p.ProductID,p.ProductName,MIN(c.Price)
FROM Product p INNER JOIN
ProductPriceHistory c ON c.ProductID = p.ProductID
GROUP BY p.ProductID, p.ProductName
HAVING MIN(c.Price) < 10
)
B. WITH LowPricedProducts (ProductID, ProductName, Price)
AS
(SELECTp.ProductID,p.ProductName,MIN(c.Price)
FROM Product p INNER JOIN
ProductPriceHistory c ON c.ProductID = p.ProductID
GROUP BY p.ProductID, p.ProductName
HAVING MIN(c.Price) < 10
)
SELECT * FROM LowPricedProducts ORDER BY ProductName;
C. SELECT p.ProductID, p.ProductName, MIN(c.Price)
FROM Product p INNER JOIN
ProductPriceHistory c ON c.ProductID = p.ProductID
GROUP BY p.ProductID, p.ProductName
HAVING MIN(c.Price) < 10
OUTPUT * INTO LowPricedProducts )
SELECT *
FROM LowPricedProducts
ORDER BY ProductName;
D. WITH LowPricedProducts (ProductID, ProductName, Price)
AS
SELECT * FROM LowPricedProducts ORDER BY ProductName;
(SELECTp.ProductID,p.ProductName,MIN(c.Price)
FROM Product p INNER JOIN
ProductPriceHistory c ON c.ProductID = p.ProductID
GROUP BY p.ProductID, p.ProductName
ORDER BY p.ProductName
HAVING MIN(c.Price) < 10)
Correct Answer: B
Explanation/Reference:
The following Transact-SQL successfully implements a Common Table Expression (CTE):
WITH LowPricedProducts (ProductID, ProductName, Price) AS(SELECTp.ProductID,p.ProductName,MIN(c.Price)FROM Product pINNER JOIN ProductPriceHistory cON c.ProductID = p.ProductIDGROUP BY p.ProductID, p.ProductNameHAVING MIN(c.Price) < 10)
SELECT * FROM LowPricedProducts ORDER BY ProductName;
When defining a CTE, the WITH clause specifies the expression name that will be used in the subsequent statement. The WITH clause must contain a column list identifying the available columns, unless all columns in the expression’s query have distinct names. The syntax for creating a CTE is as follows:
WITH expression_name [(column_name [,…n])]AS (CTE_query_definition)
After you create the CTE, the statement immediately following the CTE definition can reference the CTE expression by name one or more times, as if it were a table or view. Only the columns defined in the CTE expression are accessible.
You can also create two CTEs in a single WITH clause by separating the expressions with a comma. Within the WITH clause, the CTE can also reference itself or another CTE that you previously created. Note that only one WITH clause is allowed, even if the query defining the CTE contains a subquery. In addition, a CTE query definition cannot contain an ORDER BY, COMPUTE, COMPUTE BY, INTO, FOR XML, or FOR BROWSE clause, or an OPTION clause that specifies query hints.
CTE expressions can be used as an alternative to a view, temporary table, or subquery. Using a CTE expression makes the Transact-SQL code more readable than using a subquery, especially if the query using the CTE needs to reference the same result set multiple times.
The Transact-SQL that includes two SELECT statements after the CTE definition will generate an error because the statement that uses the CTE must be the first statement after the CTE definition. If you executed this code, the following error would occur:
Msg 208, Level 16, State 1, Line 16Invalid object name ‘LowPricedProducts’.
The Transact-SQL that uses an ORDER BY clause in the CTE definition will generate an error because an ORDER BY clause is not allowed for a query that defines a CTE.
The Transact-SQL that omits the WITH keyword and includes an OUTPUT clause in the CTE definition will generate an error. The WITH keyword must be included to define a CTE. In addition, the OUTPUT clause is not allowed for a query that defines a CTE because the OUTPUT clause is not valid for SELECT statements.
However, a DML statement that immediately follows the CTE definition and references the CTE can include an OUTPUT clause. When performing inserts, updates, deletes, and merges, you can use the OUTPUT clause to obtain and display information about the rows affected by the DML operation. The OUTPUT clause can display this information to the user, insert the data into another permanent or temporary table or table variable using an INTO clause, or pass the data to a nested
DML statement for processing. Within the OUTPUT clause, you specify the column values that should be captured by using the column names with the INSERTED and DELETED prefixes.
