Exam Code: 70 462 exam dumps (Practice Exam Latest Test Questions VCE PDF)
Exam Name: Administering Microsoft SQL Server 2012 Databases
Certification Provider: Microsoft
Free Today! Guaranteed Training- Pass exam 70 462 Exam.
Q1. 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
Q2. 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. . SQL Server that includes an application database configured to perform transactional replication
C. . 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 D
. 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 in different data centers
. SQL Server Availability Group configured in Synchronous-Commit Availability Mode
. One server configured as an Active Secondary
F. . Two servers configured on the same subnet
. SQL Server Availability Group configured in Synchronous-Commit Availability Mode
G. . Two servers configured in different data centers
. SQL Server Availability Group configured in Asynchronous-Commit Availability Mode
Answer: E
Q3. You administer a Microsoft SQL Server 2012 database.
You configure Transparent Data Encryption (TDE) on the Orders database by using the following statements:
A. Option A
B. Option B
C. Option C
D. Option D
Answer: C
Q4. Your database contains a table named Purchases. The table includes a DATETIME column named PurchaseTime that stores the date and time each purchase is made. There is a non-clustered index on the PurchaseTime column. The business team wants a report that displays the total number of purchases made on the current day. You need to write a query that will return the correct results in the most efficient manner.
Which Transact-SQL query should you use?
A. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = CONVERT(DATE, GETDATE())
B. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime = GETDATE()
C. SELECT COUNT(*)
FROM Purchases
WHERE CONVERT(VARCHAR, PurchaseTime, 112) = CONVERT(VARCHAR,
GETDATE(), 112)
D. SELECT COUNT(*)
FROM Purchases
WHERE PurchaseTime >= CONVERT(DATE, GETDATE())
AND PurchaseTime < DATEADD(DAY, 1, CONVERT(DATE, GETDATE()))
Answer: D
Q5. You administer a single server that contains a Microsoft SQL Server 2012 default instance on which several production databases have been deployed.
You plan to install a new ticketing application that requires the deployment of a database on the server. The SQL login for this application requires sysadmin permissions.
You need to ensure that the login for the ticketing application cannot access other production databases.
What should you do?
A. Use the SQL Server default instance and enable Contained Databases.
B. Use the SQL Server default instance and configure a user-defined server role. Add the login for the ticketing application to this role.
C. Install a new named SQL Server instance on the server.
D. Install a new default SQL Server instance on the server.
Answer: C
Q6. You administer a Microsoft SQL Server 2012 instance.
You need to stop a blocking process that has an SPID of 64 without stopping other processes
What should you do?
A. Execute the following Transact-SQL statement: EXECUTE sp_KillSPID 64
B. Restart the SQL Server service.
C. Execute the following Transact-SQL statement: KILL 64
D. Execute the following Transact-SQL statement: ALTER SESSION KILL '64'
Answer: C
Q7. Your database contains tables named Products and ProductsPriceLog. The Products table contains columns named ProductCode and Price. The ProductsPriceLog table contains columns named ProductCode, OldPrice, and NewPrice. The ProductsPriceLog table stores the previous price in the OldPrice column and the new price in the NewPrice column.
You need to increase the values in the Price column of all products in the Products table by 5 percent.
You also need to log the changes to the ProductsPriceLog table.
Which Transact-SQL query should you use?
A. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
B. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, inserted.Price, deleted.Price
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
C. UPDATE Products SET Price = Price * 1.05
OUTPUT inserted.ProductCode, deleted.Price, inserted.Price *
INTO ProductsPriceLog(ProductCode, OldPrice, NewPrice)
D. UPDATE Products SET Price = Price * 1.05
INSERT INTO ProductsPriceLog (ProductCode, CldPnce, NewPrice;
SELECT ProductCode, Price, Price * 1.05 FROM Products
Answer: A
Q8. You administer a SQL Server 2012 server that contains a database named SalesDb. SalesDb contains a schema named Customers that has a table named Regions. A user named UserA is a member of a role named Sales. UserA is granted the Select permission on the Regions table. The Sales role is granted the Select permission on the Customers schema.
You need to ensure that the following requirements are met:
The Sales role does not have the Select permission on the Customers schema. UserA has the Select permission on the Regions table.
Which Transact-SQL statement should you use?
A. DENY SELECT ON Object::Regions FROM Sales
B. DENY SELECT ON Schema::Customers FROM Sales
C. REVOKE SELECT ON Object::Regions FROM Sales
D. REVOKE SELECT ON Schema::Customers FROM Sales
E. DENY SELECT ON Object::Regions FROM UserA
F. DENY SELECT ON Schema::Customers FROM UserA
G. REVOKE SELECT ON Object::Regions FROM UserA
H. REVOKE SELECT ON Schema::Customers FOR UserA
I. EXEC sp_addrolemember 'Sales', 'UserA'
J. EXEC sp_droprolemember 'Sales', 'UserA'
Answer: D