70-461 Exam
Today Big Q: schedule 70-461 exam?
Which has a 70-461 certificate can be your studies that you are being certified. Countless The item individuals watch a Microsoft as among the many respected plus highly regarded accreditations obtainable in the details technological innovation earth. So if youre unsure, Microsoft 70-461 accreditation will be worth using a following check out. And also if you have already thought we would bring this place about, youre on on your path to the very priceless plus enjoyable abilities.
2021 Oct sql server 2012 exam 70-461:
Q71. You are a database developer of a Microsoft SQL Server 2012 database.
The database contains a table named Customers that has the following definition:
You need to ensure that the CustomerId column in the Orders table contains only values that exist in the CustomerId column of the Customer table.
Which Transact-SQL statement should you use?
A. ALTER TABLE Orders
ADD CONSTRAINT FX_Orders_CustomerID FOREIGN KEY (CustomerId) REFERENCES
Customer (CustomerId)
B. ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_CustomerID FOREIGN KEY {CustomerID)
REFERENCES
Orders (CustomerId)
C. ALTER TABLE Orders
ADD CONSTRAINT CK_Crders_CustomerID
CHECK (CustomerId IN (SELECT CustomerId FROM Customer))
D. ALTER TABLE Customer
ADD OrderId INT NOT NULL;
ALTER TABLE Customer
ADD CONSTRAINT FK_Customer_OrderID FOREIGN KEY (CrderlD) REFERENCES
Orders
(CrderlD);
E. ALTER TABLE Orders
ADD CONSTRAINT PK Orders CustomerId PRIMARY KEY (CustomerID)
Answer: A
Reference: http://msdn.microsoft.com/en-us/library/ms189049.aspx
Q72. You create a table that has the StudentCode, SubjectCode, and Marks columns to record mid-year marks for students. The table has marks obtained by 50 students for various subjects.
You need to retrieve the students who scored the highest marks for each subject along with the marks.
Which Transact-SQL query should you use?
A. SELECT StudentCode as Code, RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
B. SELECT Id, Name, Marks, DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank FROM StudentMarks
C. SELECT StudentCode as Code, DENSE_RANK() OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
D. SELECT StudentCode as Code, NTILE(2) OVER(ORDER BY AVG(Marks) DESC) AS Value FROM StudentMarks GROUP BY StudentCode
E. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1
F. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY SubjectCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1
G. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANK() OVER(PARTITION BY StudentCode ORDER BY Marks ASC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1
H. SELECT StudentCode AS Code, Marks AS Value FROM ( SELECT StudentCode, Marks AS Marks, RANXO OVER(PARTITION BY StudentCode ORDER BY Marks DESC) AS Rank FROM StudentMarks) tmp WHERE Rank = 1
Answer: F
Q73. 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 PrevProfit FROM Profits
B. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits
C. SELECT Territory, Year, Profit, LAG(Profit, 1, 0) OVER (PARTITION BY Territory ORDER BY Year) AS PrevProfit FROM Profits
D. SELECT Territory, Year, Profit, LEAD(Profit, 1, 0) OVER (PARTITION BY Year ORDER BY Territory) AS PrevProfit FROM Profits
Answer: C
Q74. You develop a Microsoft SQL Server 2012 database. The database is used by two web applications that access a table named Products.
You want to create an object that will prevent the applications from accessing the table directly while still providing access to the required data.
You need to ensure that the following requirements are met:
. Future modifications to the table definition will not affect the applications' ability to
access data.
. The new object can accommodate data retrieval and data modification.
. You need to achieve this goal by using the minimum amount of changes to the
existing applications.
What should you create for each application?
A. views
B. table partitions
C. table-valued functions
D. stored procedures
Answer: A
Q75. You use Microsoft SQL Server 2012 database to develop a shopping cart application.
You need to invoke a table-valued function for each row returned by a query.
Which Transact-SQL operator should you use?
A. CROSS JOIN
B. UNPIVOT
C. PIVOT
D. CROSS APPLY
Answer: D
Renovate sql server 70-461 dumps:
Q76. You create a view based on the following statement:
You grant the Select permission to User1.
You need to change the view so that it displays only the records that were processed in the month prior to the current month. You need to ensure that after the changes, the view functions correctly for User1.
Which Transact-SQL statement should you use?
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q77. You administer a Microsoft SQL Server database that supports a shopping application.
You need to retrieve a list of customers who live in territories that do not have a sales person.
Which Transact- SQL query or queries should you use? (Each correct answer presents a complete solution. Choose all that apply.)
A. SELECT CustomerID FROM Customer
WHERE TerritoryID <> SOME(SELECT TerritoryID FROM Salesperson)
B. SELECT CustomerID FROM Customer
WHERE TerritoryID <> ALL(SELECT TerritoryID FROM Salesperson)
C. SELECT CustomerID FROM Customer
WHERE TerritoryID <> ANY(SELECT TerritoryID FROM Salesperson)
D. SELECT CustomerID FROM Customer
WHERE TerritoryID NOT IN(SELECT TerritoryID FROM Salesperson)
Answer: B,D
Q78. CORRECT TEXT
You have a database that contains the tables as shown in the exhibit. (Click the Exhibit button.)
You need to create a query that returns a list of products from Sales.ProductCatalog. The solution must meet the following requirements:
UnitPrice must be returned in descending order.
The query must use two-part names to reference the table.
The query must use the RANK function to calculate the results.
The query must return the ranking of rows in a column named PriceRank.
The list must display the columns in the order that they are defined in the table.
PriceRank must appear last.
Which code segment should you use?
To answer, type the correct code in the answer area.
Answer:
Q79. You are developing a database application by using Microsoft SQL Server 2012.
An application that uses a database begins to run slowly.
You discover that during reads, the transaction experiences blocking from concurrent updates.
You need to ensure that throughout the transaction the data maintains the original version.
What should you do?
A. Add a HASH hint to the query.
B. Add a LOOP hint to the query.
C. Add a FORCESEEK hint to the query.
D. Add an INCLUDE clause to the index.
E. Add a FORCESCAN hint to the Attach query.
F. Add a columnstore index to cover the query.
G. Enable the optimize for ad hoc workloads option.
H. Cover the unique clustered index with a columnstore index.
I. Include a SET FORCEPLAN ON statement before you run the query.
J. Include a SET STATISTICS PROFILE ON statement before you run the query.
K. Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
L. Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
M. Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
N. Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
Answer: M
Q80. You have a Microsoft SQL Server 2012 database that contains tables named Customers and Orders.
The tables are related by a column named CustomerID.
You need to create a query that meets the following requirements:
. Returns the CustomerName for all customers and the OrderDate for any orders that they have placed. . Results must include customers who have not placed any orders.
Which Transact-SQL query should you use?
A. SELECT CustomerName, OrderDate FROM Customers RIGHT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
B. SELECT CustomerName, CrderDate FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID
C. SELECT CustomerName, OrderDate FROM Customers CROSS JOIN Orders ON Customers.CustomerID = Orders.CustomerID
D. SELECT CustomerName, OrderDate FROM Customers LEFT OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID
Answer: D