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

1. 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.)
Build List and Reorder:

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

3. You administer a Microsoft SQL Server 2012 Enterprise Edition server that uses 64 cores. You discover performance issues when large amounts of data are written to tables under heavy system load. You need to limit the number of cores that handle I/O. What should you configure?

A) I/O affinity
B) Lightweight pooling
C) Processor affinity
D) Max worker threads

4. You administer a Microsoft SQL Server 2012 failover cluster that contains two nodes named Node A and Node B.
A single instance of SQL Server is installed on the cluster. An additional node named Node C has been added to the existing cluster. You need to ensure that the SQL Server instance can use all nodes of the cluster. What should you do?

A) Use Node A to install SQL Server on Node C.
B) Use Node B to install SQL Server on Node
C) Run the New SQL Server stand-alone installation Wizard on Node C.
D) Run the Add Node to SQL Server Failover Cluster Wizard on Node C.

5. You administer all the deployments of Microsoft SQL Server 2012 in your company. A database contains a large product catalog that is updated periodically. You need to be able to send the entire product catalog to all branch offices on a monthly basis. Which configuration should you use?

A) Two servers configured in the same data center A primary server configured to perform log-shipping every 10 minutes A backup server configured as a warm standby
B) Two servers configured in different data centers SQL Server Availability Group configured in Synchronous-Commit Availability Mode
One server configured as an Active Secondary
C) Two servers configured in a Windows Failover Cluster in the same data center SQL Server configured as a clustered instance
D) SQL Server that includes an application database configured to perform snapshot replication
E) Two servers configured on the same subnet SQL Server Availability Group configured in Synchronous-Commit Availability Mode
F) Two servers configured in different data centers SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
G) Two servers configured in the same data center SQL Server Availability Group configured in Asynchronous-Commit Availability Mode One server configured as an Active Secondary
H) SQL Server that includes an application database configured to perform transactional replication

Solutions: