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:
Reference:
http://msdn.microsoft.com/en-us/library/ms187956.aspx
Seems to me that both expressions are the same, one is <= and the other is < and difference is one day
Difference is one day so they are not the same. Answer with <= is correct answer
these date functions are only dealing with the first second of the day and not the following 24 hours… you need to add one more day and use < only to include the entire 24 hours of the last day of the month…
In order to have 4 statements then instead of alter view, you drop and create the view and then add the grant select.
wouldnt it be create indexed view?
Wait never mind iam stupid
https://msdn.microsoft.com/en-AU/library/ms187956.aspx
Quote: “Forces all data modification statements executed against the view to follow the criteria set within select_statement.”
CHECK OPTION helps this: Allows insertion of details of only students from Canada.
Correct answer here:
http://www.aiotestking.com/microsoft/which-four-transact-sql-statements-should-you-use-5/
Its 1 5 4 6
grant select is missing at the end part
Displays the details of only students from Canada (by securing the view definition to prevent alteration): WITH ENCRYPTION
Allows insertion of details of only students from Canada (by filtering what data can be inserted): WITH CHECK OPTION
WITH SCHEMABINDING will prevent the view from being altered.
Guys,
The question answer options were recorded wrong for this one, but the listed answer is correct.
You do not need to drop the view because it was not create WITH SCHEMABINDING.
Because you do not need to drop the view, you also do not need to grant any rights to User1.
Given this info, you only need ALTER, a SELECT statement, and a WHERE clause.
Follow Enrique’s link for the correct layout of the answer.
2, 5, 4 according to JENSON dump
2, 5, 4, 6
Read the Question exacly, it says that you need four statements… also you need a batch seperator b….
yeah Thomas is right! FOUR statements! greetz to bavaria 😉
ALTER TABLE dbo.BOOK
ADD BookGuid Uniqueidentifier NULL
CONSTRAINT DF_BookGuid DEFAULT
newid()
WITH VALUES
** Index View Must be created with Schema binding
** WITH CHECK OPTION to grantee inserted records will be within view scope
Grant Select on SCHEMA::vwItemList TO USER1 => is wrong because the schema is dbo.
to grant select on SCHEMA use: GRANT SELECT ON SCHEMA::dbo TO USER1
to grant select on object use: GRANT SELECT ON dbo.vwItemList TO USER1
That is the awesome wonderful search engine in the planet
http://google.com
New 70-461 Exam Questions and Answers (5/May/2017 Updated):
NEW QUESTION 169
You have a database that contains a table named Customer. The customer table contains a column named LastName that has a column definition of varchar(50). An application named App1 reads from the table frequently. You need to change the column definition to nvarchar(100). The solution must minimize the amount of time it takes for App1 to read the data. Which statement should you execute?
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1691.png
Answer: C
Explanation:
To change the data type of a column in a SQL Server (or Microsoft access) table, use the following syntax:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
NEW QUESTION 170
You are maintaining a SQL Server database that uses the default settings. The database contains a table that is defined by the following Transact-SQL statement:
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1701.png
Answer: A
NEW QUESTION 171
A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its previous year. Which Transact-SQL query should you use?
A. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits
C. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits
Answer: C
NEW QUESTION 172
Your database contains a table named Products that has columns named ProductID and Name. You want to write a query that retrieves data from the Products table sorted by Name listing 15 rows at a time. You need to view rows 31 through 45. Which Transact-SQL query should you create?
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1721.png
Answer: C
Explanation:
The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.
Example: skip first 10 rows from the sorted resultset and return next 5 rows.
SELECT First Name + ‘ ‘ + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
NEW QUESTION 173
A database named AdventureWorks contains two tables named Production.Product and Sales.SalesOrderDetail. The tables contain data on the available products and a detailed order history. The Production.Product table contains the following two columns:
– ProductID
– Name
The Sales.SalesOrderDetail table contains the following three columns:
– SalesOrderID
– ProductID
– OrderQty
You need to create a query listing all of the products that were never ordered. Which statements should you execute?
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1731.png
Answer: A
Explanation:
EXCEPT and INTERSECT returns distinct rows by comparing the results of two queries. EXCEPT returns distinct rows from the left input query that aren’t output by the right input query. Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
Example: the following query returns any distinct values from the query to the left of the EXCEPT operator that are not also found on the right query.
— Uses AdventureWorks
SELECT CustomerKey
FROM FactInternetSales
EXCEPT
SELECT CustomerKey
FROM DimCustomer
WHERE DimCustomer.Gender = ‘F’
ORDER BY CustomerKey;
–Result: 9351 Rows (Sales to customers that are not female.)
Incorrect Answers:
B: Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
C, D: INTERSECT returns distinct rows that are output by both the left and right input queries operator.
NEW QUESTION 174
You plan to write a query for a new business report that will contain several nested queries. You need to ensure that a nested query can call a table-valued function for each row in the main query. Which query operator should you use in the nested query?
A. CROSS APPLY
B. INNER JOIN
C. OUTER JOIN
D. PIVOT
Answer: A
Explanation:
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input. There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSSAPPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
NEW QUESTION 175
You are designing a table for a SQL Server database. The table uses horizontal partitioning. You have the following requirements:
– Each record in the table requires a unique key.
– You must minimize table fragmentation as the table grows.
You need to choose the appropriate data type for the key value. What should you do?
A. Use the NEWID function to create a unique identifier.
B. Use the NEWSEQUENTIALID function to create a unique identifier.
C. Generate a random value that uses the bigint datatype.
D. Generate a random value that uses the char(16) data type.
Answer: B
NEW QUESTION 176
You are a database developer of a Microsoft SQL Server database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table.
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1761.jpg
You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use?
A. CREATE TABLE Customer
(SourceID int NOT NULL,
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL
CONSTRAINT UQ_Customer UNIQUE
(SourceID, CustomerID));
B. CREATE TABLE Customer
(SourceID int NOT NULL UNIQUE,
CustomerID int NOT NULL UNIQUE,
CustomerName varchar(255) NOT NULL);
C. CREATE TABLE Customer
(SourceID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL UNIQUE,
CustomerName varchar(255) NOT NULL);
D. CREATE TABLE Customer
(SourceID int NOT NULL,
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL,
CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED
(SourceID, CustomerID));
Answer: D
NEW QUESTION 177
Drag and Drop Question
You maintain a SQL Server database that is used by a retailer to track sales and salespeople. The database includes two tables and two triggers that is defined by the following Transact-SQL statements:
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1771.jpg
During days with a large sales volume, some new sales transaction fail and report the following error:
Arithmetic overflow error converting expression to data type int.
You need to ensure that the two triggers are applied once per sale, and that they do not interfere with each other. How should you complete the relevant Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct location or locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1772.jpg
Answer:
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1773.png
Explanation:
Box 1: OFF
Box 2: 0
Only direct recursion of AFTER triggers is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. To disable indirect recursion of AFTER triggers, also set the nested triggers server option to 0.
Note:
Both DML and DDL triggers are nested whena trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. You can control whether AFTER triggers can be nested through the nested triggers server configuration option. If nested triggers are allowed and a trigger in the chain starts an infinite loop, the nesting level is exceeded and the trigger terminates.
NEW QUESTION 178
……
P.S. Get The Newest 70-461 Dumps In PDF And VCE From — http://www.passleader.com/70-461.html (200q VCE and PDF)
Good Luck!
By the way, part of new Microsoft 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!
(5/May/2017 Updated) New 70-461 Exam Questions and Answers:
NEW QUESTION 169
You have a database that contains a table named Customer. The customer table contains a column named LastName that has a column definition of varchar(50). An application named App1 reads from the table frequently. You need to change the column definition to nvarchar(100). The solution must minimize the amount of time it takes for App1 to read the data. Which statement should you execute?
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1691.png
Answer: C
Explanation:
To change the data type of a column in a SQL Server (or Microsoft access) table, use the following syntax:
ALTER TABLE table_name
ALTER COLUMN column_name datatype
NEW QUESTION 170
You are maintaining a SQL Server database that uses the default settings. The database contains a table that is defined by the following Transact-SQL statement:
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1701.png
Answer: A
NEW QUESTION 171
A table named Profits stores the total profit made each year within a territory. The Profits table has columns named Territory, Year, and Profit. You need to create a report that displays the profits made by each territory for each year and its previous year. Which Transact-SQL query should you use?
A. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits
C. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PreviousYearProfit FROM Profits
D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PreviousYearProfit FROM Profits
Answer: C
NEW QUESTION 172
Your database contains a table named Products that has columns named ProductID and Name. You want to write a query that retrieves data from the Products table sorted by Name listing 15 rows at a time. You need to view rows 31 through 45. Which Transact-SQL query should you create?
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1721.png
Answer: C
Explanation:
The OFFSET-FETCH clause provides you with an option to fetch only a window or page of results from the result set. OFFSET-FETCH can be used only with the ORDER BY clause.
Example: skip first 10 rows from the sorted resultset and return next 5 rows.
SELECT First Name + ‘ ‘ + Last Name FROM Employees ORDER BY First Name OFFSET 10 ROWS FETCH NEXT 5 ROWS ONLY;
NEW QUESTION 173
A database named AdventureWorks contains two tables named Production.Product and Sales.SalesOrderDetail. The tables contain data on the available products and a detailed order history. The Production.Product table contains the following two columns:
– ProductID
– Name
The Sales.SalesOrderDetail table contains the following three columns:
– SalesOrderID
– ProductID
– OrderQty
You need to create a query listing all of the products that were never ordered. Which statements should you execute?
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1731.png
Answer: A
Explanation:
EXCEPT and INTERSECT returns distinct rows by comparing the results of two queries. EXCEPT returns distinct rows from the left input query that aren’t output by the right input query. Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
Example: the following query returns any distinct values from the query to the left of the EXCEPT operator that are not also found on the right query.
— Uses AdventureWorks
SELECT CustomerKey
FROM FactInternetSales
EXCEPT
SELECT CustomerKey
FROM DimCustomer
WHERE DimCustomer.Gender = ‘F’
ORDER BY CustomerKey;
–Result: 9351 Rows (Sales to customers that are not female.)
Incorrect Answers:
B: Column names or aliases in ORDER BY clauses must reference column names returned by the left-side query.
C, D: INTERSECT returns distinct rows that are output by both the left and right input queries operator.
NEW QUESTION 174
You plan to write a query for a new business report that will contain several nested queries. You need to ensure that a nested query can call a table-valued function for each row in the main query. Which query operator should you use in the nested query?
A. CROSS APPLY
B. INNER JOIN
C. OUTER JOIN
D. PIVOT
Answer: A
Explanation:
The APPLY operator allows you to invoke a table-valued function for each row returned by an outer table expression of a query. The table-valued function acts as the right input and the outer table expression acts as the left input. The right input is evaluated for each row from the left input and the rows produced are combined for the final output. The list of columns produced by the APPLY operator is the set of columns in the left input followed by the list of columns returned by the right input. There are two forms of APPLY: CROSS APPLY and OUTER APPLY. CROSSAPPLY returns only rows from the outer table that produce a result set from the table-valued function. OUTER APPLY returns both rows that produce a result set, and rows that do not, with NULL values in the columns produced by the table-valued function.
NEW QUESTION 175
You are designing a table for a SQL Server database. The table uses horizontal partitioning. You have the following requirements:
– Each record in the table requires a unique key.
– You must minimize table fragmentation as the table grows.
You need to choose the appropriate data type for the key value. What should you do?
A. Use the NEWID function to create a unique identifier.
B. Use the NEWSEQUENTIALID function to create a unique identifier.
C. Generate a random value that uses the bigint datatype.
D. Generate a random value that uses the char(16) data type.
Answer: B
NEW QUESTION 176
You are a database developer of a Microsoft SQL Server database. You are designing a table that will store Customer data from different sources. The table will include a column that contains the CustomerID from the source system and a column that contains the SourceID. A sample of this data is as shown in the following table.
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1761.jpg
You need to ensure that the table has no duplicate CustomerID within a SourceID. You also need to ensure that the data in the table is in the order of SourceID and then CustomerID. Which Transact- SQL statement should you use?
A. CREATE TABLE Customer
(SourceID int NOT NULL,
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL
CONSTRAINT UQ_Customer UNIQUE
(SourceID, CustomerID));
B. CREATE TABLE Customer
(SourceID int NOT NULL UNIQUE,
CustomerID int NOT NULL UNIQUE,
CustomerName varchar(255) NOT NULL);
C. CREATE TABLE Customer
(SourceID int NOT NULL PRIMARY KEY CLUSTERED,
CustomerID int NOT NULL UNIQUE,
CustomerName varchar(255) NOT NULL);
D. CREATE TABLE Customer
(SourceID int NOT NULL,
CustomerID int NOT NULL,
CustomerName varchar(255) NOT NULL,
CONSTRAINT PK_Customer PRIMARY KEY CLUSTERED
(SourceID, CustomerID));
Answer: D
NEW QUESTION 177
Drag and Drop Question
You maintain a SQL Server database that is used by a retailer to track sales and salespeople. The database includes two tables and two triggers that is defined by the following Transact-SQL statements:
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1771.jpg
During days with a large sales volume, some new sales transaction fail and report the following error:
Arithmetic overflow error converting expression to data type int.
You need to ensure that the two triggers are applied once per sale, and that they do not interfere with each other. How should you complete the relevant Transact-SQL statement? To answer, drag the appropriate Transact-SQL segments to the correct location or locations. Each Transact-SQL segment may be used once, more than once, or not at all. You may need to drag the split bar between panes or scroll to view content.
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1772.jpg
Answer:
Image URL: passleader.org/wp-content/uploads/2017/05/passleader-70-461-dumps-1773.png
Explanation:
Box 1: OFF
Box 2: 0
Only direct recursion of AFTER triggers is prevented when the RECURSIVE_TRIGGERS database option is set to OFF. To disable indirect recursion of AFTER triggers, also set the nested triggers server option to 0.
Note:
Both DML and DDL triggers are nested whena trigger performs an action that initiates another trigger. These actions can initiate other triggers, and so on. DML and DDL triggers can be nested up to 32 levels. You can control whether AFTER triggers can be nested through the nested triggers server configuration option. If nested triggers are allowed and a trigger in the chain starts an infinite loop, the nesting level is exceeded and the trigger terminates.
NEW QUESTION 178
……
P.S. These New 70-461 Exam Questions Were Just Updated From The Real 70-461 Exam, You Can Get The Newest 70-461 Dumps In PDF And VCE From — http://www.passleader.com/70-461.html (200q VCE and PDF)
Good Luck!
What’s more, part of new Microsoft 200Q 70-461 dumps are available here:
https://drive.google.com/open?id=0B-ob6L_QjGLpfnJldlZxTklTaHM0akpJUzhja2pETHJOS0owMzd4eVk1UTVNQUpvdlVxVWM
Best Regards!