You administer a Microsoft SQL Server 2008 database.
The database contains tables named Customer, Subscriptions, and Orders that have the following definitions:
CREATE TABLE dbo.Customer (
CustomerID int NOT NULL PRIMARY KEY,
FirstName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL,
CustomerAddress varchar(1024))
CREATE TABLE dbo.Subscriptions (SubscriptionID int NOT NULL PRIMARY KEY
SubscriptionName varchar(255) NOT NULL
CustomerID int FOREIGN KEY NOT NULL REFERENCES Customer(CustomerID))
CREATE TABLE dbo.Orders (
OrderID int NOT NULL PRIMARY KEY
OrderText varchar(255) NOT NULL
CustomerID int FOREIGN KEY NOT NULL REFERENCES Customer(CustomerID))
Customers are considered active if they meet the following requirements:
•Placed an order for a subscription that is recorded in the Subscriptions table.
•Placed an order for an individual product that is recorded in the Orders table.
You need to create a view that shows unique rows where the customer either has made an Order or has a Subscription.
Which Transact-SQL statement should you use?
A. CREATE VIEW dbo.vw_ActiveCustomers
AS
SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM dbo.Customer c
WHERE dbo.CustomerId in (SELECT CustomerId
FROM dbo.Subscriptions s
UNION ALL
SELECT CustomerId
FROM dbo.Orders o)
B. CREATE VIEW dbo.vw_ActiveCustomers
AS
SELECT DISTINCT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM dbo.Customer c
LEFT OUTER JOIN dbo.Subscriptions s ON c.CustomerID = s.CustomerID
LEFT OUTER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
C. CREATE VIEW dbo.vw_ActiveCustomers
AS
SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM dbo.Customer c
WHERE CustomerId in (SELECT CustomerId
FROM dbo.Subscriptions s
INTERSECT
SELECT CustomerId
FROM dbo.Orders o)
D. CREATE VIEW dbo.vw_ActiveCustomers
AS
SELECT DISTINCT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM dbo.Customer c
INNER JOIN dbo.Subscriptions s ON c.CustomerID = s.CustomerID
EXCEPT
SELECT DISTINCT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM dbo.Customer c
INNER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
E. CREATE VIEW dbo.vw_ActiveCustomers
AS
SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM dbo.Customer c
LEFT OUTER JOIN dbo.Subscriptions s ON c.CustomerID = s.CustomerID
LEFT OUTER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
WHERE s.CustomerID IS NOT NULL
OR o.CustomerID IS NOT NULL
F. CREATE VIEW dbo.vw_ActiveCustomers
AS
SELECT DISTINCT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM dbo.Customer c
INNER OUTER JOIN dbo.Subscriptions s ON c.CustomerID = s.CustomerID
INNER OUTER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
G. CREATE VIEW dbo.vw_ActiveCustomers
AS
SELECT DISTINCT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM dbo.Customer c
INNER JOIN dbo.Subscriptions s ON c.CustomerID = s.CustomerID
UNION ALL
SELECT DISTINCT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM dbo.Customer c
INNER JOIN dbo.Orders o ON c.CustomerID = o.CustomerID
H. CREATE VIEW dbo.vw_ActiveCustomers
AS
SELECT DISTINCT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM dbo.Customer c
WHERE EXISTS
(SELECT * FROM dbo.subscriptions s WHERE c.CustomerID = s.CustomerID)
OR EXISTS
(SELECT * FROM dbo.orders o WHERE c.CustomerID = o.CustomerID)
