You are the database administrator for an order management system. The database has the following two schemas:
•The dbo schema that is used by the main data processing group
•A Reporting schema that is used by the reporting group
The application contains a product table that has the following definition:
CREATE TABLE dbo.Product( ProductID INT, ProductName VARCHAR(100), SalePrice MONEY, ManufacturerName VARCHAR(150));
The application contains an order table that has the following definition:
CREATE TABLE dbo.[Order]( OrderID INT, ProductID INT, CustomerID INT, OrderDate DATETIME2);
The application also contains a customer table that has the following definition:
CREATE TABLE dbo.Customer(CustomerID INT, CustomerName VARCHAR(100), [Address] VARCHAR(200), City VARCHAR(100), [State] VARCHAR (50), ZipCode VARCHAR(5));
You want to load a subset of data from the product and order tables in the dbo schema to a new ProductOrderHistory table in the Reporting schema.
You need to ensure that the table meets the following requirements:
•Includes rows for every product that has been manufactured by "Contoso".
•Includes rows for every product that was ordered from March 1, 2011 to May 31, 2011.
The Reporting.ProductOrderHistory columns have the following types:
CustomerName VARCHAR(100)
ProductName VARCHAR(100)
SalePrice MONEY
ManufacturerName VARCHAR(150)
ProductOrderDate DATETIME
Which Transact-SQL query should you use?
A. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,CAST(o.OrderDate as datetime) as ProductOrderDate
INTO Reporting.ProductOrderHistory
FROM dbo.product p
LEFT JOIN (dbo.[order] o
INNER JOIN dbo.customer c ON o.CustomerID = c.CustomerID) ON p.ProductID = o.ProductID
WHERE p.ManufacturerName = ‘Contoso’
OR (o.OrderDate >= ‘20110301’ AND o.OrderDate < ‘20110601’)
B. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,o.OrderDate as ProductOrderDate
INTO Reporting.ProductOrderHistory
FROM dbo.product p
INNER JOIN dbo.[order] o ON p.ProductID = o.ProductID
INNER JOIN dbo.customer c ON o.CustomerID = c.CustomerID
WHERE p.ManufacturerName = ‘%Contoso%’
OR (o.OrderDate >= ‘20110301’ AND o.OrderDate < ‘20110601’)
C. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,CAST(o.OrderDate as datetime) as ProductOrderDate
INTO Reporting.ProductOrderHistory
FROM dbo.Product p
LEFT JOIN (dbo.[Order] o
INNER JOIN dbo.Customer c ON o.CustomerID = c.CustomerID) ON p.ProductID = o.ProductID
WHERE p.ManufacturerName = ‘Contoso’
OR o.OrderDate BETWEEN ‘20110301’ AND ‘20110531’;
D. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,CAST(o.OrderDate as datetime) as ProductOrderDate
INTO reporting.ProductOrderHistory
FROM dbo.product p
LEFT JOIN dbo.[order] o ON p.ProductID = o.ProductID
LEFT JOIN dbo.customer c ON o.CustomerID = c.CustomerID
WHERE p.ManufacturerName = ‘Contoso’
OR (o.OrderDate between ‘20110301’ AND ‘20110531 23:59:59.999’);
E. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,CAST(o.OrderDate as datetime) as ProductOrderDate
INTO TABLE reporting.ProductOrderHistory
FROM dbo.product p
LEFT JOIN dbo.[order] o ON p.ProductID = o.ProductID
LEFT JOIN dbo.customer c ON o.CustomerID = c.CustomerID
WHERE p.ManufacturerName LIKE ‘%contoso%’
OR o.OrderDate >= ‘20110301’
AND o.OrderDate < ‘20110601’
F. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,o.OrderDate as ProductOrderDate
INTO reporting.ProductOrderHistory
FROM dbo.product p
RIGHT JOIN dbo.[order] o ON p.ProductID = o.ProductID
INNER JOIN dbo.customer c ON o.CustomerID = c.CustomerID
WHERE p.ManufacturerName = ‘Contoso’
OR (o.OrderDate >= ‘20110301’ AND o.OrderDate < ‘20110601’)
G. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,CAST(o.OrderDate as datetime) as ProductOrderDate
INTO reporting.ProductOrderHistory
FROM dbo.product p
CROSS JOIN dbo.[order] o ON p.productID = o.productID
CROSS JOIN dbo.customer c ON o.CustomerID = c.CustomerID
WHERE p.ManufacturerName = ‘Contoso’
OR (o.OrderDate >= ‘20110301’ AND o.OrderDate < ‘20110601’)
H. SELECT c.CustomerName,p.ProductName,p.SalePrice,p.ManufacturerName,CAST(o.OrderDate as datetime) as ProductOrderDate
INTO reporting.ProductOrderHistory
FROM dbo.product p
LEFT JOIN dbo.[order] o ON p.ProductID = o.ProductID
LEFT JOIN dbo.customer c ON o.CustomerID = c.CustomerID
WHERE p.ManufacturerName = ‘Contoso’
OR o.OrderDate >= ‘20110301’
AND o.OrderDate < ‘20110601’
