100% Microsoft 070-457 Guaranteed Success With Testing Engine

Microsoft Transition Your MCTS on SQL Server 2008 to MCSA: SQL Server 2012, Part 1 Sample Questions:

1. You use a Microsoft SQL Server 2012 database that contains two tables named SalesOrderHeader and SalesOrderDetail. The indexes on the tables are as shown in the exhibit. (Click the Exhibit button.)

You write the following Transact-SQL query:

You discover that the performance of the query is slow. Analysis of the query plan shows table scans where the estimated rows do not match the actual rows for SalesOrderHeader by using an unexpected index on SalesOrderDetail. You need to improve the performance of the query. What should you do?

A) Add a clustered index on SalesOrderId in SalesOrderHeader.
B) Use a FORCESEEK hint in the query.
C) Update statistics on SalesOrderId on both tables.
D) Use a FORCESCAN hint in the query.

2. DRAG AND DROP You administer several Microsoft SQL Server 2012 servers. Your company has a number of offices across the world connected by using a wide area network (WAN). Connections between offices vary significantly in both bandwidth and reliability. You need to identify the correct replication method for each scenario. What should you do? (To answer, drag the appropriate replication method or methods to the correct location or locations in the answer area. Each replication method may be used once, more than once, or not at all.)
Select and Place:

3. You administer a Microsoft SQL Server 2012 database named ContosoDb. Tables are defined as shown in the exhibit. (Click the Exhibit button.)

You need to display rows from the Orders table for the Customers row having the CustomerIdvalue set to 1 in the following XML format.

Which Transact-SQL query should you use?

A) SELECT Name AS 'Customers/Name', Country AS 'Customers/Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')
B) SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers.CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
C) SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO, ELEMENTS
D) SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML RAW
E) SELECT Name, Country, OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
F) SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers=CustomerId = 1 FOR XML RAW, ELEMENTS
G) SELECT OrderId, OrderDate, Amount, Name, Country FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML AUTO
H) SELECT Name AS '@Name', Country AS '@Country', OrderId, OrderDate, Amount FROM Orders INNER JOIN Customers ON Orders.CustomerId = Customers. CustomerId WHERE Customers.CustomerId = 1 FOR XML PATH ('Customers')

4. 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) stored procedures
C) table partitions
D) table-valued functions

5. 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 ensure that the following requirements are met:
Students must be ranked based on their average marks.
If one or more students have the same average, the same rank must be given to these students.
Consecutive ranks must be skipped when the same rank is assigned.
Which Transact-SQL query should you use?

A) 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
B) 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
C) 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
D) SELECT Id, Name, Marks,
DENSE_RANK() OVER(ORDER BY Marks DESC) AS Rank
FROM StudentMarks
E) SELECT StudentCode as Code,
NTILE(2) OVER(ORDER BY AVG (Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode
F) SELECT StudentCode as Code,
RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode
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,
DENSE_RANK() OVER(ORDER BY AVG (Marks) DESC) AS Value
FROM StudentMarks
GROUP BY StudentCode

Solutions: