Passing exam with 070-457 latest training materials

You use Microsoft SQL Server 2012 to develop a database application. You need to implement a computed
column that references a lookup table by using an INNER JOIN against another table.
What should you do?

• A. Add a default constraint to the computed column that implements hard-coded CASE statements.
• B. Reference a user-defined function within the computed column.
• C. Create a BEFORE trigger that maintains the state of the computed column.
• D. Add a default constraint to the computed column that implements hard-coded values.

You support a database structure shown in the exhibit. (Click the Exhibit button.)

You need to write a query that displays the following details:
Total sales made by sales people, year, city, and country
Sub totals only at the city level and country level
A grand total of the sales amount
Which Transact-SQL query should you use?

• A. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate))
• B. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY GROUPING SETS((SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate)), (Country, City), (Country), ())
• C. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY CUBE(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)
• D. SELECT SalesPerson.Name, Country, City, DatePart(yyyy, SaleDate) AS Year, Sum(Amount) AS Total FROM Sale INNER JOIN SalesPerson ON Sale.SalesPersonID = SalesPerson.SalesPersonID GROUP BY ROLLUP(SalesPerson.Name, DatePart(yyyy, SaleDate), City, Country)

You administer a Microsoft SQL Server 2012 instance that contains a financial database hosted on a storage area network (SAN). The financial database has the following characteristics:
A data file of 2 terabytes is located on a dedicated LUN (drive D).
A transaction log of 10 GB is located on a dedicated LUN (drive E).
Drive D has 1 terabyte of free disk space.
Drive E has 5 GB of free disk space.
The database is continually modified by users during business hours from Monday through Friday between
09:00
hours and 17:00 hours. Five percent of the existing data is modified each day. The Finance department loads large CSV files into a number of tables each business day at 11:15 hours and 15:15 hours by using the BCP or BULK INSERT commands. Each data load adds 3 GB of data to the database. These data load operations must occur in the minimum amount of time. A full database backup is performed every Sunday at 10:00 hours. Backup operations will be performed every two hours (11:00, 13:00, 15:00, and 17:00) during business hours. You need to ensure that the minimum amount of data is lost. Which recovery model should the database use?

• A. NO_CHECKSUM
• B. Differential
• C. RESTART
• D. STANDBY
• E. NORECOVERY
• F. Transaction log
• G. CONTINUE_AFTER_ERROR
• H. SKIP
• I. CHECKSUM
• J. FULL
• K. SIMPLE
• L. BULK_LOGGED
• M. DBO_ONLY
• N. COPY_ONLY

You are a database developer at an independent software vendor. You create stored procedures that contain proprietary code. You need to protect the code from being viewed by your customers. Which stored procedure option should you use?

• A. ENCRYPTBYPASSPHRASE
• B. ENCRYPTION
• C. ENCRYPTBYKEY
• D. ENCRYPTBYCERT

You administer a Microsoft SQL Server 2012. A process that normally runs in less than 10 seconds has been running for more than an hour. You examine the application log and discover that the process is using session ID 60. You need to find out whether the process is being blocked. Which Transact-SQL statement should you use?

• A. DBCC INPUTBUFFER (60)
• B. EXEC sp_who 60
• C. SELECT * FROM sys.dm_exec_sessions WHERE sessionid = 60
• D. EXEC sp_helpdb 60