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 PreferredCustomerList.Data will be added to the PreferredCustomerList table regularly.
The PreferredCustomerList table has the following definition:
CREATE TABLE PreferredCustomerList (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 also present in the PreferredCustomerList 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 PreferredCustomerList
B. CREATE VIEW vw_ValidCustomer
AS
SELECT c.CustomerID,c.FirstName,c.LastName,c.CustomerAddress
FROM Customer c
INNER JOIN PreferredCustomerList 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 PreferredCustomerList 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 PreferredCustomerList cel ON c.Firstname = cel.FirstName
INNER JOIN PreferredCustomerList 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 PreferredCustomerList 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 PreferredCustomerList 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 PreferredCustomerList cel ON c.Firstname = cel.FirstName AND c.LastName = cel.LastName
WHERE cel.LastName IS NULL
H. CREATE VIEW vw_ValidCustomer
AS
SELECT CustomerID,FirstName,LastName,CustomerAddress
FROM Customer c
EXCEPT
SELECT CustomerID,FirstName,LastName,CustomerAddress
FROM PreferredCustomerList
