You have a database that contains the tables shown in the exhibit. (Click the Exhibit button.)
You need to create a view named uv_CustomerFullName to meet the following
requirements:
• The code must NOT include object delimiters.
• The view must be created in the Sales schema.
• Columns must only be referenced by using one-part names.
• The view must return the first name and the last name of all customers.
• The view must prevent the underlying structure of the customer table from being
changed.
• The view must be able to resolve all referenced objects, regardless of the user’s
default schema.
Which code segment should you use?
To answer, type the correct code in the answer area.
Answer: See the explanation.
Explanation:
CREATE VIEW Sales.uv_CustomerFullName
WITH SCHEMABINDING
AS
SELECT FirstName, LastName
FROM Sales.CustomersReference:
http://msdn.microsoft.com/en-us/library/ms187956.aspx
The views must be created in the sales schema. However, the table given in the question is normal without any schema. Therefore, it should appear as Customers
Create View Sales.uv_CustomerFullName
With Schemabinding
AS
Select FirstName, LastName
From Customers
0
0
I agree. The diagram provided is not in the sales schema.
it’s in the dbo schema. It should be
select firstname, lastname
from dbo.Customer
0
0
Incorrect. Schema “Sales”!
0
0
why does it say this then? The view must be created in the Sales schema.
0
0
The view must be able to resolve all referenced objects, regardless of the user’s
default schema.
>>> Doesn’t it mean that we have to put “From Sales.Customers” so that it doesn’t matter what the user’s default schema is, the server will know that it has to look in Sales schema for Customer table.
0
0
But siddique & sqlninja have a point too. The exhibit shows that the tables do not belong to Sales schema.
0
0
CREATE VIEW Sales.uv_CustomerFullName
WITH SCHEMABINDING
AS
SELECT
FirstName,LastName
FROM
dbo.Customers
;
GO
2
0
The FROM clause of the view definition, has to reference a table that could be accessed by users belonging to ANY schema: the ‘dbo’ schema. Hence, the correct query is:
CREATE VIEW Sales.uv_CustomerFullName
WITH SCHEMABINDING
AS
SELECT FirstName, LastName
FROM dbo.Customers;
This way, user access need be granted only for the ‘uv_CustomerFullName’ view. If however, the view definition had included a “FROM Sales.Customers” clause, users referencing the view from a schema other than the ‘Sales’ schema would have required access to the underlying SALES table; and “to resolve all referenced objects”, wouldn’t be realistic.
0
0
Also:
“When you use SCHEMABINDING, the select_statement must include the two-part names (schema.object) of tables, views, or user-defined functions that are referenced”
https://msdn.microsoft.com/en-us/library/ms173846.aspx
So if FirstName and LastName were in a schema they would have to be referenced with schema.object syntax which would violate the question’s condition:Columns must only be referenced by using one-part names.
0
0
@Wojtek: You don’t give schema names to columns, so the last part of your post is wrong. I think, the question should be showing the Sales schema in the tables. Without that schema name, dbo.Customers should be correct.
0
0
BTW, part of new 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
0
0