A database named AdventureWorks contains two tables named Production.Product and Sales.SalesOrderDetail. The tables contain data on the available products and a detailed order history.
The Production.Product table contains the following two columns:
- ProductID
- Name
The Sales.SalesOrderDetail table contains the following three columns:
- SalesOrderID
- ProductID
- OrderQty
You need to create a query listing all of the products that were never ordered.
Which statements should you execute?
A. Option A
B. Option B
C. Option C
D. Option D
Explanation:
EXCEPT and INTERSECT returns distinct rows by comparing the results of two queries.
EXCEPT returns distinct rows from the left input query that aren-t output by the right input query.
Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
Example: The following query returns any distinct values from the query to the left of the EXCEPT operator that are not also found on the right query.
— Uses AdventureWorks
SELECT CustomerKey
FROM FactInternetSales
EXCEPT
SELECT CustomerKey
FROM DimCustomer
WHERE DimCustomer.Gender = F
ORDER BY CustomerKey;
–Result: 9351 Rows (Sales to customers that are not female.)
Incorrect Answers:
B: Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
C, D: INTERSECT returns distinct rows that are output by both the left and right input queries operator.
References: https://msdn.microsoft.com/en-us/library/ms188055.aspx