Online Access Free 70-457 Exam Questions

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 top half of the students arranged by their average marks must be given a rank of 1 and the remaining students must be given a rank of 2. 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, RANXO OVER (PARTITION BY StudentCode 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,
  DENSE_RANK() 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 DESC) 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 StudentCode ORDER BY Marks ASC) AS Rank
  FROM StudentMarks) tmp
  WHERE Rank = 1
• G.SELECT Id, Name, Marks,
  DENSE_RANK() OVER (ORDER BY Marks DESC) AS Rank
  FROM StudentMarks
• H.SELECT StudentCode as Code,
  NTILE (2) OVER (ORDER BY AVG (Marks) DESC) AS Value
  FROM StudentMarks
  GROUP BY StudentCode

You administer a Microsoft SQL Server 2012 default instance. The instance is hosted by a server that has a local firewall configured. The firewall only allows inbound connections on port 1433. The server only hosts a single instance of SQL Server. You need to ensure that the instance is configured to allow remote connections even if the SQL Server is unresponsive to client connections. What should you do? Choose all that apply.

• A.Execute the following Transact-SQL command: sp_configure 'remote admin connections',
• B.Restart the SQL Server Agent Service.
• C.Execute the Reconfigure command.
• D.Enable inbound connections on TCP port 1434 in the Windows Firewall on the server.
• E.Enable inbound connections on TCP port 135 in the Windows Firewall on the server.
• F.Execute the following Transact-SQL command: sp_configure 'remote access', 1

You administer a Microsoft SQL Server 2012 database. You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'MyPassword1!';
CREATE CERTIFICATE TDE_Certificate WITH SUBJECT = 'TDE Certificate';
BACKUP CERTIFICATE TDE_Certificate TO FILE = 'd:\TDE_Certificate.cer'
WITH PRIVATE KEY (FILE = 'd:\TDE_Certificate.key', ENCRYPTION BY PASSWORD =
'MyPassword1!');
CREATE DATABASE ENCRYPTION KEY
WITH ALGORITHM = AES_256
ENCRYPTION BY SERVER CERTIFICATE TDE_Certificate;
ALTER DATABASE Orders SET ENCRYPTION ON;
You attempt to restore the Orders database and the restore fails. You copy the encryption file to the original location. A hardware failure occurs and so a new server must be installed and configured. After installing SQL Server to the new server, you restore the Orders database and copy the encryption files to their original location. However, you are unable to access the database. You need to be able to restore the database. Which Transact-SQL statement should you use before attempting the restore?

• A.DECLARE @startdate date SET @startdate = GETDATE() CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer' WITH START_DATE = @startdate;
• B.CREATE CERTIFICATE TDE_Certificate FROM FILE = 'd:\TDE_Certificate.cer' WITH PRIVATE KEY (FILE = 'd:\TDE_Certificate.key', DECRYPTION BY PASSWORD 'MyPassword1!');
• C.CREATE CERTIFICATE TDE_Certificate FROM EXECUTABLE FILE = 'd:\TDE_Certificate.cer'
• D.CREATE ASSEMBLY TDE_Assembly FROM 'd:\TDE_Certificate.cer' WITH PERMISSION_SET = SAFE;
  GO
  CREATE CERTIFICATE TDE_Certificate
  FROM ASSEMBLY TDE_Assembly;

You are developing a database application by using Microsoft SQL Server 2012. You have a query that runs slower than expected. You need to capture execution plans that will include detailed information on missing indexes recommended by the query optimizer. What should you do?

• A.Add a LOOP hint to the query.
• B.Add a FORCESEEK hint to the query.
• C.Include a SET TRANSACTION ISOLATION LEVEL SNAPSHOT statement before you run the query.
• D.Include a SET STATISTICS PROFILE ON statement before you run the query.
• E.Include a SET FORCEPLAN ON statement before you run the query.
• F.Add an INCLUDE clause to the index.
• G.Include a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE statement before you run the query.
• H.Enable the optimize for ad hoc workloads option.
• I.Include a SET STATISTICS SHOWPLAN_XML ON statement before you run the query.
• J.Add a HASH hint to the query.
• K.Include a SET TRANSACTION ISOLATION LEVEL REPEATABLE READ statement before you run the query.
• L.Add a FORCESCAN hint to the Attach query.
• M.Cover the unique clustered index with a columnstore index.
• N.Add a columnstore index to cover the query.

You administer a Microsoft SQL Server 2012 database. You use an OrderDetail table that has the following definition: You need to create a non-clustered index on the SalesOrderID column in the OrderDetail table to include only rows that contain a value in the SpecialOfferID column. Which four Transact-SQL statements should you use? (To answer, move the appropriate statements from the list of statements to the answer area and arrange them in the correct order.)

Build List and Reorder:

Correct Answer:

Explanation:
--Burgos - NO