You administer a Microsoft SQL Server 2008 R2 database that has a table named Customer.
The table has the following definition:
CREATE TABLE Customer (
CustomerID int NOT NULL PRIMARY KEY,
FirstName varchar(255) NOT NULL,
LastName varchar(255) NOT NULL,
CustomerAddress varchar(1024))
The database also has a table named CustomerExclusionList. Data will be added to the CustomerExclusionList table regularly.
The CustomerExclusionList table has the following definition:
CREATE TABLE CustomerExclusionList (FirstName varchar(255) NOT NULL, LastName varchar(255) NOT NULL)
You need to create a view that returns all records and columns of the Customer table that are not present in the CustomerExclusionList table.
Which Transact-SQL statement should you use?
A. CREATE VIEW vw_ValidCustomer AS
SELECT FirstName,LastName
FROM Customer c
EXCEPT
SELECT FirstName,LastName
FROM CustomerExclusionList
B. CREATE VIEW vw_ValidCustomer AS
SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM Customer c
INNER JOIN CustomerExclusionList cel ON c.Firstname = cel.FirstName AND c.LastName = cel.LastName
C. CREATE VIEW vw_ValidCustomer AS
SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM Customer c
LEFT OUTER JOIN CustomerExclusionList cel ON c.Firstname = cel.FirstName AND c.LastName = cel.LastName
WHERE cel.FirstName IS NULL
D. CREATE VIEW vw_ValidCustomer AS
SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM Customer c
INNER JOIN CustomerExclusionList cel ON c.Firstname = cel.FirstName
INNER JOIN CustomerExclusionList cel ON c.LastName = cel.LastName
E. CREATE VIEW vw_ValidCustomer AS
SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM Customer c
EXCEPT
SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM Customer c
INNER JOIN CustomerExclusionList cel ON c.Firstname = cel.FirstName AND c.LastName = cel.LastName
F. CREATE VIEW vw_ValidCustomer AS
SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM Customer c
INTERSECT
SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM Customer c
INNER JOIN CustomerExclusionList cel ON c.Firstname = cel.FirstName AND c.LastName = cel.LastName
G. CREATE VIEW vw_ValidCustomer AS
SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM Customer c
LEFT OUTER JOIN CustomerExclusionList cel ON c.Firstname = cel.FirstName AND c.LastName = cel.LastName
WHERE cel.FirstName IS NOT NULL
H. CREATE VIEW vw_ValidCustomer AS
SELECT CustomerID,FirstName,LastName,CustomerAddress
FROM Customer c
EXCEPT
SELECT CustomerID,FirstName,LastName,CustomerAddress
FROM CustomerExclusionList
