DRAG DROP
You develop a database application for a university. You need to create a view that will be indexed
that meets the following requirements:
Displays the details of only students from Canada.
Allows insertion of details of only students from Canada.
Which four Transact-SQL statements should you use? (To answer, move the appropriate SQL
statements from the list of statements to the answer area and arrange them in the correct order.)
Answer: 
Explanation:
http://msdn.microsoft.com/en-us/library/ms187956.aspx
WITH VALUES must be used. If not, the value in the already existing columns would be NULL.
Quote:
The following example adds a nullable column with a DEFAULT definition, and uses WITH VALUES to provide values for each existing row in the table. If WITH VALUES is not used, each row has the value NULL in the new column.
I think that you also have to add statement 6 at the end. The question states that you need four statements and that you have to grant permissions for User1.
What do you guys think?
I think that if you’re altering the view then User1 still has the correct access. I reckon there’s a typo in the question and you only need 3.
Drop an create the view makes you to have to grant the user access. So using 1 you have to use 6 as well and then you already use 4 options as the question asks for. 1 + 5 + 4 + 6
YOU CAN ALSO START WITH DROPING THE VIEW AND RECREATING THE VIEW THEN ALL ..2…3 OPTIONS AND FINALLY ADD THE USER BACK
YOU GET 4-T-SQL STATEMENTS AS BEEN ASKED
THIS ONE REQUIRES MORE EFFORT THAN NECCESSARLY
The first one should be…Add BookGuid varchar(10) NOT NULL
newid() does not fix in a varchar(10) type
5-3-7-2
2-5-4
6-4-1-3
8-6-1-3
5 4 1 3 is correct
I think the grant statement is wrong. Permission should be granted to the view not schema. even its granted at schema level, that does not appear to be the correct syntax. I’d 2-5-4, most likely there is a typo in the question, should be select 3
CREATE NONCLUSTERED
INDEX
Findx_SpecialOrderID
ON
dbo.OrderDEtail(SalesOrderID)
where
SpecialOfferID IS NOT null
CREATE VIEW dbo.CanadianStudents
WITH schemabinding
AS
SELECT a.LastName, a.firstName, s.JobTitle, a.Country, e.LastQualification
FROM Students AS s
INNER JOIN NativeAddress AS a ON a.AddressID=s.AddressID
INNER JOIN EducationHistory AS e ON s.StudentID=e.StudentID
WHERE a.Country=’Canada’
WITH CHECK option
3=4 ???
2,5,4,6